R, Databases & SQL

R DBI tutorial: connect R to SQLite, query with dbplyr and SQL — free 2-hour primer before R4DS.

Preface

Last verified: 2026-09-26 · R 4.5.2 · DBI · dbplyr 2.6.0 · RSQLite 2.4.6 · duckdb.

New here? Grab the one-page DBI cheatsheet, then work Chapters 1–5 in order — runnable code for each chapter lives in code/.

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Structure of the book

Chapter 1  DBI introduces the DBI package. Chapter 2  dbplyr explores data wrangling within database using the dplyr package. Chapters 3  SQL Basics and 4  SQL Advanced introduce basic and advanced SQL. Chapter 5  JOINs combines tables with JOINs in SQL and dbplyr.

Software information

The R session information when compiling this book is shown below:

sessionInfo()

R version 4.4.2 (2024-10-31)
Platform: x86_64-pc-linux-gnu
Running under: Ubuntu 24.04.5 LTS

Matrix products: default
BLAS:   /usr/lib/x86_64-linux-gnu/openblas-pthread/libblas.so.3 
LAPACK: /usr/lib/x86_64-linux-gnu/openblas-pthread/libopenblasp-r0.3.26.so;  LAPACK version 3.12.0

locale:
 [1] LC_CTYPE=C.UTF-8       LC_NUMERIC=C           LC_TIME=C.UTF-8       
 [4] LC_COLLATE=C.UTF-8     LC_MONETARY=C.UTF-8    LC_MESSAGES=C.UTF-8   
 [7] LC_PAPER=C.UTF-8       LC_NAME=C              LC_ADDRESS=C          
[10] LC_TELEPHONE=C         LC_MEASUREMENT=C.UTF-8 LC_IDENTIFICATION=C   

time zone: UTC
tzcode source: system (glibc)

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

loaded via a namespace (and not attached):
 [1] compiler_4.4.2  fastmap_1.2.0   cli_3.6.6       tools_4.4.2    
 [5] htmltools_0.5.9 rmarkdown_2.32  knitr_1.52      jsonlite_2.0.0 
 [9] xfun_0.61       digest_0.6.39   rlang_1.3.0     evaluate_1.0.5 

We do not add prompts (> and +) to R source code in this book, and we comment out the text output with two hashes ## by default, as you can see from the R session information above. This is for your convenience when you want to copy and run the code (the text output will be ignored since it is commented out). Package names are in bold text (e.g., rmarkdown), and function names are followed by parentheses (e.g., bookdown::render_book()). The double-colon operator :: means accessing an object from a package.