SQLite first
Why I like to start projects with SQLite


SQLite is a file-based in-process ACID compliant SQL database. That means: No database server, no setup, no runtime maintenance, just a single file that hold all data. While it's not suitable for all use cases under the sun, it is a good starting point for development, and even for production. It's a C library, but it can be used in Java, Go, Python and many more programming languages.

Development.
SQLite is, in contrast to RDBMS server databases (Postgres, MySQL), trivial to setup. You pass a filename to SQLite and it will open that file, or create it if it does not exist. The special filename :memory: produces a pure in-memory database that keeps all data in RAM. Re-creating a database is also trivial: Just delete the database file and restart your app.

Unit Testing.
Unit testing with 'real' databases quickly leads to problems: Re-creating databases, server startup and shutdown, schema migrations for existing databases, all that has to be done by the unit test. The usual advice is: "Test against a mockup of you DB layer", or "Test against another in-memory DB" (e.g. HSQLDB for Java). With SQLite, none of that is required. For unit tests, a :memory: database can be used on each unit test invocation. Starting a fresh SQLite instance is so fast that it will not slow down unit tests considerably. Moreover, unit tests use the real DB, so as a developer you can be sure that if tests pass, production will work too.

Schema migration.
Nothing different here. Since SQLite is a SQL database, you will sooner or later need to make schema updates. PRAGMA user_version, together with automated schema upgrades, is your friend here. Again, this is easily unit-testable.

Deployment & Operations.
I usually pass the database filename as a config parameter (config file entry, environment variable, command line argument, whatever is suitable). As long a the app is running, SQLite will read/write from/to that file. Backups can be done by cron: sqlite3 my_db ".backup 'my_backup'" or within the app: VACUUM INTO 'my_backup';.

Limitations of SQLite.
When your app's requirements outgrow SQLite (high workload, heavy read/write concurrency required, etc.) then it's time to replace SQLite with another solution (Postgres, MongoDB, etc.) However, the assumption is: Most projects never outgrow SQLite. And if, you can start re-writing the data layer of your app from a much more informed point than if you had started your project with a client-server database in the first place.

Summary.
SQLite may not be suited for all projects, but I think the time-saving in development and production are worth the risk of out-growing SQLite.


2022-12-27