# Instatic configuration: PostgreSQL vs SQLite Trade-offs and Best Practices > Explore Instatic configuration: PostgreSQL vs SQLite trade-offs for your CoreBunch/Instatic project. Choose scalability or simplicity via the DATABASE_URL. Learn best practices. - Repository: [CoreBunch/Instatic](https://github.com/CoreBunch/Instatic) - Tags: best-practices - Published: 2026-07-31 --- **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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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: ```bash docker run -e DATABASE_URL=sqlite:///app/storage/db.sqlite corebunch/instatic ``` Deploy with the bundled PostgreSQL service using Docker Compose: ```bash 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: ```bash 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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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`](https://github.com/CoreBunch/Instatic/blob/main/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.