0
# Migrations
1

2
SQL-based migration system supporting up/down migrations, rollback functionality, and migration state management for database schema evolution.
3

4
## Capabilities
5

6
### Migration Execution
7

8
Run database migrations with comprehensive configuration options and rollback support.
9

10
```typescript { .api }
11
// Available on Database instance
12
migrate(config?: MigrationParams): Promise<void>;
13

14
interface MigrationParams {
15
  /** Force rollback and re-apply the latest migration on each app launch */
16
  force?: boolean;
17
  /** Migration table name (default: 'migrations') */
18
  table?: string;
19
  /** Path to migrations folder (default: path.join(process.cwd(), 'migrations')) */
20
  migrationsPath?: string;
21
  /** Migration data array (if provided, migrationsPath is ignored) */
22
  migrations?: readonly MigrationData[];
23
}
24
```
25

26
### Migration File Structure
27

28
Migrations are stored as SQL files with a specific naming convention and structure:
29

30
```typescript { .api }
31
interface MigrationFile {
32
  /** Migration ID extracted from filename */
33
  id: number;
34
  /** Migration name extracted from filename */
35
  name: string;
36
  /** Full filename */
37
  filename: string;
38
}
39

40
interface MigrationData {
41
  /** Migration ID */
42
  id: number;
43
  /** Migration name */
44
  name: string;
45
  /** SQL for applying the migration */
46
  up: string;
47
  /** SQL for rolling back the migration */
48
  down: string;
49
}
50
```
51

52
**Migration File Naming Convention:**
53
- Files must follow the pattern: `{id}.{name}.sql`
54
- Examples: `001-initial.sql`, `002-add-users-table.sql`, `003-user-indexes.sql`
55

56
**Migration File Content Structure:**
57
```sql
58
-- Up migration (applied when migrating forward)
59
CREATE TABLE users (
60
  id INTEGER PRIMARY KEY AUTOINCREMENT,
61
  name TEXT NOT NULL,
62
  email TEXT UNIQUE NOT NULL,
63
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
64
);
65

66
-- down
67
-- Down migration (applied when rolling back)
68
DROP TABLE users;
69
```
70

71
**Usage Examples:**
72

73
```typescript
74
import { open } from "sqlite";
75
import sqlite3 from "sqlite3";
76

77
const db = await open({
78
  filename: "./app.db",
79
  driver: sqlite3.Database
80
});
81

82
// Run migrations from default ./migrations directory
83
await db.migrate();
84

85
// Run migrations from custom directory
86
await db.migrate({
87
  migrationsPath: "./database/migrations"
88
});
89

90
// Use custom migration table name
91
await db.migrate({
92
  table: "schema_versions",
93
  migrationsPath: "./migrations"
94
});
95

96
// Force re-apply latest migration (useful for development)
97
await db.migrate({
98
  force: true
99
});
100

101
// Use programmatic migration data instead of files
102
const migrationData: MigrationData[] = [
103
  {
104
    id: 1,
105
    name: "initial",
106
    up: "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);",
107
    down: "DROP TABLE users;"
108
  },
109
  {
110
    id: 2,
111
    name: "add-email",
112
    up: "ALTER TABLE users ADD COLUMN email TEXT;",
113
    down: "ALTER TABLE users DROP COLUMN email;"
114
  }
115
];
116

117
await db.migrate({
118
  migrations: migrationData
119
});
120
```
121

122
### Migration Directory Setup
123

124
Create a migrations directory with properly structured SQL files:
125

126
```
127
migrations/
128
├── 001-initial.sql
129
├── 002-create-users.sql
130
├── 003-create-posts.sql
131
└── 004-add-indexes.sql
132
```
133

134
**Example Migration Files:**
135

136
`001-initial.sql`:
137
```sql
138
-- Create initial database structure
139
CREATE TABLE schema_info (
140
  version INTEGER PRIMARY KEY,
141
  applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
142
);
143

144
-- down
145
DROP TABLE schema_info;
146
```
147

148
`002-create-users.sql`:
149
```sql
150
-- Create users table
151
CREATE TABLE users (
152
  id INTEGER PRIMARY KEY AUTOINCREMENT,
153
  username TEXT UNIQUE NOT NULL,
154
  email TEXT UNIQUE NOT NULL,
155
  password_hash TEXT NOT NULL,
156
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
157
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
158
);
159

160
-- Create trigger to update updated_at
161
CREATE TRIGGER update_users_updated_at 
162
  AFTER UPDATE ON users
163
BEGIN
164
  UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
165
END;
166

167
-- down
168
DROP TRIGGER IF EXISTS update_users_updated_at;
169
DROP TABLE users;
170
```
171

172
`003-create-posts.sql`:
173
```sql
174
-- Create posts table
175
CREATE TABLE posts (
176
  id INTEGER PRIMARY KEY AUTOINCREMENT,
177
  title TEXT NOT NULL,
178
  content TEXT,
179
  author_id INTEGER NOT NULL,
180
  published BOOLEAN DEFAULT FALSE,
181
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
182
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
183
  FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
184
);
185

186
-- down
187
DROP TABLE posts;
188
```
189

190
`004-add-indexes.sql`:
191
```sql
192
-- Add performance indexes
193
CREATE INDEX idx_users_email ON users(email);
194
CREATE INDEX idx_users_username ON users(username);
195
CREATE INDEX idx_posts_author ON posts(author_id);
196
CREATE INDEX idx_posts_published ON posts(published);
197
CREATE INDEX idx_posts_created ON posts(created_at);
198

199
-- down
200
DROP INDEX IF EXISTS idx_posts_created;
201
DROP INDEX IF EXISTS idx_posts_published;
202
DROP INDEX IF EXISTS idx_posts_author;
203
DROP INDEX IF EXISTS idx_users_username;
204
DROP INDEX IF EXISTS idx_users_email;
205
```
206

207
### Migration Behavior
208

209
The migration system provides intelligent migration management:
210

211
1. **Automatic Migration Table**: Creates a `migrations` table (or custom name) to track applied migrations
212
2. **Sequential Application**: Applies migrations in ID order, skipping already-applied migrations
213
3. **Rollback Support**: Can rollback migrations that exist in database but not in files
214
4. **Force Mode**: When `force: true`, rolls back and re-applies the latest migration
215
5. **Transaction Safety**: Each migration runs in a transaction - if it fails, changes are rolled back
216
6. **Validation**: Ensures migration IDs are sequential and unique
217

218
### Advanced Migration Patterns
219

220
**Development Workflow with Force Mode:**
221
```typescript
222
// During development, force re-apply latest migration
223
if (process.env.NODE_ENV === 'development') {
224
  await db.migrate({ force: true });
225
} else {
226
  await db.migrate();
227
}
228
```
229

230
**Multiple Database Migration:**
231
```typescript
232
const databases = await Promise.all([
233
  open({ filename: "./users.db", driver: sqlite3.Database }),
234
  open({ filename: "./products.db", driver: sqlite3.Database }),
235
  open({ filename: "./orders.db", driver: sqlite3.Database })
236
]);
237

238
// Run migrations on all databases
239
await Promise.all(databases.map(db => db.migrate({
240
  migrationsPath: "./migrations"
241
})));
242
```
243

244
**Custom Migration Logic:**
245
```typescript
246
// Read and modify migrations programmatically
247
import { readMigrations } from "sqlite/utils/migrate";
248

249
const migrationData = await readMigrations("./migrations");
250

251
// Add environment-specific modifications
252
const modifiedMigrations = migrationData.map(migration => ({
253
  ...migration,
254
  up: process.env.NODE_ENV === 'test' 
255
    ? migration.up.replace('users', 'test_users')
256
    : migration.up
257
}));
258

259
await db.migrate({
260
  migrations: modifiedMigrations
261
});
262
```
263

264
### Error Handling
265

266
Proper error handling for migration operations:
267

268
```typescript
269
try {
270
  await db.migrate();
271
  console.log("Migrations completed successfully");
272
} catch (error) {
273
  console.error("Migration failed:", error);
274
  
275
  // Migrations are automatically rolled back on failure
276
  // Check migration state
277
  const appliedMigrations = await db.all(
278
    "SELECT id, name FROM migrations ORDER BY id"
279
  );
280
  console.log("Currently applied migrations:", appliedMigrations);
281
  
282
  throw error; // Re-throw to halt application startup
283
}
284
```
285

286
## Utility Functions
287

288
```typescript { .api }
289
/**
290
 * Read migration files from a directory and parse their content
291
 * @param migrationPath - Path to migrations directory (default: './migrations')
292
 * @returns Promise resolving to array of migration data with parsed up/down SQL
293
 */
294
function readMigrations(migrationPath?: string): Promise<MigrationData[]>;
295
```
296

297
This utility function is available for advanced use cases where you need to read and process migration files programmatically before applying them. The function reads migration files following the `{id}-{name}.sql` naming pattern and parses the SQL content into `up` and `down` sections.
298

299
**Usage Examples:**
300

301
```typescript
302
import { readMigrations } from "sqlite/utils/migrate";
303

304
// Read migrations from default directory
305
const migrations = await readMigrations();
306
console.log(`Found ${migrations.length} migration files`);
307

308
// Read migrations from custom directory
309
const customMigrations = await readMigrations("./database/migrations");
310

311
// Process migrations programmatically
312
const processedMigrations = migrations.map(migration => ({
313
  ...migration,
314
  up: `-- Environment: ${process.env.NODE_ENV}\n${migration.up}`,
315
  down: migration.down
316
}));
317

318
// Use with database migrate method
319
await db.migrate({
320
  migrations: processedMigrations
321
});
322
```
323

324
**Return Value Structure:**
325

326
Each migration object returned contains:
327
- `id`: Migration ID extracted from filename
328
- `name`: Migration name extracted from filename  
329
- `up`: SQL statements for applying the migration (comments removed)
330
- `down`: SQL statements for rolling back the migration (comments removed)