Repository files navigation

RPostgres is an DBI-compliant interface to the postgres database. It's a ground-up rewrite using C++ andcpp11. Compared to RPostgreSQL, it:

• Has full support for parameterised queries viadbSendQuery(), anddbBind().

• Automatically cleans up open connections and result sets, ensuring that youdon't need to worry about leaking connections or memory.

• Is a little faster, saving ~5 ms per query. (For reference, it takes around 5msto retrieve a 1000 x 25 result set from a local database, so this isdecent speed up for smaller queries.)

• A simplified build process that relies on system libpq.

# Install the latest RPostgres release from CRAN:install.packages("RPostgres")# Or the the development version from GitHub:# install.packages("remotes")remotes::install_github("r-dbi/RPostgres")

Discussions associated with DBI and related database packages take place onR-SIG-DB.The websiteDatabases using R describes the tools and best practices in this ecosystem.

library(DBI)# Connect to the default postgres databasecon<- dbConnect(RPostgres::Postgres())dbListTables(con)dbWriteTable(con,"mtcars",mtcars)dbListTables(con)dbListFields(con,"mtcars")dbReadTable(con,"mtcars")# You can fetch all results:res<- dbSendQuery(con,"SELECT * FROM mtcars WHERE cyl = 4")dbFetch(res)dbClearResult(res)# Or a chunk at a timeres<- dbSendQuery(con,"SELECT * FROM mtcars WHERE cyl = 4")while(!dbHasCompleted(res)){chunk<- dbFetch(res,n=5)  print(nrow(chunk))}# Clear the resultdbClearResult(res)# Disconnect from the databasedbDisconnect(con)

library(DBI)# Connect to a specific postgres database i.e. Herokucon<- dbConnect(RPostgres::Postgres(),dbname='DATABASE_NAME',host='HOST',# i.e. 'ec2-54-83-201-96.compute-1.amazonaws.com'port=5432,# or any other port specified by your DBAuser='USERNAME',password='PASSWORD')

The original DBI design imagined that each package could instantiate X drivers, with each driver having Y connections and each connection having Z results. This turns out to be too general: a driver has no real state, for PostgreSQL each connection can only have one result set. In the RPostgres package there's only one class on the C side: a connection, which optionally contains a result set. On the R side, the driver class is just a dummy class with no contents (used only for dispatch), and both the connection and result objects point to the same external pointer.

Please note that the 'RPostgres' project is released with aContributor Code of Conduct.By contributing to this project, you agree to abide by its terms.