22 Working with Databases
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.
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):
| 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:
For persistent storage in a real project, specify a file path:
Connecting to Other Databases
Different databases require different connection parameters:
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:
Reading Tables
You can read an entire table into R:
| 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:
SQL Query Structure
A SQL query follows this general pattern:
The clauses must appear in this order, though not all are required.
| 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:
| 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:
Collecting Results
Use collect() to execute the query and bring results into R:
| 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 |
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
Mutating (Creating New Columns)
Grouping and Summarizing
Arranging (Sorting)
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.
| 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:
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):
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 mtcars22.11 Best Practices
1. Filter Early
Push filtering to the database rather than collecting all data and filtering in R:
2. Select Only Needed Columns
3. Aggregate in the Database
4. Close Connections
Always close your database connection when you’re done:
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.
For reference on dplyr and tidyverse functions used with databases, see Appendix E.
Exercises
Basic Connection: Connect to a duckdb database and load the
nycflights13::flightsdata into a table called “flights”.-
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
dbplyr Translation: Use dbplyr to write the equivalent dplyr code for each SQL query above. Compare the generated SQL with your hand-written queries.
-
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?
- What’s wrong with:
-
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
- DBI package documentation
- dbplyr package documentation
- duckdb for R
- SQL for Data Scientists by Renée M. P. Teate
- Practical SQL by Anthony DeBarros