When I inherited a project that had to work with SQLite on the server side (before Heroku had postgres) I just kept a dedicated thread and used a Queue to perform writes by monkey patching save, delete and update methods. The tiny patch renamed write related functions, queued the write operation for the thread to consume and waited on a condition variable. Once condition was set by the thread, it returned the result or rethrew the exception. This basically got rid of write collisions and enabled me to use SQLite instead of a dedicated db server on any Ruby on Rails production environment where you don’t have to scale for the whole web. I was so ashamed that I never turned it into a gem.
I implemented a very similar pattern for queuing writes to SQLite in Datasette - I talk about that in some detail here: https://architecturenotes.co/datasette-simon-willison/#asyncio-threading-and-database-connections
Thanks, lots of good information. I got internal server error when I clicked the datasette_plugin_fly link.
Kinda surprised hat rails wouldn’t have an sqlite gem or something.
Django is nice, out of the box it’s set up for sqlite so you can do stuff locally very quickly (of course this doesn’t really work server side in the docker world, and non-docker… python is a big pain in the butt)
Oh it did, but writes weren’t queued, so you would get db file locked errors. That situation got better with WAL, but not completly solved until I started manually queueing all writes.
Django is heavily inspired from Rails. Agreed that python is a bit of a pain. I would avoid it if it didn’t have such a nice typing module and the free pycharm community.