0
# Migration System
1

2
Comprehensive database migration utilities for schema management, data transformations, and production deployments. The migration system provides both automated schema migrations and custom migration support.
3

4
## Capabilities
5

6
### Migration Function Arguments
7

8
Standard arguments provided to all migration functions for database and API access.
9

10
```typescript { .api }
11
interface MigrateUpArgs {
12
  /**
13
   * The SQLite Drizzle instance for direct SQL execution within current transaction
14
   * Provides access to raw SQL execution and Drizzle query builder
15
   */
16
  db: Drizzle;
17
  /**
18
   * The Payload instance for Local API method execution
19
   * Use with 'req' parameter to maintain transaction context
20
   */
21
  payload: Payload;
22
  /**
23
   * The PayloadRequest object containing the current transaction
24
   * Required for transactional Local API operations
25
   */
26
  req: PayloadRequest;
27
}
28

29
interface MigrateDownArgs {
30
  /**
31
   * The SQLite Drizzle instance for direct SQL execution within current transaction
32
   * Provides access to raw SQL execution and Drizzle query builder
33
   */
34
  db: Drizzle;
35
  /**
36
   * The Payload instance for Local API method execution
37
   * Use with 'req' parameter to maintain transaction context
38
   */
39
  payload: Payload;
40
  /**
41
   * The PayloadRequest object containing the current transaction
42
   * Required for transactional Local API operations
43
   */
44
  req: PayloadRequest;
45
}
46
```
47

48
### Production Migrations
49

50
Define custom migration functions for production deployments.
51

52
```typescript { .api }
53
interface ProductionMigration {
54
  /** Migration name for tracking and identification */
55
  name: string;
56
  /** Forward migration function */
57
  up: (args: MigrateUpArgs) => Promise<void>;
58
  /** Rollback migration function */
59
  down: (args: MigrateDownArgs) => Promise<void>;
60
}
61

62
interface ProductionMigrationConfiguration {
63
  /** Array of production migration definitions */
64
  prodMigrations?: ProductionMigration[];
65
}
66
```
67

68
**Usage Examples:**
69

70
```typescript
71
import { sqliteAdapter, sql } from '@payloadcms/db-sqlite';
72
import type { MigrateUpArgs, MigrateDownArgs } from '@payloadcms/db-sqlite';
73

74
const adapter = sqliteAdapter({
75
  client: { url: './payload.db' },
76
  prodMigrations: [
77
    {
78
      name: '20240101_add_user_preferences',
79
      async up({ db, payload, req }: MigrateUpArgs) {
80
        // Direct SQL execution
81
        await db.run(sql`
82
          ALTER TABLE users 
83
          ADD COLUMN preferences TEXT DEFAULT '{}'
84
        `);
85
        
86
        // Use Payload Local API within transaction
87
        const users = await payload.find({
88
          collection: 'users',
89
          req, // Pass req to maintain transaction context
90
        });
91
        
92
        for (const user of users.docs) {
93
          await payload.update({
94
            collection: 'users',
95
            id: user.id,
96
            data: {
97
              preferences: JSON.stringify({ theme: 'light' }),
98
            },
99
            req,
100
          });
101
        }
102
      },
103
      async down({ db, payload, req }: MigrateDownArgs) {
104
        // Rollback changes
105
        await db.run(sql`
106
          ALTER TABLE users 
107
          DROP COLUMN preferences
108
        `);
109
      },
110
    },
111
  ],
112
});
113
```
114

115
### Migration Directory Configuration
116

117
Configure custom migration directory paths for automated migrations.
118

119
```typescript { .api }
120
interface MigrationDirectoryConfiguration {
121
  /** Custom path to migration files directory */
122
  migrationDir?: string;
123
}
124
```
125

126
**Usage Examples:**
127

128
```typescript
129
// Default migrations directory (./migrations)
130
const defaultMigrations = sqliteAdapter({
131
  client: { url: './payload.db' },
132
  // migrationDir not specified - uses default
133
});
134

135
// Custom migrations directory
136
const customMigrations = sqliteAdapter({
137
  client: { url: './payload.db' },
138
  migrationDir: './database/migrations',
139
});
140
```
141

142
### SQL Template Literal
143

144
Utility for constructing raw SQL queries with proper escaping and parameter binding.
145

146
```typescript { .api }
147
/**
148
 * SQL template literal function for safe query construction
149
 * Imported from drizzle-orm with proper SQLite dialect support
150
 */
151
declare const sql: <T = unknown>(
152
  strings: TemplateStringsArray,
153
  ...values: any[]
154
) => SQL<T>;
155
```
156

157
**Usage Examples:**
158

159
```typescript
160
import { sql } from '@payloadcms/db-sqlite';
161

162
// Basic migration with raw SQL
163
export async function up({ db }: MigrateUpArgs) {
164
  // Create index
165
  await db.run(sql`
166
    CREATE INDEX idx_users_email 
167
    ON users(email)
168
  `);
169
  
170
  // Insert data with parameters
171
  const userData = { name: 'Admin', email: 'admin@example.com' };
172
  await db.run(sql`
173
    INSERT INTO users (name, email) 
174
    VALUES (${userData.name}, ${userData.email})
175
  `);
176
  
177
  // Complex query with multiple parameters
178
  const threshold = 100;
179
  const status = 'active';
180
  const results = await db.run(sql`
181
    UPDATE posts 
182
    SET status = ${status}
183
    WHERE view_count > ${threshold}
184
  `);
185
}
186
```
187

188
### Migration File Structure
189

190
Standard structure for migration files in the migrations directory.
191

192
```typescript { .api }
193
/**
194
 * Standard migration file exports
195
 * Each migration file should export up and down functions
196
 */
197
interface MigrationFile {
198
  /** Forward migration function */
199
  up: (args: MigrateUpArgs) => Promise<void>;
200
  /** Rollback migration function */
201
  down: (args: MigrateDownArgs) => Promise<void>;
202
}
203
```
204

205
**Migration File Example:**
206

207
```typescript
208
// migrations/20240101_001_add_user_roles.ts
209
import type { MigrateUpArgs, MigrateDownArgs } from '@payloadcms/db-sqlite';
210
import { sql } from '@payloadcms/db-sqlite';
211

212
export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
213
  // Create roles table
214
  await db.run(sql`
215
    CREATE TABLE user_roles (
216
      id INTEGER PRIMARY KEY AUTOINCREMENT,
217
      user_id INTEGER NOT NULL,
218
      role TEXT NOT NULL,
219
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
220
      FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
221
    )
222
  `);
223
  
224
  // Migrate existing data using Payload API
225
  const users = await payload.find({
226
    collection: 'users',
227
    req,
228
  });
229
  
230
  for (const user of users.docs) {
231
    await db.run(sql`
232
      INSERT INTO user_roles (user_id, role)
233
      VALUES (${user.id}, 'user')
234
    `);
235
  }
236
}
237

238
export async function down({ db }: MigrateDownArgs): Promise<void> {
239
  await db.run(sql`DROP TABLE user_roles`);
240
}
241
```
242

243
### Transaction Context
244

245
All migrations run within database transactions, ensuring data consistency.
246

247
```typescript { .api }
248
/**
249
 * Migration execution context
250
 * All migration functions run within a database transaction
251
 * Transaction is automatically committed on success or rolled back on error
252
 */
253
interface MigrationContext {
254
  /** Automatic transaction management */
255
  autoTransaction: true;
256
  /** Transaction isolation level */
257
  isolation: 'READ_COMMITTED';
258
  /** Rollback on error */
259
  rollbackOnError: true;
260
}
261
```
262

263
**Best Practices:**
264

265
```typescript
266
export async function up({ db, payload, req }: MigrateUpArgs) {
267
  try {
268
    // All operations are automatically wrapped in a transaction
269
    await db.run(sql`ALTER TABLE posts ADD COLUMN featured BOOLEAN DEFAULT FALSE`);
270
    
271
    // Update existing records
272
    await db.run(sql`UPDATE posts SET featured = TRUE WHERE view_count > 1000`);
273
    
274
    // Use Payload API (will use same transaction via req)
275
    const featuredPosts = await payload.find({
276
      collection: 'posts',
277
      where: { featured: { equals: true } },
278
      req, // Critical: pass req to maintain transaction context
279
    });
280
    
281
    console.log(`Updated ${featuredPosts.totalDocs} featured posts`);
282
    
283
    // Transaction commits automatically on success
284
  } catch (error) {
285
    // Transaction rolls back automatically on error
286
    console.error('Migration failed:', error);
287
    throw error; // Re-throw to trigger rollback
288
  }
289
}
290
```
291

292
### Error Handling
293

294
Proper error handling patterns for migration functions.
295

296
```typescript { .api }
297
/**
298
 * Migration error handling
299
 * Errors in migration functions trigger automatic transaction rollback
300
 */
301
interface MigrationErrorHandling {
302
  /** Automatic rollback on uncaught errors */
303
  autoRollback: true;
304
  /** Error logging and reporting */
305
  errorReporting: boolean;
306
  /** Migration status tracking */
307
  statusTracking: boolean;
308
}
309
```
310

311
**Error Handling Examples:**
312

313
```typescript
314
export async function up({ db, payload, req }: MigrateUpArgs) {
315
  // Validate preconditions
316
  const tableExists = await db.get(sql`
317
    SELECT name FROM sqlite_master 
318
    WHERE type='table' AND name='users'
319
  `);
320
  
321
  if (!tableExists) {
322
    throw new Error('Users table does not exist - cannot proceed with migration');
323
  }
324
  
325
  try {
326
    // Perform migration operations
327
    await db.run(sql`ALTER TABLE users ADD COLUMN last_login DATETIME`);
328
    
329
    // Validate migration success
330
    const columnExists = await db.get(sql`
331
      PRAGMA table_info(users)
332
    `).then(info => info.some(col => col.name === 'last_login'));
333
    
334
    if (!columnExists) {
335
      throw new Error('Failed to add last_login column');
336
    }
337
    
338
  } catch (error) {
339
    // Log detailed error information
340
    console.error('Migration failed at step:', error.message);
341
    console.error('Stack trace:', error.stack);
342
    
343
    // Re-throw to trigger rollback
344
    throw error;
345
  }
346
}
347
```