Re: more on Sqlite vs MySQL/Postgres

A reader asked for elaboration on a previous post:

One thought, not that it all needs to be in detail, but are there some quick things to mention here? "usable for 90% of use cases"
Are there considerations that make or break the use of SQLite?

I'm a fan of the cloud (I'm a Cloud Engineer, and have a Kubernetes certificate), but you can get a lot done with a single VM with Sqlite.

Case for SQL

SQL is a well-understood data language, with a very rich ecosystem of vendors and extensions. It's possible to use one flavor of SQL, then port the application to another.

In SQL, the data has a specification independent of the client applications. This makes it resistent to application bugs -- very nice for financial applications. The schema of the data can be discussed and revised even if there are no applications for it. Lastly, business people can write reports querying live data without being a software developer. They write queries in SQL, and move on with their day.

Sqlite vs others

Unlike most SQL engines, Sqlite is a library vs a service. An app using Sqlite must live on the same VM as the database file. Since Sqlite doesn't have to do network locking, queries can be much faster than with other SQL engines like Postgres. However, locking can be slow, which means multiple concurrent writers are not recommended.

Sqlite is very, very high quality. It's running on nearly all devices, with hundreds of databases per device. It uses four completely different test harnesses, and the amount of test code is 600x (!!) the library code.

For app developers, especially those who want compatibility with e.g. Postgres, there's a big gotcha with Sqlite: ~~Imaginative~~ err Flexible Typing. Traditional SQL limits field sizes and types, and will complain when a different thing is INSERTed or UPDATEd. Sqlite is completely happy with putting a string into an integer field. This can cause problems. Example: if your CICD Testing and Staging environments use Sqlite for speed, but Production uses Postgres. Sometimes bugs will appear in production, and they'll be hard to track down.

Recommendation: use Sqlite for many use cases

In general, I use Sqlite if my app will fit on a single VM, and won't need much scaling in the future. The cloud is incredibly useful and flexible, but you can get lot done with a single VM and Sqlite!

Example:

Shotglass -- analyze large codebases with numbers and Hilbert fractals

Don't use if...

Multiple heavy writers
Must modify database independently of app
App might scale beyond a single VM
Data might scale beyond single file
Need (ex: Postgres) plugin, like PostGIS
Need sophisticated user management and permissions

Read the When to use Sqlite doc from Sqlite.org -- its advice is incredibly clear and easy to apply.

Of particular focus is the "data must fit in single file" restriction, for Linux Ext3 this is 2 TB. Secondly if your app needs a nice service-only extension like PostGis for mapping, this might dictate not using Sqlite.

Lastly even if your app doesn't hit any of the above issues, but might in the future, consider switching to a SQL service now. You'll squash the complexity which could lead to a fountain of bugs.