22  Working with Databases

NoteOptional chapter

This chapter is not assigned in any week of the course schedule. It is here as a reference for when you need it—most likely around the data-wrangling weeks (Chapter 14), or when your term-project data outgrow what flat files handle comfortably.

NoteLearning Objectives

After completing this chapter, you will be able to:

  • Understand database fundamentals and when to use them
  • Connect to databases from R using DBI
  • Write basic SQL queries
  • Use dbplyr to work with databases using familiar dplyr syntax
  • Translate between dplyr operations and SQL
  • Work with in-process databases like duckdb for large datasets

22.1 Why Databases?

A huge amount of scientific data lives in databases. While you can sometimes ask a collaborator to export a snapshot to a CSV file, this approach becomes painful quickly: every time you need updated data or a different subset, you must coordinate with another person. Being able to access databases directly gives you the data you need, when you need it.

Databases offer several advantages over flat files. They scale to datasets far too large to fit in memory, because the data lives on disk and only the rows a query asks for are read. They are fast, because indexes let the engine find particular rows without scanning every one. They allow many people to read and write at the same time without corrupting one another’s work, they enforce constraints that keep invalid data from being entered in the first place, and they offer fine-grained access control for sensitive information. In the biosciences you will meet them constantly, often without noticing: the genomic sequence repositories and public resources such as NCBI’s RefSeq (National Center for Biotechnology Information, 2024), Ensembl, and UniProt are databases with web front ends; clinical-trial data, laboratory information management systems (LIMS), and institutional data warehouses are databases you may be given credentials for.

22.2 Database Basics

At the simplest level, you can think of a database as a collection of data frames, called tables in database terminology. Like a data frame, a database table is a collection of named columns where every value in a column is the same type.

Three differences matter in practice. The first is where the data lives: a database table is stored on disk and can be arbitrarily large, whereas a data frame is held in memory and limited by RAM. The second is indexing: database tables almost always carry indexes that make it possible to find particular rows without scanning the whole table, in the way that a book’s index saves you from reading every page. The third is orientation: traditional databases store data row by row, which suits systems that add records one at a time, while analytical databases such as DuckDB increasingly store it column by column, which suits the summaries and aggregations that data analysis is made of.

Types of Database Systems

Database management systems (DBMS) come in three main forms (Table 22.1):

Table 22.1: Types of database management systems
Type Description Examples
Client-server Central server, multiple client connections PostgreSQL, MySQL, SQL Server
Cloud Managed cloud services, auto-scaling Amazon Redshift, Google BigQuery, Snowflake
In-process Runs entirely within your application SQLite, DuckDB

For learning and local analysis, in-process databases like duckdb are ideal—no server setup required, and they’re designed for analytical workloads.

22.3 Connecting to Databases

To connect to a database from R, you need two packages:

  • DBI (DataBase Interface): Provides generic functions for connecting, querying, and managing databases
  • A database driver: A package specific to your DBMS (e.g., RPostgres, RMariaDB, duckdb)

Using duckdb for Learning

duckdb is a high-performance analytical database that’s perfect for learning. It runs entirely within R and requires no external setup:

# Create an in-memory database (temporary, deleted when R closes)
con <- DBI::dbConnect(duckdb::duckdb())

For persistent storage in a real project, specify a file path:

# Persistent database stored in a file
con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "my_database.duckdb")

Connecting to Other Databases

Different databases require different connection parameters:

# PostgreSQL
con <- DBI::dbConnect(
  RPostgres::Postgres(),
  host = "database.example.com",
  port = 5432,
  dbname = "mydb",
  user = "username",
  password = "password"
)

# MySQL/MariaDB
con <- DBI::dbConnect(
  RMariaDB::MariaDB(),
  host = "localhost",
  dbname = "mydb",
  username = "user"
)
Tip

Never put passwords directly in your code! Use environment variables, the keyring package, or configuration files that aren’t committed to version control.

22.4 Loading Data into a Database

Let’s create some example tables to work with. We’ll use familiar datasets:

# Write data frames to database tables
dbWriteTable(con, "mtcars", mtcars)
dbWriteTable(con, "iris", iris)

# Verify the tables exist
dbListTables(con)
#> [1] "iris"   "mtcars"

Reading Tables

You can read an entire table into R:

# Read a table into R as a data frame
con |>
  dbReadTable("mtcars") |>
  as_tibble()
mpg cyl disp hp drat wt qsec vs am gear carb
21.0 6 160.0 110 3.90 2.620 16.46 0 1 4 4
21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4
22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1
21.4 6 258.0 110 3.08 3.215 19.44 1 0 3 1
18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2
18.1 6 225.0 105 2.76 3.460 20.22 1 0 3 1
14.3 8 360.0 245 3.21 3.570 15.84 0 0 3 4
24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2
22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2
19.2 6 167.6 123 3.92 3.440 18.30 1 0 4 4
17.8 6 167.6 123 3.92 3.440 18.90 1 0 4 4
16.4 8 275.8 180 3.07 4.070 17.40 0 0 3 3
17.3 8 275.8 180 3.07 3.730 17.60 0 0 3 3
15.2 8 275.8 180 3.07 3.780 18.00 0 0 3 3
10.4 8 472.0 205 2.93 5.250 17.98 0 0 3 4
10.4 8 460.0 215 3.00 5.424 17.82 0 0 3 4
14.7 8 440.0 230 3.23 5.345 17.42 0 0 3 4
32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1
30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1
15.5 8 318.0 150 2.76 3.520 16.87 0 0 3 2
15.2 8 304.0 150 3.15 3.435 17.30 0 0 3 2
13.3 8 350.0 245 3.73 3.840 15.41 0 0 3 4
19.2 8 400.0 175 3.08 3.845 17.05 0 0 3 2
27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1
26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2
30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2
15.8 8 351.0 264 4.22 3.170 14.50 0 1 5 4
19.7 6 145.0 175 3.62 2.770 15.50 0 1 5 6
15.0 8 301.0 335 3.54 3.570 14.60 0 1 5 8
21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2

However, for large tables this defeats the purpose of using a database. Instead, you’ll want to query just the data you need.

22.5 SQL: The Language of Databases

SQL (Structured Query Language) is the standard language for working with databases. While we’ll primarily use dbplyr to write dplyr code that gets translated to SQL, understanding SQL basics is valuable. Table 22.2 maps each SQL clause to the dplyr verb you already know from Section 14.3.

Basic SQL Queries

The most common SQL operation is SELECT, which retrieves data:

# Run a SQL query directly
sql <- "SELECT mpg, cyl, hp FROM mtcars WHERE mpg > 25"
result <- dbGetQuery(con, sql)
as_tibble(result)
mpg cyl hp
32.4 4 66
30.4 4 52
33.9 4 65
27.3 4 66
26.0 4 91
30.4 4 113

SQL Query Structure

A SQL query follows this general pattern:

SELECT columns
FROM table
WHERE conditions
GROUP BY columns
ORDER BY columns

The clauses must appear in this order, though not all are required.

Table 22.2: SQL clauses and their dplyr equivalents
Clause Purpose dplyr equivalent
SELECT Choose columns, compute values select(), mutate()
FROM Specify the table The data frame name
WHERE Filter rows filter()
GROUP BY Define groups for aggregation group_by()
ORDER BY Sort results arrange()

22.6 Using dbplyr: dplyr for Databases

dbplyr is a dplyr backend that translates your dplyr code to SQL and runs it on the database. This means you can use familiar tidyverse syntax (Section 14.3) without learning SQL.

Creating a Database Reference

Use tbl() to create a reference to a database table:

mtcars_db <- tbl(con, "mtcars")
mtcars_db
mpg cyl disp hp drat wt qsec vs am gear carb
21.0 6 160.0 110 3.90 2.620 16.46 0 1 4 4
21.0 6 160.0 110 3.90 2.875 17.02 0 1 4 4
22.8 4 108.0 93 3.85 2.320 18.61 1 1 4 1
21.4 6 258.0 110 3.08 3.215 19.44 1 0 3 1
18.7 8 360.0 175 3.15 3.440 17.02 0 0 3 2
18.1 6 225.0 105 2.76 3.460 20.22 1 0 3 1
14.3 8 360.0 245 3.21 3.570 15.84 0 0 3 4
24.4 4 146.7 62 3.69 3.190 20.00 1 0 4 2
22.8 4 140.8 95 3.92 3.150 22.90 1 0 4 2
19.2 6 167.6 123 3.92 3.440 18.30 1 0 4 4
17.8 6 167.6 123 3.92 3.440 18.90 1 0 4 4
16.4 8 275.8 180 3.07 4.070 17.40 0 0 3 3
17.3 8 275.8 180 3.07 3.730 17.60 0 0 3 3
15.2 8 275.8 180 3.07 3.780 18.00 0 0 3 3
10.4 8 472.0 205 2.93 5.250 17.98 0 0 3 4
10.4 8 460.0 215 3.00 5.424 17.82 0 0 3 4
14.7 8 440.0 230 3.23 5.345 17.42 0 0 3 4
32.4 4 78.7 66 4.08 2.200 19.47 1 1 4 1
30.4 4 75.7 52 4.93 1.615 18.52 1 1 4 2
33.9 4 71.1 65 4.22 1.835 19.90 1 1 4 1
21.5 4 120.1 97 3.70 2.465 20.01 1 0 3 1
15.5 8 318.0 150 2.76 3.520 16.87 0 0 3 2
15.2 8 304.0 150 3.15 3.435 17.30 0 0 3 2
13.3 8 350.0 245 3.73 3.840 15.41 0 0 3 4
19.2 8 400.0 175 3.08 3.845 17.05 0 0 3 2
27.3 4 79.0 66 4.08 1.935 18.90 1 1 4 1
26.0 4 120.3 91 4.43 2.140 16.70 0 1 5 2
30.4 4 95.1 113 3.77 1.513 16.90 1 1 5 2
15.8 8 351.0 264 4.22 3.170 14.50 0 1 5 4
19.7 6 145.0 175 3.62 2.770 15.50 0 1 5 6
15.0 8 301.0 335 3.54 3.570 14.60 0 1 5 8
21.4 4 121.0 109 4.11 2.780 18.60 1 1 4 2

Notice that this doesn’t load the data—it just creates a reference. The data stays in the database.

Lazy Evaluation

dbplyr operations are lazy: they build up a query without executing it until you explicitly request the data:

# Build a query (doesn't execute yet)
efficient_cars <- mtcars_db |>
  filter(mpg > 25) |>
  select(mpg, cyl, hp, wt) |>
  arrange(desc(mpg))

# See the generated SQL
efficient_cars |> show_query()
#> <SQL>
#> SELECT mpg, cyl, hp, wt
#> FROM mtcars
#> WHERE (mpg > 25.0)
#> ORDER BY mpg DESC

Collecting Results

Use collect() to execute the query and bring results into R:

# Execute query and return results to R
efficient_cars |> collect()
mpg cyl hp wt
33.9 4 65 1.835
32.4 4 66 2.200
30.4 4 52 1.615
30.4 4 113 1.513
27.3 4 66 1.935
26.0 4 91 2.140
Important

Only collect() what you need! Fetching an entire large table defeats the purpose of using a database. Filter and aggregate in the database first, then collect the summarized results.

22.7 Translating dplyr to SQL

dbplyr translates dplyr verbs to SQL clauses. Let’s see how common operations map:

Filtering and Selecting

mtcars_db |>
  filter(cyl == 6, mpg > 18) |>
  select(mpg, cyl, hp, wt) |>
  show_query()
#> <SQL>
#> SELECT mpg, cyl, hp, wt
#> FROM mtcars
#> WHERE (cyl = 6.0) AND (mpg > 18.0)

Mutating (Creating New Columns)

mtcars_db |>
  mutate(
    efficiency = mpg / wt,
    hp_per_cyl = hp / cyl
  ) |>
  select(mpg, wt, efficiency, hp_per_cyl) |>
  show_query()
#> <SQL>
#> SELECT mpg, wt, mpg / wt AS efficiency, hp / cyl AS hp_per_cyl
#> FROM mtcars

Grouping and Summarizing

mtcars_db |>
  group_by(cyl) |>
  summarize(
    n = n(),
    avg_mpg = mean(mpg, na.rm = TRUE),
    max_hp = max(hp, na.rm = TRUE)
  ) |>
  show_query()
#> <SQL>
#> SELECT cyl, COUNT(*) AS n, AVG(mpg) AS avg_mpg, MAX(hp) AS max_hp
#> FROM mtcars
#> GROUP BY cyl

Arranging (Sorting)

mtcars_db |>
  arrange(cyl, desc(mpg)) |>
  show_query()
#> <SQL>
#> SELECT mtcars.*
#> FROM mtcars
#> ORDER BY cyl, mpg DESC

22.8 SQL Syntax Details

Understanding SQL helps you write better dbplyr code and debug issues.

Key Differences from R

Table 22.3 lists the syntax differences that trip people up most often.

Table 22.3: Syntax differences between R and SQL
Aspect R/dplyr SQL
Equality == =
Logical AND & AND
Logical OR | OR
Missing values NA NULL
Strings "text" or 'text' 'text' only
Case sensitivity Case sensitive Keywords case insensitive

NULL Handling

SQL’s NULL behaves like R’s NA—it’s “contagious” in calculations:

mtcars_db |>
  filter(!is.na(mpg)) |>
  show_query()
#> <SQL>
#> SELECT mtcars.*
#> FROM mtcars
#> WHERE (NOT((mpg IS NULL)))

Subqueries

Sometimes dbplyr generates subqueries—queries nested inside other queries:

mtcars_db |>
  mutate(mpg_group = if_else(mpg > 20, "high", "low")) |>
  filter(mpg_group == "high") |>
  show_query()
#> <SQL>
#> SELECT q01.*
#> FROM (
#>   SELECT
#>     mtcars.*,
#>     CASE WHEN (mpg > 20.0) THEN 'high' WHEN NOT (mpg > 20.0) THEN 'low' END AS mpg_group
#>   FROM mtcars
#> ) q01
#> WHERE (mpg_group = 'high')

The subquery is needed because SQL evaluates WHERE before SELECT, so you can’t filter on a column you’re creating in the same query.

22.9 Working with Multiple Tables

Databases often store data across multiple related tables (normalized data). You can use joins in dbplyr just like with regular data frames (Section 14.8):

# Join tables in the database
orders_db |>
  left_join(customers_db, by = "customer_id") |>
  show_query()

The join is executed in the database, which is much more efficient than loading both tables into R and joining there.

22.10 Window Functions

SQL window functions perform calculations across sets of rows related to the current row. dbplyr translates functions like lag(), lead(), and rank() to their SQL equivalents:

mtcars_db |>
  group_by(cyl) |>
  mutate(
    rank_mpg = min_rank(desc(mpg)),
    mpg_vs_avg = mpg - mean(mpg, na.rm = TRUE)
  ) |>
  select(cyl, mpg, rank_mpg, mpg_vs_avg) |>
  show_query()
#> <SQL>
#> SELECT
#>   cyl,
#>   mpg,
#>   CASE
#> WHEN (NOT((mpg IS NULL))) THEN RANK() OVER (PARTITION BY cyl, (CASE WHEN ((mpg IS NULL)) THEN 1 ELSE 0 END) ORDER BY mpg DESC)
#> END AS rank_mpg,
#>   mpg - AVG(mpg) OVER (PARTITION BY cyl) AS mpg_vs_avg
#> FROM mtcars

22.11 Best Practices

1. Filter Early

Push filtering to the database rather than collecting all data and filtering in R:

# Good: Filter in database
mtcars_db |>
  filter(mpg > 25) |>
  collect()

# Bad: Collect everything then filter
mtcars_db |>
  collect() |>
  filter(mpg > 25)

2. Select Only Needed Columns

# Good: Select specific columns
mtcars_db |>
  select(mpg, cyl, hp) |>
  collect()

# Bad: Collect all columns
mtcars_db |>
  collect() |>
  select(mpg, cyl, hp)

3. Aggregate in the Database

# Good: Summarize in database
mtcars_db |>
  group_by(cyl) |>
  summarize(avg_mpg = mean(mpg)) |>
  collect()

# Bad: Collect then summarize
mtcars_db |>
  collect() |>
  group_by(cyl) |>
  summarize(avg_mpg = mean(mpg))

4. Close Connections

Always close your database connection when you’re done:

dbDisconnect(con)

22.12 When to Use Databases

Databases are valuable when:

  • Data is too large for memory: Even modest databases handle billions of rows
  • Multiple users need access: Databases manage concurrent access safely
  • Data changes frequently: Updates are immediate, no file copying needed
  • You need specific subsets: Queries return just what you need
  • Data integrity matters: Constraints prevent invalid data

For smaller datasets that fit comfortably in memory, regular data frames are simpler and faster for exploratory analysis.

The same idea, an index that lets you pull out just the rows you need without reading the whole file, is why genomics has its own “databases in a file”: a sorted, indexed BAM or VCF queried with samtools and bcftools (Danecek et al., 2021; Li et al., 2009) returns the reads or variants in one region in milliseconds, exactly as a SQL WHERE clause on an indexed column does. See Appendix C for those formats.

22.13 Summary

In this chapter, you learned how to:

  • Connect to databases from R using DBI
  • Use duckdb as a lightweight analytical database
  • Write basic SQL queries with SELECT, WHERE, and GROUP BY
  • Use dbplyr to work with databases using familiar dplyr syntax
  • Translate dplyr operations to SQL and understand the generated queries
  • Apply best practices for efficient database access

The combination of dbplyr and DBI lets you leverage your existing tidyverse skills while accessing data at any scale. When you need to work with data too large for memory or collaborate with others through a shared database, these tools let you stay productive without learning an entirely new programming paradigm.

Tip

For reference on dplyr and tidyverse functions used with databases, see Appendix E.

Exercises

  1. Basic Connection: Connect to a duckdb database and load the nycflights13::flights data into a table called “flights”.

  2. SQL Practice: Write SQL queries to:

    • Find all flights from JFK airport
    • Calculate the average departure delay by carrier
    • Find the 10 destinations with the most flights
  3. dbplyr Translation: Use dbplyr to write the equivalent dplyr code for each SQL query above. Compare the generated SQL with your hand-written queries.

  4. Efficient Querying: Given a database with a large “sales” table:

    • What’s wrong with: tbl(con, "sales") |> collect() |> filter(year == 2023)?
    • How would you rewrite this query efficiently?
  5. Joins in Databases: If you have two tables, “experiments” and “samples”, linked by sample_id:

    • Write dbplyr code to join them
    • Use show_query() to see the generated SQL
    • Explain when the join happens (in R or in the database)

Additional Resources