RPostgres v1.1.1

0

Monthly downloads

0th

Percentile

'Rcpp' Interface to 'PostgreSQL'

Fully 'DBI'-compliant 'Rcpp'-backed interface to 'PostgreSQL' <https://www.postgresql.org/>, an open-source relational database.

Readme

RPostgres

Travis-CI Build Status AppVeyor Build Status codecov

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

  • Has full support for parameterised queries via dbSendQuery(), and dbBind().

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

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

  • A simplified build process that relies on system libpq.

Installation

# 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")

Basic usage

library(DBI)
# Connect to the default postgres database
con <- 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 time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)){
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
# Clear the result
dbClearResult(res)

# Disconnect from the database
dbDisconnect(con)

Connecting to a specific Postgres instance

library(DBI)
# Connect to a specific postgres database i.e. Heroku
con <- 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 DBA
                 user = 'USERNAME',
                 password = 'PASSWORD')

Design notes

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.

Functions in RPostgres

Name Description
postgres-transactions Transaction management.
postgres-query Execute a SQL statement on a database connection
RPostgres-package RPostgres: 'Rcpp' Interface to 'PostgreSQL'
postgres-tables Convenience functions for reading/writing DBMS tables
dbDataType,PqConnection-method Determine database type for R vector.
Postgres Postgres driver
dbConnect,PqDriver-method Connect to a PostgreSQL database.
dbIsValid,PqDriver-method PostgreSQL results.
postgresHasDefault Check if default database is available.
quote Quote postgres strings, identifiers, and literals
PqDriver-class PqDriver and methods.
reexports Objects exported from other packages
PqConnection-class PqConnection and methods.
No Results!

Last month downloads

Details

Date 2018-05-05
License GPL-2
URL https://github.com/r-dbi/RPostgres
BugReports https://github.com/r-dbi/RPostgres/issues
LinkingTo BH, plogr (>= 0.2.0), Rcpp
Encoding UTF-8
LazyLoad true
RoxygenNote 6.0.1.9000
SystemRequirements libpq >= 9.0: libpq-dev (deb) or postgresql-devel (rpm)
Collate 'PqDriver.R' 'PqConnection.R' 'PqResult.R' 'RPostgres-pkg.R' 'RcppExports.R' 'default.R' 'export.R' 'names.R' 'quote.R' 'tables.R' 'transactions.R' 'utils.R'
NeedsCompilation yes
Packaged 2018-05-06 17:03:57 UTC; kirill
Repository CRAN
Date/Publication 2018-05-06 20:51:11 UTC

Include our badge in your README

[![Rdoc](http://www.rdocumentation.org/badges/version/RPostgres)](http://www.rdocumentation.org/packages/RPostgres)