As long as the connections are from the same machine (local filesystem), it's fine.

Reads are concurrent, writes block reads (and other writes) while they write. (There's also something called WAL mode that enables reads during a write, which uses multiple files for each database.)

They don't just get blocked, they error out. This is a well defined error condition, so you can detect it easily, but it's not going to happen if you stick to the one connection rule. This means you have to intentionally think about handling it everywhere if you want to use another connection anywhere.

Edit: looks like you can set up blocking on the client using pragma busy_timeout [1] so the above is only true in default configuration

[1]: https://www.sqlite.org/pragma.html#pragma_busy_timeout

> This means you have to intentionally think about handling it everywhere if you want to use another connection anywhere.

so things like mutexes (one process, multiple threads) or mutexes over shared memory (multiple processes, multiple threads) ?

sounds complicated (and error-prone).

but at the end of the day i guess that kind of juggling has to be done somewhere, either a database server does it for you or you do it yourself in the database client.

i wouldn't mind sqlite on the server, but i'm not sure I would want to trade zero-downtime-deployments (spin up new version, move load from old to new, spin down old version) for single-file ease of operations.

You don't have to really think about it anywhere.

It's handled by the file lock. You don't need to track anything.

Yes there is a timeout value that will return an error if the database file hasn't unlocked in time. In Python the default timeout is 5 seconds, which is plenty if your queries execute on the order of milliseconds or tens of milliseconds.

We do zero-downtime deployments with a single Docker volume containing the db. Spin up a container running the new code, wait til it's healthy, then kill the old container.

You definitely want WAL mode (unless you’re accessing SQLite over NFS). It’s only not the default for backwards compatibility. I’d enable:

PRAGMA journal_mode = WAL; PRAGMA foreign_keys = 1; PRAGMA mmap_size = 1099511627776;

Unless you know a reason that you can’t (like NFS or using a 32-bit cpu).

> Unless you know a reason that you can’t (like NFS or using a 32-bit cpu).

interesting, why there's that limitation when nfs and a 32-bit cpu are involved?

WAL mode requires shared memory, so doesn’t work over the network.

mmap_size = 1TiB requires 1TiB of address space per connection. This is fine on 64-bit, unless you have millions of connections. Doesn’t work with 32-bit (4GiB) address space.

You want mmap_size to be greater than the size of your database. If you know your database will be small then you could set it to a smaller number, but you could still become address space constrained with many connections.

The historical answer has generally been: it doesn't (at least, not well). The traditional recommendations were not to open multiple connections to the same file, and especially not to share the same file over a network filesystem. This remains the general advice, as far as I can tell, but it may be out of date against some newer features (like WAL mode) and better filesystems (like NFSv4).

You are still correct for network filesystems, but there are no issues opening multiple connections on the same host (even across OCI container boundaries).

There are no data corruption risks in general for multiple processes on the same local filesystem, but the lock obtained by write operations is pretty aggressive. Multiple readers are okay but a single writer will cause transient errors in all other concurrent read and write operations (edit: though you can turn this into just blocking instead with pragma busy_timeout).

> Multiple readers are okay but a single writer will cause transient errors in all other concurrent read and write operations

While in WAL mode, readers and writers don't block one another.

> WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. https://www.sqlite.org/wal.html

> Multiple readers are okay but a single writer will cause transient errors in all other concurrent read and write operations

They don't cause "transient errors", they cause transaction failures, which you should be handling by retrying the transaction anyway.

If you could retry it, then obviously the error was transient. I don't know what the issue is with me describing it that way.

But you generally won't have to write retry logic if you have the file open in only one place. The other issues that can arise, like constraint violations or no space left on disk, usually aren't transient and thus can't be fixed with retry anyway.

You can have as many connections from as many processes as you want.