I'd like to avoid writing boilerplate CRUD code. Is there an equivalent of PostgREST but for SQLite? Essentially, a standard binary would read the schema metadata, and generate standard CRUD APIs: https://postgrest.org/en/stable/api.html

The APIs can even support authentication and authorization with the help of JWT tokens. SQLite may not have row-level security, but even a convention (eg: if a row has user_id column, the JWT must have the same user_id value to get access to a row) would go a long way.

Not exactly what you want, but I've been using https://sqlc.dev to generate hydration code from SQL queries and loving it!

I've not use this myself, but Ben Johnson's https://github.com/benbjohnson/postlite in front of SQlite might allow you to use PostgREST? I recall him saying on a podcast that his goal was to be able to point the large ecosystem of PG tools at SQlite.

A long way off from PostgREST, but you might be able to hack together something sort of similar with Datasette [0] and the new JSON api. Not out of the box at all, but the potential is there to get a similar product.

[0]: https://datasette.io/

AFAIK, Datasette only offers read-only APIs.

Not anymore [0]. The announcement [1]

[0]: https://docs.datasette.io/en/latest/json_api.html#the-json-w...

[1]: https://simonwillison.net/2022/Dec/2/datasette-write-api/

Yes there is but not FOSS

https://github.com/subzerocloud/showcase/tree/main/flyio-sql...

Have you tried pocketbase?

Nice article, what is your opinion on pocketbase.io which match your title really well, it would be great a follow-up article for LiteFS and pocketbase :)

I don’t know pocketbase, will have a look.

Hey, I am author of Marmot (https://github.com/maxpert/marmot) would love to get in touch and explore a few things with you. I’ve been using Marmot in production in a while scaling couple of services, and since you mentioned LiteFS I would love to pick your brain, and explore couple of ideas with you. Multiple ways to get in touch including Github discussions or discord channel. Hope to see you around!

Feel free to reach out, [email protected]

I suggest you run some simple load tests that do inserts, you'll find WAL is not sufficient for concurrency. Multiple reader, single writers handle this well though.

You can put all your writes in a queue to be processed by a finite set of writers. I think most CRUD applications can afford the delay and if not you can pair it with a write through cache.

It sounds simpler to setup postgres and never have to set up a queue (unless you’re using an in memory queue, and that has its drawbacks).

Although I definitely would like to use SQLite just for the cost savings for something. Litefs/litestream looks great.

I was surprised at the amount of performance SQLite provided. I get about 700 inserts/sec with the WAL enabled sitting behind a Django app.

https://imgur.com/a/a3U41Zo

Without the WAL enabled, I get a around 400req/sec.

edit: clarity

Concurrent writes are the bane of SQLite's existence, but for a one-person blog, you shouldn't run into any issues.

You would have trouble scaling up to fives of authors, though, which would be a deal breaker for any serious production app.

@levelsio famously runs a dozen websites all backed by SQLite, with 200M reqs / month: https://twitter.com/levelsio/status/1520356430800617472

Expensify got 4 million request _per second_ out of a custom SQLite-based setup (on a huge machine, but still): https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

I would call those serious production apps.

I specifically cited concurrent writes. SQLite is fantastic for read-only data. Those websites are using SQLite for what it's designed for. But, if they were trying to use SQLite to do even dozens of writes per second, it would crash and burn.

Well, that claim is fairly easy to test. So I did: https://gist.github.com/markuswustenberg/0030360d7d4679f2656...

Nothing fancy, just a small table that holds some blog posts with an ID, a title, some content, and a creation timestamp.

I ran the benchmark with and without WAL enabled on my Macbook Air (2020, M1) with some SSD drive inside. Results:

  $ make benchmark 
  go test -bench=.
  goos: darwin
  goarch: arm64
  pkg: sqlite
  BenchmarkWriteBlogPost/write_blog_post_without_WAL-8              6441     191735 ns/op
  BenchmarkWriteBlogPost/write_blog_post_with_WAL-8               102559      11205 ns/op
  PASS
  ok   sqlite 3.725s

That's around 89k writes per second in parallel on all available cores with WAL enabled. I know this is a trivial setup, but adjust to your liking. You'll find that SQLite probably doesn't crash and burn with dozens of writes.

This is single-threaded, which a production app would not be. That's when things get scary with SQLite.

Yes, I was incorrect when I said SQLite couldn't handle lots of writes quickly; what I should have said is that it can't handle lots of writes from multiple threads quickly.

Okay, fair enough. I thought my previous test was parallel as well, but I was mistaken.

Here's one for 64 parallel writes: https://gist.github.com/markuswustenberg/f35ab7e191137dca5f7...

  $ make benchmark
  go test -bench=.
  goos: darwin
  goarch: arm64
  pkg: sqlite
  BenchmarkWriteBlogPost/write_blog_post_without_WAL-8               100   16317415 ns/op
  BenchmarkWriteBlogPost/write_blog_post_with_WAL-8                 1196    1198679 ns/op
  BenchmarkWriteBlogPost/write_blog_post_with_WAL_and_Go_mutex-8             58455      17461 ns/op
  PASS
  ok   sqlite 4.557s

1e9/1198679 = 834 writes per second is still far from crash and burn territory when using just WAL mode.

Of course, it gets more interesting when there are also concurrent readers, as people are trying out elsewhere in the discussion. But the point still stands: it can handle way, waaaay better than "fives of authors" in a not-at-all scary way.

You should duplicate this test against postgres inserts. I did the same thing for sqlite; in go using a mutex on inserts, and sqlite still out performs postgres on inserts (on the same machine).

I'm not understanding this. Wouldn't the lower bound on performance be the single thread? Any worse performance and you queue the write requests to match the single thread performance.

No, because of SQLite's locking mechanism. It uses filesytem-level constructs to support concurrency, and this gets dicey, quickly.

As my test above shows, use a mutex for writes in Go directly and whatever locking performance problems SQLite exhibits disappear. I suspect because of the polling used in SQLite as described elsewhere on this page.

I have a very basic question regarding SQLite. How does it work in different languages? Is the SQLite server code completely implemented in Go when we use go and completely implemented in php when we use php? Because there is no actual server and it is just a file, I'm wondering who does the ACID compliancy and query executions and all the other things needed for a db server and how it is done.

The implementation used in this article is actually in C (which is the official, extremely well-tested one), with bindings to the Go language through something called CGo. There's no "server", it's all just code run in your application directly, so think of it more as a library that can access a special database file on your disk.

I don't know how it's done in PHP, I would think it's similar.

Would you provide a summary of how you noticed the increased traffic and how you found this post?

Saw the request spike, searched HN for "sqlite".