In eokodie/dbmanager: A connection manager for databases and compute engines.

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "man/figures/README-",
  out.width = "100%"
)

dbmanager

[](https://www.tidyverse.org/lifecycle/#experimental)

A consistent interface for managing connections to database and compute engines.

This package is currently under active development. The API is likely to change and some features are incomplete.

Features

The goal of dbmanager is to provide connection management to the following databases and compute engines.

yes <- ":heavy_check_mark:"
no <- ":heavy_multiplication_x:"

features <- tibble::tribble(
  ~Engine, ~Implemented,
  "MySQL",        yes,
  "PostgreSQL",   yes,
  "SQL Server",   yes,
  "MonetDB",      yes,
  "SQLite",       yes,
  "CouchDB",      no,
  "MongoDB",      no,
  "Spark",        no
)
knitr::kable(features, align = "lc")

Installation

dbmanager is not yet on CRAN. You can install from GitHub with:

# install.packages("remotes")
remotes::install_github("eokodie/dbmanager", ref = "main")

Usage

dbmanager is implemented with R6 classes to give a consistent interface to common databases and compute engines. We will illustrate this with some code snippets.

Let's begin with some credentials.

db_name  = Sys.getenv("db_name")
host     = Sys.getenv("host") 
user     = Sys.getenv("user") 
password = Sys.getenv("password")

We can connect to a MySQL database with.

library(dbmanager)

db <- MySQL$new(
  db_name  = db_name,
  host     = host, 
  user     = user, 
  password = password
)

db$available_databases
#> [1] "test_db"                   "information_schema"       
#> [3] "mysql"                     "performance_schema"       
#> [5] "previous_close_prices_db"  "sys"                      
#> [7] "example_db"

db$connected_database
#> [1] "information_schema"

db$tables
#>  [1] "ADMINISTRABLE_ROLE_AUTHORIZATIONS"    
#>  [2] "APPLICABLE_ROLES"                     
#>  [3] "CHARACTER_SETS"                       
#>  [4] "CHECK_CONSTRAINTS"                    
#>  [5] "COLLATIONS"                           
#>  [6] "COLLATION_CHARACTER_SET_APPLICABILITY"
#>  [7] "COLUMNS"                              
#>  [8] "COLUMNS_EXTENSIONS"                   
#>  [9] "COLUMN_PRIVILEGES"                    
#> [10] "COLUMN_STATISTICS"                    
#> [11] "ENABLED_ROLES"                        
#> [12] "ENGINES"                              
#> [13] "EVENTS"                               
#> [14] "FILES"                                

# You can also get the pool object to run queries etc.
pool <- db$pool

DBI::dbListTables(pool)
#>  [1] "ADMINISTRABLE_ROLE_AUTHORIZATIONS"    
#>  [2] "APPLICABLE_ROLES"                     
#>  [3] "CHARACTER_SETS"                       
#>  [4] "CHECK_CONSTRAINTS"                    
#>  [5] "COLLATIONS"                           
#>  [6] "COLLATION_CHARACTER_SET_APPLICABILITY"

db$close()

Similarly, we can connect to PostgreSQL with.

library(dbmanager)

db <- PostgreSQL$new(
  db_name  = db_name,
  host     = host, 
  user     = user, 
  password = password
)

db$available_databases
db$connected_database
db$tables
pool <- db$pool

db$close()

Using SQL

It is possible to execute SQL queries directly against tables within remote databases using the query method. dbmanager implements a DBI interface for all connection objects, so you can use query to execute SQL and return the result as an R data frame:

db <- MySQL$new(
  db_name  = db_name,
  host     = host, 
  user     = user, 
  password = password
)

db$query("show databases")
#>                    Database
#> 1                example_db
#> 2        information_schema
#> 3                     mysql
#> 4        performance_schema
#> 5                  price_db
#> 6                       sys
#> 7                   test_db

Issues & Contributions

If you encounter a clear bug, please file an issue with a minimal reproducible example on GitHub.

Contributions to the package are welcome. Please start by filing an issue, outlining the bug you intend to fix or functionality you intend to add or modify.

Code of Conduct

Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.

eokodie/dbmanager documentation built on Jan. 28, 2021, 2:42 p.m.