Porting a Postgres-backed app to SQLite because the target host offers only a persistent disk and no managed Postgres. The app depended on genuinely Postgres-specific features — pg_trgm `similarity()` for fuzzy retrieval, `jsonb` columns with `@>` containment and `->>` extraction, `UNIQUE ... NULLS NOT DISTINCT`, `serial` keys — so the question is which of these have faithful SQLite equivalents, which need reimplementation, and what silently changes behavior rather than failing loudly.
Fix: A workable mapping, in rough order of risk. (1) `pg_trgm similarity()` has no SQLite equivalent, but it does not require rewriting call sites: register a user-defined function named `similarity` on the connection (better-sqlite3 `db.function("similarity", { deterministic: true }, fn)`) and the existing `ORDER BY similarity(col, ?) DESC` SQL keeps working. Reimplement pg_trgm's actual algorithm rather than substituting Levenshtein or a different metric, or ranking silently shifts: lowercase, split on non-alphanumerics, pad each word with two leading and one trailing space, take the set of 3-char windows, and return |shared| / |union|. Mark it `deterministic` so SQLite may use it in indexes/optimizations. The important caveat is performance, not correctness: the GIN trigram index is gone, so each lookup is a full scan of the candidate rows with a JS call per row. Memoizing trigram sets per input string keeps this acceptable at moderate row counts; at large scale this is the part that needs rethinking, not the semantics. Alias the score in SQL (`similarity(...) AS sim ... ORDER BY sim DESC`) so it is computed once per row rather than twice. (2) `jsonb` becomes `text({ mode: "json" })` in Drizzle, which serializes/parses transparently. Containment queries (`flags @> '[{"type":"ai_pending"}]'::jsonb`) become `EXISTS (SELECT 1 FROM json_each(col) WHERE json_extract(value, '$.type') = 'ai_pending')` via SQLite's built-in JSON functions. Prefer expressing this as a Drizzle `sql` fragment inside a normal `db.select()` rather than a raw query — a raw `SELECT *` returns snake_case columns with JSON still as strings, quietly bypassing the ORM's parsing and column mapping. (3) `data->>'key'` is tempting to translate to `json_extract(data, '$.' || ?)`, but breaks when keys contain dots or spaces (common with spreadsheet-derived column names) because the argument is a JSON *path*, not a key. Where the row count is already bounded by a LIMIT, selecting the whole JSON document and extracting in JS is simpler and safer. Note `->>` yields text in Postgres, so cast extracted values to string to preserve prior behavior when a JSON number is involved. (4) `UNIQUE ... NULLS NOT DISTINCT` has no SQLite counterpart — SQLite treats NULLs as distinct in unique indexes, so a constraint over a nullable scoping column stops preventing duplicates. Recover it with a partial unique index (`CREATE UNIQUE INDEX ... WHERE scope_col IS NULL`), which Drizzle expresses as `uniqueIndex(...).on(...).where(sql\`...\`)`. (5) `serial`/`bigserial` become `integer().primaryKey({ autoIncrement: true })`; `boolean` becomes `integer({ mode: "boolean" })`; `timestamp` becomes `integer({ mode: "timestamp" })` storing unix seconds, with `DEFAULT (unixepoch())`. For JSON columns, prefer `$defaultFn(() => [])` over a SQL default so the value is produced JS-side on insert. (6) Deployment-shape consequences that are easy to miss: co-locate uploaded files with the DB file on the persistent volume (derive the directory from the DB path) — anything written to `process.cwd()` is lost on redeploy; and commit generated migrations so the runtime can apply them via `drizzle-orm/better-sqlite3/migrator` at startup, since `drizzle-kit push` is a devDependency that gets pruned from a production install. On Next.js specifically, `better-sqlite3` is already on the auto-externalized package list, so no `serverExternalPackages` entry is needed. Confirm the port with behavioral tests rather than a passing build — the failure modes here (ranking drift, unenforced constraints, JSON round-tripping) are silent.
sqlitepostgresdrizzlebetter-sqlite3pg_trgmjsonbmigrationnext.jsdeployment
References
- https://www.postgresql.org/docs/current/pgtrgm.html — Defines a trigram as a group of three consecutive characters and specifies that pg_trgm ignores non-word characters and measures similarity by counting the number of trigrams two strings share; it documents that each word is considered to be prefixed by two spaces and suffixed by one space when generating trigrams, which is the padding rule a reimplementation must reproduce. similarity() returns a number between 0 and 1.
- https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md — Documents Database#function(name, [options], fn) for registering user-defined SQL functions, including the deterministic option indicating the function returns the same output for the same input, and Database#pragma() for connection settings.
- https://www.sqlite.org/json1.html — Documents the built-in JSON functions including json_extract(X, PATH) and the json_each table-valued function that iterates the elements of a JSON array or object, and specifies that the second argument is a path expression (e.g. '$.key') rather than a bare key name.
- https://orm.drizzle.team/docs/column-types/sqlite — Documents Drizzle's SQLite column types: text with { mode: 'json' } for JSON storage, integer with { mode: 'boolean' } and { mode: 'timestamp' }, and integer primary keys with autoIncrement.
- https://www.sqlite.org/partialindex.html — Documents partial indexes created with a WHERE clause, including their use as partial UNIQUE indexes to enforce uniqueness over only a subset of rows.
- https://www.postgresql.org/docs/current/sql-createtable.html — Documents UNIQUE NULLS NOT DISTINCT, noting that by default null values in a unique constraint are considered distinct, and that NULLS NOT DISTINCT changes this so nulls compare equal — a modifier SQLite does not provide.
- https://nextjs.org/docs/app/api-reference/config/next-config-js/serverExternalPackages — Lists the packages Next.js automatically opts out of Server Components bundling, which includes better-sqlite3, so native SQLite bindings do not require a manual serverExternalPackages entry.
- https://orm.drizzle.team/docs/migrations — Describes the generate/migrate workflow in which drizzle-kit generates SQL migration files that are applied at runtime by the ORM's migrate() function, separating the dev-time CLI from the runtime application of migrations.