[graemep]

Sqlite is a bad fit for anything where ephemeral storage is the default. On the other hand is you use a simple VPS there is no problem.

There are multiple simple ways of doing SQLite backups https://sqlite.org/lang_vacuum.html#vacuuminto https://sqlite.org/rsync.html - or just lock and copy the file.

If you need to scale enough that it is a concern, then its not a good fit for your use case.

[degamad]

> If you need to scale enough that it is a concern, then its not a good fit for your use case.

If you need to scale writes.

[andersmurphy]

You can hit 40000-80000+writes/s with sqlite on a 10$ VPS just by batching transactions (i.e wrapping all inserts/updates in a single transaction every 100ms). This is easy to do at the application level, then you also avoid BUSY/LOCK.

I'd argue writes scale better wtih sqlite than postgresql.

[hu3]

I love SQLite but how would batching work in CRUD apps where you need to rollback a dozen SQL inserts/updates in case of error in a request?

Also I often need to read-after-write during the same request, using transactions.

And rails apps are often CRUDy.

[andersmurphy]

With a single writer (as it the case with sqlite). You don't need transactions and rollbacks. As all writes happen in sequence.

Each batch item can be a combination of read/write/update that happen in sequence and therefore can give you the same semantics as a traditional transaction/rollback. eg:

- read -> Does the account have enough funds?

- write -> transfer 100$ from this user to another account

This is also much simpler to write than in other databases as you don't have to worry about n+1.

[graemep]

I definitely want transactions and rollbacks even if writes happen in sequence.

To go with your example, take something like

1) add $100 to this user's account 2) add $100 to the service fees account 3) deduct $101 from the other user's account to cover these

Must all happen or none.

[andersmurphy]

The batch is still atomic (as it's wrapped in a database transaction). So you batch items will never partially happen (say in the case of a crash).

You do have to write your batch items so that they check their own constraints though. I.e check the accounts have funds etc.