0
# CLI Tools
1

2
Command-line interface for managing migrations, seeds, and project initialization with comprehensive tooling support for database development workflows.
3

4
## Capabilities
5

6
### Project Initialization
7

8
Initialize a new knex project with configuration files and directory structure.
9

10
```bash { .api }
11
# Initialize a new knex project
12
knex init [options]
13

14
# Options:
15
#   --knexfile <path>        Specify knexfile path (default: knexfile.js)
16
#   --cwd <path>             Specify working directory
17
#   --client <name>          Database client (pg, mysql, sqlite3, etc.)
18
#   --connection <string>    Connection string
19
#   --esm                    Generate ES module knexfile
20
```
21

22
### Migration Commands
23

24
Comprehensive migration management for database schema version control.
25

26
```bash { .api }
27
# Create a new migration file
28
knex migrate:make <name> [options]
29

30
# Run all pending migrations to latest version
31
knex migrate:latest [options]
32

33
# Run the next migration up
34
knex migrate:up [name] [options]
35

36
# Run the specified migration down
37
knex migrate:down [name] [options]
38

39
# Rollback the last batch of migrations
40
knex migrate:rollback [options]
41

42
# Show current migration version
43
knex migrate:currentVersion [options]
44

45
# List migration status (completed and pending)
46
knex migrate:list [options]
47
knex migrate:status [options]
48

49
# Force unlock migration lock (use with caution)
50
knex migrate:unlock [options]
51

52
# Migration options:
53
#   --knexfile <path>           Knexfile path
54
#   --cwd <path>                Working directory
55
#   --env <name>                Environment name
56
#   --esm                       Use ES modules
57
#   --specific <version>        Run specific migration version
58
#   --all                       Rollback all migrations (for rollback command)
59
#   --migrations-directory <path>  Migrations directory
60
#   --migrations-table-name <name>  Migration table name
61
#   --timestamp-filename-prefix     Use timestamp prefix for new migrations
62
```
63

64
### Seed Commands
65

66
Database seeding for populating tables with development and test data.
67

68
```bash { .api }
69
# Create a new seed file
70
knex seed:make <name> [options]
71

72
# Run all seed files
73
knex seed:run [options]
74

75
# Seed options:
76
#   --knexfile <path>        Knexfile path
77
#   --cwd <path>             Working directory  
78
#   --env <name>             Environment name
79
#   --esm                    Use ES modules
80
#   --specific <file>        Run specific seed file
81
#   --timestamp-filename-prefix  Use timestamp prefix for new seeds
82
```
83

84
### Global Options
85

86
Options available for all knex CLI commands.
87

88
```bash { .api }
89
# Global options (available for all commands):
90
#   --help                   Show help information
91
#   --version                Show version number
92
#   --debug                  Enable debug output
93
#   --verbose                Enable verbose output
94
#   --knexfile <path>        Path to knexfile (default: knexfile.js)
95
#   --knexpath <path>        Path to knex module
96
#   --cwd <path>             Current working directory
97
#   --client <client>        Database client override
98
#   --connection <string>    Connection string override
99
#   --env <name>             Environment name (default: development)
100
#   --esm                    Enable ES module support
101
```
102

103
### Configuration Files
104

105
Structure and options for knex configuration files.
106

107
```javascript { .api }
108
/**
109
 * Knexfile configuration object
110
 */
111
interface KnexConfig {
112
  // Database connection settings
113
  client: string;
114
  connection: string | ConnectionConfig | (() => ConnectionConfig);
115
  
116
  // Connection pool settings
117
  pool?: {
118
    min?: number;
119
    max?: number;
120
    createTimeoutMillis?: number;
121
    acquireTimeoutMillis?: number;
122
    idleTimeoutMillis?: number;
123
    reapIntervalMillis?: number;
124
    createRetryIntervalMillis?: number;
125
    propagateCreateError?: boolean;
126
  };
127

128
  // Migration settings
129
  migrations?: {
130
    database?: string;
131
    directory?: string | string[];
132
    extension?: string;
133
    tableName?: string;
134
    schemaName?: string;
135
    disableTransactions?: boolean;
136
    sortDirsSeparately?: boolean;
137
    loadExtensions?: string[];
138
    stub?: string;
139
  };
140

141
  // Seed settings
142
  seeds?: {
143
    database?: string;
144
    directory?: string | string[];
145
    loadExtensions?: string[];
146
    timestampFilenamePrefix?: boolean;
147
    stub?: string;
148
  };
149

150
  // Additional options
151
  debug?: boolean;
152
  useNullAsDefault?: boolean;
153
  acquireConnectionTimeout?: number;
154
  asyncStackTraces?: boolean;
155
}
156

157
/**
158
 * Multi-environment knexfile
159
 */
160
interface KnexFileConfig {
161
  development: KnexConfig;
162
  staging?: KnexConfig;
163
  production: KnexConfig;
164
  test?: KnexConfig;
165
  [environment: string]: KnexConfig;
166
}
167
```
168

169
### Environment Variables
170

171
Environment variables that affect knex CLI behavior.
172

173
```bash { .api }
174
# Environment variables:
175
# NODE_ENV              - Sets the default environment (development, production, etc.)
176
# KNEX_PATH             - Path to knex module
177
# DEBUG                 - Enable debug output (set to 'knex:*')
178
# DATABASE_URL          - Database connection URL
179
```
180

181
### Exit Codes
182

183
Standard exit codes returned by knex CLI commands.
184

185
```bash { .api }
186
# Exit codes:
187
# 0  - Success
188
# 1  - General error  
189
# 2  - Migration up to date (migrate:latest when no migrations to run)
190
# 3  - Migration warning/non-critical error
191
```
192

193
## File Templates
194

195
### Default Knexfile Template
196

197
```javascript { .api }
198
/**
199
 * Default knexfile.js template generated by 'knex init'
200
 */
201
module.exports = {
202
  development: {
203
    client: 'sqlite3',
204
    connection: {
205
      filename: './dev.sqlite3'
206
    },
207
    useNullAsDefault: true
208
  },
209

210
  staging: {
211
    client: 'postgresql',
212
    connection: {
213
      database: 'my_db',
214
      user: 'username', 
215
      password: 'password'
216
    },
217
    pool: {
218
      min: 2,
219
      max: 10
220
    },
221
    migrations: {
222
      tableName: 'knex_migrations'
223
    }
224
  },
225

226
  production: {
227
    client: 'postgresql',
228
    connection: {
229
      database: 'my_db',
230
      user: 'username',
231
      password: 'password'
232
    },
233
    pool: {
234
      min: 2,
235
      max: 10
236
    },
237
    migrations: {
238
      tableName: 'knex_migrations'
239
    }
240
  }
241
};
242
```
243

244
### Migration File Template
245

246
```javascript { .api }
247
/**
248
 * Default migration template generated by 'knex migrate:make'
249
 */
250
exports.up = function(knex) {
251
  // Add schema changes here
252
};
253

254
exports.down = function(knex) {
255
  // Add rollback logic here
256
};
257

258
/**
259
 * Migration with configuration
260
 */
261
exports.config = {
262
  transaction: false  // Disable transaction for this migration
263
};
264

265
exports.up = async function(knex) {
266
  // Non-transactional operations like CREATE INDEX CONCURRENTLY
267
};
268

269
exports.down = async function(knex) {
270
  // Corresponding rollback operations
271
};
272
```
273

274
### Seed File Template
275

276
```javascript { .api }
277
/**
278
 * Default seed template generated by 'knex seed:make'
279
 */
280
exports.seed = async function(knex) {
281
  // Deletes ALL existing entries
282
  await knex('table_name').del();
283
  
284
  // Inserts seed entries
285
  await knex('table_name').insert([
286
    {id: 1, colName: 'rowValue1'},
287
    {id: 2, colName: 'rowValue2'},
288
    {id: 3, colName: 'rowValue3'}
289
  ]);
290
};
291
```
292

293
## Types
294

295
```javascript { .api }
296
/**
297
 * CLI command result interface
298
 */
299
interface CommandResult {
300
  success: boolean;
301
  message?: string;
302
  data?: any;
303
  error?: Error;
304
}
305

306
/**
307
 * Migration status result
308
 */
309
interface MigrationStatus {
310
  currentVersion: string;
311
  completed: string[];
312
  pending: string[];
313
  isUpToDate: boolean;
314
}
315

316
/**
317
 * Migration list result
318
 */
319
interface MigrationList {
320
  completed: Array<{
321
    name: string;
322
    batch: number;
323
    migration_time: Date;
324
  }>;
325
  pending: string[];
326
}
327
```
328

329
**Usage Examples:**
330

331
```bash
332
# Initialize a new project
333
knex init
334
knex init --client postgresql
335
knex init --esm  # Generate ES module knexfile
336

337
# Create configuration for different databases
338
knex init --client mysql --connection mysql://user:pass@localhost/dbname
339
knex init --client sqlite3 --connection ./database.sqlite
340

341
# Create migrations
342
knex migrate:make create_users_table
343
knex migrate:make add_email_to_users --timestamp-filename-prefix
344
knex migrate:make create_posts_table --migrations-directory ./database/migrations
345

346
# Run migrations
347
knex migrate:latest                    # Run all pending migrations
348
knex migrate:latest --env production   # Run in production environment
349
knex migrate:up                        # Run next migration
350
knex migrate:up 20231201_create_users  # Run specific migration
351

352
# Rollback migrations
353
knex migrate:rollback                  # Rollback last batch
354
knex migrate:rollback --all           # Rollback all migrations
355
knex migrate:down 20231201_create_users # Rollback specific migration
356

357
# Check migration status
358
knex migrate:status                   # Show migration status
359
knex migrate:currentVersion          # Show current version
360
knex migrate:list                    # List all migrations
361

362
# Create and run seeds
363
knex seed:make users                 # Create users seed file
364
knex seed:make initial_data --timestamp-filename-prefix
365
knex seed:run                        # Run all seeds
366
knex seed:run --specific users.js    # Run specific seed
367

368
# Using different knexfiles and environments
369
knex migrate:latest --knexfile ./config/database.js
370
knex migrate:latest --env staging
371
knex migrate:latest --cwd /path/to/project
372

373
# Debug mode
374
knex migrate:latest --debug
375
DEBUG=knex:* knex migrate:latest
376

377
# ES module support
378
knex init --esm
379
knex migrate:make create_users --esm
380

381
# Advanced usage with custom directories
382
knex migrate:make create_users \
383
  --migrations-directory ./database/migrations \
384
  --migrations-table-name custom_migrations
385

386
# Production deployment example
387
knex migrate:latest \
388
  --env production \
389
  --knexfile ./production.knexfile.js \
390
  --no-color \
391
  --verbose
392

393
# Seed with specific environment
394
knex seed:run --env test
395

396
# Check if migrations are up to date (useful in CI/CD)
397
if knex migrate:status --env production | grep -q "No pending migrations"; then
398
  echo "Migrations are up to date"
399
else
400
  echo "Pending migrations found"
401
  exit 1
402
fi
403

404
# Unlock migrations (emergency use only)
405
knex migrate:unlock --env production
406

407
# Multiple database support
408
knex migrate:latest --connection postgres://user:pass@localhost/db1
409
knex migrate:latest --connection mysql://user:pass@localhost/db2
410

411
# TypeScript configuration example
412
knex init --client postgresql --esm
413
# Creates knexfile.mjs for TypeScript/ES module projects
414
```
415

416
**Knexfile Examples:**
417

418
```javascript
419
// knexfile.js - Basic configuration
420
module.exports = {
421
  client: 'postgresql',
422
  connection: process.env.DATABASE_URL,
423
  migrations: {
424
    directory: './migrations'
425
  },
426
  seeds: {
427
    directory: './seeds'
428
  }
429
};
430

431
// knexfile.js - Multi-environment with connection pooling
432
module.exports = {
433
  development: {
434
    client: 'sqlite3',
435
    connection: { filename: './dev.sqlite3' },
436
    useNullAsDefault: true,
437
    migrations: { directory: './migrations' },
438
    seeds: { directory: './seeds' }
439
  },
440

441
  production: {
442
    client: 'postgresql',
443
    connection: {
444
      connectionString: process.env.DATABASE_URL,
445
      ssl: { rejectUnauthorized: false }
446
    },
447
    pool: { min: 2, max: 20 },
448
    migrations: {
449
      directory: './migrations',
450
      tableName: 'knex_migrations'
451
    }
452
  }
453
};
454

455
// knexfile.mjs - ES module configuration
456
export default {
457
  client: 'postgresql',
458
  connection: process.env.DATABASE_URL,
459
  migrations: {
460
    directory: './migrations',
461
    extension: 'mjs'
462
  }
463
};
464
```