README.md

In tjpalanca/dbtools: Database Tools for R

dbtools

This package is a set of utilities with the goal of providing a consistent interface for applications that read and write data from databases.

Installation

You can install from R Universe:

install.packages("dbtools", repos = "https://tjpalanca.r-universe.dev")

Getting Started

Construct and Deconstruct

This provides the bridge between data frames (that have excellent support for interfacing with databases), and objects (that don’t). Outside of a full ORM solution, this is best for just mapping S3, S4, and R6 classes to databases with the goal of recreating them in some other session.

# Define a task S4 Class
Task <- setClass(
  "Task",
  slots = c(
    name        = "character",
    description = "character",
    due_date    = "Date"
  )
)

# Instantiate some tasks
tasks <- list(
  Task(name        = "Wake up",
       description = "Stop snoozing the alarm!",
       due_date    = as.Date("2021-10-01")),
  Task(name        = "Brush teeth",
       description = "Keep them white",
       due_date    = as.Date("2021-10-02")),
  Task(name        = "Take a bath",
       description = "Wash behind the ears!",
       due_date    = as.Date("2021-10-03"))
)

# Deconstruct into a tibble
(deconstructed <- deconstruct(tasks, Task))
#> # A tibble: 3 × 3
#>   name        description              due_date  
#>   <chr>       <chr>                    <date>    
#> 1 Wake up     Stop snoozing the alarm! 2021-10-01
#> 2 Brush teeth Keep them white          2021-10-02
#> 3 Take a bath Wash behind the ears!    2021-10-03

# Construct the tasks back from the tibble 
(constructed <- construct(deconstructed, Task))
#> [[1]]
#> An object of class "Task"
#> Slot "name":
#> [1] "Wake up"
#> 
#> Slot "description":
#> [1] "Stop snoozing the alarm!"
#> 
#> Slot "due_date":
#> [1] "2021-10-01"
#> 
#> 
#> [[2]]
#> An object of class "Task"
#> Slot "name":
#> [1] "Brush teeth"
#> 
#> Slot "description":
#> [1] "Keep them white"
#> 
#> Slot "due_date":
#> [1] "2021-10-02"
#> 
#> 
#> [[3]]
#> An object of class "Task"
#> Slot "name":
#> [1] "Take a bath"
#> 
#> Slot "description":
#> [1] "Wash behind the ears!"
#> 
#> Slot "due_date":
#> [1] "2021-10-03"

Data Manipulation Statements (DML)

We introduce a new tibble class: tbl_db which behaves like a remote SQL table source that can be operated on with {dplyr} functions, but is also able to accept DML statements via update(), insert(), upsert().

# Connect to a postgres database
dbconn <- connect("postgres", Sys.getenv("TJPALANCA_DB_ADDR"))

# Query the tasks table (already pre-defined)
(tasks.tbl_db <- tbl_db(dbconn, "tasks"))
#> # Source:   table<tasks> [?? x 4]
#> # Database: postgres [[e-mail]:5432/tjpalanca]
#> # … with 4 variables: id <int>, name <chr>, description <chr>, due_date <date>

# Insert the deconstructed tasks
tasks.tbl_db %>%
  insert(deconstructed)
#> # Source:   table<tasks> [?? x 4]
#> # Database: postgres [[e-mail]:5432/tjpalanca]
#>      id name        description              due_date  
#>   <int> <chr>       <chr>                    <date>    
#> 1     1 Wake up     Stop snoozing the alarm! 2021-10-01
#> 2     2 Brush teeth Keep them white          2021-10-02
#> 3     3 Take a bath Wash behind the ears!    2021-10-03

# Once happy, commit
tasks.tbl_db %>%
  insert(deconstructed) %>%
  commit()
#> # Source:   table<tasks> [?? x 4]
#> # Database: postgres [[e-mail]:5432/tjpalanca]
#>      id name        description              due_date  
#>   <int> <chr>       <chr>                    <date>    
#> 1     4 Wake up     Stop snoozing the alarm! 2021-10-01
#> 2     5 Brush teeth Keep them white          2021-10-02
#> 3     6 Take a bath Wash behind the ears!    2021-10-03

# Fetch from DB and construct the S4 objects again
collect(tasks.tbl_db) %>%
  construct(Task)
#> [[1]]
#> An object of class "Task"
#> Slot "name":
#> [1] "Wake up"
#> 
#> Slot "description":
#> [1] "Stop snoozing the alarm!"
#> 
#> Slot "due_date":
#> [1] "2021-10-01"
#> 
#> 
#> [[2]]
#> An object of class "Task"
#> Slot "name":
#> [1] "Brush teeth"
#> 
#> Slot "description":
#> [1] "Keep them white"
#> 
#> Slot "due_date":
#> [1] "2021-10-02"
#> 
#> 
#> [[3]]
#> An object of class "Task"
#> Slot "name":
#> [1] "Take a bath"
#> 
#> Slot "description":
#> [1] "Wash behind the ears!"
#> 
#> Slot "due_date":
#> [1] "2021-10-03"

Related work

• Construction and deconstruction was inspired by the Strapping.jl package in the Julia ecosystem.
• Data Manipulaiton is powered by the excellent dbx package by Andrew Kane.

tjpalanca/dbtools documentation built on Oct. 7, 2021, 6:43 a.m.