1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
|
# Explanation
This section provides deeper explanations of how Bolt works internally and the
design decisions behind it.
## How Are Migration Scripts Executed?
Bolt executes upgrade and downgrade migration scripts in a transaction by
default. This ensures that if any errors occur during the execution of a
migration script, the transaction will be rolled back and the migration will be
marked as failed. Bolt will then exit with an error code and output the error to
standard error.
### Transaction Behavior
When you run a migration:
1. Bolt opens a database connection.
2. Begins a transaction.
3. Executes the SQL statements in the migration script.
4. If successful, commits the transaction and records the migration as applied.
5. If an error occurs, rolls back the transaction and exits with an error.
### Database-Specific Considerations
!!! warning "Auto-commit DDL Statements"
Some databases, i.e. MySQL, auto-commit certain DDL statements even
within a transaction. This means that if a migration fails partway through,
some changes may have already been committed.
### Opting Out of Transactions
For operations that cannot run within a transaction (like `CREATE INDEX
CONCURRENTLY` in PostgreSQL), you can opt out using the `transaction:false`
option:
```sql
-- migrate:up transaction:false
```
## How Does Bolt Know What Migrations Have Been Applied?
Bolt keeps track of which migrations have been applied by creating a tracking
table in your database (by default named `bolt_migrations`).
### Migrations Tracking Table
The tracking table has a simple structure with a single column:
- `version` - Stores the version identifier of each applied migration.
When you run `bolt status`, Bolt:
1. Reads all migration files from your migrations directory.
2. Queries the tracking table for applied versions.
3. Compares the two lists to show you which migrations are pending or applied.
Any pending migrations will be shown in the order they would be executed.
### Why Not Use Migration Names?
Bolt only uses the version portion of the migration filename (e.g.,
`20240316145038` from `20240316145038_create_users.sql`) for tracking.
This allows the descriptive message portion to be purely for human readability.
It is not stored in the database.
## How Are Migrations Applied?
When you run `bolt up`, the following process occurs:
1. **Discovery**: Bolt scans your migrations directory for all `.sql` files.
2. **Parsing**: Each filename is parsed to extract the version and message, and
each file is parsed to extract dependencies.
3. **Comparison**: Bolt queries the tracking table to determine which migrations
haven't been applied.
4. **Ordering**: Pending migrations are ordered based on their dependencies to
ensure each migration runs after its dependencies.
5. **Execution**: For each pending migration:
- The file is read and parsed to extract the `-- migrate:up` portion.
- All dependencies are verified to be applied.
- The transaction is started (unless `transaction:false` is specified)
- The SQL is executed
- The version is inserted into the tracking table
- The transaction is committed
6. **Reporting**: Success or failure is reported for each migration. If any
migrations fail to be applied, the process halts and no more migrations are
applied.
### Applying Up to a Specific Version
When you specify a target version with `bolt up -v <version>`, only migrations up
to and including the target version are considered.
## How Are Migrations Reverted?
When you run `bolt down`, the following process occurs:
1. **Discovery**: Bolt scans your migrations directory for all `.sql` files.
2. **Query**: Bolt queries the tracking table for applied migrations.
3. **Matching**: Applied versions are matched against local migration files.
4. **Ordering**: Applied migrations are ordered in reverse dependency order.
(migrations that depend on others are reverted first)
5. **Execution**: For the most recent applied migration (or down to a target
version):
- The file is read and parsed to extract the `-- migrate:down` portion.
- Bolt verifies that no other applied migrations depend on this one
- A transaction is started (unless `transaction:false` is specified)
- The SQL is executed
- The version is removed from the tracking table
- The transaction is committed
5. **Reporting**: Success or failure is reported for each migration. If any
migrations fail to be reverted, the process halts and no more migrations are
applied.
### Reverting to a Specific Version
When you specify a target version with `bolt down -v <version>`, all migrations
down to and including the target version are reverted.
### Missing Migration Files
!!! danger "Missing Migration Files"
If a migration has been applied (its version is in the tracking table) but
the migration file no longer exists locally, Bolt cannot revert it
automatically. You'll need to either:
- Restore the missing migration file.
- Manually revert the changes and remove the version from the tracking
table.
Generally, migration files should be committed to version control and not deleted.
## How Do Migration Dependencies Work?
Migration dependencies solve a fundamental problem in collaborative database
development: how do many developers create migrations on different branches
without conflicts once merged?
### The Problem with Version-Based Ordering
Traditionally, migration tools order migrations by version number (timestamp or
sequential).
#### Sequential
- Developer A creates migration `002` for feature X on branch A.
- Developer B creates migration `002` for feature Y on branch B.
- When branches merge, there's a filename conflict that one of them will need
to resolve.
#### Timestamp
- Developer A creates migration `20251013214743` for feature X on branch A.
- Developer B creates migration `20251013214943` for feature Y on branch B.
- Developer B merges first and applies their migration successfully.
- Developer A merges second and applies their migration successfully.
- Everything works in this environment.
However, when a fresh database is set up (staging, production, new developer):
- Migrations run in **timestamp** order: A's migration runs first, then B's
- This is the *opposite* order from the original development environment
- If B's migration depends on A's changes, the fresh setup fails, even though
the original environment worked fine.
While timestamp-versioned migrations generally won't run into filename
conflicts, they don't respect the actual dependencies between migrations
or the order they were validated in.
### The Dependency Solution
Instead of relying solely on version numbers, migrations explicitly declare
which other migrations they depend on. This creates a dependency graph where
Bolt can determine the correct execution order.
The key insight is that execution order should be based on what migrations need
to run before others, not when they were created.
### Automatic Dependency Population
Managing these migration dependencies is low-friction because Bolt automatically
populating them on creation. When you create a new migration, Bolt finds all
"leaf" migrations (migrations that nothing else depends on) and makes your new
migration depend on them.
This means:
- The first migration in a project has no dependencies.
- The second migration automatically depends on the first.
- If developers create migrations in parallel branches, a subsequent migration
will depend on both when merged.
You'll get a dependency chain by default but can manually edit dependencies for
more complex scenarios.
### How Execution Order is Determined
Bolt uses topological sorting to determine execution order. Migrations are
modeled as a directed graph:
- Each migration is a node.
- Dependencies are edges pointing from a migration to what it depends on.
- Bolt finds an ordering where every migration runs after its dependencies.
When multiple migrations have all their dependencies satisfied, Bolt uses
version number as a tiebreaker for deterministic ordering.
### Validation and Safety
Bolt validates the dependency graph before execution:
**Before applying**:
- Verifies that all dependencies exist and have been applied.
- Detects circular dependencies (A depends on B, B depends on A).
**Before reverting**: Ensures no other applied migrations depend on the
migration being reverted. This prevents breaking the dependency chain and
leaving the database in an inconsistent state.
### Why This Matters
Dependencies enable:
- **Parallel development**: Multiple developers can create migrations
simultaneously without version conflicts.
- **Flexible merging**: Migrations can be merged in any order as long as
dependencies are correct.
- **Explicit ordering**: The dependency graph makes execution order explicit
and reviewable.
- **Safer operations**: Bolt prevents invalid apply/revert operations that would
violate dependencies
## What are Migration Version Styles?
Whenever you create a migration with `bolt new`, it's prefixed with a "version".
This version is used by Bolt to:
- Uniquely identify each migration.
- Name the migration file.
- Track which migrations have been applied.
Bolt supports two version styles.
### Timestamp Versions (Default)
Format: `YYYYMMDDHHMMSS` (e.g., `20240316145038`)
The version represents the timestamp when the migration was created:
- `2024` - Year
- `03` - Month
- `16` - Day
- `14` - Hour (24-hour format)
- `50` - Minute
- `38` - Second
### Sequential Versions
Format: Zero-padded integers (e.g., `001`, `002`, `003`)
The version is simply an incrementing counter.
## Can I switch between version styles?
Yes, _if_ you do the work to migrate all the way to one version style or the
other. There is no automated tooling support for that.
In a mixed state? Kind of. It's not well supported or recommended. Some aspects
of using Bolt would not work properly if your migration file versions are in a
mixed state. Some examples:
- If you were using timestamp versions and switched to sequential versions, the
next migration would simply be the highest timestamp version incremented by 1.
- If you were using sequential versions and switched to timestamp versions, the
sorting would error out because Bolt wouldn't be able to parse the sequential
versions as timestamps.
## How is the migration message used?
When you create a migration with `bolt new -m "create users table"`, the
resulting file is named:
```
20240316145038_create_users_table.sql
```
The format is: `<version>_<message>.sql`
The message is purely for human understanding of the migration. It is not used
by Bolt for any purpose.
### Message Formatting
When you provide a message:
- Spaces are converted to underscores.
- Extra leading or trailing spaces are trimmed.
- The message is lowercased.
Example:
```bash
bolt new -m " add INDEX to users table "
# Creates: 20240316145038_add_index_to_users_table.sql
```
## What restrictions are there on the custom migration table?
While you can configure the database table that Bolt uses for tracking applied
migrations, there are restrictions on the name.
### Naming Restrictions
The migrations table name must contain only alphanumeric characters and
underscores.
Valid examples:
- `bolt_migrations` (default)
- `my_custom_migrations`
- `migrations_v2` -
- `app_schema_versions`
Invalid examples:
- `my-migrations` (contains hyphen)
- `my.custom.migrations` (contains multiple dots)
- `migrations!` (contains special character)
### Schema Qualification
You may specify a schema-qualified table name using a single dot (`.`):
```toml
[database]
migrations_table = "myschema.migrations"
```
**Schema support:**
- **PostgreSQL**: Full support for schemas
- **SQL Server**: Full support for schemas
- **MySQL**: Does not support schemas
- **SQLite**: Does not support schemas
### Existing Table Handling
!!! info
If you change the `migrations_table` setting after migrations have already
been applied, Bolt will look for the new table name. This effectively resets
your migration state unless you manually copy the data from the old table to
the new one.
|