Quickstart

Installation

# From CRAN
install.packages("rpgconn")

# From GitHub (development version)
# install.packages("pak")
pak::pkg_install("r-data-science/rpgconn")

Choose Your Setup Path

Pick the setup that matches your environment:

Cloud Database Setup

Step 1: Get Your Connection String

Most cloud providers give you a connection string. Examples:

DigitalOcean Managed Database:

postgresql://doadmin:[email protected]:25060/defaultdb?sslmode=require

AWS RDS:

postgresql://dbuser:[email protected]:5432/production

Heroku Postgres:

postgres://user:[email protected]:5432/dbname

Step 2: Set Environment Variable

In R session:

Sys.setenv(RPG_CONN_STRING = "postgresql://user:pass@host:5432/db?sslmode=require")

In .Renviron (persistent across sessions):

# Open .Renviron file
usethis::edit_r_environ()

# Add this line:
RPG_CONN_STRING=postgresql://user:pass@host:5432/db?sslmode=require

# Save, close, restart R

In system environment (best for production):

# Linux/Mac (~/.bashrc or ~/.zshrc)
export RPG_CONN_STRING="postgresql://user:pass@host/db"

# Windows (PowerShell as Administrator)
[Environment]::SetEnvironmentVariable("RPG_CONN_STRING", "postgresql://...", "User")

Step 3: Connect

library(rpgconn)

# Connect using environment variable
cn <- dbc()

# Verify connection
DBI::dbGetQuery(cn, "SELECT version()")

# Disconnect when done
dbd(cn)

✅ Done! Your cloud database is ready to use.

Local Database Setup

Prerequisites

Install PostgreSQL:

  • Mac: brew install postgresql@15 then brew services start postgresql@15
  • Ubuntu: sudo apt-get install postgresql postgresql-contrib
  • Windows: Download from postgresql.org

Step 1: Create Database and User

# Connect to PostgreSQL as superuser
psql postgres

# Create database and user
CREATE DATABASE myapp_dev;
CREATE USER myapp_user WITH PASSWORD 'dev_password';
GRANT ALL PRIVILEGES ON DATABASE myapp_dev TO myapp_user;

# Exit
\q

Step 2: Build Connection String

# Format: postgresql://user:password@host:port/database
conn_str <- "postgresql://myapp_user:dev_password@localhost:5432/myapp_dev"

# Set environment variable
Sys.setenv(RPG_CONN_STRING = conn_str)

Step 3: Connect and Test

library(rpgconn)

cn <- dbc()

# Create a test table
DBI::dbExecute(cn, "
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
  )
")

# Insert test data
DBI::dbExecute(cn, "INSERT INTO users (name) VALUES ('Alice'), ('Bob')")

# Query
DBI::dbGetQuery(cn, "SELECT * FROM users")

# Cleanup
dbd(cn)

✅ Done! Local development environment ready.

YAML Configuration Setup

Best for teams or when managing multiple databases.

Step 1: Initialize Config Files

library(rpgconn)

init_yamls()
# Output: rpgconn: configs created
#   ...Update connection configs: edit_config()
#   ...Update connection options: edit_options()

Step 2: Edit Configuration

edit_config()

This opens ~/.config/rpgconn/config.yml in your editor:

config:
  local:
    host: localhost
    port: 5432
  prod:
    host: example_prod_host
    port: 5432
    user: "some user"
    password: "some password"

Update it:

config:
  dev:
    host: localhost
    port: 5432
    user: myapp_user
    password: dev_password

  staging:
    host: staging-db.example.com
    port: 5432
    user: app_staging
    password: staging_secret

  prod:
    host: prod-db.example.com
    port: 25060
    user: app_prod
    password: prod_secret

Step 3: Connect Using Named Config

# Development database
cn_dev <- dbc(cfg = "dev", db = "myapp_dev")
dbd(cn_dev)

# Staging database
cn_staging <- dbc(cfg = "staging", db = "myapp_staging")
dbd(cn_staging)

# Production database (be careful!)
cn_prod <- dbc(cfg = "prod", db = "myapp_prod")
dbd(cn_prod)

Step 4: Optional - Customize Connection Options

edit_options()

Opens ~/.config/rpgconn/options.yml:

options:
  connect_timeout: 10
  timezone: UTC
  application_name: rpgconn
  client_encoding: UTF8

These options are applied to all connections automatically.

Common Workflows

Workflow 1: Quick Query

library(rpgconn)

# One-liner connection
cn <- dbc()

# Run query
result <- DBI::dbGetQuery(cn, "SELECT * FROM users WHERE active = TRUE")

# Don't forget to disconnect
dbd(cn)

# Or use withr for automatic cleanup
withr::with_db_connection(
  cn <- dbc(),
  {
    DBI::dbGetQuery(cn, "SELECT count(*) FROM orders")
  }
)

Workflow 2: Long-Running Script

library(rpgconn)

# Connect once at the start
cn <- dbc()

# Do lots of work
orders <- DBI::dbReadTable(cn, "orders")
customers <- DBI::dbReadTable(cn, "customers")

# ... process data ...

# Write results back
DBI::dbWriteTable(cn, "daily_summary", summary_df, overwrite = TRUE)

# Disconnect at the end
dbd(cn)

Workflow 3: Shiny App

library(shiny)
library(rpgconn)

ui <- fluidPage(
  tableOutput("data")
)

server <- function(input, output, session) {
  # Connect once when app starts
  cn <- dbc()

  # Ensure cleanup when app closes
  onStop(function() {
    dbd(cn)
  })

  output$data <- renderTable({
    # Query database on demand
    DBI::dbGetQuery(cn, "SELECT * FROM products LIMIT 10")
  })
}

shinyApp(ui, server)

Workflow 4: Testing with Fixtures

test_that("database operations work", {
  # Use test-specific config
  withr::local_envvar(
    RPG_CONN_STRING = "postgresql://test:test@localhost:5432/testdb"
  )

  cn <- dbc()

  # Setup
  DBI::dbExecute(cn, "CREATE TABLE test_users (id INT, name TEXT)")
  DBI::dbExecute(cn, "INSERT INTO test_users VALUES (1, 'Alice')")

  # Test
  result <- DBI::dbGetQuery(cn, "SELECT * FROM test_users")
  expect_equal(nrow(result), 1)

  # Cleanup
  DBI::dbExecute(cn, "DROP TABLE test_users")
  dbd(cn)
})

Debugging Connection Issues

Check Connection String is Set

Sys.getenv("RPG_CONN_STRING")
# Should print your connection string
# If empty, it's not set

Preview Connection Arguments Without Connecting

args <- dbc(args_only = TRUE)
str(args)

# Verify:
# - host is correct
# - port is correct
# - user/password are not NA
# - dbname is correct

Test Raw DBI Connection

# If rpgconn works but connection fails, test with raw DBI
library(DBI)
library(RPostgres)

cn <- dbConnect(
  Postgres(),
  host = "localhost",
  port = 5432,
  dbname = "mydb",
  user = "myuser",
  password = "mypassword"
)

Next Steps

  • Package Overview: See vignette("rpgconn") for design philosophy
  • Advanced Workflow: See vignette("advanced-workflow") for:
    • SSL configuration
    • IPv6 hosts
    • Custom configs per project
    • Troubleshooting guide

Need Help?

  • Documentation: ?dbc, ?use_config
  • Issues: GitHub Issues
  • Examples: example(dbc)