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

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.

Originally published on govuekit.dev. GoVueKit is a complete Go + Vue 3 SaaS codebase you buy once and own; the whole stack runs on your machine with one docker compose up: https://govuekit.dev/labs