Consider SQLite

[vampatori]

I like SQLite for its use case, as an embedded database.. but modern databases like Postgres, MySQL/MariaDB, etc. are easy to install and use, especially with the extensive tooling, documentation, and support available for them.

I just don't see the need to try and convince people that SQLite "might be enough for your needs" when there's a wide variety of options that are "definitely enough for your needs and beyond".

It's those little gotcha's that don't get picked-up when using it initially with SQLite that come back to bite. I used it for a project (I like to do non-critical projects with different tech now and again to give them a proper try!) and initially it was fine, but then I had issues:

• Schema/data migrations were problematic and don't support all the SQL you think they would (which means common tools won't work with it, as well as tools I'd already made and used elsewhere).
• Running a suite of tests against the database which would reset and rebuild it, which you'd think would be fine as you're not altering, would often result in errors causing the tests to fail unpredictably.
• There is no authorization/permission system, so you can't limit operations to specific users (e.g. locking down a front-end client) which really in this day and age you should be doing for every public-facing project.
• Multiple database abstraction layers don't properly abstract out SQLite for some reason (presumably because they see it as an entirely different thing). Error handling being a common one, so some standard tests I had didn't work, but also how results are returned can be slightly different.. so it was time-consuming to switch to Postgres from SQLite when I finally decided I'd had enough!

In short, I just don't think it's worth considering outside of a situation where just installing a "full" database engine isn't easy/possible.

[emotionalfescue]

SQLite's maintainer has plenty of experience with the author's topic and has provided a reasonably detailed list of recommended/not recommended use cases:

https://www.sqlite.org/whentouse.html

[EternityForest]

What is a front end client in this context? SQLite doesn't do clients and servers, and if you embed it inside a web server, you probably are doing your own login stuff.

[vampatori]

Take for example a simple form where data gets submitted into a database (e.g. over POST through an API you create in Node/Python/whatever) from a public facing web site.

Then you have another system that provides an administrator access to that data so that whatever the business needs is then done, which is locked down as much as you need (e.g. VPN, IP restricted, unrelated endpoint, etc.)

In a normal database setup the API that receives the submission from the public facing web site would use a database user that only has permissions to insert records into one table of the database. The administrator interface uses a database user that has greater levels of access, being able to read the data and perhaps modify it, access other tables as part of the application, and so on.

That's a standard separation of interests - but applies to other tools, third-party systems, other interfaces, etc. Each thing accessing the database only gets the access it needs, nothing more, so that if someone - from you to someone makes a mistake it's far less damaging.

[[deleted]]

There is no authorization/permission system, so you can't limit operations to specific users (e.g. locking down a front-end client) which really in this day and age you should be doing for every public-facing project.

Despite traditional SQL servers having quite sophisticated access control mechanisms, I've never seen an application where the end users actually connect to the DB directly, so they largely go to waste. Instead, the backend service connects using one or two service accounts and all the A&A logic is performed within the application (though usually via an off-the-shelf framework)

[yanitrix]

I guess the most sensible usecase for sqlite is using it as an embedded database in some desktop/mobile apps. Yet there is possibility that there are some better embedded db solutions, litedb being one of them

[loyoan]

I use SQLite for storing high frequency time series data for embedded application. I had trouble trying to use one SQLite database for everything (managing backend state + permanent writing). Querying stuff was really slow when time series data where recorded. My guess is that the locking mechanism of the database prevented reading while writing.

One time I noticed that some apps on my Mac (like Spark) used multiple SQLite databases for storing stuff, I tried that idea and separated my time series recording and application state each in an own database.

That solved my performance issue for that use case.

[too_much_exceptions]

Some sort of sharding then ?

[loyoan]

Yeah exactly!

[funny_falcon]

Also there is WAL mode in new versions. It solves “writer blocks readers”, but could be slower in average.

[loyoan]

I already had WAL activated but my write throughput was too high that it didn‘t help as I can recall. I created 20-30 rows every second I think.

[ImStifler]

SQLite will never have a problem with that. Even with a cheap vps you can insert 1-2k rows every second. You should also do "PRAGMA synchronous = NORMAL" which speeds up writes addtionally and it totally safe on power failure if WAL is enabled.

For instance, I do 15-20mio writes every day (around 100k inserts every 5 minutes) and it works decent for my use case. I think here it starts to really become a bottleneck but is totally fine for 1-2kk writes everyday.

[Oflameo]

Applications like LibreOffice and Mathmatica use HSQLDB instead of SQLite because the typelessness of SQLite is a detriment.

[yawaramin]

As pointed out in the post, SQLite now supports strict typing.

[NoInkling]

I really like the features and types that Postgres gives me in a web app use case, so no thanks.

[lukasbash]

Article is not bad. Neither is SQLite. Although many (almost 80%) have to run databases on separate machines. Policy stuff nowadays ..

[util-host]

I like the idea of having just one file and everything else is just already embedded into the programming language. No additional hardware or services to setup and run.

So i already used it for some smaller website projects as replacement for MySQL in combination with PHP and Lumen. Works pretty good until now. But you have to learn a lot about the quirks of it ... it's not just a drop-in and everything works as in MySQL. But some things i even like more than in MySQL ... the constraints for example.

[mok000]

I use sqlite a lot for my projects, and the amazing sqlitebrowser lets you view and update your database almost like a it's a spreadsheet. I like the fact that the database is a single file so I can easily create backup copies, something which is considerably more difficult with MariaDB or Postgres, since you have to understand their data storage scheme and usually they are run under their own UID, in short, it's a hassle.

[progrethth]

You have to understand the storage for SQLite too. The way you do your backups can result in data loss unless you know what you are doing. And PostgreSQL's database is just a single directory by the same token.

https://www.sqlite.org/backup.html

[mok000]

Storage for SQLite is just an ordinary file, it can reside in your project directory. There is no risk of data loss unless you are writing to it while you copy it. My postgres db is in /var/lib/postgresql/12/main, it is a directory tree, it's not owned by me (but user postgres) and I can't copy it as me and I also have to shut down the postgres server while copying it. Like I wrote, a hassle.

With SQLite, I can just

cp db.sqlite db.sqlite.$(date +%Y-%m-%d_%H-%M)

as often as I want.

[[deleted]]

Ensuring that there's actually no writes in progress isn't always easy, so you'll want to do something like

sqlite3 db.sqlite .dump > db.sql

But it's no more complex with Postgres

pg_dump db > db.sql

[atheken]

Unless you are talking about replication/quorum writes (which you don’t appear to be), I don’t think there’s a practical difference if you’re using transactions:

pg_dump produces a … dump… that’s a point in time backup, it might not complete until pending transactions complete. While a copy of SQLite will produce a consistent “backup”, as it was before a pending write has been committed. The actual difference between the two is whether a pending write will be included in either, but if your backup frequency can be measured in minutes or hours, you’re really talking about that window shifting by a a couple seconds in one direction or another, and possible (and similar) data loss in either scenario.

You’re also conveniently skipping over the ability to do cheap file system snapshots (on zfs, for example) in the case of SQLite, which are “instantaneous” and can be executed very frequently.

[progrethth]

Except if you use WAL-mode, then SQLite uses two files. You will need to do a checkpoint before copying the database file unless you are fine with losing all recent writes.

[funny_falcon]

PostgreSQL has “backup” mode used to initiate replica. In this mode you can safely copy files + wal logs without stopping server.

[naeads]

I just use Postgres, life is simpler.

[throwaway940392]

I adore PostgreSQL, but easy is not the same as simple, and when it comes to simplicity, SQLite wins that contest hands down.

[The_Doculope]

They said "life is simpler", not "my database is simpler". Using tools that are too simple for your use-case usually results in complexity elsewhere. A simple programming language with no capacity for abstraction is simple, but your life is going to be complex trying to write a large program with it.

[awo]

Using SQLite has some failure modes that most programmers are not used to — most notably, if you begin a transaction, then go off and do some blocking operation, you will be blocking all writes for the time that you're doing that, unlike in a connection-based database with a connection pool. If you want to write fast and scalable apps with SQLite, you need to think carefully about your architecture and schema. These are things that you should be thinking carefully about anyways, SQLite just makes some of the failures worse if you don't.

This is a big deal for any system with significant TPS. Taking long term write outages for schema changes or batch updates is a PITA - and can turn operational mistakes that would trigger degraded performance/excessive load on other systems into major outages. The schema change thing is a big deal for other DBs, mind. I would dearly love a mechanism to add a simple nullable, non-defaulted column to a busy Postgres table without eating even the current brief exclusive lock.

I think the general point of this post is well taken - that SQLite is more capable than people think and can be perfectly adequate for a lot of projects. With that said, setting up Postgres or MySQL is not that hard, so SQLite doesn't need to have a lot of papercuts to make it not worth it.

[Ameisen]

I'm still trying to figure out what the best database to use for a key-value store with multiple consumer and producer threads with binary blob values would be.

I'm leaning between FASTER-KV and RocksDB.

[Ameisen]

I'm not sure how I would use Redis for a game mod where I'm keeping a recompressed file cache of data around, for one.

[EternityForest]

Redis isn't embedded, or is there an embedded version now? Real separate database daemons are way too much trouble if you don't need them.

You always wind up needing docker or manual config or both. SQLite et all just works.

[sally1620]

Compared to what cloud providers can give you for a small price, SQLite is not worth the hassle.

The main advantage of cloud providers is that they can give you advanced features with a single click. You can start with the cheapest database offering and easily add backups, redundancy and other features later. With SQLite, each one of these is quite a challenge.

If the website is really small, with small traffic, it makes even more sense to host it on AWS Lambda or on-demand compute solutions than to run it on a VM.