Unfortunately backing up with a simply file copy operation isn't guaranteed to work if you have write traffic at the same time.

Instead you need to use the .backup mechanism or the VACCUM INTO command, both of which safely create a backup copy of your database in another file - which you can then move anywhere you like.

Not really, it still works exactly as you should expect: writes are handled atomically, using a separate file, so if you copy the database file "mid-write" you're not actually copying a database in flux, you're copying the database before the write operation gets applied, and SQLite applies the write after the copy lock is gone.

The main issue here is time: because copying temporarily locks the db out of further changes, it will be "out of sync" if you naively believe that performing a write means your next call will see that data, and you bake that assumption into your code. While there's a wait, on modern hardware using SQLite for its intended purposes (any volume of reads, but low volume of writes), that's just not an issue.

There are other problems associated with file-based backup, of course, such as missing out on in-memory data, or corruptions caused by power outages, but those problems only exist if we were to copy a db once. Backups run regularly, and any data we miss out on the current pass, we'll get on the next pass.

Basically: when used for the purpose that SQLite was created for, file copies are a perfectly fine backup strategy that really only shows its limitations when you start to push your project into "SQLite isn't really appropriate here anymore" territory.

(having said that: the fact that VACUUM can be run into a new file is super nice, and everyone should know that it exists)

SQLite uses advisory locks but the `cp` command won't adhere to those so you can get a copy that contains only some of the writes from a transaction. If you're lucky, you just get some wonky data. If you're unlucky, you can get a corrupted copy.

Transactions are only atomic from the perspective of other transactions. Other processes or commands on the system can see partial state. This applies to both the rollback journal & the WAL modes.

File copies are also covered in their "How to Corrupt a SQLite Database File" web page[1].

[1]: https://www.sqlite.org/howtocorrupt.html#_backup_or_restore_...

Depends on the file system. On Windows you can use shadow copy for example.

Oh man, I didn't know about VACCUM INTO, thanks!

Every database is ultimately on the file system but there’s a reason that method of backup is rarely used.

Except not every database was designed from the ground up to BE a file, that's one of Sqlite's benefits. A database is by definition a single, fully self-contained file. Unless you go out of your way to make Postgres, MySQL, MariaDb, etc. do this, that is very much not the case.

For SQLite, "copying files", rolling file backups, shadow volumes etc. are by definition valid strategies when it comes to SQLite.

Postgres lets you write directly to block. In practice, nobody does this though.