Two hundred call sites, one seam: SQLite to Postgres without touching the queries

DataJuly 2026

The control service — accounts, rooms, sessions, API keys, recordings — grew up on SQLite: 16 tables, about 140 SQL call sites, roughly 200 ? placeholders, all of it auth-adjacent. When the service moved to a platform where container filesystems are replaced on every deploy, SQLite had to go. The platform's shared storage explicitly bans file databases (its locks are per-mount), and streaming WAL to object storage felt like scaffolding around the wrong decision. The state belonged in the managed PostgreSQL next door.

The risk wasn't the schema — 16 tables convert in an afternoon. It was the placeholders. Postgres numbers its parameters ($1, $2, …) where SQLite uses positional ?, and hand-editing 200 of them across authentication queries is exactly the kind of mechanical change where one transposed number silently returns the wrong user.

The MariaDB temptation

MariaDB uses ? too, which would have made the migration a connection-string swap. It lost anyway, for one decisive reason: MariaDB cannot index a TEXT column without a declared prefix length. This schema has ten TEXT primary-key or unique columns — emails, API keys, session tokens, room slugs — plus a WebAuthn credential id in a BLOB. Each would need a hand-picked VARCHAR width, and the failure mode of guessing short is silent truncation of credentials. Postgres indexes TEXT natively; its cost is the placeholder problem, whose failure mode is a loud error. Between silent-wrong and loud-broken, always take loud-broken. (MariaDB's only offered LTS was also months from end-of-life, which settled any doubt.)

Rebinding at the driver seam

The trick that made the migration tractable: don't touch the queries — rewrite them in flight. The package's *sql.DB is wrapped in a thin DB type whose Exec, Query and QueryRow rebind ? to $N before handing off:

func rebind(q string) string {
    var b strings.Builder
    n := 0
    inStr := false
    for _, r := range q {
        switch {
        case r == '\'':
            inStr = !inStr
            b.WriteRune(r)
        case r == '?' && !inStr:
            n++
            fmt.Fprintf(&b, "$%d", n)
        default:
            b.WriteRune(r)
        }
    }
    return b.String()
}

Every one of the ~200 call sites compiles and runs unchanged, and there is no opportunity to mis-number anything by hand. The wrapper skips quoted sections; a quick audit confirmed no SQL string in the package carries a literal ? inside quotes, so the rebind is total. The driver moved to pgx (still cgo-free, like the pure-Go SQLite driver before it), INSERT OR IGNORE became ON CONFLICT DO NOTHING, and the one place that used LastInsertId — which pgx doesn't offer — became RETURNING id. The connection pool finally got to be a pool: SQLite's single-writer SetMaxOpenConns(1) relaxed to a real limit.

Proving it on the real data

The test wasn't a fresh schema — it was a copy of the production database. Bootstrapping, then importing every row with explicit ids, exposed the classic trap: sequences don't advance on explicit-id inserts. Skip the setval() pass and the first real signup collides with row one. The acceptance checks were end-to-end and unglamorous: matching row counts per table, encrypted TOTP secrets round-tripping at exactly the right AEAD sizes (12-byte nonce, 48-byte ciphertext), an API key minting a token, bad credentials still rejected, duplicate signup still refused by the unique index. Sessions and short-lived reset rows were deliberately dropped rather than migrated — users re-log-in once; nobody inherits a stale token.