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.