[tomjen3]

Use WAL (yes this should be the default, or at least explained much better) and you can have one writer, many readers.

Don't move to the network unless you have to - every single request gets massively slowed down because it has replaced local reads with network connections.

Of course if you are building a startup you must consider scaling.

[Kalanos]

I tried WAL, but like you said, it froze up during multiple writes (when creating records, not updating). I guess what I needed was a write queue - not sure if that exists.

[tomjen3]

I am curious what is your write pattern? How long do you keep the transactions open for?

There are definitely cases where a single lock does not fit. Even though you can only write one at a time, the transactions ideally should be open for a very short period. That way the throughput, as measured in writes per second, should still be really high.

If there is some other network call in there, SQLite is not a good fit for it and you should definitely move to some other database.

As for write queue, SQLite has that built in if you do transactions — it should unlock other transactions when one is closed.

If you specify IMMEDIATE as the transaction type, it should try to get an exclusive write lock and will timeout after the default time of 30 seconds.