# Database Dialect Rules for PostgreSQL and SQLite Compatibility in Instatic

> Learn Instatic's database dialect rules for PostgreSQL and SQLite compatibility. Discover how dialect-naive repositories, _json suffixes, and split migrations enable seamless TypeScript code execution on both databases.

- Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic)
- Tags: internals
- Published: 2026-08-01

---

**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`](https://github.com/CoreBunch/Instatic/blob/main/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.

```typescript
// ✅ 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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts)): Automatically `JSON.stringify`s objects on write and `JSON.parse`s them on read
- **PostgreSQL adapter** ([`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/db-json-column-naming.test.ts).

```typescript
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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) – PostgreSQL definitions
- [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/migration-parity.test.ts) validates this alignment.

```typescript
// 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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) | `DbClient` interface used by all repositories |
| [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts) | Adapter normalizing PostgreSQL results and `jsonb` handling |
| [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts) | Adapter auto‑stringifying/parsing `_json` columns |
| [`docs/reference/database-dialects.md`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts) can reliably transform values by pattern‑matching column names, avoiding expensive runtime schema introspection or fragile heuristics.