How Instatic Handles Database Dialect Differences Between SQLite and PostgreSQL

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 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 $1for 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 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; otherwise, it initializes the SQLite adapter from 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 adapter implements DbClient using 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 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, 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, 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.

// 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 for PostgreSQL and 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 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 abstracts placeholder syntax and connection details
• Automatic adapter selection via 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 and 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. If the URL protocol is postgres://, it instantiates the PostgreSQL adapter from server/db/postgres.ts; otherwise, it loads the SQLite adapter from 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 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 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 and server/db/migrations-sqlite.ts but share identical migration IDs. The parity test in 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.

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