All Projects → r-dbi → Rpostgres

r-dbi / Rpostgres

Licence: other
A DBI-compliant interface to PostgreSQL

Programming Languages

r
7636 projects

Projects that are alternatives of or similar to Rpostgres

Postgres Operator
Production PostgreSQL for Kubernetes, from high availability Postgres clusters to full-scale database-as-a-service.
Stars: ✭ 2,166 (+784.08%)
Mutual labels:  database, postgresql, postgres
Postgres
🐘 Run PostgreSQL in Kubernetes
Stars: ✭ 205 (-16.33%)
Mutual labels:  database, postgresql, postgres
Laravel Scout Postgres
PostgreSQL Full Text Search Engine for Laravel Scout
Stars: ✭ 140 (-42.86%)
Mutual labels:  database, postgresql, postgres
Pg flame
A flamegraph generator for Postgres EXPLAIN ANALYZE output.
Stars: ✭ 1,391 (+467.76%)
Mutual labels:  database, postgresql, postgres
Massive Js
A data mapper for Node.js and PostgreSQL.
Stars: ✭ 2,521 (+928.98%)
Mutual labels:  database, postgresql, postgres
Libpq.jl
A Julia wrapper for libpq
Stars: ✭ 109 (-55.51%)
Mutual labels:  database, postgresql, postgres
Doctrine Postgis
Spatial and Geographic Data with PostGIS and Doctrine.
Stars: ✭ 161 (-34.29%)
Mutual labels:  database, postgresql, postgres
Electrocrud
Database CRUD Application Built on Electron | MySQL, Postgres, SQLite
Stars: ✭ 1,267 (+417.14%)
Mutual labels:  database, postgresql, postgres
Postgres
Postgres.js - The Fastest full featured PostgreSQL client for Node.js
Stars: ✭ 2,193 (+795.1%)
Mutual labels:  database, postgresql, postgres
Pg hashids
Short unique id generator for PostgreSQL, using hashids
Stars: ✭ 164 (-33.06%)
Mutual labels:  database, postgresql, postgres
Activerecord Clean Db Structure
Automatic cleanup for the Rails db/structure.sql file (ActiveRecord/PostgreSQL)
Stars: ✭ 101 (-58.78%)
Mutual labels:  database, postgresql, postgres
Prest
PostgreSQL ➕ REST, low-code, simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new
Stars: ✭ 3,023 (+1133.88%)
Mutual labels:  database, postgresql, postgres
Pgcli
Postgres CLI with autocompletion and syntax highlighting
Stars: ✭ 9,985 (+3975.51%)
Mutual labels:  database, postgresql, postgres
Ship Hold
data access framework for Postgresql on nodejs
Stars: ✭ 110 (-55.1%)
Mutual labels:  database, postgresql, postgres
Prisma
Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite & MongoDB (Preview)
Stars: ✭ 18,168 (+7315.51%)
Mutual labels:  database, postgresql, postgres
Postgres Meta
A RESTful API for managing your Postgres. Fetch tables, add roles, and run queries
Stars: ✭ 146 (-40.41%)
Mutual labels:  database, postgresql, postgres
Squid
🦑 Provides SQL tagged template strings and schema definition functions.
Stars: ✭ 57 (-76.73%)
Mutual labels:  database, postgresql, postgres
Plv8
V8 Engine Javascript Procedural Language add-on for PostgreSQL
Stars: ✭ 1,195 (+387.76%)
Mutual labels:  database, postgresql, postgres
Postgres Migrations
🐦 A Stack Overflow-inspired PostgreSQL migration library with strict ordering and immutable migrations
Stars: ✭ 161 (-34.29%)
Mutual labels:  database, postgresql, postgres
Condenser
Condenser is a database subsetting tool
Stars: ✭ 189 (-22.86%)
Mutual labels:  database, postgresql, postgres

RPostgres

rcc Codecov test coverage CRAN status

RPostgres is an DBI-compliant interface to the postgres database. It's a ground-up rewrite using C++ and Rcpp. Compared to RPostgreSQL, 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")

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

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.


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

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].