SQLite or PostgreSQL: one codebase, two engines, one env var

May 20, 2026

Most boilerplates pick one database and bake it in. GoVueKit supports both, selected by DB_DRIVER, and the same SQL runs on either. This post explains how that is done, what it costs, and — the part that actually matters — when SQLite is a legitimate production choice rather than a toy.

Why bother supporting both

Development. SQLite means make dev works on a fresh laptop with no service to install, no container to start, no credentials to invent. The file is created and migrated on first run.

Small production. A single-server product with a few thousand users and read-heavy traffic runs perfectly on SQLite, with backups that are a file copy.

Tests that mean something. CI runs the entire Go suite twice, once per engine. Bugs that only appear on one engine are found by the machine, not by a customer.

Optionality. If you grow into concurrent writes, you change one variable instead of rewriting queries.

How portability is actually achieved

Four rules, and they are less restrictive than they sound.

1. Portable types only. TEXT for ids (UUIDs generated in Go, never SERIAL), TIMESTAMP for time, BOOLEAN, INTEGER. No engine-specific column types, no JSONB, no arrays.

2. RETURNING is allowed. Both engines support it, which removes the last-insert-id dance and keeps inserts to one round trip.

3. One code generation. sqlc generates against the PostgreSQL dialect with $1 placeholders — and the pure-Go SQLite driver (modernc.org/sqlite) accepts $N natively. So one set of generated Go types serves both engines instead of two divergent packages.

4. No CGO. modernc.org/sqlite is SQLite translated to Go. That is what keeps go build cross-compiling to a static binary with no C toolchain, and it is the reason the "one file to deploy" story holds on both engines.

What you give up: JSONB operators, PostgreSQL full-text search, array columns, materialised views, LISTEN/NOTIFY. If your product's core feature needs one of those, use PostgreSQL from day one — the kit runs on it natively and you simply stop caring about the SQLite path.

When SQLite is enough in production

SQLite's limit is concurrent writes: one writer at a time, serialised. In practice, with WAL mode, that supports far more than people assume — thousands of writes per second on decent hardware, because each write is short.

It is a good fit when:

• one application server is enough (SQLite is a file, not a network service);
• traffic is read-heavy, which describes most B2B SaaS;
• you value operational simplicity over horizontal scaling;
• your backup story can be "copy the file, plus the WAL".

Switch to PostgreSQL when:

• you need more than one application instance sharing state;
• writes are sustained and concurrent (event ingestion, chat, telemetry);
• you want managed backups, replicas, point-in-time recovery;
• your queries need what only PostgreSQL offers, per the list above.

The honest summary: SQLite scales further than its reputation, and the moment you need two application servers, none of that matters — the file cannot be shared, and you move.

Moving from one to the other

The schema is the same; only the data has to travel. The path is a dump and a load, done during a maintenance window:

1. Stop the app.
2. Export each table from SQLite (CSV or INSERT statements).
3. Start with DB_DRIVER=postgres and AUTO_MIGRATE=true so goose creates the schema.
4. Load the data, then check row counts per table and re-run the test suite against the new database.
5. Start the app.

There is no ORM-generated schema drift to reconcile, because the migrations are the same files. Do it early — moving 50,000 rows is an evening, moving 50 million is a project.

The rules you must keep if you edit the SQL

The portability is a discipline, not a library. Three habits keep it:

• Write the query in internal/db/queries/*.sql, never inline in Go, so every statement is reviewable in one place.
• Run make test before committing. CI runs both engines; your laptop runs SQLite by default, and TEST_DB_DRIVER=postgres (with TEST_POSTGRES_DSN pointing at a server) runs the same suite locally.
• Avoid the tempting extension. The moment you write JSONB, decide explicitly that PostgreSQL is now required, and note it in DECISIONS.md.

That last one is the real trade-off. Portability costs you the fancy features of both engines; in exchange, development has no dependencies and production has an escape hatch.

The uncomfortable question

Is supporting two engines worth it for your product? If you know you will run PostgreSQL, you could delete the SQLite path and use JSONB freely. The kit does not stop you — it is your source code.

What you would lose is the property that makes a first run cheap: clone the repository, start the server, and the database is a file that did not exist a second ago. That is also what makes the test suite fast enough to run on every save.

Keep both while you are small. Drop one deliberately, with a line in DECISIONS.md, if your product outgrows the compromise.

See how the data layer fits together, or read the deployment guide for the production setup on either engine.