5 ms·

When I start a project, my priority will be SQLite>PostgreSQL>Others. If the application only runs on one server, then definitely SQLite. That means you do not

When I start a project, my priority will be SQLite>PostgreSQL>Others. If the application only runs on one server, then definitely SQLite. That means you do not need fail over, replication or other fancy features. Why not simple SQLite? A complex system is more likely to have problems. Network, TLS, runtime, your application may have so many reasons to crash. But for SQLite, just check IO. If it does not have problem, then you check your application.

chipdart 2y ago
> If the application only runs on one server, then definitely SQLite. That means you do not need fail over, replication or other fancy features. Why not simple SQLite? I agree. So many usecases require a bounded context, and even ephemeral data stores. Memory caches are all the rage but the cost of doing network calls to fetch data from a memory cache can eclipse the cost of simply fetching the data from a local disk.
- ammo1662 2y ago
  I experienced that an application randomly throw exceptions when trying to access the SQL Server in the same PC. And finally we found that it is caused by a broken firewall software. A traditional DBMS is a quite complex application, sometimes using that may introduce more bugs than your application itself.
TheRoque 2y ago
Well, since the SQLite database is one simple file, isn't SQLite a hassle when you have some concurrency/parallelism ? And what if you have separate services that need to access the same DB ?
- adamtaylor_13 2y ago
  Nope! That’s what the write-ahead log (WAL) is for: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html Of course, if you experience scaling issues there (you probably won’t if it’s anything less than enterprise-level usage), you can always just add a second db file!
  - TheRoque 2y ago
    I see. Wasn't aware of this. I considered taking SQLite for my current stack, but dismissed it because I've read lot of bad stuff with concurrency. However, it says that it doesn't work over networks, so basically you can't do WAL with a containerized stack ?
    - hruk 2y ago
      You can have multiple containers on the same host use the same db via Docker volumes - we have a number of production services set up this way.
    - adamtaylor_13 2y ago
      As hruk pointed out, you can use docker volumes to solve for this. However, you can also ship multiple DBs depending on your use-case. If it's a shared DB, it's probably not a great idea for micro-services. But if you're building a majestic monolith, there's few reasons NOT to go with SQLite. Especially paired with litestream[0]. 0: https://litestream.io/ https://litestream.io/
- ammo1662 2y ago
  > isn't SQLite a hassle when you have some concurrency/parallelism If you decide to put your application on a single server, that means you do not care about the single point failure, and all your workload can be handled by the single server. So you can just run only one instance of your application, then you will not have such kind of problem. Even if you want to have multiple instances of your application running on the same machine, SQLite can also handle that. > what if you have separate services that need to access the same DB? That means one single server cannot handle the workload. If the bottleneck is in the database module, a cluster is required to process the data. Clearly the SQLite is not a good option in this case. If it is not that case, you can separate the database module and provide a lightweight wrapper around the SQLite to create a database service. And use multiple instances for calculation, then call the single database service instance for persistance. I think comparing to the single instance of MySQL/Postgres/SQL Server, the performance of SQLite is not too bad. So we should keep the architecture simple, if possible.
  - TheRoque 2y ago
    Well, running multiple instances of the same app is not only about "points of failure", it's also about using all the cores of your machine. Typically, using PI (nodejs), Docker or Uvicorn (python), you can spin up multiple instances of your backend to handle more concurrently.
    - Filligree 2y ago
      SQLite connections can be used concurrently inside a single process. You can spin up more threads instead, and spare yourself the filesystem locking overhead.
      - TheRoque 2y ago
        Threads in Python in python are locked to a single core, and in NodeJS it's really annoying to pull off. That's why people use PM2, Uvicorn and Gunicorn.
        2y ago

chipdart 2y ago

> If the application only runs on one server, then definitely SQLite. That means you do not need fail over, replication or other fancy features. Why not simple SQLite? I agree. So many usecases require a bounded context, and even ephemeral data stores. Memory caches are all the rage but the cost of doing network calls to fetch data from a memory cache can eclipse the cost of simply fetching the data from a local disk.

ammo1662 2y ago
I experienced that an application randomly throw exceptions when trying to access the SQL Server in the same PC. And finally we found that it is caused by a broken firewall software. A traditional DBMS is a quite complex application, sometimes using that may introduce more bugs than your application itself.

TheRoque 2y ago

Well, since the SQLite database is one simple file, isn't SQLite a hassle when you have some concurrency/parallelism ? And what if you have separate services that need to access the same DB ?

adamtaylor_13 2y ago
Nope! That’s what the write-ahead log (WAL) is for: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html Of course, if you experience scaling issues there (you probably won’t if it’s anything less than enterprise-level usage), you can always just add a second db file!
- TheRoque 2y ago
  I see. Wasn't aware of this. I considered taking SQLite for my current stack, but dismissed it because I've read lot of bad stuff with concurrency. However, it says that it doesn't work over networks, so basically you can't do WAL with a containerized stack ?
  - hruk 2y ago
    You can have multiple containers on the same host use the same db via Docker volumes - we have a number of production services set up this way.
  - adamtaylor_13 2y ago
    As hruk pointed out, you can use docker volumes to solve for this. However, you can also ship multiple DBs depending on your use-case. If it's a shared DB, it's probably not a great idea for micro-services. But if you're building a majestic monolith, there's few reasons NOT to go with SQLite. Especially paired with litestream[0]. 0: https://litestream.io/ https://litestream.io/
ammo1662 2y ago
> isn't SQLite a hassle when you have some concurrency/parallelism If you decide to put your application on a single server, that means you do not care about the single point failure, and all your workload can be handled by the single server. So you can just run only one instance of your application, then you will not have such kind of problem. Even if you want to have multiple instances of your application running on the same machine, SQLite can also handle that. > what if you have separate services that need to access the same DB? That means one single server cannot handle the workload. If the bottleneck is in the database module, a cluster is required to process the data. Clearly the SQLite is not a good option in this case. If it is not that case, you can separate the database module and provide a lightweight wrapper around the SQLite to create a database service. And use multiple instances for calculation, then call the single database service instance for persistance. I think comparing to the single instance of MySQL/Postgres/SQL Server, the performance of SQLite is not too bad. So we should keep the architecture simple, if possible.
- TheRoque 2y ago
  Well, running multiple instances of the same app is not only about "points of failure", it's also about using all the cores of your machine. Typically, using PI (nodejs), Docker or Uvicorn (python), you can spin up multiple instances of your backend to handle more concurrently.
  - Filligree 2y ago
    SQLite connections can be used concurrently inside a single process. You can spin up more threads instead, and spare yourself the filesystem locking overhead.
    - TheRoque 2y ago
      Threads in Python in python are locked to a single core, and in NodeJS it's really annoying to pull off. That's why people use PM2, Uvicorn and Gunicorn.
      - 2y ago