7 ms·

Maybe I misunderstand what this is, but why would I use this and not MySQL, Postgres, or any other proper database? Seems like a hack to get SQLite to do what t

Maybe I misunderstand what this is, but why would I use this and not MySQL, Postgres, or any other proper database? Seems like a hack to get SQLite to do what those do by design.

victorbjorklund 1y ago
Why use Postgres if all you need is sqlite? Postgres is way overkill for a simple app with few users and no advanced database functionality.
- TemptedMuse 1y ago
  I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.
  - victorbjorklund 1y ago
    Why would you argue that? Do you have some benchmarks backing it up or is it more a personal preference?
    - crazygringo 1y ago
      It's literally what they're designed for. SQLite is designed for one local client at a time. Client-server relational databases are designed for many clients at a time.
      - simonw 1y ago
        That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem. If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine. SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately. (Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )
        skeeter2020 1y ago
        the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.
        crazygringo 1y ago
        > 2. duplicate the entire environment for the next n clients That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only. And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying. That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.
      - victorbjorklund 1y ago
        Not true. Can you back up your claim that the developers of Sqlite says they dont recommend it for webservers? (hint they recommend it). If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.
        crazygringo 1y ago
        I didn't say that. I said one local client at a time. If you're running on one server then your webserver is the one local client. Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.
- vmg12 1y ago
  Let's say I'm building a small app that I'm hosting on some shared vps, if I think about the effort involved in setting up sqlite with litestream and just getting a $5 (or free) postgres provider I don't think sqlite makes my life easier. Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.
  - bccdee 1y ago
    Litestream is dead simple to setup. You make an S3 bucket (or any compatible storage bucket), paste the access keys and the path to your db file in /etc/litestream, and then run dpkg -i litestream.deb systemctl enable litestream systemctl start litestream The fact it's so simple is my favourite thing about it.
    - indigodaddy 1y ago
      Are there any use cases/documentation about how litestream can be used within a docker based deployment? (Eg where systemctl wouldn't be used)
      - bccdee 1y ago
        You'd probably want to put the sqlite db in a volume & run litestream in a separate container that restarts automatically on failure. Systemctl's only in there to restart it if it crashes; litestream itself is (iirc) a single cli binary.
      - airblade 1y ago
        This is documented on the Litestream website.
      - simonw 1y ago
        Here's their docs on running in a Docker container: https://litestream.io/guides/docker/ https://litestream.io/guides/docker/
  - victorbjorklund 1y ago
- yellow_lead 1y ago
  Because Postgres is mature, works, and has a version number above v1.0?
  - zwnow 1y ago
    Version numbers dont mean anything as the whole Elixir ecosystem shows:D
  - teeray 1y ago
    If v1.0 is your North Star, you should re-evaluate a whole lot of software in your stack: https://0ver.org/#notable-zerover-projects https://0ver.org/#notable-zerover-projects
    - 8organicbits 1y ago
      I think you're focusing on the wrong parts of the comment. People care about things like long-term support. Postgres 13, from 2020, is still officially supported. Litestream 0.1.0 was the first release, also from 2020, but I can't tell if it is supported still. Worrying about the maturity, stability, and support of an application database is very reasonable in risk adverse projects.
      - victorbjorklund 1y ago
        Litestream is just a backup solution. Should probably be compared to a backup solution for postgres that does automated backups over the network etc. That isnt part of postgres. Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.
        threatofrain 1y ago
        The original response at least concerned litestream because the not-1.0 comment only applies to that.
  - victorbjorklund 1y ago
    I'm guessing this is a joke?
- danenania 1y ago
  For a cloud service, I think it comes down to whether you’ll ever want more than one app server. If you’re building something as a hobby project and you know it will always fit on one server, sqlite is perfect. If it’s meant to be a startup and grow quickly, you don’t want to have to change your database to horizontally scale. Deploying without downtime is also much easier with multiple servers. So again, it depends whether you’re doing something serious enough that you can’t tolerate dropping any requests during deploys.
  - victorbjorklund 1y ago
    99,99% of apps dont need more than one app server. You can serve a lot of traffic on the larges instances. For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.
    - danenania 1y ago
      Right, but if your goal is to have a lot of users (and minimal downtime), there's no point in putting a big avoidable obstacle in your path when the alternative is just as easy.
      - victorbjorklund 1y ago
        If your goal is to serve billions of users you should probably use cassandra etc. Why limit yourself to postgres if your goal is to have a billion users online at the same time?
        danenania 1y ago
        Because cassandra isn't easy to set up and has all kinds of tradeoffs on consistency, transactions, et al compared to an SQL db. On the other side, why not just store everything in memory and flush to a local json file if you won't have any users? sqlite is overkill!
        skeeter2020 1y ago
kblissett 1y ago
One of the big advantages people enjoy is the elimination of the network latency between the application server and the DB. With SQLite your DB is right there often directly attached over NVME. This improves all access latencies and even enables patterns like N+1 queries which would typically be considered anti-patterns in other DBs.
- turnsout 1y ago
  Real talk, how do you actually avoid N+1? I realize you can do complicated JOINs, but isn't that almost as bad from a performance perspective? What are you really supposed to do if you need to, e.g. fetch a list of posts along with the number of comments on each post?
  - vhcr 1y ago
    No, JOINs are pretty much always faster than performing N+1 queries.
  - erpellan 1y ago
    You do indeed use JOINS. The goal is to retrieve exactly the data you require in a single query. Then you get the DB to `EXPLAIN VERBOSE` or similar and ensure that full table scans aren't happening and that you have indexed the columns the query is being filtered on.
  - simjnd 1y ago
    AFAIK the problem of N+1 isn't necessarily one more DB query, but one more network roundtrip. So if for each page of your app you have an API endpoint that provides exactly all of the data required for that page, it doesn't matter how many DB queries your API server makes to fulfill that request (provided that the API server and the DB are on the same machine). This is essentially what GraphQL does instead of crafting each of these super tailored API endpoints for each of your screens, you use their query language to ask for the data you want, it queries the DB for you and get you the data back in a single network roundtrip from the user perspective. (Not an expert, so I trust comments to correct what I got wrong)
manishsharan 1y ago
I have a branch office in boondocks with limited internet connection. The branch office cannot manage a RDBMS or access cloud services. They can use sqlite app on LAN and we could do reconciliation at end of the business day.
- skeeter2020 1y ago
  they can also run the entire application in these scenarios on the resources of a 10-yr-old phone.
andrewmutz 1y ago
I'm not sure, I've never done it, but I think the idea is to have many tiny customer-specific databases and move them to be powered by sqlite very close to the customer. But I'd love to hear more from someone more well-versed in the use cases for reliable sql-lite
sauercrowd 1y ago
It's a good question, and I don't think answered sufficiently in the recent sqlite hype. In my opinion if you have an easy way to run postgres,MySQL,... - just run that. There's usually a lot of quirks in the details of DB usage (even when it doesn't immediately seem like it - got bitten by it a few times). Features not supported, different semantics, ... IMO every project has an "experimental stuff" budget and if you go over it it's too broken to recover, and for most projects there's just not that much to win by spending them on a new database thing
- skeeter2020 1y ago
  >> the recent sqlite hype. This is an interesting take; why do you see recent hype around the most boring and stone-age of technologies, SQLite?
  - sauercrowd 1y ago
    The rails creator dhh has been hyping it up a lot in the first 6 month of this year, and quite a few followed of the "Dev influencers" scene. Fly's litestream came out around that time, and there's been more sqlite in the cloud companies/discussions, in particular with the AI agent use-case. Not super sure who followed who but there was all of a sudden a lot of excitement
    - simonw 1y ago
      Litestream's first release was February 2021: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776 SQLite's "buzz" isn't new, type "sqlite" into my https://tools.simonwillison.net/hacker-news-histogram https://tools.simonwillison.net/hacker-news-histogram tool and you'll see interest (on HN at least) has been pretty stable since 2021.
      - nirvdrum 1y ago
        Maybe it's a local bump, but it sure seems like SQLite has become a fair more popular topic in the Rails world. I wouldn't expect to find it in a HN search tool. SQLite has gone from the little database you might use to boostrap or simplify local development to something products are shipping with in production. Functionality like solid_cable, solid_cache, and solid_queue allow SQLite to be used in more areas of Rails applications and is pitched as a way to simplify the stack. While I don't have stats about every conference talk for the last decade, my experience has been that SQLite has been featured more in Rails conference talks. There's a new book titled "SQLite on Rails: The Workbook" that I don't think would have had an audience five years ago. And I've noticed more blog posts and more discussion in Rails-related discussion platforms. Moreover, I expect we'll see SQLite gain even more in popularity as it simplifies multi-agent development with multiple git worktrees.
WorldMaker 1y ago
The common answer (especially from Fly.io) is "at-the-edge" computing/querying. There is network latency involved in sending a query to MySQL or Postgres and getting the data returned, whereas with Litestream you could put a read replica of the entire SQLite DB at every edge. Queries become fast and efficient only to the local read replica. There's still network latency associated with updating that read replica over time, but it is amortized based on the number of overall writes rather than the number of queries, is more fault tolerant in "eventually consistent" workflows (you can answer queries from the read replica at the edge in the state that you have it while you wait for the network to reconnect and replay the writes you missed during the fault), and with SQLite backing it still has much of the same full relational DB query power of SQL you would expect from a larger (or "proper") database like MySQL or Postgres.
- SchwKatze 1y ago
  Law Theorem[1] fits perfectly for this scenario 1- https://law-theorem.com/ https://law-theorem.com/
tptacek 1y ago
It's significantly faster and incurs less ops overhead. That's it. But most apps should just use a classic n-tier database architecture like Postgres. We mostly do too (though Litestream does back some stuff here like our token system).
bob1029 1y ago
I find myself mostly in this camp now. In every case where I had a SQLite vertical that required resilience, the customer simply configured the block storage device for periodic snapshots. Litestream is approximately the same idea, except you get block device snapshots implicitly as part of being in the cloud. There is no extra machinery to worry about and you won't forget about a path/file/etc. Also, streaming replication to S3 is not that valuable an idea to me when we consider the recovery story. All other solutions support hot & ready replicas within seconds.
canadiantim 1y ago
To enable local-first or offline-first design. I prefer having data stored on-device and only optionally backed up to cloud
bccdee 1y ago
Whatever database you end up using, you'll need some sort of backup solution. Litestream is a streamed backup solution which effectively doubles as replication for durability purposes. MySQL, Postgres, etc. have a much greater overhead for setup, unless you want to pay for a managed database, which is not going to be worth the price for small quantities of data.
arbll 1y ago
To avoid operating a database by yourself and dealing with incidents, backups, replicas, failovers, etc... You can use cheap commoditised S3-like storage and run your application statelessly. If you have access to a database that is well managed on your behalf I would definitely still go with that for many usecases.
Quarrelsome 1y ago
this is infra for a single-user app. SQLite is THE replacement for file databases like MSAccess, but the box goes down and your database dies with all your data. So this fills that gap by giving you a database as a service level of QOL without needing to provision a database as a service backend. Otherwise you're dicking about maintaining a service with all that comes with that (provisioning, updating, etc) when really all you need is a file that is automagically backed up or placed somewhere on the web to avoid the drawbacks of the local file system.
- trallnag 1y ago
  But aren't many single-user apps still multi-platform? For example as an Android application but also as a web app the user might access from his desktop device?
  - Quarrelsome 1y ago
    Ye that's fine because same user access wont be concurrent. We're avoiding data corruption or the need to take out an expensive and broad write lock.
- kristianp 1y ago
  Sqlite and msaccess can and have often been used with multiple users. I have experience with the latter in the 2000s with Access on a network share.
  - Quarrelsome 1y ago
    it's not the correct solution for multiple users. If you want that then you should be running a database as a service. > If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
kristianp 1y ago
An argument for using postgres is that you can still use one server, and postrgres has multithreading which allows for more performance.

Maybe I misunderstand what this is, but why would I use this and not MySQL, Postgres, or any other proper database? Seems like a hack to get SQLite to do what those do by design.

victorbjorklund 1y ago

Why use Postgres if all you need is sqlite? Postgres is way overkill for a simple app with few users and no advanced database functionality.

TemptedMuse 1y ago
I'd argue that anything larger than a desktop app should not use SQLite. If you need Litestream for replication and backup it is probably better to just use Postgres. There are a ton of one-click deployment offerings for proper databases, Fly.io actually offers managed Postgres.
- victorbjorklund 1y ago
  Why would you argue that? Do you have some benchmarks backing it up or is it more a personal preference?
  - crazygringo 1y ago
    It's literally what they're designed for. SQLite is designed for one local client at a time. Client-server relational databases are designed for many clients at a time.
    - simonw 1y ago
      That's not entirely true. SQLite is designed to support many processes reading the same file on disk at once. It only allows one process to write at a time, using locks - but since most writes finish in less than a ms in most cases having a process wait until another process finishes their write isn't actually a problem. If you have lots of concurrent writes SQLite isn't the right solution. For concurrent reads it's fine. SQLite also isn't a network database out-of-the-box. If you want to be able to access it over the network you need to solve that separately. (Don't try and use NFS. https://sqlite.org/howtocorrupt.html#_filesystems_with_broken_or_missing_lock_implementations https://sqlite.org/howtocorrupt.html#_filesystems_with_broke... )
      - skeeter2020 1y ago
        the reality is very few workloads have access patterns that SQLite can't support. I would much rather start with a strategy like 1. use sqlite for my beta / single client, 2. duplicate the entire environment for the next n clients, 3. solve the "my application is wildly successful" and SQLite is no longer appropriate problem at a future date. Spoiler: you're never going to get to step #3.
        crazygringo 1y ago
        > 2. duplicate the entire environment for the next n clients That becomes an instant problem if users ever write to your database. You can't duplicate the environment unless it's read-only. And even if the database is read-only for users, the fact that every time you update it you need to redeploy the database to every client, is pretty annoying. That's why it's usually better to start with Postgres or MySQL. A single source of truth for data makes everything vastly easier.
    - victorbjorklund 1y ago
      Not true. Can you back up your claim that the developers of Sqlite says they dont recommend it for webservers? (hint they recommend it). If you have a read-heavy app (99% of saas) that runs on one server and dont have millions of users then sqlite is a great option.
      - crazygringo 1y ago
        I didn't say that. I said one local client at a time. If you're running on one server then your webserver is the one local client. Usually you want to be able to run multiple webservers against a single database though, since that's the first thing you'll usually need to scale.
vmg12 1y ago
Let's say I'm building a small app that I'm hosting on some shared vps, if I think about the effort involved in setting up sqlite with litestream and just getting a $5 (or free) postgres provider I don't think sqlite makes my life easier. Now if I'm building a local app then absolutely sqlite makes the most sense but I don't see it otherwise.
- bccdee 1y ago
  Litestream is dead simple to setup. You make an S3 bucket (or any compatible storage bucket), paste the access keys and the path to your db file in /etc/litestream, and then run dpkg -i litestream.deb systemctl enable litestream systemctl start litestream The fact it's so simple is my favourite thing about it.
  - indigodaddy 1y ago
    Are there any use cases/documentation about how litestream can be used within a docker based deployment? (Eg where systemctl wouldn't be used)
    - bccdee 1y ago
      You'd probably want to put the sqlite db in a volume & run litestream in a separate container that restarts automatically on failure. Systemctl's only in there to restart it if it crashes; litestream itself is (iirc) a single cli binary.
    - airblade 1y ago
      This is documented on the Litestream website.
    - simonw 1y ago
      Here's their docs on running in a Docker container: https://litestream.io/guides/docker/ https://litestream.io/guides/docker/
- victorbjorklund 1y ago
yellow_lead 1y ago
Because Postgres is mature, works, and has a version number above v1.0?
- zwnow 1y ago
  Version numbers dont mean anything as the whole Elixir ecosystem shows:D
- teeray 1y ago
  If v1.0 is your North Star, you should re-evaluate a whole lot of software in your stack: https://0ver.org/#notable-zerover-projects https://0ver.org/#notable-zerover-projects
  - 8organicbits 1y ago
    I think you're focusing on the wrong parts of the comment. People care about things like long-term support. Postgres 13, from 2020, is still officially supported. Litestream 0.1.0 was the first release, also from 2020, but I can't tell if it is supported still. Worrying about the maturity, stability, and support of an application database is very reasonable in risk adverse projects.
    - victorbjorklund 1y ago
      Litestream is just a backup solution. Should probably be compared to a backup solution for postgres that does automated backups over the network etc. That isnt part of postgres. Besides the question wasnt litestream vs postgres backup apps. It was sqlite vs postgres.
      - threatofrain 1y ago
        The original response at least concerned litestream because the not-1.0 comment only applies to that.
- victorbjorklund 1y ago
  I'm guessing this is a joke?
danenania 1y ago
For a cloud service, I think it comes down to whether you’ll ever want more than one app server. If you’re building something as a hobby project and you know it will always fit on one server, sqlite is perfect. If it’s meant to be a startup and grow quickly, you don’t want to have to change your database to horizontally scale. Deploying without downtime is also much easier with multiple servers. So again, it depends whether you’re doing something serious enough that you can’t tolerate dropping any requests during deploys.
- victorbjorklund 1y ago
  99,99% of apps dont need more than one app server. You can serve a lot of traffic on the larges instances. For sure downtime is easier with kubernete etc but again overkill for 99,99% of apps.
  - danenania 1y ago
    Right, but if your goal is to have a lot of users (and minimal downtime), there's no point in putting a big avoidable obstacle in your path when the alternative is just as easy.
    - victorbjorklund 1y ago
      If your goal is to serve billions of users you should probably use cassandra etc. Why limit yourself to postgres if your goal is to have a billion users online at the same time?
      - danenania 1y ago
        Because cassandra isn't easy to set up and has all kinds of tradeoffs on consistency, transactions, et al compared to an SQL db. On the other side, why not just store everything in memory and flush to a local json file if you won't have any users? sqlite is overkill!
      - skeeter2020 1y ago

kblissett 1y ago

One of the big advantages people enjoy is the elimination of the network latency between the application server and the DB. With SQLite your DB is right there often directly attached over NVME. This improves all access latencies and even enables patterns like N+1 queries which would typically be considered anti-patterns in other DBs.

turnsout 1y ago
Real talk, how do you actually avoid N+1? I realize you can do complicated JOINs, but isn't that almost as bad from a performance perspective? What are you really supposed to do if you need to, e.g. fetch a list of posts along with the number of comments on each post?
- vhcr 1y ago
  No, JOINs are pretty much always faster than performing N+1 queries.
- erpellan 1y ago
  You do indeed use JOINS. The goal is to retrieve exactly the data you require in a single query. Then you get the DB to `EXPLAIN VERBOSE` or similar and ensure that full table scans aren't happening and that you have indexed the columns the query is being filtered on.
- simjnd 1y ago
  AFAIK the problem of N+1 isn't necessarily one more DB query, but one more network roundtrip. So if for each page of your app you have an API endpoint that provides exactly all of the data required for that page, it doesn't matter how many DB queries your API server makes to fulfill that request (provided that the API server and the DB are on the same machine). This is essentially what GraphQL does instead of crafting each of these super tailored API endpoints for each of your screens, you use their query language to ask for the data you want, it queries the DB for you and get you the data back in a single network roundtrip from the user perspective. (Not an expert, so I trust comments to correct what I got wrong)

manishsharan 1y ago

I have a branch office in boondocks with limited internet connection. The branch office cannot manage a RDBMS or access cloud services. They can use sqlite app on LAN and we could do reconciliation at end of the business day.

skeeter2020 1y ago
they can also run the entire application in these scenarios on the resources of a 10-yr-old phone.

andrewmutz 1y ago

I'm not sure, I've never done it, but I think the idea is to have many tiny customer-specific databases and move them to be powered by sqlite very close to the customer. But I'd love to hear more from someone more well-versed in the use cases for reliable sql-lite

sauercrowd 1y ago

It's a good question, and I don't think answered sufficiently in the recent sqlite hype. In my opinion if you have an easy way to run postgres,MySQL,... - just run that. There's usually a lot of quirks in the details of DB usage (even when it doesn't immediately seem like it - got bitten by it a few times). Features not supported, different semantics, ... IMO every project has an "experimental stuff" budget and if you go over it it's too broken to recover, and for most projects there's just not that much to win by spending them on a new database thing

skeeter2020 1y ago
>> the recent sqlite hype. This is an interesting take; why do you see recent hype around the most boring and stone-age of technologies, SQLite?
- sauercrowd 1y ago
  The rails creator dhh has been hyping it up a lot in the first 6 month of this year, and quite a few followed of the "Dev influencers" scene. Fly's litestream came out around that time, and there's been more sqlite in the cloud companies/discussions, in particular with the AI agent use-case. Not super sure who followed who but there was all of a sudden a lot of excitement
  - simonw 1y ago
    Litestream's first release was February 2021: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776 SQLite's "buzz" isn't new, type "sqlite" into my https://tools.simonwillison.net/hacker-news-histogram https://tools.simonwillison.net/hacker-news-histogram tool and you'll see interest (on HN at least) has been pretty stable since 2021.
    - nirvdrum 1y ago
      Maybe it's a local bump, but it sure seems like SQLite has become a fair more popular topic in the Rails world. I wouldn't expect to find it in a HN search tool. SQLite has gone from the little database you might use to boostrap or simplify local development to something products are shipping with in production. Functionality like solid_cable, solid_cache, and solid_queue allow SQLite to be used in more areas of Rails applications and is pitched as a way to simplify the stack. While I don't have stats about every conference talk for the last decade, my experience has been that SQLite has been featured more in Rails conference talks. There's a new book titled "SQLite on Rails: The Workbook" that I don't think would have had an audience five years ago. And I've noticed more blog posts and more discussion in Rails-related discussion platforms. Moreover, I expect we'll see SQLite gain even more in popularity as it simplifies multi-agent development with multiple git worktrees.

WorldMaker 1y ago

The common answer (especially from Fly.io) is "at-the-edge" computing/querying. There is network latency involved in sending a query to MySQL or Postgres and getting the data returned, whereas with Litestream you could put a read replica of the entire SQLite DB at every edge. Queries become fast and efficient only to the local read replica. There's still network latency associated with updating that read replica over time, but it is amortized based on the number of overall writes rather than the number of queries, is more fault tolerant in "eventually consistent" workflows (you can answer queries from the read replica at the edge in the state that you have it while you wait for the network to reconnect and replay the writes you missed during the fault), and with SQLite backing it still has much of the same full relational DB query power of SQL you would expect from a larger (or "proper") database like MySQL or Postgres.

SchwKatze 1y ago
Law Theorem[1] fits perfectly for this scenario 1- https://law-theorem.com/ https://law-theorem.com/

tptacek 1y ago

It's significantly faster and incurs less ops overhead. That's it. But most apps should just use a classic n-tier database architecture like Postgres. We mostly do too (though Litestream does back some stuff here like our token system).

bob1029 1y ago

I find myself mostly in this camp now. In every case where I had a SQLite vertical that required resilience, the customer simply configured the block storage device for periodic snapshots. Litestream is approximately the same idea, except you get block device snapshots implicitly as part of being in the cloud. There is no extra machinery to worry about and you won't forget about a path/file/etc. Also, streaming replication to S3 is not that valuable an idea to me when we consider the recovery story. All other solutions support hot & ready replicas within seconds.

canadiantim 1y ago

To enable local-first or offline-first design. I prefer having data stored on-device and only optionally backed up to cloud

bccdee 1y ago

Whatever database you end up using, you'll need some sort of backup solution. Litestream is a streamed backup solution which effectively doubles as replication for durability purposes. MySQL, Postgres, etc. have a much greater overhead for setup, unless you want to pay for a managed database, which is not going to be worth the price for small quantities of data.

arbll 1y ago

To avoid operating a database by yourself and dealing with incidents, backups, replicas, failovers, etc... You can use cheap commoditised S3-like storage and run your application statelessly. If you have access to a database that is well managed on your behalf I would definitely still go with that for many usecases.

Quarrelsome 1y ago

this is infra for a single-user app. SQLite is THE replacement for file databases like MSAccess, but the box goes down and your database dies with all your data. So this fills that gap by giving you a database as a service level of QOL without needing to provision a database as a service backend. Otherwise you're dicking about maintaining a service with all that comes with that (provisioning, updating, etc) when really all you need is a file that is automagically backed up or placed somewhere on the web to avoid the drawbacks of the local file system.

trallnag 1y ago
But aren't many single-user apps still multi-platform? For example as an Android application but also as a web app the user might access from his desktop device?
- Quarrelsome 1y ago
  Ye that's fine because same user access wont be concurrent. We're avoiding data corruption or the need to take out an expensive and broad write lock.
kristianp 1y ago
Sqlite and msaccess can and have often been used with multiple users. I have experience with the latter in the 2000s with Access on a network share.
- Quarrelsome 1y ago
  it's not the correct solution for multiple users. If you want that then you should be running a database as a service. > If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html

kristianp 1y ago

An argument for using postgres is that you can still use one server, and postrgres has multithreading which allows for more performance.