Postgres vs SQLite Dialect Rules for Instatic: Ensuring Database Portability

Instatic enforces three strict dialect rules—dialect-naive repositories, _json column suffixes, and split migrations with identical IDs—to ensure the same codebase runs on both Postgres and SQLite without modification.

Instatic is an open-source TypeScript framework designed to operate interchangeably on both Postgres (Bun.sql) and SQLite (bun:sqlite) engines. By adhering to specific Postgres vs SQLite dialect rules for Instatic, developers can write ANSI-SQL-compliant repositories that remain fully portable across database engines simply by changing the DATABASE_URL environment variable.

Rule 1: Repositories Must Be Dialect-Naive

All repository code in Instatic must use pure ANSI-SQL syntax, avoiding any dialect-specific features that could break portability. According to the documentation in docs/reference/database-dialects.md, five specific Postgres-isms are banned in any file importing DbClient:

• now() in DML statements
• ::int casting syntax
• ::jsonb casting syntax
• any($N::…) array operations
• distinct on clauses

This prohibition is mechanically enforced by src/__tests__/db/db-postgres-isms.test.ts, which scans repository files for forbidden patterns. By eliminating these Postgres-specific constructs, Instatic guarantees that repository logic in server/repositories/*.ts executes identically on both engines.

Rule 2: JSON Columns Must End With _json

To handle the different native JSON implementations between Postgres (JSONB) and SQLite (TEXT), Instatic mandates that all JSON columns use the _json suffix. As implemented in the SQLite adapter, columns ending with _json are automatically parsed on read and stringified on write, allowing application code to treat JSON data as native JavaScript objects regardless of the underlying storage format.

This convention is verified by src/__tests__/db/db-json-column-naming.test.ts, ensuring that migrations and queries remain consistent across dialects.

Rule 3: Migrations Are Split Per Dialect With Identical IDs

Schema changes are organized into two separate files: server/db/migrations-pg.ts for Postgres-specific DDL and server/db/migrations-sqlite.ts for SQLite-specific DDL. Each migration entry must share the same ID and label across both files, even when the SQL statements differ. For example, a Postgres migration might use JSONB while the SQLite equivalent uses TEXT, but both entries carry identical id and label properties.

The test suite src/__tests__/db/migration-parity.test.ts validates this parity, preventing schema drift between environments.

Architectural Implementation

The portability system relies on three architectural components defined in the server/db/ directory.

Database Client

server/db/client.ts exposes a dialect property typed as Dialect = 'pg' | 'sqlite'. This property is reserved exclusively for placeholder substitution, never for branching logic.

Placeholder Utility

server/db/utils.ts exports the placeholder(db.dialect, index) function, which abstracts syntax differences by expanding to $1, $2, etc., for Postgres and ? for SQLite.

Tagged-Template API

The db`…` template literal function in server/db/client.ts automatically handles parameter binding, using the dialect property to determine the correct placeholder format at runtime.

Code Examples

Dialect-Naive Repository Query

The following repository function works unchanged on both engines because it uses standard ANSI-SQL without dialect-specific features:

import { db } from '@/server/db/client';

// Retrieve a published page by slug
export async function getPageBySlug(slug: string) {
  const rows = await db.all<{ id: number; title: string }>`
    SELECT id, title
    FROM pages
    WHERE slug = ${slug}
  `;
  return rows[0];
}

Dialect-Aware Placeholder Usage

When parameter binding order matters, use the placeholder utility from server/db/utils.ts:

import { db } from '@/server/db/client';
import { placeholder } from '@/server/db/utils';

// Delete a row by primary key
export async function deleteRow(id: number) {
  await db.run`
    DELETE FROM data_rows
    WHERE id = ${placeholder(db.dialect, 1)}
  `(id);
}

placeholder expands to $1 on Postgres and ? on SQLite.

Adding Dialect-Specific Migrations

Postgres migration in server/db/migrations-pg.ts:

export const migrations = [
  // …previous migrations
  {
    id: 42,
    label: 'Add media_metadata_json column',
    statements: [
      `ALTER TABLE media ADD COLUMN metadata_json JSONB NOT NULL DEFAULT '{}'`,
    ],
  },
];

Equivalent SQLite migration in server/db/migrations-sqlite.ts:

export const migrations = [
  // …previous migrations
  {
    id: 42,
    label: 'Add media_metadata_json column',
    statements: [
      `ALTER TABLE media ADD COLUMN metadata_json TEXT NOT NULL DEFAULT '{}'`,
    ],
  },
];

Both files share the same id: 42 and label; only the DDL syntax differs.

Working With JSON Columns

Because the column name ends with _json, the SQLite adapter automatically parses JSON strings into objects:

// The column name ends with `_json` → SQLite auto‑parses it.
export async function getMediaMetadata(id: number) {
  const row = await db.get<{ metadata_json: Record<string, unknown> }>`
    SELECT metadata_json
    FROM media
    WHERE id = ${id}
  `;
  return row.metadata_json; // Already a parsed object.
}

Summary

• Dialect-naive repositories in server/repositories/*.ts must use pure ANSI-SQL, banning five specific Postgres-isms enforced by db-postgres-isms.test.ts.
• JSON column naming requires the _json suffix to enable automatic parsing in SQLite, validated by db-json-column-naming.test.ts.
• Migration parity demands identical IDs and labels across migrations-pg.ts and migrations-sqlite.ts, ensuring schema consistency verified by migration-parity.test.ts.
• The placeholder utility in server/db/utils.ts abstracts parameter binding differences, generating $N for Postgres and ? for SQLite.
• Switching between database engines requires only changing the DATABASE_URL environment variable, with no code modifications needed.

Frequently Asked Questions

How does Instatic handle parameter binding differences between Postgres and SQLite?

Instatic uses the placeholder function exported from server/db/utils.ts to abstract syntax differences. When passed the db.dialect property from server/db/client.ts, this utility returns $1, $2, etc., for Postgres and ? placeholders for SQLite. Repository functions call placeholder(db.dialect, index) within tagged template literals, allowing the database client to bind parameters correctly regardless of the active engine.

What happens if I accidentally use Postgres-specific syntax in a repository?

The test suite src/__tests__/db/db-postgres-isms.test.ts mechanically scans all files importing DbClient for banned patterns including now(), ::int, ::jsonb, any($N::…), and distinct on. If detected, the test fails, preventing the code from merging. This enforcement ensures that all repository code remains portable and ANSI-SQL compliant.

How do I add a new migration that works for both dialects?

Create entries with identical id and label values in both server/db/migrations-pg.ts and server/db/migrations-sqlite.ts. Translate the SQL statements to each dialect's syntax—for example, using JSONB in Postgres and TEXT in SQLite. The migration-parity.test.ts suite verifies that IDs and labels match across both files, preventing schema drift.

Can I switch between Postgres and SQLite without modifying application code?

Yes. Because repositories are dialect-naive and migrations are split by dialect with identical IDs, you can switch engines solely by changing the DATABASE_URL environment variable. The server/db/client.ts initializes the appropriate adapter (Bun.sql for Postgres or bun:sqlite for SQLite) at runtime, while the placeholder system and _json column convention handle all syntax translations automatically.

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