Guten Tag!

Many greetings from Munich, Germany. In today’s newsletter, I want to teach you about database transactions. Sounds simple, but it’s actually a very crucial concept that we haven’t covered before.

You see, in many cases you’ll want to write to your database using transactions. Technically, you could just write to your database as-is. But this leaves you without any safety precautions in case something goes wrong.

For example, you could lose your internet connection in the middle of uploading a large dataset. If that happens your database ends up with only part of the data, and then you’re stuck trying to figure out:

• What was successfully transferred?

• What’s still only on your local machine?

These kinds of situations can easily leave your database in a broken or inconsistent state. And that’s really really frustrating to deal with.

So that’s where transactions come in. Let’s dive in.

What Are Transactions?

Transactions allow you to tell your database ahead of time

“Hey, I’m going to write something. I’ll let you know when I’m done. Don’t finalize anything until I say so.”

If everything works fine, you “commit” the transaction, and the data gets saved. If not, you “roll it back,” and it’s like nothing ever happened.

Sounds simple, right? Well, it is. You just have to use this nifty little safety feature. Your database will thank you.

How to Use Transactions in R

It’s surprisingly easy to use transactions in R when working with databases. You start a transaction with:

Then, you perform your usual data writing operations. Let’s assume we’re just going to add a few rows:

If everything goes well, you finalize the changes with:

Or if anything goes wrong, you can cancel the whole transaction, i.e. everything you did since you called dbBegin():

Why Is This Helpful?

This is especially useful when you’re working with multiple tables.

Let’s say you need to insert data into one table and then into another table that references the first table. Let’s try this:

Notice how the second call failed? That happened because I put restrictions on the second table whereas the first one doesn’t have them.

Either way, that’s when we call dbRollback(). And in order for all of this to happen automatically, we can wrap the whole procedure into a tryCatch() statement.

And with that, you’ve got a new set of tools to interact with databases safely and reliably. Use transactions whenever you write to a database, especially when multiple related operations are involved. Your future self (and your data) will thank you!

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