In tjpalanca/dbtools: Database Tools for R

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

# Load the library
devtools::load_all()
library(DBI)

# Prepare postgres table
dbconn <- connect("postgres", Sys.getenv("TJPALANCA_DB_ADDR"))
dbExecute(dbconn, "drop table if exists tasks;")
dbExecute(dbconn, paste0(
  "create table tasks (id serial primary key, ",
  "name text, description text, due_date date);"
))

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

# Construct the tasks back from the tibble 
(constructed <- construct(deconstructed, Task))

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

# Insert the deconstructed tasks
tasks.tbl_db %>%
  insert(deconstructed)

# Once happy, commit
tasks.tbl_db %>%
  insert(deconstructed) %>%
  commit()

# Fetch from DB and construct the S4 objects again
collect(tasks.tbl_db) %>%
  construct(Task)

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.