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
|
# 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, particularly 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. Always test your migrations and have a backup strategy.
### 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
### Why Not Use Migration Names?
Bolt uses only the version portion of the migration filename (e.g., `20240316145038` from `20240316145038_create_users.sql`) for tracking. The descriptive message portion is purely for human readability and 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
3. **Comparison**: Bolt queries the tracking table to determine which migrations
haven't been applied
4. **Sorting**: Pending migrations are sorted by version (oldest first)
5. **Execution**: For each pending migration:
- The file is read and parsed to extract the `-- migrate:up` portion
- A 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 to a Specific Version
When you specify a target version with `bolt up -v <version>`:
- Only migrations with versions less than or equal to the target are considered
- Migrations are still applied in order from oldest to newest
- The process stops after applying the target version (or before if an error occurs)
## 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. **Sorting**: Applied migrations are sorted by version (newest first for reverting)
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
- 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
6. **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 with versions greater than or equal to the target are reverted
- Migrations are reverted in order from newest to oldest
- The process stops after reverting the target version
### 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
This is one reason why migration files should be committed to version control and never deleted.
## 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
- Determine the order in which migrations should be applied or reverted
- Track which migrations have been applied
Bolt supports two version styles:
### Timestamp Versions
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
**Trade-offs:**
- **Pros:**
- Avoids filename conflicts when multiple developers create migrations on different branches
- Embeds creation time in the version number
- **Cons:**
- Migration order may not match merge order (e.g., if a migration with an earlier timestamp is merged after one with a later timestamp, it will run out of chronological order)
- Longer, harder to remember version numbers
**Best for:** Teams working on multiple branches simultaneously where avoiding Git merge conflicts is prioritized over strict chronological ordering.
### Sequential Versions
Format: Zero-padded integers (e.g., `001`, `002`, `003`)
The version is simply an incrementing counter.
**Trade-offs:**
- **Pros:**
- Simple, easy-to-remember version numbers
- Clear sequential ordering intention
- **Cons:**
- Multiple developers creating migrations on different branches will create filename conflicts (e.g., both create `003_...sql`), requiring manual resolution during merge
- Forces explicit coordination between developers
**Best for:** Single-developer projects or teams with strict coordination where you want simple version numbers and explicit conflict resolution.
## Why can't I change between version styles?
Switching between version styles after you have existing migrations is not supported for several important reasons:
### Sorting Ambiguity
Bolt needs to sort migrations to determine the correct application order. Consider this scenario:
```
001_first_migration.sql
002_second_migration.sql
20240316145038_third_migration.sql
```
How should these be sorted? As strings:
- `001` comes before `002` comes before `20240316145038` ✓
But what if you had:
```
001_first_migration.sql
20240316145038_second_migration.sql
002_third_migration.sql
```
Now the string sorting would be:
- `001`, then `002`, then `20240316145038`
But the chronological creation order was:
- `001`, then `20240316145038`, then `002`
This creates ambiguity about the intended order.
### Parsing Complexity
Bolt parses version numbers according to the configured version style:
- **Timestamp style**: Expects exactly 14 digits in `YYYYMMDDHHMMSS` format
- **Sequential style**: Expects an integer (any length)
If you mix styles, Bolt cannot reliably parse version numbers, leading to potential errors or incorrect ordering.
### Manual Migration Path
If you absolutely need to change version styles:
1. Export your current migration state
2. Rename all migration files to use the new version style
3. Update all version entries in the tracking table to match
4. Update your `bolt.toml` or environment variables
5. Verify the migrations are correctly ordered with `bolt status`
!!! warning
This is an advanced operation and should be done carefully.
## 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`
### Purpose of the Message
The message serves several purposes:
1. **Human readability**: Makes it easy to understand what a migration does without opening the file
2. **File organization**: Helps you quickly find migrations in your editor or file browser
3. **Code review**: Makes git diffs and pull requests more understandable
4. **Documentation**: Serves as lightweight documentation of your schema changes
### What the Message is NOT Used For
- **Tracking**: Bolt uses only the version portion for tracking applied migrations
- **Ordering**: The message has no impact on migration order
- **Uniqueness**: Two migrations can have the same message (though different versions)
### Message Formatting
When you provide a message:
- Spaces are converted to underscores
- Special characters may be sanitized
- The message becomes part of the filename
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
- Follow your database's table naming rules
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 (use databases instead)
- ✗ **SQLite**: Does not support schemas
### Why These Restrictions?
These restrictions exist to:
- Ensure cross-database compatibility
- Prevent SQL injection risks
- Avoid parsing ambiguities
- Follow database best practices
### 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.
|