7 ms·

Does someone know if there is something like an SQLite server, so that it could be used like a 'normal' database server? Why? Well, for benchmarking for exampl

Does someone know if there is something like an SQLite server, so that it could be used like a 'normal' database server? Why? Well, for benchmarking for example, or if the application you are using has a bad multi-user implementation and you don't want to migrate to another database...

miohtama 6y ago
AFAIK SQLite can do only one transaction commit at the same time even if the commits do not clash. Thus, it is not useful for multiple writing users use cases.
- simonw 6y ago
  But those commits are FAST - tens of thousands of writes a second, easily. So if you want to handle multiple writing users at once, put the writes in an in-memory queue and send them to SQLite as fast as it will accept them.
  - jlokier 6y ago
    If you mean tens of thousands of commits a second, it can only go that fast on SSD or battery-backed HDD. write+sync is a speed limiter in general (even on SSDs), because of waiting for the syncs which are needed for commit durability. On HDD, serial transactions can't go faster than roughly twice the rotation time of the HDD per write, plus seeks, and on network filesystems they can't go faster than four times the network latency (two syncs per commit) plus whatever the backing store needs. The trick to getting high commit throughput is to allow the durability part of multiple independent transactions to be committed concurrently. On a filesystem-backed DB this means writing multiple transactions per fdatasync. It would be tricky for SQLite to do this because of its architecture as a library and synchronous API, but not impossible.
- stephen82 6y ago
  I guess with the introduction of BEGIN CONCURRENT resolves this. https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...
goatinaboat 6y ago
Does someone know if there is something like an SQLite server, so that it could be used like a 'normal' database server? $ ssh dbserver -c sqlite3 /path/to/database.db Is as close as you’re going to get. You can have multiple users doing this but all transactions are serialised so don’t expect concurrent writes to be performant - concurrent SELECTs will be fine.
- leicmi 6y ago
  Possibly Bedrock fits your needs: https://bedrockdb.com https://bedrockdb.com "Bedrock [..] is a networking and distributed transaction layer built atop SQLite" "... That query can be any SQLite-compatible query – including schema changes, foreign key constraints, partial indexes, native JSON expressions, or any of the tremendous amount of functionality SQLite offers."
siscia 6y ago
I am the one behind https://RediSQL.com https://RediSQL.com It is a Redis module that embed SQLite. The idea is to run medium complexity project on top of a single runtime dependencies, Redis. You will find Redis in most applications anyway, and for a simple crud application sqlite is more than enough. In this way you get rid of a lot of systems complexity. And I works both in memory (crazy fast) or with standard files.
zbentley 6y ago
Perhaps dqlite? https://dqlite.io/ https://dqlite.io/
simonw 6y ago
My Datasette project is leaning in that direction: it's a web application that provides a JSON API (or GraphQL with the datasette-graphql plugin) to execute read queries against your database, and I've been adding write functionality to it as well. https://docs.datasette.io https://docs.datasette.io
3np 6y ago
What could be really useful would be that can be exposed as a unix socket in a way that is transparent to sqlite, ie it still believes it's working with a file. Don't know if this is possible and just how horribly it would perform, but it could be legit useful if it can solve e.g. the remote fs conondrum mentioned in other comments.
petercooper 6y ago
Yes. https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite

miohtama 6y ago

AFAIK SQLite can do only one transaction commit at the same time even if the commits do not clash. Thus, it is not useful for multiple writing users use cases.

simonw 6y ago
But those commits are FAST - tens of thousands of writes a second, easily. So if you want to handle multiple writing users at once, put the writes in an in-memory queue and send them to SQLite as fast as it will accept them.
- jlokier 6y ago
  If you mean tens of thousands of commits a second, it can only go that fast on SSD or battery-backed HDD. write+sync is a speed limiter in general (even on SSDs), because of waiting for the syncs which are needed for commit durability. On HDD, serial transactions can't go faster than roughly twice the rotation time of the HDD per write, plus seeks, and on network filesystems they can't go faster than four times the network latency (two syncs per commit) plus whatever the backing store needs. The trick to getting high commit throughput is to allow the durability part of multiple independent transactions to be committed concurrently. On a filesystem-backed DB this means writing multiple transactions per fdatasync. It would be tricky for SQLite to do this because of its architecture as a library and synchronous API, but not impossible.
stephen82 6y ago
I guess with the introduction of BEGIN CONCURRENT resolves this. https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

goatinaboat 6y ago

Does someone know if there is something like an SQLite server, so that it could be used like a 'normal' database server? $ ssh dbserver -c sqlite3 /path/to/database.db Is as close as you’re going to get. You can have multiple users doing this but all transactions are serialised so don’t expect concurrent writes to be performant - concurrent SELECTs will be fine.

leicmi 6y ago
Possibly Bedrock fits your needs: https://bedrockdb.com https://bedrockdb.com "Bedrock [..] is a networking and distributed transaction layer built atop SQLite" "... That query can be any SQLite-compatible query – including schema changes, foreign key constraints, partial indexes, native JSON expressions, or any of the tremendous amount of functionality SQLite offers."

siscia 6y ago

I am the one behind https://RediSQL.com https://RediSQL.com It is a Redis module that embed SQLite. The idea is to run medium complexity project on top of a single runtime dependencies, Redis. You will find Redis in most applications anyway, and for a simple crud application sqlite is more than enough. In this way you get rid of a lot of systems complexity. And I works both in memory (crazy fast) or with standard files.

zbentley 6y ago

Perhaps dqlite? https://dqlite.io/ https://dqlite.io/

simonw 6y ago

My Datasette project is leaning in that direction: it's a web application that provides a JSON API (or GraphQL with the datasette-graphql plugin) to execute read queries against your database, and I've been adding write functionality to it as well. https://docs.datasette.io https://docs.datasette.io

3np 6y ago

What could be really useful would be that can be exposed as a unix socket in a way that is transparent to sqlite, ie it still believes it's working with a file. Don't know if this is possible and just how horribly it would perform, but it could be legit useful if it can solve e.g. the remote fs conondrum mentioned in other comments.

petercooper 6y ago

Yes. https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite