[tedmiston]

I have used SQLite for similar use cases, but occasionally it's led to a corrupted db. I had a cron task writing to it once a day, but an issue with the scheduler led to 2 tasks one day with the latter one finishing before the former.

Of course I can add locking or something in my code, but I'd prefer to handle at a lower level — for example, have SQLite take the latest write without corrupting. I'm hoping someone has solved this problem with SQLite elegantly.

[dietrichepp]

SQLite is supposed to handle multiple concurrent writes by returning SQLITE_BUSY. I'm imagining hypothetical other causes for your corruption problem, like power loss at a bad moment.

[kentonv]

SQLite is designed to handle power loss at bad moments (indeed, anything that calls itself a "database" ought to be resilient against power failure).

Relevant: https://www.sqlite.org/howtocorrupt.html

[dietrichepp]

The article you linked has an interesting line: "Unfortunately, most consumer-grade mass storage devices lie about syncing." That's the kind of problem I was talking about.

[tedmiston]

This is helpful; it's possible I'm assuming too much. The corruption did happen only once and was also during a time period that the server (a PaaS) was running maintenance including some downtime.

[2muchcoffeeman]

I may be imagining things but I seem to recall that at one point you had to build your own SQLite to get safe concurrent writes, but that was made the default years ago.

[takeda]

Haven't used SQLite in a while, but this is how I did it.

You should enable WAL (pragma journal_mode=wal) I believe you need to do this on every connection.

Now, whenever you are accessing the data do it within transactions (https://www.sqlite.org/lang_transaction.html) generally SQLite will acquire proper locks when needed (it will minimize amount of locked time by deferring locks until they are needed, but you can use immediate or exclusive modifiers. If you for example put exclusive modifier only one program will be able to read/write at the time, you generally will want to use defer (default) behavior since it still provides the safety but multiple applications still can access the database at the same time)

Also note that locking might not work if you keep the database on network file system.