# Postgres vs SQLite Dialect Rules for Instatic: Ensuring Database Portability

> Discover Postgres vs SQLite dialect rules for Instatic. Ensure database portability with Instatic's three strict rules for seamless codebase compatibility across databases.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: deep-dive
- Published: 2026-07-29

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) for Postgres-specific DDL and [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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:

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/utils.ts):

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts):

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts):

```typescript
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:

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/db-json-column-naming.test.ts).
- **Migration parity** demands identical IDs and labels across [`migrations-pg.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-pg.ts) and [`migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/migrations-sqlite.ts), ensuring schema consistency verified by [`migration-parity.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/migration-parity.test.ts).
- The **`placeholder`** utility in [`server/db/utils.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/utils.ts) to abstract syntax differences. When passed the `db.dialect` property from [`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) and [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) initializes the appropriate adapter ([`Bun.sql`](https://github.com/CoreBunch/Instatic/blob/main/Bun.sql) for Postgres or `bun:sqlite` for SQLite) at runtime, while the placeholder system and `_json` column convention handle all syntax translations automatically.