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.
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 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
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.
Nothing different here. Since SQLite is a SQL database, you will sooner or later need to make schema updates.
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.
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.