I like sqlite as much as the next guy but it's built-in datatypes are limited.
Things like arrays, UUIDs, geometry stuff, JSON, etc.
Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
SQLite the only database you will ever need in most cases (2021)
11–20 of 378 posts
Re: SQLite the only database you will ever need in most cases (2021)
#12>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…
https://litestream.io/ does streaming replication to S3 (or similar service). With this, you probably have better data durability than a small database cluster.
Re: SQLite the only database you will ever need in most cases (2021)
#13SQLite — works 80% of the time — every time.
Re: SQLite the only database you will ever need in most cases (2021)
#14>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…
https://litestream.io/ does streaming replication to S3 (or similar service). With this, you probably have better data durability than a small database cluster.
Came here to say this
Re: SQLite the only database you will ever need in most cases (2021)
#15From a technical perspective, almost every tool is more than what you need. There is a lot of mature software that does amazing things.
Whether the tool fits with your architecture and specific use case is much more important than whether it does the job. You can make most tools work for most use cases, but it might not be a natural fit.
For example, an in-memory database is probably not conducive with a serverless environment and you would prefer to either host your own DB server or use a serverless DB.
Or perhaps there are specific Postgres plugins that enable your use case, or a specific Postgres feature like n-gram search (I don't know if SQLite supports that), etc.
Technical maximalism ("it does all the things!") is great for marketing, but a poor way to choose the appropriate technology for your application.
Re: SQLite the only database you will ever need in most cases (2021)
#16Re: SQLite the only database you will ever need in most cases (2021)
#17>The only time you need to consider a client-server setup is: Where you have multiple physical machines accessing the same database server over a network. In this setup you have a shared database between multiple clients. This caveat covers "most cases". If there's only a single machine, then any data stored is not durable. Additionally, to my knowledge SQLite doesn't have a solution for durability other than asynchr…
people have been providing acid transaction semantics on single machines for 50 years
do you think ims/db ran on a cluster
the d in acid stands for durability
you're talking about pitr, which is what mysql semi-sync provides (and afaik you are correct that sqlite doesn't offer pitr)
Re: SQLite the only database you will ever need in most cases (2021)
#18I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
Re: SQLite the only database you will ever need in most cases (2021)
#19I like sqlite as much as the next guy but it's built-in datatypes are limited. Things like arrays, UUIDs, geometry stuff, JSON, etc. Sure you can store more advanced stuff as blobs or text but then you have to mess around with deserializing it in the host language and you lose the ability to query it directly in the db engine.
I agree. For my little applications I've looked at Postgres because it has much richer data types, but I can't justify the huge complexity increase of Postgres. So SQLite it is.
Re: SQLite the only database you will ever need in most cases (2021)
#20> I have run SQLite as a web application database with thousands concurrent writes every second, coming from different HTTP requests, without any delays or issues. Is this with nodejs or something single threaded as the webserver? I would kind of assume you'd run into issues with something like PHP.
Blocking can become an issue pretty quickly with SQLite unless you do what this guy does and make a different database for essentially every process writing to the db. This can crop up in all sorts of situations.
For example today I was writing tests for a python application I'm working on that uses SQLite. SQLite is the only option I have for this program as I have literally no way to setup a client server db in it's environment.
I had the tests all configured to write to the same test db file. The program I wrote the tests for had no issues with locking because it doesn't run anything concurrently.
However, when I did the same in my test suite, they ran in parallel, which caused my tests to hang. Of course, I figured this out quickly and made the tests write to their own individual DB. This is a really small program, so cleaning up afterwards is no big deal. (I could also just use an in memory db instance instead of even dealing with files). But, for a program with a LOT of tests, I could see this being an issue.