DatabaseQueue in WAL mode "enough" for App + Widget?

[gopeter]

I'm building my first app and trying to wrap my head around the shared database part. To be honest, I don't fully understand all of the parts of the docs.

I have a fairly basic app where I can read and write some data. Now I want to show this data to (multiple) widgets on the homescreen. From my understanding, it should be possible to still use the DatabaseQueue that I'm using at the moment in my app code and connecting with a DatabaseQueue in WAL mode from my widget code, which just reads data from time to time.

And if this is the case, what happens if I'm adding two or three widgets on the homescreen? They would try to read from the database at the same time and I'm quite sure that I'm not able to share a connection for multiple widgets.

Any help would be much appreciated!

[groue]

Hello @gopeter,

I'm building my first app and trying to wrap my head around the shared database part. To be honest, I don't fully understand all of the parts of the docs.

That's because I'm a poor writer, and because sharing a database is HARD. I'll try to illustrate the Sharing a Database guide from the description of your app.

From my understanding, it should be possible to still use the DatabaseQueue [...] in WAL mode from my widget code, which just reads data from time to time.

Definitely, yes.

The WAL mode allows your widget to open a read-only connection, and read from the database, with very little chance of error, even if the app is toying with it (read/write) at the same time.

Since it is a read-only connection, the WAL mode has been set by the main app, when it has created the database (the WAL mode is a property of the SQLite file, not of individual SQLite connections).

The problems I can think of can happen when the read-only widget boots:

• Rarely, it won't be able to open the database with an error SQLITE_BUSY_RECOVERY. It might happen when the main app boots at the same time and recovers from a crash.

• After the user has upgraded your app, the widget may find a database that has not yet been migrated to its most recent schema version (look for migrator.hasBeenSuperseded is the guide). When this happens, the widget must wait for the main app to boot and migrate the database (or deal with the obsolete schema).

And if this is the case, what happens if I'm adding two or three widgets on the homescreen? They would try to read from the database at the same time and I'm quite sure that I'm not able to share a connection for multiple widgets.

That's fine.

Each widget runs in its own process and uses its own read-only connection to the WAL database. That's what the WAL mode is for.

---

Remains the problem of 0xDEAD10CC exception.

As soon as your app stores a WAL database in a shared container, the operating system activates a nasty little monitor that mercilessly crashes your app as soon as it gets suspended while holding a lock on the database (i.e. if a write hasn't completed).

Simple apps don't do that, except when they do. For example, the user hits "Refresh" is a screen, and sends the app to the background (because people are bored, you know). Yikes: the system decides to suspend the app right in the middle of the database write, and there you are: 💥 0xDEAD10CC.

Preventing this exception is possible, but it is not exactly trivial (background tasks that postpone app suspension, and in some apps, database suspension). I'm sorry if the guide is not very clear, but maybe give a second read.

---

In the end, if you really want to keep your app simple, DON'T SHARE THE DATABASE.

For example, what about having the main app export some JSON files that widgets will feed from?

I'm sorry sharing a database is so complicated. 0xDEAD10CC is part of the OS, not something the library has invented to make developers miserable 😅 It's the same for the file coordinators you'll find in the guide: awfully complicated and verbose, but their role is to prevent errors when multiple processes want to create or open the same file concurrently 🤷‍♂️

[gopeter]

Wow, thank you SO much for your answer! Incredible detailed answer and very useful! You should link it in the documentation 😄 I think I will stick with the JSON approach but have to figure out how to write these files periodically in the background. But that's another topic – thank you so much for your help!

[nickkohrn]

@gopeter Thank you for asking this question! I am just beginning to look at using GRDB with a main app target and widget extensions, so your questions are very much relevant.

@groue Thank you for providing such a detailed response! I am new to interacting with SQLite in a more direct fashion. I've used Core Data and SwiftData, but I'm keen to learn something new. I hadn't considered having the main app target write to a JSON file from which the widgets can read. Since I intend to refresh widgets only when a user has caused a write. Thank you!