4 ms·

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

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?

cldellow 4y ago
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.
- bravura 4y ago
  Thank you for the thoughtful response. I was looking at https://github.com/irskep/cheapo_website 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.
  - cldellow 4y ago
    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.
    - irskep 4y ago
      > 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.
      - cldellow 4y ago
        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-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s...
    - sanderjd 4y ago
      > Migrations are just writes It is possible to change the schema of a SQLite db as "just a write"?
      - cldellow 4y ago
        Yes. SQLite supports transactional DDL.
- yencabulator 4y ago
  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 https://www.sqlite.org/pragma.html#pragma_synchronous
deathclassic 4y ago
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.
- plonq 4y ago
  Doesn't django use sqlite by default?
  - imran-iq 4y ago
    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/ https://docs.djangoproject.com/en/4.1/intro/tutorial02/
- bravura 4y ago
  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.
- irskep 4y ago
  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.
- acdha 4y ago
  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/ https://docs.djangoproject.com/en/4.1/ref/databases/
- adamckay 4y ago
  I take it you're not aware that the default database of new Django projects created by the admin commands is SQLite?
tptacek 4y ago
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.
- irskep 4y ago
  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!

cldellow 4y ago

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.

bravura 4y ago
Thank you for the thoughtful response. I was looking at https://github.com/irskep/cheapo_website 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.
- cldellow 4y ago
  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.
  - irskep 4y ago
    > 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.
    - cldellow 4y ago
      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-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s...
  - sanderjd 4y ago
    > Migrations are just writes It is possible to change the schema of a SQLite db as "just a write"?
    - cldellow 4y ago
      Yes. SQLite supports transactional DDL.
yencabulator 4y ago
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 https://www.sqlite.org/pragma.html#pragma_synchronous

deathclassic 4y ago

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.

plonq 4y ago
Doesn't django use sqlite by default?
- imran-iq 4y ago
  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/ https://docs.djangoproject.com/en/4.1/intro/tutorial02/
bravura 4y ago
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.
irskep 4y ago
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.
acdha 4y ago
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/ https://docs.djangoproject.com/en/4.1/ref/databases/
adamckay 4y ago
I take it you're not aware that the default database of new Django projects created by the admin commands is SQLite?

tptacek 4y ago

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.

irskep 4y ago
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!