Instatic configuration: PostgreSQL vs SQLite Trade-offs and Best Practices

Instatic supports both PostgreSQL and SQLite through a unified adapter interface, letting you choose between horizontal scalability and operational simplicity by setting the DATABASE_URL environment variable.

Instatic, maintained by CoreBunch, ships with dual-database support that allows the same codebase to run on either PostgreSQL for production workloads or SQLite for lightweight deployments. The database selection happens at startup through the DATABASE_URL environment variable, with the system enforcing three strict dialect rules to ensure query compatibility across both engines. Understanding the architectural trade-offs between these options is essential for planning your Instatic configuration strategy.

How Database Selection Works

Instatic determines which database engine to use based on the DATABASE_URL format provided at runtime. The repository implements this abstraction through dedicated adapter files that normalize differences between the two dialects.

Set DATABASE_URL=postgres://… (or postgresql://…) to enable the PostgreSQL adapter located in server/db/postgres.ts. This adapter normalizes result rows, extracts rowCount from result.count, and handles native jsonb columns.

Omit DATABASE_URL or use a file: URL to activate the SQLite adapter in server/db/sqlite.ts. This implementation auto-stringifies JSON objects on write and parses them on read, maintaining the same DbClient interface as the PostgreSQL version.

Core Trade-offs: PostgreSQL vs SQLite

Scalability and Concurrency

PostgreSQL handles multiple simultaneous admin writers through true row-level locking and advisory locks. The system uses a dedicated server/db/advisoryLock.ts module to provide Postgres advisory-lock primitives for recurring tick loops, enabling safe multi-process access across horizontal container deployments.

SQLite limits concurrent writes to a single writer at a time using file-based locking. While suitable for one-admin or low-traffic sites, this serialization can become a bottleneck under heavy load or when multiple processes attempt simultaneous writes.

Performance Characteristics

PostgreSQL excels with large datasets and complex queries, offering native jsonb handling for JSON columns and robust connection pooling. The engine supports sharding across multiple app containers and benefits from mature query optimization.

SQLite delivers faster performance for small-to-medium sites where all data resides in a single file, eliminating network latency between the application and database layers. This makes it ideal for single-site, self-hosted installations with minimal resource requirements.

JSON Handling Implementation

The adapters handle JSON data differently to accommodate each engine's capabilities:

• PostgreSQL: Uses native jsonb columns that automatically return parsed JavaScript objects without additional serialization overhead.
• SQLite: Stores JSON in text columns ending with _json. The adapter automatically performs JSON.stringify on write and JSON.parse on read, providing identical JavaScript interfaces despite the text-based storage.

Migration Compatibility

Instatic uses additive migrations that remain identical across both dialects. Migration IDs stay in lock-step regardless of which database you choose. The placeholder() helper function in the migration system emits $N style placeholders for PostgreSQL and ? for SQLite, ensuring parameter binding works correctly on both platforms.

Backup and Recovery Strategies

PostgreSQL backups utilize pg_dump or managed service snapshots. Production deployments on Render, Railway, or similar platforms leverage these native PostgreSQL backup mechanisms as documented in the backup-restore guide.

SQLite offers simpler backup through direct file copies or Litestream replication. Restoring involves copying the .db file along with its WAL/SHM side-car files, making disaster recovery straightforward for single-server deployments.

Deployment Configuration Examples

Run Instatic with SQLite using Docker:

docker run -e DATABASE_URL=sqlite:///app/storage/db.sqlite corebunch/instatic

Deploy with the bundled PostgreSQL service using Docker Compose:

docker compose -f compose.prod.yml -f compose.build.yml up -d --build

The compose configuration automatically sets DATABASE_URL=postgres://postgres:password@postgres:5432/instatic.

Connect to an external Postgres instance for development:

export DATABASE_URL="postgresql://user:pwd@db.example.com:5432/instatic"
bun run dev

When to Choose Each Database

Choose PostgreSQL when:

• You have multiple simultaneous admin users requiring concurrent write access
• You need managed backups, high availability, or horizontal scaling across containers
• You anticipate growth beyond single-process write limitations
• You require complex queries against large datasets with native JSONB indexing

Choose SQLite when:

• You are running a single-site, low-traffic deployment with minimal operational overhead
• You prioritize simplicity and minimal resource usage over horizontal scalability
• You are configuring development and testing environments (the default configuration)
• You prefer file-based backups and single-file portability

Summary

• Instatic configuration relies on the DATABASE_URL environment variable to select between PostgreSQL and SQLite adapters
• PostgreSQL provides true concurrency, advisory locks via server/db/advisoryLock.ts, and horizontal scaling for multi-author teams
• SQLite offers lower latency for small deployments with automatic JSON serialization handled in server/db/sqlite.ts
• Both engines share migration files and use the placeholder() helper to manage dialect-specific syntax differences
• PostgreSQL stores JSON in native jsonb columns while SQLite uses _json text columns with automatic stringification
• Backup strategies differ: PostgreSQL uses pg_dump while SQLite supports simple file copying or Litestream replication

Frequently Asked Questions

How do I switch from SQLite to PostgreSQL in an existing Instatic installation?

Migrate by setting DATABASE_URL to your PostgreSQL connection string and running the migration scripts. Instatic's additive migrations apply identically to both dialects, though you must manually migrate existing data using PostgreSQL import tools or SQL dumps since the system does not provide automatic data migration between database engines.

Does Instatic support running PostgreSQL and SQLite simultaneously?

No. Instatic uses a single database connection per instance determined at startup by DATABASE_URL. You cannot run both adapters concurrently within the same process, though you can run separate Instatic instances pointing to different database types.

What are the three strict dialect rules mentioned in the database reference?

According to docs/reference/database-dialects.md, Instatic enforces dialect rules that ensure SQL compatibility across both engines: using the placeholder() helper for parameterized queries, adhering to additive-only migration strategies, and relying on the _json column naming convention for JSON data in SQLite versus native jsonb in PostgreSQL.

Why does the SQLite adapter need to stringify JSON while PostgreSQL does not?

PostgreSQL supports native jsonb binary JSON storage that preserves object structure at the database level. SQLite lacks a native JSON type (in the version used), so server/db/sqlite.ts implements automatic serialization using JSON.stringify on writes and JSON.parse on reads to maintain API compatibility with the PostgreSQL adapter while storing data as text.

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