In partnership with

Guten Tag!

Many greetings from Munich, Germany. Last week, I showed you how to connect to databases with R. And I also mentioned that it’s helpful to write a bit more SQL code than just grabbing all the data and processing it locally. This might sound like a lot of effort, though. So here’s the good news:

If you’re already using dplyr for data wrangling, you can get the best of both worlds. You can write dplyr code and let R translate that into SQL so that time-saving and resource-efficient calculations happen in the database. Then, you only retrieve the results.

So in today’s newsletter I’ll show you how that works. Let’s go!

Getting Started with {dbplyr}

First things first: you’ll need to install the {dbplyr} package. This package extends dplyr so it can translate R code into SQL syntax for supported databases.

To get started, establish a connection to your database using DBI::dbConnect(), just like before. This part hasn’t changed.

Working with Tables Using {dbplyr}

Now you can use {dbplyr} functions to interact with the database using familiar dplyr verbs. For example, in our example database, you can copy an R dataset like palmerpenguins::penguins into a database table using copy_to():

Fun fact: Notice that I never loaded {dbplyr} 😮 {dplyr} will detect when you want to use a {dbplyr} function.

Once the data is in the database, you can reference it with tbl(). This will connect to the table in the database (which is why the con variable is needed.)

Notice how the output almost looks like a regular tibble. The only difference is that it shows only a preview and not the full dataset. And in case you’re wondering: That’s a good thing. You never know how large the database is and often a preview will suffice.

Writing SQL Through dplyr Verbs

Now that you have a table-like object, you can use familiar dplyr verbs to generate SQL queries. For example:

Again, this only shows a preview. To pull the full result set from the database, use collect().

Seeing the Generated SQL

And if you’re ever wondering how {dbplyr} translated your dplyr code into SQL, you can inspect it using show_query().

Also, this is a great way to learn more about SQL by seeing how familiar R code maps to SQL commands.

Nice. We’ve learned a simple way to interact with databases using familiar syntax. However, using {dbplyr} doesn’t mean you’ll never need to write raw SQL again.

It helps you get started quickly and efficiently, though. Next week, I’ll show you how to combine {dbplyr} with raw SQL for more advanced database workflows.

And as always, if you have any questions, or just want to reach out, feel free to contact me by replying to this mail or finding me on LinkedIn or on Bluesky.

See you next week,
Albert 👋

Enjoyed this newsletter? Here are other ways I can help you:

• My playlist on YouTube

• Free book: Yet Again - R + Data Science

• Free book: Beautiful tables with {gt}

• Blog posts