> sqlite in its default (journal_mode = DELETE) is not durable.

Not true. In its default configuration, SQLite is durable.

If you switch to WAL mode, the default behavior is that transactions are durable across application crashes (or SIGKILL or similar) but are not necessarily durable across OS crashes or power failures. Transactions are atomic across OS crashes and power failures. But if you commit a transaction in WAL mode and take a power loss shortly thereafter, the transaction might be rolled back after power is restored.

This behavior is what most applications want. You'll never get a corrupt database, even on a power loss or similar. You might lose a transaction that happened within the past second or so. So if you cat trips over the power cord a few milliseconds after you set a bookmark in Chrome, that bookmark might not be there after you reboot. Most people don't care. Most people would rather have the extra day-to-day performance and reduced SSD wear. But if you have some application where preserving the last moment of work is vital, then SQLite provides that option, at run-time, or at compile-time.

When WAL mode was originally introduced, it was guaranteed durable by default, just like DELETE mode. But people complained that they would rather have increased performance and didn't really care if a recent transaction rolled back after a power-loss, just as long as the database didn't go corrupt. So we changed the default. I'm sorry if that choice offends you. You can easily restore the original behavior at compile-time if you prefer.

For DELETE (rollback) mode, and given the way fsync works, FULL should not lose committed transactions (plural) but it might lose at most one committed transaction.

Because DELETE depends on deleting a file (not modifying file contents), it much depends on the specific file system's (not SQLite's) journaling behavior.

> That said, the documentation about this is pretty confusing.

I now filed a suggestion to clarify the docs on this:

https://sqlite.org/forum/forumpost/ec171a77a3

It isn’t worth it. Mostly financial transactions are done via append only ledgers, not updating; two phase auth and then capture; a settlement process to actually move money, and a reconciliation process to check all the accounts and totals. Even without DB corruptions they have enough problems (fraud and buggy code) with creating money and having to go back and ask people to more money or to give them more money so they have those systems in place any ways.

SERIALIZABLE isn't zero-surprise, since applications must be prepared to retry transactions under that isolation level. There is no silver bullet here.

A CRUD architecture with proper ACID is an OK contender against other possible architectures. Personally I always go for event-sourcing (a.k.a. WAL per the article's title).

But a CRUD that doesn't do ACID properly is crap. And since the people making these decisions don't understand event-sourcing or that they're not being protected by ACID, CRUD gets chosen every time.

The DB also won't be set to SERIALIZABLE because it's too slow.