Hacker News Reader

Comment by arendtio | Hacker News Reader

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...

View on HN

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

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.

Possibly Bedrock fits your needs: 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."

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

Perhaps dqlite? https://dqlite.io/

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.

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.

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.

I guess with the introduction of BEGIN CONCURRENT resolves this.

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

I am the one behind 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.

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.

Reply on news.ycombinator.com