Just been reading this great blogpost again:
From my patchy understanding, it seems like there are a lot of upsides and no real downsides to this approach - having SQLite built into each app instance and sync’d with Litestream - compared to eg. the standard Fly.io approach of postgresql servers in a leader-replica arrangement. If your database is part of your app process, you don’t have to worry about database servers. And it makes read queries so fast you don’t need to worry about batching them up into as few transactions as possible, leading to more maintainable code. It sounds amazing. I want to try it.
But there must be some downsides? Why isn’t everyone doing it? Are there hidden complexities with maintaining it? Is it incompatible with other things like ORM systems?
I’ve been using Postgresql for mutable data and SQLite for fixed data in my application with a view to switch everything to SQLite in the future.
Off the top of my head:
Having separate database pools for read vs write queries is pretty standard and will work fine on a single node. The issue for me is if I move to multiple nodes with SQLite, I will also have to write code to handle queries over the network. This may not be the case for other applications, but as I’m using Phoenix Liveview the way that Fly potentially re-submits queries(fly-replay) to the write-only postgresql node won’t work the same way as the initial connection was over a websocket.
GUI admin. If you like having a GUI admin which connects to a remote server, well, again, there’s no network connection.
For read-only replicas, backups, etc. the replication lag and monitoring will be very new. Not sure how complex to maintain compared to MySQL for instance.
Regarding 1 - it looks like this repo could be repurposed for sqlite and using RPC.
@brainlid - are there any plans for a separate fly_sqlite_elixir library?