Hacker News Reader

Comment by bravura | Hacker News Reader

I would love to use SQLite for all my Django webapps that have only several simultaneous users, but this article suggests there are too many footguns for me to be able to do that.

Is there a "using SQLite for a multi-threaded webapp for dummies" package that does all the config I need so I can just drop it in and go and not tune anything?

Paging fly.io founders etc! If I have a persistent volume can my fly.io apps use SQLite? What are other good micro-hosting options?

View on HN

IMO, the config you need is:

1) When you open the database:

    pragma journal_mode = wal;
    pragma synchronous = normal;

2) When you want to do a transaction that does writes, use `BEGIN IMMEDIATE`, not `BEGIN`.

3) Don't have long-running transactions.

4) Have some process to do backups.

(3) might be a big ask for some systems. Long-running transactions should be avoided even in systems like Postgres, but on a SQLite system with writers, they're the difference between an amazing experience and a garbage one.

I'm hopeful that Fly can eventually make (4) painless by having super-easy out-of-the-box litestream and S3 backups. Until then, roll your own cron scripts or what-have-you.

Thank you for the thoughtful response.

I was looking at https://github.com/irskep/cheapo_website from commenter irskep above, and they make a nice point that render.com has automatic daily backups, solving 4)

However, in another comment they mention "You can't(?) run migrations from another process" and that "people don't talk about the completely ordinary need to run migrations on a database".

I guess this is also the piece that I'm missing. How do I run migrations? Do I deploy a new version with the migration and temporarily take down the server? I'm glad to do that.

I guess I'm also walking through this because---as I said---I'd love just to switch to SQLite but I'm still not sure how many simple non-esoteric gotchas will pop up.

You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes.

A single transaction that does a very large write will likely impact the reader -- the reader will be blocked while the write finishes.

I use SQLite in a web scraper on my laptop. The scraper runs as 16 processes hammering the database, doing about 5,000 write transactions/sec. Occasionally, simple SELECT queries experience high latency (what would be a 1ms query takes 100-200ms), because they're blocked while the WAL gets checkpointed into the main database.

If you have much lower volume of writes, the checkpoint is smaller and so completes much faster, and so the worst case latency is much better. Since crawling is a non-interactive process, I don't care about the worst case latency. If I was writing a website, I'd feel differently--but most websites won't do 5,000 writes/second.

> You can run migrations from another process. Migrations are just writes, and SQLite supports writes from multiple processes.

Thank you for explicitly saying this! I'll see if I can update my repo to allow for online migrations. I was trying to be as conservative as possible based on what I knew at the time. It'll be nice to update the migration steps to be simpler.

BTW, for schema migrations, I've found the approach described here [1] to be pretty good!

[1]: https://david.rothlis.net/declarative-schema-migration-for-s...

> Migrations are just writes

It is possible to change the schema of a SQLite db as "just a write"?

Yes. SQLite supports transactional DDL.

Thanks! The article someone posted in another comment in this thread seemed a lot more complicated than that.

    pragma journal_mode = wal;
    pragma synchronous = normal;

I think it's worth noting that this combination loses committed transactions on kernel crash / power failure / etc. That is, your app replied "OK" to the client, but the data still gets lost.

> A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash.

https://www.sqlite.org/pragma.html#pragma_synchronous

You can certainly put a SQLite database on a Fly.io persistent volume. You can use Litestream to keep that SQLite database backed up, transaction-by-transaction, on other volumes or in S3; soon, you'll be able to use LiteFS to replicate that SQLite database as well.

I'm extremely excited about LiteFS. Looking forward to trying it out once write forwarding lands. I think it has the potential to make hobby projects scale to zero but also serve reasonable traffic, which is exactly what I'm looking for. Thanks so much for hiring Ben!

It would probably be harder to bend Django to use SQLite as a backend then it would be to just setup MySQL or PostGRES and use the existing Django tooling for it.

Doesn't django use sqlite by default?

Yes it does[0]. Which makes the parent comment even more hilarious.

> By default, the configuration uses SQLite.

--- 0: https://docs.djangoproject.com/en/4.1/intro/tutorial02/

Agreed perhaps, but I still don't want to devops Postgres and I'd rather having tiny hosting costs for tiny apps. So I think there's a definite need here for this tooling.

I'm not convinced this is true. Django supports SQLite out of the box, and otherwise database management is an exercise for the reader. The migration tools, for example, work fine with SQLite.

Why? It’s been supported since the 2000s and is popular for test suites so it gets more exercise than you might think.

https://docs.djangoproject.com/en/4.1/ref/databases/

I take it you're not aware that the default database of new Django projects created by the admin commands is SQLite?

It's been at least 3 years since I did anything with django and that was with mysql.

Ignore this, I was wrong. Django supports sqlite just fine. Last time I did anything with django I was an intern and just copied the setup from the readme which happened to include a mysql setup. I have not touched django since.

Reply on news.ycombinator.com