Hacker News Reader

Comment by ramraj07 | Hacker News Reader

I'm constantly scratching an itch to use it in a project, has anyone done so at any level of scale?

I can't say it's "any level of scale," but I used this for https://diyband.net and it was very straightforward for the needs of a hobby-scale web site.

The pitfalls are:

1. It's SQLite, with everything that entails. The schema migration story is "SSH into your production machine and run your migration scripts."

2. Every deploy to a new machine involves downloading your database from S3. Zero-downtime deploys aren't possible because you need to pause writes, sync, and then download the DB to the new container or whatever. If your database is gigabytes (mine is kilobytes), this could be a deal breaker.

Honestly, if managed databases were as cheap as SQLite hosting, I'd just use a managed database, because the SQLite ops workflows are too weird. But I'm confident in the reliability and performance of the site, and it is genuinely cheaper to not run a managed database, which is important for hobby projects.

(Standard internet comment disclaimer that I am a fallible human who might have missed something or am somehow doing it wrong...)

Too many teams I know still log into the database to migrate manually for many SaaS apps and the most well intentioned apps still have unexpected downtime so I don't see how a minute of scheduled downtime at 3 am can be a bad thing!

I use SQLite/Litestream for https://extensionpay.com! Serves about 120m requests per month (most of those are cached and don't hit the db), but it's been great!

I have no affiliation with Litestream but I was convinced that SQLite could be a viable db option from this great post about it called Consider SQLite: https://blog.wesleyac.com/posts/consider-sqlite

Using SQLite with Litestream helped me to launch the site quickly without having to pay for or configure/manage a db server, especially when I didn't know if the site would make any money and didn't have any personal experience with running production databases. Litestream streams to blackblaze b2 for literally $0 per month which is great. I already had a backblaze account for personal backups and it was easy to just add b2 storage. I've never had to restore from backup so far.

There's a pleasing operational simplicity in this setup — one $14 DigitalOcean droplet serves my entire app (single-threaded still!) and it's been easy to scale vertically by just upgrading the server to the next tier when I started pushing the limits of a droplet. DigitalOcean's "premium" intel and amd droplets use NVMe drives which seem to be especially good with SQLite.

One downside of using SQLite is that there's just not as much community knowledge about using and tuning it for web applications. For example, I'm using it with SvelteKit and there's not much written online about deploying multi-threaded SvelteKit apps with SQLite. Also, not many example configs to learn from. By far the biggest performance improvement I found was turning on memory mapping for SQLite.

Happy to answer any questions you might have!

Thanks a lot, this is roughly the path I was also hoping to take and happy to hear it's not unfounded. I am planning on a python backend though which does both add more documentation in some ways but adds performance issues in others. But I'll be happy to worry about the performance problem when it arises!

My goal is also to try and create an app that I don't expect to immediately be successful so it has to be cheap to run in the long term!

I use it to create realtime backups of game databases. in my app each game has it's own sqlite database to store game state and litestream creates a real time copy of the database on cloud storage. if a game server crashes, i recover using the litestream copy.

This model ends up with a sort of db-per-user/team model no?

My questions are around that. Are there good solutions for migrations? Has anyone made it relatively easy to handle in terms of deploy/scale/manage?

I've been working on a system for systematically applying migrations to SQLite databases here, inspired (a bit) by how Django migrations work: https://github.com/simonw/sqlite-migrate

I've used it in a few places myself, but I've not yet released a non-beta version of it. I'm close to having the confidence to say other people should use it too!

Why should it be a db per user though? All we have to do is ensure we have a single machine doing all the db writes, which might not be a terrible idea for simple apps..

Reply on news.ycombinator.com