Why SQLite Is Taking over with Brian Holt and Marco Bambini

Re:Why SQLite Is Taking over with Brian Holt and Marco Bambini by eli on Wednesday June 12, 2024 @12:47PM (#40660188)

You could simply turn off WAL for a simple application database, if you were really worried. You lose some performance and scale, but it should work fine.

[ Parent | on HN ]

Re:Why SQLite Is Taking over with Brian Holt and Marco Bambini by fauigerzigerk on Wednesday June 12, 2024 @03:00PM (#40661676)

Setting the journal_mode to OFF or MEMORY is a sure-fire way of corrupting the database. It's not fine.

https://sqlite.org/pragma.html#pragma_journal_mode

[ Parent | on HN ]

Re:Why SQLite Is Taking over with Brian Holt and Marco Bambini by eli on Wednesday June 12, 2024 @03:42PM (#40662160)

If the application crashes in the middle of a writing a transaction, yes. You can work around this if you care to, and I don't think it's anything specific to SQLite. What happens if your application crashes in the middle writing out a JSON file?

I had actually meant JOURNAL_MODE=DELETE, though. This would not technically use a WAL file, though there would be a rollback journal file that exists only for the duration of the transaction.

[ Parent | on HN ]

Re:Why SQLite Is Taking over with Brian Holt and Marco Bambini by fauigerzigerk on Wednesday June 12, 2024 @04:28PM (#40662721)

>What happens if your application crashes in the middle writing out a JSON file?

The usual way to deal with this is to write to a temporary file and then atomically replace the original file with that temporary file. You could do the same thing with an SQLite database. This is the workaround I was referring to earlier.

journal_mode=delete may not be the worst compromise, but I hate the idea of exposing users to potential database corruption, even in a relatively unlikely event.

[ Parent | on HN ]