Database Dialect Rules for PostgreSQL and SQLite Compatibility in Instatic

Instatic enforces three strict architectural rules—dialect‑naive repositories, _json column suffixes, and split migrations with identical IDs—to run the same TypeScript code on both PostgreSQL and SQLite without modification.

The Instatic repository (CoreBunch/Instatic) maintains a single DbClient abstraction that transparently switches between PostgreSQL and SQLite based on DATABASE_URL. Rather than maintaining separate code paths, the project enforces portability constraints that are validated by dedicated architecture tests on every CI build.

Rule 1: Repositories Must Be Dialect‑Naive

All SQL in files that import DbClient must be plain ANSI‑SQL. Five PostgreSQL‑specific features are explicitly banned:

• now() in DML statements
• ::int and ::jsonb casting operators
• any($N::…) array bindings
• distinct on clauses

Use portable equivalents instead. Replace now() with current_timestamp, use standard CAST(... AS INTEGER) syntax, and construct IN (...) lists in JavaScript rather than relying on PostgreSQL array bindings.

This rule is enforced by src/__tests__/architecture/db-postgres-isms.test.ts. According to the Instatic source code, this test scans repository files and fails the build if any banned syntax is detected.

// ✅ Portable SQL – works on both PostgreSQL and SQLite
await db`insert into audit (user_id, action, ts) values (${userId}, ${action}, current_timestamp)`;

// ❌ Non‑portable – now() would fail on SQLite
await db`insert into audit (user_id, action, ts) values (${userId}, ${action}, now())`;

Rule 2: JSON Columns Must End in _json

Any column storing JSON data must use the suffix _json. This naming convention enables automatic value transformation in each adapter:

• SQLite adapter (server/db/sqlite.ts): Automatically JSON.stringifys objects on write and JSON.parses them on read
• PostgreSQL adapter (server/db/postgres.ts): Uses native jsonb handling without transformation

Both adapters present identical Record<string, unknown> values to repository code, eliminating dialect‑specific JSON handling.

This rule is enforced by src/__tests__/architecture/db-json-column-naming.test.ts.

interface Settings { theme: string; layout: string; }

const newSettings: Settings = { theme: 'dark', layout: 'grid' };

// ✅ The _json suffix triggers automatic adapter handling
await db`update site set settings_json = ${newSettings} where id = ${siteId}`;

// Reading back – already an object on both databases
const { rows } = await db<{ id: string; settings_json: Record<string, unknown> }>`
  select id, settings_json from site where id = ${siteId}
`;
const settings = rows[0].settings_json as Settings;

Rule 3: Migrations Are Split Per Dialect With Identical IDs

Migration files are separated by engine:

• server/db/migrations-pg.ts – PostgreSQL definitions
• server/db/migrations-sqlite.ts – SQLite definitions

Each file exports an ordered Migration[] array where entries share the same id and label across both files. The SQL bodies are dialect‑specific, but their semantic effect must be identical.

Keeping IDs in lockstep ensures the migration runner applies the same logical schema changes regardless of database engine. The parity test in src/__tests__/architecture/migration-parity.test.ts validates this alignment.

// server/db/migrations-pg.ts
{
  id: '0042-add-subscribers',
  label: 'Add subscribers table',
  sql: `
    create table subscribers (
      id text primary key,
      email text not null unique,
      metadata_json jsonb not null default '{}',
      created_at timestamptz not null default current_timestamp
    );
  `,
},

// server/db/migrations-sqlite.ts
{
  id: '0042-add-subscribers',
  label: 'Add subscribers table',
  sql: `
    create table subscribers (
      id text primary key,
      email text not null unique,
      metadata_json text not null default '{}',
      created_at text not null default current_timestamp
    );
  `,
},

Note the identical id values and the use of timestamptz (PostgreSQL) versus text (SQLite) for timestamps—both accept ISO‑8601 strings produced by current_timestamp.

Key Implementation Files

File	Purpose
server/db/client.ts	DbClient interface used by all repositories
server/db/postgres.ts	Adapter normalizing PostgreSQL results and jsonb handling
server/db/sqlite.ts	Adapter auto‑stringifying/parsing _json columns
docs/reference/database-dialects.md	Complete rule documentation

Summary

• Dialect‑naive repositories guarantee portable SQL by banning PostgreSQL‑specific syntax
• _json suffix convention enables automatic JSON serialization in the SQLite adapter while using native jsonb in PostgreSQL
• Split migrations with identical IDs ensure schema parity across both engines
• All three rules are validated by architecture tests running on every CI build

Frequently Asked Questions

How does Instatic switch between PostgreSQL and SQLite at runtime?

Instatic reads DATABASE_URL at startup. If the URL starts with postgres://, the PostgreSQL adapter is instantiated; if it starts with sqlite:, the SQLite adapter is used. Both implement the same DbClient interface, so repository code requires no changes.

What happens if I use now() instead of current_timestamp in a query?

The CI build fails. src/__tests__/architecture/db-postgres-isms.test.ts scans all files importing DbClient and rejects any occurrence of banned PostgreSQL syntax including now(), ::int casts, and distinct on.

Why require the _json suffix instead of detecting column types automatically?

Explicit naming eliminates ambiguity and adapter complexity. The SQLite adapter in server/db/sqlite.ts can reliably transform values by pattern‑matching column names, avoiding expensive runtime schema introspection or fragile heuristics.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}