Hacker News Reader

Comment by krat0sprakhar | Hacker News Reader

What's the best way to run a service that uses SQLite in a serverless / container environment (e.g. Cloud Run).

I'd love to use this for my personal projects and I'm not sure how to set this up in a container given their ephemeral nature.

View on HN

I run SQLite in serverless environments (Cloud Run, Vercel, Heroku) for dozens of projects... but the trick is that they all treat the database as a read-only asset.

If I want to deploy updated data, I build a brand new image and deploy the application bundled with the data. I tend to run the deploys for these (including the database build) in GitHub Actions workflows.

This works really well, but only for applications that don't need to apply updates more than a few times an hour! If you have a constant stream of updates I still think you're better off using a hosted database like Heroku PostgreSQL or Google Cloud SQL.

One example of a site I deploy like that is https://datasette.io/ - it's built and deployed by this GitHub Actions workflow here: https://github.com/simonw/datasette.io/blob/main/.github/wor...

I've seen some folks use Litestream [1] to continuously replicate to S3 storage and restore it later after their container is killed. There are also other serverless platforms like Fly[2] that provide persistent disk and long-running (but cheap) container VMs.

Disclaimer: I'm the author of Litestream.

[1]: https://litestream.io/

[2]: https://fly.io/

If your app is in a Docker container, you need to have some way to mount a persistent volume for the database. AWS Fargate and fly.io both offer storage volumes.

I doubt you can have a writable filesystem with any server less offering.

Serverless offerings force you to have all your state network accessible so that they can offer you horizontal scalability by using load balancers - so no writable+persistable file system that SQLite needs.

What I do is use a normal VM on GCP, run my docker container on that, and write to a regionally replicated disk (so it is copied between zones on every write). Snapshots run hourly and are stored in GCP storage. This gets you high reliability (if you can have a few mins down time to reboot) but not horizontal scalability.

You can check out Dokku, which is the open source, single server Heroku equivalent. You can mount a storage volume to the container and access it from your app running in Docker. Dokku makes it stupid simple, so it might be worth reading the source code to see how they do that.

I use Dokku, but it also has a Postgres plugin which is basically just a few commands. That also gives you the niceties of dokku pg:backup etc. If you’re on Dokku anyway then that’s what I would recommend.

For me, SQLite remains the perfect file format for command line tools.

Reply on news.ycombinator.com