3MW ({dbplyr} combines the best of R & SQL)

In partnership with

Enjoy 3 Minute Wednesdays at no cost.

This newsletter is brought to you for free. If you want to advance your R skills and support my work at the same time, then you will like my paid offerings:

Or use my affiliate code “RAPP10” to get 10% off the fantastic data science courses from Athlyticz:

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.

Click to Support 👇️ 

Hey friends 👋 I’m experimenting with ads in my newsletter to finance all the free stuff I give away. If you enjoy this newsletter and want to support it, a click on the the ad below would be a super simple but effective way to do so. 🤗 

No need to sign up. A click is enough support 🥳 

You’ve never experienced business news like this.

Morning Brew delivers business news the way busy professionals want it — quick, clear, and written like a human.

No jargon. No endless paragraphs. Just the day’s most important stories, with a dash of personality that makes them surprisingly fun to read.

No matter your industry, Morning Brew’s daily email keeps you up to speed on the news shaping your career and life—in a way you’ll actually enjoy.

Best part? It’s 100% free. Sign up in 15 seconds, and if you end up missing the long, drawn-out articles of traditional business media, you can always go back.

Alright, now back to the good stuff.

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:

Reply

or to participate.