0
# Migrations & Seeds
1

2
Database version control through migrations and data seeding functionality for managing schema changes and populating databases with development and production data.
3

4
## Capabilities
5

6
### Migration Management
7

8
Database migration system for version control of schema changes with up/down operations and rollback support.
9

10
```typescript { .api }
11
/**
12
 * Access the migrator instance
13
 * @returns Migrator for database migration operations
14
 */
15
migrate: Knex.Migrator;
16

17
interface Migrator {
18
  /**
19
   * Create a new migration file
20
   * @param name - Name of the migration
21
   * @param config - Optional migration configuration
22
   * @returns Promise resolving to the migration file path
23
   */
24
  make(name: string, config?: MigratorConfig): Promise<string>;
25

26
  /**
27
   * Run all pending migrations to latest
28
   * @param config - Optional migration configuration
29
   * @returns Promise with batch number and list of executed migrations
30
   */
31
  latest(config?: MigratorConfig): Promise<[number, string[]]>;
32

33
  /**
34
   * Rollback the last batch of migrations
35
   * @param config - Optional migration configuration
36
   * @param all - Whether to rollback all migrations
37
   * @returns Promise with batch number and list of rolled back migrations
38
   */
39
  rollback(config?: MigratorConfig, all?: boolean): Promise<[number, string[]]>;
40

41
  /**
42
   * Run the next migration up
43
   * @param config - Optional migration configuration
44
   * @returns Promise with batch number and list of executed migrations
45
   */
46
  up(config?: MigratorConfig): Promise<[number, string[]]>;
47

48
  /**
49
   * Run the next migration down
50
   * @param config - Optional migration configuration
51
   * @returns Promise with batch number and list of rolled back migrations
52
   */
53
  down(config?: MigratorConfig): Promise<[number, string[]]>;
54

55
  /**
56
   * Get current migration status
57
   * @param config - Optional migration configuration
58
   * @returns Promise with current migration status code
59
   */
60
  status(config?: MigratorConfig): Promise<number>;
61

62
  /**
63
   * Get current schema version
64
   * @param config - Optional migration configuration
65
   * @returns Promise with current version string
66
   */
67
  currentVersion(config?: MigratorConfig): Promise<string>;
68

69
  /**
70
   * List all migrations with their status
71
   * @param config - Optional migration configuration
72
   * @returns Promise with arrays of completed and pending migrations
73
   */
74
  list(config?: MigratorConfig): Promise<[string[], string[]]>;
75

76
  /**
77
   * Force free migration lock (use with caution)
78
   * @param config - Optional migration configuration
79
   * @returns Promise that resolves when lock is freed
80
   */
81
  forceFreeMigrationsLock(config?: MigratorConfig): Promise<void>;
82
}
83

84
interface MigratorConfig {
85
  /**
86
   * Database name for migrations (overrides connection config)
87
   */
88
  database?: string;
89

90
  /**
91
   * Directory containing migration files
92
   */
93
  directory?: string | readonly string[];
94

95
  /**
96
   * File extension for migration files (default: 'js')
97
   */
98
  extension?: string;
99

100
  /**
101
   * Name of the migrations table (default: 'knex_migrations')
102
   */
103
  tableName?: string;
104

105
  /**
106
   * Schema name for the migrations table
107
   */
108
  schemaName?: string;
109

110
  /**
111
   * Whether to disable transactions for migrations (default: false)
112
   */
113
  disableTransactions?: boolean;
114

115
  /**
116
   * Whether to sort directories separately (default: false)
117
   */
118
  sortDirsSeparately?: boolean;
119

120
  /**
121
   * File extensions to load for migration files
122
   */
123
  loadExtensions?: readonly string[];
124

125
  /**
126
   * Custom migration source interface
127
   */
128
  migrationSource?: MigrationSource<any>;
129

130
  /**
131
   * Whether to include timestamp filename prefix (default: true)
132
   */
133
  timestampFilenamePrefix?: boolean;
134

135
  /**
136
   * Stub file to use for new migrations
137
   */
138
  stub?: string;
139
}
140
```
141

142
### Seed Management
143

144
Database seeding system for populating tables with development and test data.
145

146
```typescript { .api }
147
/**
148
 * Access the seeder instance
149
 * @returns Seeder for database seeding operations
150
 */
151
seed: Knex.Seeder;
152

153
interface Seeder {
154
  /**
155
   * Create a new seed file
156
   * @param name - Name of the seed
157
   * @param config - Optional seeder configuration
158
   * @returns Promise resolving to the seed file path
159
   */
160
  make(name: string, config?: SeederConfig): Promise<string>;
161

162
  /**
163
   * Run all seed files
164
   * @param config - Optional seeder configuration
165
   * @returns Promise with array of executed seed files
166
   */
167
  run(config?: SeederConfig): Promise<[string[]]>;
168

169
  /**
170
   * Set seeder configuration
171
   * @param config - Seeder configuration
172
   * @returns Seeder instance
173
   */
174
  setConfig(config: SeederConfig): Seeder;
175
}
176

177
interface SeederConfig {
178
  /**
179
   * Database name for seeds (overrides connection config)
180
   */
181
  database?: string;
182

183
  /**
184
   * Directory containing seed files
185
   */
186
  directory?: string | readonly string[];
187

188
  /**
189
   * File extensions to load for seed files
190
   */
191
  loadExtensions?: readonly string[];
192

193
  /**
194
   * Whether to include timestamp filename prefix (default: false)
195
   */
196
  timestampFilenamePrefix?: boolean;
197

198
  /**
199
   * Whether to sort directories separately (default: false)
200
   */
201
  sortDirsSeparately?: boolean;
202

203
  /**
204
   * Stub file to use for new seeds
205
   */
206
  stub?: string;
207

208
  /**
209
   * Whether to run seeds recursively in subdirectories
210
   */
211
  recursive?: boolean;
212
}
213
```
214

215
### Migration Files
216

217
Structure and interface for migration files with up/down operations.
218

219
```typescript { .api }
220
/**
221
 * Migration file interface
222
 */
223
interface Migration {
224
  /**
225
   * Forward migration - apply schema changes
226
   * @param knex - Knex instance
227
   * @returns Promise that resolves when migration is complete
228
   */
229
  up(knex: Knex): Promise<void>;
230

231
  /**
232
   * Reverse migration - rollback schema changes
233
   * @param knex - Knex instance
234
   * @returns Promise that resolves when rollback is complete
235
   */
236
  down(knex: Knex): Promise<void>;
237

238
  /**
239
   * Optional configuration for the migration
240
   */
241
  config?: {
242
    /**
243
     * Whether to run this migration in a transaction (default: true)
244
     */
245
    transaction?: boolean;
246
  };
247
}
248

249
/**
250
 * Migration source interface for custom migration loading
251
 */
252
interface MigrationSource<TMigrationSpec> {
253
  /**
254
   * Get list of available migrations
255
   * @param loadExtensions - File extensions to load
256
   * @returns Promise with array of migration identifiers
257
   */
258
  getMigrations(loadExtensions: readonly string[]): Promise<string[]>;
259

260
  /**
261
   * Get human-readable name for a migration
262
   * @param migration - Migration identifier
263
   * @returns Human-readable migration name
264
   */
265
  getMigrationName(migration: string): string;
266

267
  /**
268
   * Load a specific migration
269
   * @param migration - Migration identifier
270
   * @returns Promise with migration specification
271
   */
272
  getMigration(migration: string): Promise<TMigrationSpec>;
273
}
274
```
275

276
### Seed Files
277

278
Structure and interface for seed files with data insertion operations.
279

280
```typescript { .api }
281
/**
282
 * Seed file interface
283
 */
284
interface Seed {
285
  /**
286
   * Seed function - insert data into database
287
   * @param knex - Knex instance
288
   * @returns Promise that resolves when seeding is complete
289
   */
290
  seed(knex: Knex): Promise<void>;
291
}
292
```
293

294
### Utility Functions
295

296
Helper functions for common migration and seeding operations.
297

298
```typescript { .api }
299
/**
300
 * Batch insert utility for large datasets
301
 * @param knex - Knex instance
302
 * @param tableName - Target table name
303
 * @param data - Array of records to insert
304
 * @param chunkSize - Number of records per batch (default: 1000)
305
 * @returns Promise that resolves when all batches are inserted
306
 */
307
function batchInsert(knex: Knex, tableName: string, data: readonly any[], chunkSize?: number): Promise<any>;
308
```
309

310
## Types
311

312
```typescript { .api }
313
type MigrationStatus = 0 | 1 | 2 | 3;
314
// 0 = No migrations have been run
315
// 1 = Latest migration batch has been run
316
// 2 = Only older migration batches have been run (ahead of latest batch)  
317
// 3 = Latest batch includes migrations older than latest migration
318

319
interface MigrationLock {
320
  is_locked: number;
321
}
322

323
interface MigrationBatch {
324
  id: number;
325
  name: string;
326
  batch: number;
327
  migration_time: Date;
328
}
329
```
330

331
**Usage Examples:**
332

333
```javascript
334
const knex = require('knex')({ 
335
  client: 'postgresql', 
336
  connection: process.env.DATABASE_URL,
337
  migrations: {
338
    directory: './migrations',
339
    tableName: 'knex_migrations'
340
  },
341
  seeds: {
342
    directory: './seeds'
343
  }
344
});
345

346
// Create a new migration
347
await knex.migrate.make('create_users_table');
348

349
// Example migration file: 20231201_create_users_table.js
350
exports.up = function(knex) {
351
  return knex.schema.createTable('users', table => {
352
    table.increments('id');
353
    table.string('email').notNullable().unique();
354
    table.string('password_hash').notNullable();
355
    table.timestamps(true, true);
356
  });
357
};
358

359
exports.down = function(knex) {
360
  return knex.schema.dropTable('users');
361
};
362

363
// Run migrations
364
await knex.migrate.latest(); // Run all pending migrations
365
await knex.migrate.rollback(); // Rollback last batch
366
await knex.migrate.rollback(null, true); // Rollback all migrations
367

368
// Check migration status
369
const status = await knex.migrate.status();
370
const [completed, pending] = await knex.migrate.list();
371
const currentVersion = await knex.migrate.currentVersion();
372

373
// Create a new seed
374
await knex.seed.make('01_users');
375

376
// Example seed file: 01_users.js
377
exports.seed = async function(knex) {
378
  // Delete existing entries
379
  await knex('users').del();
380
  
381
  // Insert seed data
382
  await knex('users').insert([
383
    {
384
      email: 'admin@example.com',
385
      password_hash: '$2b$10$...',
386
      created_at: new Date(),
387
      updated_at: new Date()
388
    },
389
    {
390
      email: 'user@example.com', 
391
      password_hash: '$2b$10$...',
392
      created_at: new Date(),
393
      updated_at: new Date()
394
    }
395
  ]);
396
};
397

398
// Run seeds
399
await knex.seed.run();
400

401
// Complex migration with transaction control
402
exports.up = function(knex) {
403
  return knex.transaction(async trx => {
404
    // Create new table
405
    await trx.schema.createTable('user_profiles', table => {
406
      table.increments('id');
407
      table.integer('user_id').references('id').inTable('users');
408
      table.string('first_name');
409
      table.string('last_name');
410
    });
411
    
412
    // Migrate existing data
413
    const users = await trx('users').select('*');
414
    for (const user of users) {
415
      await trx('user_profiles').insert({
416
        user_id: user.id,
417
        first_name: user.first_name,
418
        last_name: user.last_name
419
      });
420
    }
421
    
422
    // Drop old columns
423
    await trx.schema.alterTable('users', table => {
424
      table.dropColumn('first_name');
425
      table.dropColumn('last_name');
426
    });
427
  });
428
};
429

430
// Batch insert for large seed files
431
exports.seed = async function(knex) {
432
  const users = [];
433
  for (let i = 0; i < 10000; i++) {
434
    users.push({
435
      email: `user${i}@example.com`,
436
      password_hash: '$2b$10$...',
437
      created_at: new Date(),
438
      updated_at: new Date()
439
    });
440
  }
441
  
442
  // Insert in batches of 1000
443
  await knex.batchInsert('users', users, 1000);
444
};
445

446
// Migration with custom configuration
447
exports.config = { transaction: false };
448

449
exports.up = async function(knex) {
450
  // Operations that can't run in a transaction
451
  await knex.raw('CREATE INDEX CONCURRENTLY idx_users_email ON users(email)');
452
};
453

454
// Custom migration source
455
const customMigrationSource = {
456
  async getMigrations() {
457
    return ['001_initial', '002_add_users', '003_add_posts'];
458
  },
459
  
460
  getMigrationName(migration) {
461
    return migration;
462
  },
463
  
464
  async getMigration(migration) {
465
    const migrationModule = await import(`./custom-migrations/${migration}`);
466
    return migrationModule;
467
  }
468
};
469

470
// Use custom migration source
471
await knex.migrate.latest({
472
  migrationSource: customMigrationSource
473
});
474
```