# RPostgres

RPostgres is an DBI-compliant interface to the postgres database. It’s a
ground-up rewrite using C++ and [cpp11](https://github.com/r-lib/cpp11).
Compared to RPostgreSQL, it:

- Has full support for parameterised queries via
  [`dbSendQuery()`](https://dbi.r-dbi.org/reference/dbSendQuery.html),
  and [`dbBind()`](https://dbi.r-dbi.org/reference/dbBind.html).

- 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 retrieve 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

``` r
# 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
on [R-SIG-DB](https://stat.ethz.ch/mailman/listinfo/r-sig-db). The
website [Databases using R](https://db.rstudio.com/) describes the tools
and best practices in this ecosystem.

## Basic usage

``` r
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

``` r
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.

------------------------------------------------------------------------

Please note that the ‘RPostgres’ project is released with a [Contributor
Code of Conduct](https://rpostgres.r-dbi.org/CODE_OF_CONDUCT.html). By
contributing to this project, you agree to abide by its terms.

# Package index

## Connecting and disconnecting

Connecting to and disconnecting from a database.

- [`Postgres()`](https://rpostgres.r-dbi.org/reference/Postgres.md)
  [`dbConnect(`*`<PqDriver>`*`)`](https://rpostgres.r-dbi.org/reference/Postgres.md)
  [`dbDisconnect(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/Postgres.md)
  : Postgres driver
- [`Redshift()`](https://rpostgres.r-dbi.org/reference/Redshift.md)
  [`dbConnect(`*`<RedshiftDriver>`*`)`](https://rpostgres.r-dbi.org/reference/Redshift.md)
  : Redshift driver/connection

## Tables

Reading and writing entire tables.

- [`dbAppendTable(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbExistsTable(`*`<PqConnection>`*`,`*`<Id>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbExistsTable(`*`<PqConnection>`*`,`*`<character>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbListFields(`*`<PqConnection>`*`,`*`<Id>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbListFields(`*`<PqConnection>`*`,`*`<character>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbListObjects(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbListTables(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbReadTable(`*`<PqConnection>`*`,`*`<character>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbRemoveTable(`*`<PqConnection>`*`,`*`<character>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`dbWriteTable(`*`<PqConnection>`*`,`*`<character>`*`,`*`<data.frame>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  [`sqlData(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-tables.md)
  : Convenience functions for reading/writing DBMS tables
- [`dbQuoteIdentifier(`*`<PqConnection>`*`,`*`<Id>`*`)`](https://rpostgres.r-dbi.org/reference/quote.md)
  [`dbQuoteIdentifier(`*`<PqConnection>`*`,`*`<SQL>`*`)`](https://rpostgres.r-dbi.org/reference/quote.md)
  [`dbQuoteIdentifier(`*`<PqConnection>`*`,`*`<character>`*`)`](https://rpostgres.r-dbi.org/reference/quote.md)
  [`dbQuoteLiteral(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/quote.md)
  [`dbQuoteString(`*`<PqConnection>`*`,`*`<SQL>`*`)`](https://rpostgres.r-dbi.org/reference/quote.md)
  [`dbQuoteString(`*`<PqConnection>`*`,`*`<character>`*`)`](https://rpostgres.r-dbi.org/reference/quote.md)
  [`dbUnquoteIdentifier(`*`<PqConnection>`*`,`*`<SQL>`*`)`](https://rpostgres.r-dbi.org/reference/quote.md)
  : Quote postgres strings, identifiers, and literals

## Queries and statements

Sending queries and executing statements.

- [`dbBind(`*`<PqResult>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-query.md)
  [`dbClearResult(`*`<PqResult>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-query.md)
  [`dbFetch(`*`<PqResult>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-query.md)
  [`dbHasCompleted(`*`<PqResult>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-query.md)
  [`dbSendQuery(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-query.md)
  : Execute a SQL statement on a database connection

## Transactions

Ensuring multiple statements are executed together, or not at all.

- [`dbBegin(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-transactions.md)
  [`dbCommit(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-transactions.md)
  [`dbRollback(`*`<PqConnection>`*`)`](https://rpostgres.r-dbi.org/reference/postgres-transactions.md)
  : Transaction management.
- [`postgresIsTransacting()`](https://rpostgres.r-dbi.org/reference/postgresIsTransacting.md)
  : Return whether a transaction is ongoing

## Misellaneous

Functions specific to Postgres

- [`RPostgres`](https://rpostgres.r-dbi.org/reference/RPostgres-package.md)
  [`RPostgres-package`](https://rpostgres.r-dbi.org/reference/RPostgres-package.md)
  : RPostgres: C++ Interface to PostgreSQL
- [`postgresHasDefault()`](https://rpostgres.r-dbi.org/reference/postgresHasDefault.md)
  [`postgresDefault()`](https://rpostgres.r-dbi.org/reference/postgresHasDefault.md)
  : Check if default database is available.
- [`postgresWaitForNotify()`](https://rpostgres.r-dbi.org/reference/postgresWaitForNotify.md)
  : Wait for and return any notifications that return within timeout
- [`postgresImportLargeObject()`](https://rpostgres.r-dbi.org/reference/postgresImportLargeObject.md)
  : Imports a large object from file
- [`postgresExportLargeObject()`](https://rpostgres.r-dbi.org/reference/postgresExportLargeObject.md)
  : Exports a large object to file

# Articles

### All vignettes

- [Implementing a Work Queue using
  RPostgres](https://rpostgres.r-dbi.org/articles/work-queue.md):