Moin Moin! 👋 

Many Greetings from Munich, Germany (using a greeting from the North of Germany 🤪). Let us finish our journey from last week. Today, we’re going to unravel how to prepare your Mirai daemons so that they can use database connections.

How Database Connections Fail

We start by investigating what kind of problem we have. That’s always a good start. After all, you probably don’t want to take my word for it. So here’s an example.

Imagine you have a database connection. Here, this will just be a connection to a local database stored in a .sqlite file. But it doesn’t matter whether we use SQLite or Postgres or something else. It’s always the same issue.

Cool, we’ve got a local database. It even has our favorite penguin data stored inside of it. That’s nifty. But let’s have a look at what Mirai does when we try do use our database connection:

Loading Generics

Ahh that’s annoying. We passed our con variable into the Mirai worker like we learned last time but we still get an error. Here, the error signals that R does not know a function dbListTables() for a SQLiteConnection.

This happens even though the full function reference (using DBI::) is used. The reason for that is that the {RSQLite} package is not loaded in the worker. You see, DBI::dbListTables() is a generic function. This means that it behaves differently depending on what you stick into it.

And if you create your own type of database connection (you know, like a SQLiteConnection). Then you’ll also have to tell DBI::dbListTables() how to behave with that type of connection. And that’s what happens in the package that created that object. So we can use everywhere() like last time to load the package on all Mirai workers:

Fixing bad pointers

Two things we can notice from this:

• Nice: We made some progress. 🥳 

• Not so nice: We get a different type of error. 😭 

To understand what’s going on here, we have to understand how database connections work. The thing is: A variable like con comprises out of a lot of things:

And one of these things is an external pointer to some address in the memory of my computer.

In simplified terms, this address is where the actual engine that talks to the database lives. Unfortunately, the Mirai workers cannot reach the address that this pointer is pointing to. So that is why we get the bad pointer error message.

Establishing a new connection per worker

One way to fix that could be to simply call db::dbConnect() within the Mirai worker. Let’s try this out in combination with mirai_map().

Nice! This approach works and in the results you can see that we manage to successfully list our “penguins” table.

I’ve also taken the liberty of extracting the address of the database pointer. As you can see, this is a different one in each iteration. You’ll have to be careful there and evaluate if that’s what you want. Your database might not like to be flooded with lots of connections.

Sharing a connection across workers

Alternatively, you could use everywhere() in combination with the magical <<- assignment operator. This one assigns variables outside the confinement of the calling environment. That’s how you can persist the same connection for all workers:

See how these pointers always have the same address? That’s what you get with everywhere() here. But (once again) you’ll have to decide if that’s what you want. Now, connections are not isolated anymore and when you work with database transactions, this may or may not cause problems for you. So as always: it’s a tradeoff.

Either way, you now have a way to successfully use generics and pointy objects with Mirai. That’s something, isn’t it? Hope you enjoyed this week’s newsletter. As always, feel free to hit the reply button to share your thoughts.

Happy Coding and see you next week,
Albert

Whenever you’re ready, there are three I can help you:

1. Automate Your Data Reports: This course helps data analysts eliminate manual copy-paste reporting by automating PDF reports end-to-end, saving hours every cycle and preventing costly mistakes. (Using the lovely Typst language 😍)

2. Generate Insights in Minutes, not Hours: This comprehensive course teaches you to handle data faster, smarter, and more efficiently.

3. Bespoke Data Science Solutions: I’ve helped clients build their own data science solutions. Whether building custom web apps, PDF reports, AI automations or teaching workshops, I’ve got you covered. You can reach out to me via this form (or simply hit reply to this email)