Database

Aphex ships with two database adapters built on Drizzle ORM, both running the same DatabaseAdapter contract and the same conformance suite — neither is a reduced tier:

• SQLite via libsql — the default. Both templates run on it out of the box: a plain local file: database, no Docker, no server, no migration step (the schema is pushed on boot). The fastest way to get running, and the reason we default to it. Scales up to a Turso-hosted libsql:// URL without code changes.
• PostgreSQL — for production scale. One env var away (APHEX_DATABASE=postgres), with Row-Level Security and connection pooling. See Switching to Postgres.

Most projects never touch this page — pnpm dev just works. The interface details at the bottom are only relevant if you're writing a custom adapter for another backend.

Quick setup

There's nothing to set up. The template defaults to SQLite, so:

pnpm dev

A local file: database is created (e.g. .aphex/base.db) and the schema is pushed on boot. To persist somewhere else or use Turso, set APHEX_SQLITE_URL:

# Local file (default if unset)
APHEX_SQLITE_URL=file:.aphex/base.db

# Turso (hosted libsql) — no code change
APHEX_SQLITE_URL=libsql://mydb-me.turso.io
DATABASE_AUTH_TOKEN=your-turso-token

Switching to Postgres

The Postgres adapter is wired into both templates — switching is one env var, no code changes:

APHEX_DATABASE=postgres
DATABASE_URL=postgres://root:my-secret-password@localhost:5432/local

pnpm db:migrate  # applies the SQL migrations in ./drizzle/

What the template wires up

src/lib/server/db/index.ts is the only file you usually touch — it picks an adapter from APHEX_DATABASE and builds a singleton DatabaseAdapter. Each adapter encapsulates its own client, Drizzle instance, schema, and dialect, so switching databases is a single env change:

import { env } from '$env/dynamic/private';
import { postgresAdapter } from './adapters/postgres';
import { sqliteAdapter } from './adapters/sqlite';

const driver = env.APHEX_DATABASE?.toLowerCase();

// Default: SQLite (zero-infra). Opt into Postgres with APHEX_DATABASE=postgres.
const database =
	driver === 'postgres'
		? await postgresAdapter({ connectionString: pgConnectionUrl(env), multiTenancy: { enableRLS: true } })
		: await sqliteAdapter({ url: env.APHEX_SQLITE_URL || 'file:.aphex/base.db' });

export const { client, drizzleDb, db, dbDialect } = database;

A single-database app can collapse this to one line — e.g. const db = createSQLiteProvider({ url }).createAdapter();. Both adapters merge CMS tables (documents, assets, schemas, organizations) with auth tables (Better Auth users, sessions, accounts), split by dialect (schema.ts for Postgres, @aphexcms/sqlite-adapter/schema for SQLite):

Connection pooling

The default pool is 10 connections. Tune it for your deployment:

export const client = postgres(pgConnectionUrl(env), {
	max: 10, // serverless: keep small (1–3); long-lived: 10–50
	idle_timeout: 20
});

SQLite (libsql)

@aphexcms/sqlite-adapter runs the same DatabaseAdapter contract on SQLite through libsql: a plain local file: database (no Docker, no server) or a Turso-hosted libsql:// URL. It is a first-class alternative to the PostgreSQL adapter, not a reduced tier — the two run the same conformance suite. Both templates use it by default.

import { createSQLiteProvider } from '@aphexcms/sqlite-adapter';

// Simplest: the adapter creates the client and tunes it for you
const db = createSQLiteProvider({ url: 'file:.aphex/blog.db' }).createAdapter();

// Turso-hosted
const db = createSQLiteProvider({
	url: 'libsql://mydb-me.turso.io',
	authToken: env.DATABASE_AUTH_TOKEN
}).createAdapter();

If you need the raw libsql client yourself (the templates share it with Better Auth and Drizzle), create it and pass it in — the adapter never modifies a client it didn't create:

import { createClient } from '@libsql/client';
import { createSQLiteProvider, applyRecommendedPragmas } from '@aphexcms/sqlite-adapter';

const client = createClient({ url });
await applyRecommendedPragmas(client, url); // see Pragmas below
const db = createSQLiteProvider({ client }).createAdapter();

SQLite has no Row-Level Security; organization isolation comes from the explicit organizationId WHERE clauses every query applies — the same mechanism that actually isolates tenants on pooled Postgres. multiTenancy.enableHierarchy works as on Postgres; there is no enableRLS option.

Pragmas

When the adapter creates the client from url, it applies a recommended pragma set to local file: databases: journal_mode=WAL (reads proceed while a write is in flight), synchronous=NORMAL (the safe WAL pairing), and busy_timeout=5000 (waits instead of throwing SQLITE_BUSY under concurrent requests). In-memory and Turso URLs are skipped — Turso manages its own journaling.

The pragmas option controls this:

// Default — the recommended set
createSQLiteProvider({ url });

// Opt out entirely and manage pragmas yourself
createSQLiteProvider({ url, pragmas: false });

// Recommended set plus extra tuning knobs (see SQLitePragmaOptions for the full list)
createSQLiteProvider({
	url,
	pragmas: {
		cacheSize: -65536, // 64 MiB page cache (negative = KiB); SQLite default ~2 MiB
		mmapSize: 268435456, // memory-mapped I/O for faster reads
		tempStore: 'MEMORY', // temp tables/indices in RAM
		busyTimeout: 10000 // overrides the default 5000
	}
});

// Raw statements, run verbatim instead of the recommended set (regardless of url)
createSQLiteProvider({ url, pragmas: 'PRAGMA busy_timeout=10000;' });

On the client path, call the exported applyRecommendedPragmas(client, url, options?) instead — it takes the same options object and skips non-local-file URLs automatically.

Migrations

drizzle.config.ts uses dialect: 'sqlite', and the CMS tables are re-exported from @aphexcms/sqlite-adapter/schema instead of the Postgres adapter. The aphex migrate CLI detects SQLite automatically from APHEX_DATABASE=sqlite or a file:/libsql: DATABASE_URL. The blog template also auto-migrates on boot — a blog deploy is single-instance, so there's no concurrent-migration race.

Behavioral parity

Both adapters run the same conformance suite (packages/sqlite-adapter/tests/), so filters, sorting, versioning, references, and org isolation behave identically — with one caveat: contains filters use SQLite's LIKE, which is case-insensitive for ASCII only (Postgres ILIKE handles full Unicode).

Multi-tenancy

Aphex is multi-tenant by nature. Every document, asset, and CMS row is stored against an organizationId, and every query is scoped to it — so one Aphex instance can serve many isolated tenants (agencies with per-client spaces, SaaS products with per-account content) with no extra modeling.

Two layers enforce isolation:

Row-Level Security (RLS)

Before each query the adapter runs:

SET LOCAL app.organization_id = '<uuid>';

The RLS policy then filters rows automatically:

That asymmetry — read down the tree, write only your own — is what enables the parent / child publishing workflows.

System operations

Background jobs, migrations, and seed scripts use systemContext(), which sets app.override_access = true and bypasses RLS:

import { systemContext } from '@aphexcms/cms-core/server';

const docs = await localAPI.collections.post.find(
	systemContext('org-id'), // overrideAccess: true
	{ perspective: 'published' }
);

Single-tenant deployments

If you don't need multi-tenancy, disable both flags. RLS still adds about 0.5–1 ms per query — measurable at scale.

const provider = createPostgreSQLProvider({
	client,
	multiTenancy: {
		enableRLS: false,
		enableHierarchy: false
	}
});

Document lifecycle

Documents follow a draft / published model with hash-based change detection:

publishedHash is a 20-character base36 hash with sorted object keys, so the admin UI can reliably show "you have unpublished changes" without diffing JSON.

Migrations workflow

Migrations only matter on the Postgres path — SQLite pushes the schema on boot, so there's no generate/migrate step. drizzle.config.ts switches dialect on APHEX_DATABASE:

import { defineConfig } from 'drizzle-kit';

const driver = process.env.APHEX_DATABASE?.toLowerCase();

export default defineConfig(
	driver === 'sqlite' || !driver
		? {
				schema: './src/lib/server/db/schema.sqlite.ts',
				dialect: 'sqlite',
				dbCredentials: { url: process.env.APHEX_SQLITE_URL || 'file:.aphex/base.db' }
			}
		: {
				schema: './src/lib/server/db/schema.ts',
				dialect: 'postgresql',
				dbCredentials: { url: databaseUrl }
			}
);

For production, always go through db:generate → review the SQL → db:migrate. db:push will happily drop a column without warning.

Advanced filtering

The adapter translates the where syntax in the Local API directly into JSONB operators:

const posts = await localAPI.collections.post.find(context, {
	where: {
		title: { contains: 'tutorial' },
		status: { equals: 'published' }
	},
	sort: ['-publishedAt'],
	limit: 10,
	depth: 1 // resolve one level of references
});

References are resolved recursively up to a configurable depth (0–5). At depth: 0, references come back as { _ref: 'doc-id' }. At higher depths the referenced document is inlined. Circular references are detected and skipped.

Table reference

You'll only need this when writing custom adapters or doing direct SQL.

cms_documents

cms_assets

Other tables

• cms_document_versions — version history (one row per draft save / publish). See Version History.
• cms_schema_types — registered document and object type definitions (fields stored as JSONB).
• cms_organizations — organizations with parentOrganizationId for hierarchy.
• cms_organization_members — user-to-organization membership with role.
• cms_organization_roles — built-in and custom roles per org with capability arrays.
• cms_invitations — pending org invitations with token and expiry.
• cms_user_sessions — tracks each user's active organization.
• cms_user_profiles — CMS-specific user data (instance role, preferences).
• cms_instance_settings — single-row instance configuration.

Custom adapters

Implement DatabaseAdapter (a composite of specialized interfaces) and pass the result to createCMSConfig({ database }):

interface DatabaseAdapter
	extends
		DocumentAdapter,
		AssetAdapter,
		UserProfileAdapter,
		SchemaAdapter,
		OrganizationAdapter,
		InstanceAdapter {
	connect?(): Promise<void>;
	disconnect?(): Promise<void>;
	isHealthy(): Promise<boolean>;

	// Multi-tenancy (optional)
	initializeRLS?(): Promise<void>;
	hierarchyEnabled: boolean;
	withOrgContext?<T>(organizationId: string, fn: () => Promise<T>): Promise<T>;
	getChildOrganizations(parentOrganizationId: string): Promise<string[]>;

	// First-user detection
	hasAnyUserProfiles?(): Promise<boolean>;
}

Each sub-interface is a focused contract:

The @aphexcms/postgresql-adapter source is the reference implementation — clone it as a starting point.

See also