5 ms·

I have tried adopting SQLite in my side projects. The problem I encountered is that using managed PostgreSQL/MySQL is still more convenient and more reliable th

I have tried adopting SQLite in my side projects. The problem I encountered is that using managed PostgreSQL/MySQL is still more convenient and more reliable than using SQLite on a bare metal VPS. I like to use Heroku or Digital Ocean App platform because I want to spend time creating and not managing the infrastructure (ci/cd, ssl certs, reverse proxy, db backup, scaling, container management and what not). I tried looking for a managed SQLite but could not find one. On an unrelated note I found using Redis a good lightweight alternative to the classical psql/MySQL. Although still multi-tier and more difficult to model data, it’s initially cheaper and easier to manage than its relational counterparts. Anyone has had similar setup/preference?

cheeselip420 5y ago
What would a managed sqlite even look like? I can't tell if this is a real response or not...
- Something1234 5y ago
  Maybe an NFS mount or something that handles back ups automatically? Scripting to handle an automatic restore of the database? Maybe a heroku that knows about your database file and automatically loads the latest version for you? I kind of feel like GP is a troll comment, as there's no real value add for a managed SQLite.
  - config_yml 5y ago
    Or just use litestream, it‘s perfect for this and the closest you can get to managed by replicating to S3 or Cloud Storage or the likes.
    - spiffytech 5y ago
      Beware that Litestream on PaaS has safety concerns unless you can get your PaaS platform to guarantee that the active app instance is terminated before a new instance is booted. Litestream doesn't turn SQLite into an multi-master distributed system. If two copies of the DB accept writes at the same time, Litestream will just send backups to two different backup generations, and when you restore you'll only pull down one of those generations and won't receive the writes that landed in the other generations. All of your writes will still be in S3, they'll just be peppered across distinct backup snapshots and you can't get all the data back unless you separately restore all relevant generations and manually merge the restored databases.
- DizzyDoo 5y ago
  When hankchinaski says 'managed' I think they really mean that there's some capital-A App dashboard somewhere, on Digital Ocean or wherever, and they log in and click 'new database' and that's it. No ssh-ing to a VPS, and choosing the file location where the sqlite file will sit, figuring out backups and so on. But as you say, while you can wrap postgres or redis in that sort of 'just take care of it for me' approach, given the simplicity of sqlite it doesn't fit that paradigm, so perhaps hankchinaski is just misunderstanding what sqlite fundamentally is and how it works.
  - hankchinaski 5y ago
    Exactly. I was expressing the limitation of that single tier paradimg viewed from an angle of someone who doesn’t want to ssh into machines to configure and deploy code... :)
- hankchinaski 5y ago
  I clearly did not express myself correctly. Being all these managed solutions (heroku, do app platform) running on an ephemeral fs it is not possible to use disk as long term storage as sqlite requires. So I’m basically forced to manage an attached disk to my container which needs to be backed up, monitored and so on
deleted 5y ago
[deleted]

cheeselip420 5y ago

What would a managed sqlite even look like? I can't tell if this is a real response or not...

Something1234 5y ago
Maybe an NFS mount or something that handles back ups automatically? Scripting to handle an automatic restore of the database? Maybe a heroku that knows about your database file and automatically loads the latest version for you? I kind of feel like GP is a troll comment, as there's no real value add for a managed SQLite.
- config_yml 5y ago
  Or just use litestream, it‘s perfect for this and the closest you can get to managed by replicating to S3 or Cloud Storage or the likes.
  - spiffytech 5y ago
    Beware that Litestream on PaaS has safety concerns unless you can get your PaaS platform to guarantee that the active app instance is terminated before a new instance is booted. Litestream doesn't turn SQLite into an multi-master distributed system. If two copies of the DB accept writes at the same time, Litestream will just send backups to two different backup generations, and when you restore you'll only pull down one of those generations and won't receive the writes that landed in the other generations. All of your writes will still be in S3, they'll just be peppered across distinct backup snapshots and you can't get all the data back unless you separately restore all relevant generations and manually merge the restored databases.
DizzyDoo 5y ago
When hankchinaski says 'managed' I think they really mean that there's some capital-A App dashboard somewhere, on Digital Ocean or wherever, and they log in and click 'new database' and that's it. No ssh-ing to a VPS, and choosing the file location where the sqlite file will sit, figuring out backups and so on. But as you say, while you can wrap postgres or redis in that sort of 'just take care of it for me' approach, given the simplicity of sqlite it doesn't fit that paradigm, so perhaps hankchinaski is just misunderstanding what sqlite fundamentally is and how it works.
- hankchinaski 5y ago
  Exactly. I was expressing the limitation of that single tier paradimg viewed from an angle of someone who doesn’t want to ssh into machines to configure and deploy code... :)
hankchinaski 5y ago
I clearly did not express myself correctly. Being all these managed solutions (heroku, do app platform) running on an ephemeral fs it is not possible to use disk as long term storage as sqlite requires. So I’m basically forced to manage an attached disk to my container which needs to be backed up, monitored and so on

deleted 5y ago

[deleted]