obsidian/wiki/payloadcms/database-sqlite.md

title	aliases	tags	sources	created	updated
Database — SQLite	sqlite-adapter payload-sqlite db-sqlite	payloadcms database sqlite drizzle libsql turso cloudflare-d1	raw/database__sqlite.md	2026-05-15	2026-05-15

Overview

Payload supports SQLite via @payloadcms/db-sqlite, built on Drizzle ORM + libSQL. Auto-manages schema changes in dev mode; full migration controls for other environments.

import { sqliteAdapter } from '@payloadcms/db-sqlite'

export default buildConfig({
  db: sqliteAdapter({
    client: {
      url: process.env.DATABASE_URL,
      authToken: process.env.DATABASE_AUTH_TOKEN, // for Turso
    },
  }),
})

Key Options

Option	Default	Notes
client *	—	{ url, authToken } passed to createClient from @libsql/client
push	true (dev only)	Disable Drizzle db push in development
migrationDir	—	Custom migration directory
idType	'number'	'number' or 'uuid'
transactionOptions	—	SQLiteTransactionConfig or false to disable
localesSuffix	'_locales'	Table name suffix for localized fields
relationshipsSuffix	'_rels'	Table name suffix for relationships
versionsSuffix	'_v'	Table name suffix for versions
autoIncrement	false	SQLite AUTOINCREMENT — prevents ID reuse from deleted rows
blocksAsJSON	false	Store blocks as JSON instead of relational tables (perf boost for many blocks)
busyTimeout	0	ms to wait when DB is locked
wal	false	Enable WAL mode — see wiki/payloadcms/database-sqlite#wal-mode
beforeSchemaInit	—	Hook before schema build
afterSchemaInit	—	Hook after schema build
generateSchemaOutputFile	src/payload-generated.schema.ts	Override generated schema path
allowIDOnCreate	false	Accept id in create operations without custom ID field

Dev Mode — db push vs Migrations

• db push (default): auto-syncs schema changes to DB, no manual migration needed; warns on data loss
• Migrations only: set push: false — use payload migrate commands exclusively
• Do not mix push and manual migrate commands

See wiki/payloadcms/database-migrations for full CLI reference.

Drizzle Direct Access

Generate types first:

npx payload generate:db-schema

Then query directly:

import { posts } from './payload-generated-schema'
import { eq, sql, and } from '@payloadcms/db-sqlite/drizzle'

// Relational API
const posts = await payload.db.drizzle.query.posts.findMany()

// Select API
const result = await payload.db.drizzle
  .select()
  .from(posts)
  .where(and(eq(posts.id, 50), sql`lower(${posts.title}) = 'example post title'`))

Exposed on payload.db:

• payload.db.drizzle — Drizzle instance
• payload.db.tables — all tables
• payload.db.relations — all relations

Schema Hooks

beforeSchemaInit

Runs before schema is built. Use to add tables Payload doesn't manage (e.g. preserving existing DB structure when migrating to Payload).

sqliteAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      return {
        ...schema,
        tables: {
          ...schema.tables,
          addedTable: sqliteTable('added_table', {
            id: integer('id').primaryKey({ autoIncrement: true }),
          }),
        },
      }
    },
  ],
})

Tip: use Drizzle Introspection to generate schema from an existing DB, then import into beforeSchemaInit.

To include custom tables/columns in payload generate:db-schema output, mutate adapter.rawTables inside beforeSchemaInit:

adapter.rawTables.posts.columns.customColumn = {
  name: 'custom_column',
  type: 'integer',
  notNull: true,
}

afterSchemaInit

Runs after schema is built. Use extendTable utility to add columns or composite indexes not in Payload config:

sqliteAdapter({
  afterSchemaInit: [
    ({ schema, extendTable }) => {
      extendTable({
        table: schema.tables.places,
        columns: { extraIntegerColumn: integer('extra_integer_column') },
        extraConfig: (table) => ({
          country_city_idx: index('country_city_idx').on(table.country, table.city),
        }),
      })
      return schema
    },
  ],
})

Note: columns/tables added in afterSchemaInit won't appear in generate:db-schema output — only beforeSchemaInit + rawTables mutation achieves that.

WAL Mode

Write-Ahead Logging improves performance and concurrency:

db: sqliteAdapter({
  wal: true, // or object with settings
  client: { url: process.env.DATABASE_URL },
})

WAL Setting	Default	Notes
journalSizeLimit	67108864 (64MB)	WAL file size before checkpointing
synchronous	'FULL'	EXTRA / FULL / NORMAL / OFF — perf vs safety tradeoff

Cloudflare D1 (Beta)

Serverless SQLite via @payloadcms/db-d1-sqlite:

import { sqliteD1Adapter } from '@payloadcms/db-d1-sqlite'

export default buildConfig({
  db: sqliteD1Adapter({
    binding: cloudflare.env.D1,
    readReplicas: 'first-primary', // optional, experimental
  }),
})

• Inherits all SQLite adapter options except client (uses binding instead)
• Read replicas: 'first-primary' strategy — writes always go to primary
• Must enable read replication in Cloudflare dashboard separately

Key Takeaways

• SQLite adapter uses Drizzle ORM + libSQL — supports local files, Turso, and Cloudflare D1
• Dev mode: push: true auto-syncs schema — don't mix with manual migrations
• Production: use migrations exclusively (push: false)
• Direct Drizzle access via payload.db.drizzle — generate types first with npx payload generate:db-schema
• beforeSchemaInit for adding non-Payload tables (e.g. DB migration); afterSchemaInit for extending Payload tables
• Only rawTables mutation in beforeSchemaInit propagates to generated schema file
• WAL mode (wal: true) recommended for concurrent read-heavy workloads
• blocksAsJSON: true can improve performance when using many blocks
• autoIncrement: true prevents ID reuse — useful for audit/compliance scenarios
• Cloudflare D1 adapter is beta — same API, binding instead of client

Sources

• raw/database__sqlite.md — official Payload CMS SQLite documentation

Related

• wiki/payloadcms/database-overview
• wiki/payloadcms/database-postgres
• wiki/payloadcms/database-mongodb
• wiki/payloadcms/database-migrations
• wiki/payloadcms/database-indexes