SQLite support is the default for local development, testing, and edge environments. It provides a zero-config database experience using Drizzle ORM with the node:sqlite or bun:sqlite driver.

🎯 Implementation Architecture

The SQLite adapter enables a seamless transition from local development to production by implementing the same IDBAdapter interface as MongoDB and PostgreSQL.

Architecture Overview

Current Status

🚀 Performance Benchmarks (Verified)

SQLite delivers exceptional response times for local and edge deployments. For detailed performance metrics, stress test results, and cross-database comparisons, please refer to the Performance Benchmarks document.

Key SQLite highlights:

• Cache L1 hit at 0.000 ms, miss at 0.338 ms. INSERT 0.038 ms (14.2k RPS via raw INSERT…RETURNING), FIND ONE 0.008 ms (100–119k RPS), UPDATE 0.080 ms (10.5k RPS).
• WAL Mode enabled by default for concurrent read/write performance.
• Runtime Agnostic: Optimized for both bun:sqlite and node:sqlite.
• findPage / count modes: Shared SqlAdapterCore path — FIND PAGE 0.073 ms vs legacy LIST+COUNT 0.191 ms (~2.6×); COUNT CACHED 0.024 ms (2026-08-04). See Performance Architecture.
• Composite list index: createModel provisions (tenantId, status, updatedAt) — the canonical tenant list query (WHERE tenantId=? AND status=? ORDER BY updatedAt DESC LIMIT n) is served from one index. Measured at 100k rows: 194 → 68,346 RPS (EXPLAIN QUERY PLAN: SEARCH … USING INDEX vs SCAN … USE TEMP B-TREE FOR ORDER BY).
• Raw findById + findMany id ultra path: pure {_id} reads go through prepared raw SELECT (~111k RPS via findMany), bypassing Drizzle AST building.

Key Optimizations

• WAL Mode: Write-Ahead Logging enabled by default for concurrent read/write performance.

• Performance PRAGMAs: 7 optimized PRAGMAs applied on connect:

  • synchronous = NORMAL — 2-5x faster writes (safe with WAL)
  • cache_size = -20000 — 20MB page cache
  • mmap_size = 536870912 — 512MB memory-mapped I/O
  • busy_timeout = 30000 — 30s wait vs immediate SQLITE_BUSY errors
  • temp_store = memory — temp tables in RAM
  • foreign_keys = ON — enforce referential integrity

• Prepared-Statement Cache: Drizzle’s SQLiteBunSession.prepareQuery re-compiles SQL on every query; the adapter wraps client.prepare with a per-connection SQL-text cache (2,000 statements) invalidated on DDL (createModel / clearDatabase). Measured: ~4µs/query re-prepare tax eliminated, 2× on write ops.

• Projection (options.fields): when all requested fields are physical columns, the raw findById and shared findMany skip the JSON data blob entirely — no JSON.parse + flattenDataColumn on list/detail reads.

• Atomic increments bind timestamps: atomicIncrement binds updatedAt as a parameter instead of interpolating now.getTime() into SQL text — keeps the statement cache warm.

• Drizzle Integration: Type-safe querying without the overhead of a large ORM.

• Typed Collection Proxy: Fully-typed access via locals.cms.collections.typed.Posts.find().

• Platinum SQL Batching: Raw multi-VALUES insertMany (single atomic statement, chunked under the 999-param limit) — BULK INSERT (100) 177 → 248 RPS (+40%).

• Website Tokens: website_tokens table via shared relational-system.ts — hash-at-rest, tenant-scoped queries, parallel list+count. See Credential Storage.

• Mongo-only fast-path: bypassSafeQuery applies to selected MongoDB internal lookups, not SQL website-token operations.

• Zero-Allocation Dates: Optimized relational-utils with dirty-bit check.

• Atomic Versioning: Native getVersion and incrementVersion support.

📂 Storage & File Management

SQLite is a file-based database. When running SveltyCMS with SQLite, the system defaults to storing the database in /config/database/SveltyCMS.db.

Version Control Best Practices

Provisioning System & Self-Healing

The SQLite adapter implements a robust provisioning system that handles the complete lifecycle of the database file and schema:

1. Idempotent Provisioning: The provision() method checks the internal _provisioned state and only executes schema creation (CREATE TABLE IF NOT EXISTS) if necessary.
2. Explicit Reset: During integration tests or system resets, clearDatabase() drops all tables and resets the _provisioned flag, ensuring the next access re-creates a clean schema.
3. Path Auto-Creation: The adapter automatically detects if the DB_HOST directory exists and creates it recursively if missing, preventing “File not found” errors on new installations.
4. TTL-Equivalent Cleanup: cleanupExpiredData() purges expired sessions/tokens (with consumed-token GC after 7 days) via parameter-bound DELETE — replicating MongoDB’s TTL indexes for SQL (same as MariaDB/PostgreSQL).
5. Boot Migration Lock: Schema migrations run once under withMigrationLock at provision() — multi-instance boots cannot race DDL.

ESM-First Driver Loading

To comply with SvelteKit 5’s strict ESM requirements and avoid “require is not defined” errors:

• The adapter uses Dynamic Imports to load binary drivers.
• It detects the runtime environment (Bun vs Node.js) and imports the appropriate library (bun:sqlite or node:sqlite) at runtime.
• This allows a single codebase to run seamlessly across local development (Bun) and production (Node.js) environments.

WAL Mode & Performance PRAGMAs

SveltyCMS enables WAL mode and 7 performance PRAGMAs on every connection. This provides concurrent read/write operations, 2-5x faster writes, resilience under load (busy_timeout), and enforced referential integrity.

🛠️ Setup & Configuration

1. Using the Setup Wizard (Recommended)

When using the Setup Wizard, select SQLite (via Drizzle). The wizard will automatically set:

• Host: /config/database (The directory where the database file will be stored)
• Database Name: SveltyCMS.db (The filename)

2. Manual Configuration

If you are configuring the system manually, ensure your config/private.ts (or environment variables) contains:

// Example config/private.ts
export const privateEnv = {
  DB_TYPE: "sqlite",
  DB_HOST: "/config/database", // Directory path
  DB_NAME: "SveltyCMS.db", // Filename
};

The adapter will resolve the final path as process.cwd() + DB_HOST + "/" + DB_NAME.

3. Implementation Pattern

The SQLite adapter follows the modular pattern established in the core infrastructure, ensuring all seeding and user data updates work seamlessly across the agnostic codebase.

🔗 Related Documentation

• Core Infrastructure - Unified architecture
• PostgreSQL Implementation - Similar Drizzle pattern
• Drizzle ORM Documentation

Related

• Database Architecture
• Performance Benchmarks
• Architecture Overview