# How Instatic Handles Database Dialect Differences Between SQLite and PostgreSQL

> Instatic seamlessly manages SQLite and PostgreSQL dialect differences with a unified DbClient interface and ANSI SQL. Run your TypeScript code on both databases without modification.

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

---

**Instatic handles database dialect differences between SQLite and PostgreSQL through a unified `DbClient` interface, dialect-naïve repositories using ANSI-standard SQL, and automatic JSON serialization conventions, allowing the same TypeScript code to run on both databases without modification.**

CoreBunch/Instatic is architected to deploy seamlessly across environments ranging from single-VPS setups to production clusters. To support this flexibility, the codebase elegantly manages database dialect differences between lightweight SQLite and robust PostgreSQL through a strict abstraction layer. This design ensures that repository code remains portable while database-specific adapters handle parameter binding, JSON serialization, and connection management.

## The Unified `DbClient` Interface

All database interactions in Instatic flow through a single, well-defined contract that abstracts away engine-specific implementation details.

### The Interface Contract

The `DbClient` interface in [`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) defines the contract that both database adapters must implement. This interface exposes a tagged-template literal function that automatically handles placeholder syntax translation—converting JavaScript template literal expressions into the appropriate parameter bindings for each dialect.

When you call `db\`select * from users where id = ${userId}\``, the adapter automatically translates the expression into `$1` for PostgreSQL or `?` for SQLite, eliminating the need for repositories to manage dialect-specific syntax.

### Automatic Adapter Selection

The entry point at [`server/db/index.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/index.ts) inspects the `DATABASE_URL` environment variable to instantiate the correct adapter at runtime. If the URL starts with `postgres://`, the system loads the PostgreSQL adapter from [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts); otherwise, it initializes the SQLite adapter from [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts). This selection happens once at startup, ensuring that all subsequent database calls use the appropriate engine without additional branching logic in business code.

## Dialect-Specific Adapter Implementations

While repositories remain dialect-agnostic, the adapters handle the mechanical differences between PostgreSQL and SQLite internals.

### PostgreSQL Adapter

The [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts) adapter implements `DbClient` using [`Bun.sql`](https://github.com/CoreBunch/Instatic/blob/main/Bun.sql), Bun's native PostgreSQL driver. This adapter passes parameters directly to the underlying driver using indexed placeholders (`$1, $2, $3...`). It relies on PostgreSQL's native `jsonb` type handling for JSON columns, requiring no additional serialization logic for objects stored in columns suffixed with `_json`.

### SQLite Adapter

The [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts) adapter implements the same interface using `bun:sqlite`. This adapter performs two critical translations to bridge database dialect differences:

1. **Placeholder conversion**: Translates template literal expressions into SQLite's positional `?` placeholders
2. **JSON serialization**: Automatically calls `JSON.stringify()` on objects being written to columns ending in `_json`, and `JSON.parse()` when reading them back, since SQLite stores JSON as text

This ensures that repositories can store and retrieve structured data using identical syntax regardless of the underlying engine.

## Enforcing Dialect-Naïve SQL

Instatic maintains strict architectural rules to prevent database dialect differences from leaking into repository code.

### ANSI-Standard SQL Constraints

Every repository under `server/` must use only ANSI-standard SQL syntax that executes identically on both engines. The test suite enforces this through [`src/__tests__/architecture/db-postgres-isms.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/db-postgres-isms.test.ts), which scans repository files for banned PostgreSQL-specific constructs.

### Banned PostgreSQL-Specific Constructs

To ensure cross-database compatibility, Instatic explicitly prohibits five PostgreSQL-specific features in repository code:

- **`now()`** – Use parameterized timestamps instead of database functions
- **`::int`** and **`::jsonb`** – Avoid PostgreSQL cast syntax
- **`any($N::...)`** – Do not use PostgreSQL array containment operators
- **`distinct on`** – Avoid PostgreSQL-specific deduplication syntax

Because the adapters handle parameter binding and the repositories use only standard SQL, the same repository implementation works unchanged against either database engine.

## Handling JSON Columns Across Dialects

JSON data representation represents one of the most significant database dialect differences between SQLite and PostgreSQL.

### The `_json` Naming Convention

Columns that store JSON data must be named with a trailing `_json` suffix. This convention 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), which validates that any column containing JSON data follows this pattern. The naming convention triggers automatic serialization behavior in the adapters while keeping repository code agnostic to the underlying storage format.

### Automatic Serialization Behavior

When writing to a `_json` column, the SQLite adapter automatically serializes JavaScript objects using `JSON.stringify()`, while the PostgreSQL adapter passes the object directly to the native `jsonb` handler. On reads, the SQLite adapter parses the text back into objects using `JSON.parse()`, whereas PostgreSQL returns the structured data directly. This allows repositories to work with native JavaScript objects regardless of which database dialect is active.

```typescript
// This code works identically on both SQLite and PostgreSQL
const { rows } = await db<{ id: string; settings_json: Record<string, unknown> }>`
  select id, settings_json from site where id = ${siteId}
`;

```

## Coordinated Schema Migrations

Managing schema evolution across two different database engines requires careful coordination to prevent database dialect differences from causing drift.

### Split Migration Files with Shared IDs

Instatic maintains separate migration files for each dialect—[`server/db/migrations-pg.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-pg.ts) for PostgreSQL and [`server/db/migrations-sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/migrations-sqlite.ts) for SQLite—but both files share identical migration IDs. This ensures that schema versions remain synchronized across environments, even when the SQL syntax differs slightly between engines (such as PostgreSQL's `jsonb` versus SQLite's `text` for JSON storage).

### Migration Parity Testing

The test file [`src/__tests__/architecture/migration-parity.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/migration-parity.test.ts) guarantees that both migration files contain the same number of migrations with matching IDs. This automated check prevents scenarios where one dialect receives schema updates that the other lacks, ensuring consistent database states across deployment targets.

## Summary

- **Unified `DbClient` interface** in [`server/db/client.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/client.ts) abstracts placeholder syntax and connection details
- **Automatic adapter selection** via [`server/db/index.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/index.ts) chooses PostgreSQL or SQLite based on `DATABASE_URL`
- **Dialect-naïve repositories** use ANSI-standard SQL with banned PostgreSQL-isms enforced by architecture tests
- **JSON handling convention** uses `_json` column suffixes with automatic serialization in the SQLite adapter
- **Synchronized migrations** share IDs between [`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), verified by parity tests

## Frequently Asked Questions

### How does Instatic automatically select between PostgreSQL and SQLite?

Instatic checks the `DATABASE_URL` environment variable in [`server/db/index.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/index.ts). If the URL protocol is `postgres://`, it instantiates the PostgreSQL adapter from [`server/db/postgres.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/postgres.ts); otherwise, it loads the SQLite adapter from [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts). This selection occurs once at application startup, ensuring all subsequent database calls use the correct engine.

### What PostgreSQL-specific SQL features are banned in Instatic repositories?

Instatic prohibits five PostgreSQL-specific constructs to maintain cross-database compatibility: `now()`, `::int` casting, `::jsonb` casting, `any($N::...)` array operators, and `distinct on` clauses. The test suite in [`src/__tests__/architecture/db-postgres-isms.test.ts`](https://github.com/CoreBunch/Instatic/blob/main/src/__tests__/architecture/db-postgres-isms.test.ts) automatically validates that no repository files contain these dialect-specific features.

### How does Instatic handle JSON data storage differently between SQLite and PostgreSQL?

Columns storing JSON must use the `_json` suffix. The SQLite adapter in [`server/db/sqlite.ts`](https://github.com/CoreBunch/Instatic/blob/main/server/db/sqlite.ts) automatically calls `JSON.stringify()` when writing to these columns and `JSON.parse()` when reading, since SQLite stores JSON as text. The PostgreSQL adapter passes objects directly to the native `jsonb` type without transformation. This allows identical repository code to handle structured data on both engines.

### How does Instatic keep database migrations synchronized across dialects?

Migrations are split into [`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) but share identical migration IDs. 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) verifies that both files contain the same migration count and IDs, ensuring schema evolution remains coordinated across SQLite and PostgreSQL deployments.