0
# Payload SQLite Adapter
1

2
The officially supported SQLite database adapter for PayloadCMS, providing seamless integration between Payload and SQLite databases. This adapter enables developers to use SQLite as the database backend for Payload projects, offering a lightweight, file-based database solution ideal for development, testing, and small to medium-scale applications.
3

4
## Package Information
5

6
- **Package Name**: @payloadcms/db-sqlite
7
- **Package Type**: npm  
8
- **Language**: TypeScript
9
- **Installation**: `npm install @payloadcms/db-sqlite`
10

11
## Core Imports
12

13
```typescript
14
import { sqliteAdapter } from "@payloadcms/db-sqlite";
15
```
16

17
For type imports:
18

19
```typescript
20
import type { SQLiteAdapterArgs, SQLiteAdapter, MigrateUpArgs, MigrateDownArgs } from "@payloadcms/db-sqlite";
21
```
22

23
CommonJS:
24

25
```javascript
26
const { sqliteAdapter } = require("@payloadcms/db-sqlite");
27
```
28

29
## Basic Usage
30

31
```typescript
32
import { buildConfig } from 'payload/config';
33
import { sqliteAdapter } from '@payloadcms/db-sqlite';
34

35
export default buildConfig({
36
  db: sqliteAdapter({
37
    client: {
38
      url: process.env.DATABASE_URI || './payload.db',
39
    },
40
  }),
41
  // ...rest of config
42
});
43
```
44

45
## Architecture
46

47
The SQLite adapter is built around several key components:
48

49
- **Database Adapter**: Core `sqliteAdapter` function that creates a Payload-compatible database adapter
50
- **Drizzle Integration**: Utilizes Drizzle ORM for type-safe database operations and query building
51
- **LibSQL Support**: Supports both traditional SQLite and LibSQL (Turso) clients for flexible deployment
52
- **Schema Management**: Automatic schema generation, migration handling, and table management
53
- **Type Safety**: Full TypeScript integration with comprehensive type definitions
54
- **Hook System**: Before/after schema initialization hooks for custom schema transformations
55

56
## Capabilities
57

58
### SQLite Adapter Configuration
59

60
Core adapter factory function for creating SQLite database adapters with comprehensive configuration options.
61

62
```typescript { .api }
63
function sqliteAdapter(args: SQLiteAdapterArgs): DatabaseAdapterObj<SQLiteAdapter>;
64

65
interface SQLiteAdapterArgs {
66
  /** LibSQL client configuration (required) */
67
  client: {
68
    url: string;
69
    authToken?: string;
70
    [key: string]: any;
71
  };
72
  /** ID type selection - 'number' for auto-increment integers, 'uuid' for UUIDs */
73
  idType?: 'number' | 'uuid';
74
  /** Enable custom ID specification during create operations */
75
  allowIDOnCreate?: boolean;
76
  /** Enable AUTOINCREMENT for Primary Keys to prevent ID reuse */
77
  autoIncrement?: boolean;
78
  /** Store blocks as JSON column instead of relational structure */
79
  blocksAsJSON?: boolean;
80
  /** SQLite transaction configuration */
81
  transactionOptions?: false | SQLiteTransactionConfig;
82
  /** Migration directory path */
83
  migrationDir?: string;
84
  /** Suffix for locale tables */
85
  localesSuffix?: string;
86
  /** Suffix for relationship tables */
87
  relationshipsSuffix?: string;
88
  /** Suffix for version tables */
89
  versionsSuffix?: string;
90
  /** Schema name */
91
  schemaName?: string;
92
  /** Generated schema output file path */
93
  generateSchemaOutputFile?: string;
94
  /** Drizzle logger configuration */
95
  logger?: DrizzleConfig['logger'];
96
  /** Production migration definitions */
97
  prodMigrations?: {
98
    down: (args: MigrateDownArgs) => Promise<void>;
99
    name: string;
100
    up: (args: MigrateUpArgs) => Promise<void>;
101
  }[];
102
  /** Enable schema push mode */
103
  push?: boolean;
104
  /** Pre-schema initialization hooks */
105
  beforeSchemaInit?: SQLiteSchemaHook[];
106
  /** Post-schema initialization hooks */
107
  afterSchemaInit?: SQLiteSchemaHook[];
108
}
109
```
110

111
[Adapter Configuration](./adapter-configuration.md)
112

113
### Migration System
114

115
Comprehensive migration utilities for database schema management and data transformations.
116

117
```typescript { .api }
118
interface MigrateUpArgs {
119
  /** SQLite Drizzle instance for direct SQL execution within transaction */
120
  db: Drizzle;
121
  /** Payload instance for Local API method execution */
122
  payload: Payload;
123
  /** PayloadRequest object containing current transaction */
124
  req: PayloadRequest;
125
}
126

127
interface MigrateDownArgs {
128
  /** SQLite Drizzle instance for direct SQL execution within transaction */
129
  db: Drizzle;
130
  /** Payload instance for Local API method execution */
131
  payload: Payload;
132
  /** PayloadRequest object containing current transaction */
133
  req: PayloadRequest;
134
}
135
```
136

137
[Migration System](./migration-system.md)
138

139
### Drizzle ORM Integration
140

141
Direct access to Drizzle ORM functionality for advanced database operations and custom queries.
142

143
```typescript { .api }
144
// Re-exported from drizzle-orm
145
import { sql } from "@payloadcms/db-sqlite";
146

147
// Access to Drizzle modules via subpaths
148
import * as drizzleCore from "@payloadcms/db-sqlite/drizzle";
149
import * as sqliteCore from "@payloadcms/db-sqlite/drizzle/sqlite-core";
150
import * as libsql from "@payloadcms/db-sqlite/drizzle/libsql";
151
import * as relations from "@payloadcms/db-sqlite/drizzle/relations";
152
```
153

154
[Drizzle Integration](./drizzle-integration.md)
155

156
## Types
157

158
```typescript { .api }
159
interface SQLiteAdapter extends DrizzleAdapter {
160
  /** Schema transformation hooks executed after schema building */
161
  afterSchemaInit: SQLiteSchemaHook[];
162
  /** Enable AUTOINCREMENT for Primary Keys */
163
  autoIncrement: boolean;
164
  /** Schema transformation hooks executed before schema building */
165
  beforeSchemaInit: SQLiteSchemaHook[];
166
  /** LibSQL client instance */
167
  client: Client;
168
  /** Original client configuration */
169
  clientConfig: SQLiteAdapterArgs['client'];
170
  /** Count distinct values utility */
171
  countDistinct: CountDistinct;
172
  /** Default Drizzle snapshot */
173
  defaultDrizzleSnapshot: any;
174
  /** Conditional delete utility */
175
  deleteWhere: DeleteWhere;
176
  /** Drizzle database instance */
177
  drizzle: Drizzle;
178
  /** Drop database utility */
179
  dropDatabase: DropDatabase;
180
  /** SQL execution utility */
181
  execute: Execute<unknown>;
182
  /** Field constraint mappings for error handling */
183
  fieldConstraints: Record<string, Record<string, string>>;
184
  /** ID type configuration */
185
  idType: 'number' | 'uuid';
186
  /** Initialization promise */
187
  initializing: Promise<void>;
188
  /** Insert operation utility */
189
  insert: Insert;
190
  /** Locale table suffix */
191
  localesSuffix?: string;
192
  /** Drizzle logger */
193
  logger: DrizzleConfig['logger'];
194
  /** Query operators mapping */
195
  operators: Operators;
196
  /** Production migrations */
197
  prodMigrations?: {
198
    down: (args: MigrateDownArgs) => Promise<void>;
199
    name: string;
200
    up: (args: MigrateUpArgs) => Promise<void>;
201
  }[];
202
  /** Schema push mode enabled */
203
  push: boolean;
204
  /** Initialization rejection handler */
205
  rejectInitializing: () => void;
206
  /** Database relations */
207
  relations: Record<string, GenericRelation>;
208
  /** Relationship table suffix */
209
  relationshipsSuffix?: string;
210
  /** Initialization resolution handler */
211
  resolveInitializing: () => void;
212
  /** Complete database schema */
213
  schema: Record<string, GenericRelation | GenericTable>;
214
  /** Schema name */
215
  schemaName?: string;
216
  /** Table name mappings */
217
  tableNameMap: Map<string, string>;
218
  /** Database tables */
219
  tables: Record<string, GenericTable>;
220
  /** SQLite transaction configuration */
221
  transactionOptions: SQLiteTransactionConfig;
222
  /** Version table suffix */
223
  versionsSuffix?: string;
224
}
225

226
type SQLiteSchemaHook = (args: {
227
  extendTable: typeof extendDrizzleTable;
228
  schema: {
229
    relations: Record<string, GenericRelation>;
230
    tables: Record<string, SQLiteTableWithColumns<any>>;
231
  };
232
}) => Promise<SQLiteSchema> | SQLiteSchema;
233

234
type IDType = 'integer' | 'numeric' | 'text';
235

236
type GenericTable = SQLiteTableWithColumns<{
237
  columns: GenericColumns;
238
  dialect: string;
239
  name: string;
240
  schema: string;
241
}>;
242

243
type GenericColumns = {
244
  [x: string]: AnySQLiteColumn;
245
};
246

247
type GenericRelation = Relations<string, Record<string, Relation<string>>>;
248
```