I've been dealing with a dataset of about 500,000 sales records lately. It's a CSV file, and while R handles it fine in memory, I'm starting to feel the lag every time I run a complex filter. Plus, in a real production environment, your data isn't sitting in a CSV on your desktop; it's in a database. I want to move this into a structured environment where I can use SQL to do the heavy lifting before the data even hits my R session.

Setting up the pipe

To do this, I need the DBI package. It's essentially the "universal translator" for R and databases. But DBI by itself doesn't know how to talk to a specific database—it needs a driver. I'll use RSQLite because it creates a database file locally on my disk, so I don't have to spend twenty minutes configuring a PostgreSQL server just to show you how this works.

library(DBI)
library(RSQLite)

# I'll create a connection to a file named 'sales_data.sqlite'
con <- dbConnect(RSQLite::SQLite(), "sales_data.sqlite")

Now, con is my connection object. It's like an open phone line. If I close R without closing this connection, the database file might stay locked or get corrupted, which is a headache I've dealt with more times than I'd like to admit.

Shoving data into the table

I have my sales data in a data frame called sales_df. I could write a bunch of INSERT INTO statements, but that's a waste of time. I'll try dbWriteTable. I'm curious if it handles the schema automatically.

# Let's assume sales_df exists with columns: order_id, product, amount, date
dbWriteTable(con, "orders", sales_df, overwrite = TRUE)

That worked. The overwrite = TRUE argument is a lifesaver during the exploration phase; otherwise, if I run this script twice, R will scream at me that the table "orders" already exists. I've just pushed the entire data frame into a SQL table. Now the data is living on my hard drive in a structured format, not just floating in my RAM.

Asking the database for answers

Here is where it gets interesting. Instead of loading the whole table back into R and using filter() or subset(), I can let the database engine find exactly what I need. I'll use dbGetQuery, which sends a SQL string to the database and returns the result as a standard R data frame.

I want to find all orders where the amount was over 1,000. Let's see if the SQL syntax is behaving.

my_big_orders <- dbGetQuery(con, "SELECT * FROM orders WHERE amount > 1000")
head(my_big_orders)

That worked perfectly. But wait—what if I want to use a variable in my query? I tried using paste0() to build the string, but I quickly remembered that's a great way to invite SQL injection attacks (or just crash your code if a product name has a single quote in it). I'll use parameterized queries instead.

threshold <- 5000
# The ? is a placeholder that DBI fills in safely
huge_orders <- dbGetQuery(con, "SELECT * FROM orders WHERE amount > ?", params = list(threshold))

This is much cleaner. I'm sending the command and the data separately, and DBI handles the quoting and escaping for me.

Hanging up the phone

I'm done with my analysis for now. If I just leave the session open, I'm wasting resources and potentially locking the file. I need to explicitly close the connection.

dbDisconnect(con)

Once that's done, the "phone line" is cut. If I try to run dbGetQuery(con, ...) now, R will throw an error telling me the connection is invalid. That's exactly what we want—it ensures we aren't leaving ghost connections hanging around on a database server.

📋 Practical Task

Exercise: Building a Local Product Inventory Archive

You have been given a data frame called inventory_df containing columns item_id, item_name, quantity, and warehouse_location. Your goal is to move this data into a SQLite database and extract a specific subset of information without loading the whole table into R.

Requirements:

• Create a connection to a new SQLite database file named warehouse.sqlite.
• Write the inventory_df data frame into a table named stock.
• Write a parameterized query using dbGetQuery to retrieve all items from a specific warehouse (create a variable called target_warehouse to hold the location name, e.g., "North_East").
• Store the resulting data frame in a variable called warehouse_subset.
• Properly close the database connection.