Guten Tag!

Many greetings from Munich, Germany. The glue_sql() function helped us last week to make our database calls safer and avoid SQL injection.

But there’s also a lot of convenience that glue_sql() offers for everyday development. So let’s check that out.

Use Curly Braces for R Variables

The first thing that’s really convenient and that you already know from last week is this: You can use curly braces {} inside your SQL string to insert R variables. This works great when the variables are just simple strings or scalar values.

And as you can see, this gives you the results you probably want:

Use Backticks for IDs

Next, let’s try to not grab all columns but only a selected one:

Well, that didn’t crash but it didn’t go as expected either. The reason for that is that you will have to tell glue_sql() that col_name (and technically also tbl_name) is an ID. You can do that with backticks inside the curly brackets.

Ahh this looks much better!

Collapse Vectors with the * Operator

If you pass a vector into a placeholder, glue_sql() by default tries to insert the whole vector resulting in multiple SQL fragments:

This is usually not what you want. Instead, you want to collapse a vector with *:

Sweet! With that you can easily get data from multiple columns:

Use IDs from different tables

Let’s imagine that our database has another table:

Then, we can make sure to reference to columns from different tables by including the table names. The Id() function helps with that:

And because we need to identify the corrsponding species columns somehow, we have to reference to the corresponding table names as well.

Now, we could easily wrap all of these IDs into backticks and everything works out nicely in assembled SQL code. Here’s an example from the glue_sql() docs for that:

And there you have it: glue_sql() will make your life as an R developer much easier. Beyond just protecting you from SQL injection, it helps you write cleaner, more flexible, and maintainable database queries from within R.

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