obsidian/wiki/payloadcms/database-sqlite.md

---
title: "Database — SQLite"
aliases: [sqlite-adapter, payload-sqlite, db-sqlite]
tags: [payloadcms, database, sqlite, drizzle, libsql, turso, cloudflare-d1]
sources: [raw/database__sqlite.md]
created: 2026-05-15
updated: 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.

```ts
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\|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|Database Migrations]] for full CLI reference.

## Drizzle Direct Access

Generate types first:
```sh
npx payload generate:db-schema
```

Then query directly:
```ts
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).

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

Tip: use [Drizzle Introspection](https://orm.drizzle.team/kit-docs/commands#introspect--pull) 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`:
```ts
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:

```ts
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:

```ts
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`:

```ts
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|Database — Overview]]
- [[wiki/payloadcms/database-postgres|Database — Postgres]]
- [[wiki/payloadcms/database-mongodb|Database — MongoDB]]
- [[wiki/payloadcms/database-migrations|Database Migrations]]
- [[wiki/payloadcms/database-indexes|Database Indexes]]