6 ms·
Hey! I'm the author of the article. Saw a sudden influx of traffic from HN and found the post here. Happy to answer any questions. Although SQLite is so dead-s
by markusw 4y ago
Hey! I'm the author of the article. Saw a sudden influx of traffic from HN and found the post here.
Happy to answer any questions. Although SQLite is so dead-simple-but-awesome that you probably don't have any. :D
Anyway, hope you enjoy it and learn a thing or two.
Markus
- longcommonname 4y agoWould you provide a summary of how you noticed the increased traffic and how you found this post?
- markusw 4y agoSaw the request spike, searched HN for "sqlite".
- yandrypozo 4y agoNice 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 :)
- markusw 4y agoI don’t know pocketbase, will have a look.
- nileshtrivedi 4y agoI'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 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.
- 0cf8612b2e1e 4y agoA 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/ https://datasette.io/
- nileshtrivedi 4y agoAFAIK, Datasette only offers read-only APIs.
- 0cf8612b2e1e 4y agoNot anymore [0]. The announcement [1] [0]: https://docs.datasette.io/en/latest/json_api.html#the-json-write-api https://docs.datasette.io/en/latest/json_api.html#the-json-w... [1]: https://simonwillison.net/2022/Dec/2/datasette-write-api/ https://simonwillison.net/2022/Dec/2/datasette-write-api/
- deleted 4y ago[deleted]
- LVB 4y agoI've not use this myself, but Ben Johnson's https://github.com/benbjohnson/postlite 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.
- Cwizard 4y agoHave you tried pocketbase?
- iccananea 4y agoNot exactly what you want, but I've been using https://sqlc.dev https://sqlc.dev to generate hydration code from SQL queries and loving it!
- 4y ago
- marktangotango 4y agoI 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.
- sharps_xp 4y agoYou 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.
- belmont_sup 4y agoIt 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.
- thomascgalvin 4y agoConcurrent 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.
- markusw 4y ago@levelsio famously runs a dozen websites all backed by SQLite, with 200M reqs / month: https://twitter.com/levelsio/status/1520356430800617472 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-qps-on-a-single-server/ https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q... I would call those serious production apps.
- thomascgalvin 4y ago
- maxpert 4y agoHey, I am author of Marmot (https://github.com/maxpert/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!
- markusw 4y agoFeel free to reach out, markus@maragu.dk
- mathewvp 4y agoI 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.
- markusw 4y agoThe 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.