aboutsummaryrefslogtreecommitdiffstats
path: root/docs/explanation.md
diff options
context:
space:
mode:
authorEugene Triguba <hello@eugenetriguba.com>2025-10-18 10:43:39 -0400
committerEugene Triguba <hello@eugenetriguba.com>2025-11-26 11:46:57 -0500
commite5658d1f61415054029b66cd2f703f1f40138460 (patch)
treec609e209f4cebc62dd985d1b773558f47eb73bf6 /docs/explanation.md
parentAdd is-branch main check for docs deployment (diff)
downloadcli-e5658d1f61415054029b66cd2f703f1f40138460.zip
Add support for migration dependencies
Diffstat (limited to 'docs/explanation.md')
-rw-r--r--docs/explanation.md351
1 files changed, 195 insertions, 156 deletions
diff --git a/docs/explanation.md b/docs/explanation.md
index aa6b9a7..15eb729 100644
--- a/docs/explanation.md
+++ b/docs/explanation.md
@@ -1,29 +1,38 @@
# Explanation
-This section provides deeper explanations of how Bolt works internally and the design decisions behind it.
+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.
+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
+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.
+ 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:
+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
@@ -31,203 +40,250 @@ For operations that cannot run within a transaction (like `CREATE INDEX CONCURRE
## 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`).
+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
+- `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
+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 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.
+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
+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. **Sorting**: Pending migrations are sorted by version (oldest first)
+ 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
- - 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
+ - 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 to a Specific Version
+### Applying Up 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)
+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. **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):
+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
+ - 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
-6. **Reporting**: Success or failure is reported for each migration. If any
+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 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
+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:
+ 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
+ - 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.
+ Generally, migration files should be committed to version control and not deleted.
-## What are Migration Version Styles?
+## How Do Migration Dependencies Work?
-Whenever you create a migration with `bolt new`, it's prefixed with a "version". This version is used by Bolt to:
+Migration dependencies solve a fundamental problem in collaborative database
+development: how do many developers create migrations on different branches
+without conflicts once merged?
-- Uniquely identify each migration
-- Determine the order in which migrations should be applied or reverted
-- Track which migrations have been applied
+### The Problem with Version-Based Ordering
-Bolt supports two version styles:
+Traditionally, migration tools order migrations by version number (timestamp or
+sequential).
-### Timestamp Versions
+#### Sequential
-Format: `YYYYMMDDHHMMSS` (e.g., `20240316145038`)
+- 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.
-The version represents the timestamp when the migration was created:
+#### Timestamp
-- `2024` - Year
-- `03` - Month
-- `16` - Day
-- `14` - Hour (24-hour format)
-- `50` - Minute
-- `38` - Second
+- 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.
-**Trade-offs:**
+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.
-- **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
+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.
-**Best for:** Teams working on multiple branches simultaneously where avoiding Git merge conflicts is prioritized over strict chronological ordering.
+### The Dependency Solution
-### Sequential Versions
+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.
-Format: Zero-padded integers (e.g., `001`, `002`, `003`)
+The key insight is that execution order should be based on what migrations need
+to run before others, not when they were created.
-The version is simply an incrementing counter.
+### Automatic Dependency Population
-**Trade-offs:**
+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.
-- **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
+This means:
-**Best for:** Single-developer projects or teams with strict coordination where you want simple version numbers and explicit conflict resolution.
+- 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.
-## Why can't I change between version styles?
+You'll get a dependency chain by default but can manually edit dependencies for
+more complex scenarios.
-Switching between version styles after you have existing migrations is not supported for several important reasons:
+### How Execution Order is Determined
-### Sorting Ambiguity
+Bolt uses topological sorting to determine execution order. Migrations are
+modeled as a directed graph:
-Bolt needs to sort migrations to determine the correct application order. Consider this scenario:
+- 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.
-```
-001_first_migration.sql
-002_second_migration.sql
-20240316145038_third_migration.sql
-```
+When multiple migrations have all their dependencies satisfied, Bolt uses
+version number as a tiebreaker for deterministic ordering.
-How should these be sorted? As strings:
+### Validation and Safety
-- `001` comes before `002` comes before `20240316145038` ✓
+Bolt validates the dependency graph before execution:
-But what if you had:
-```
-001_first_migration.sql
-20240316145038_second_migration.sql
-002_third_migration.sql
-```
+**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
-Now the string sorting would be:
+Dependencies enable:
-- `001`, then `002`, then `20240316145038`
+- **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
-But the chronological creation order was:
+## 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:
-- `001`, then `20240316145038`, then `002`
+- Uniquely identify each migration.
+- Name the migration file.
+- Track which migrations have been applied.
-This creates ambiguity about the intended order.
+Bolt supports two version styles.
-### Parsing Complexity
+### Timestamp Versions (Default)
-Bolt parses version numbers according to the configured version style:
+Format: `YYYYMMDDHHMMSS` (e.g., `20240316145038`)
-- **Timestamp style**: Expects exactly 14 digits in `YYYYMMDDHHMMSS` format
-- **Sequential style**: Expects an integer (any length)
+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
-If you mix styles, Bolt cannot reliably parse version numbers, leading to potential errors or incorrect ordering.
+### Sequential Versions
-### Manual Migration Path
+Format: Zero-padded integers (e.g., `001`, `002`, `003`)
-If you absolutely need to change version styles:
+The version is simply an incrementing counter.
-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`
+## Can I switch between version styles?
-!!! warning
- This is an advanced operation and should be done carefully.
+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:
+When you create a migration with `bolt new -m "create users table"`, the
+resulting file is named:
```
20240316145038_create_users_table.sql
@@ -235,50 +291,39 @@ When you create a migration with `bolt new -m "create users table"`, the resulti
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)
+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
-- Special characters may be sanitized
-- The message becomes part of the filename
+
+- 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"
+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.
+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
+The migrations table name must contain only alphanumeric characters and
+underscores.
Valid examples:
- `bolt_migrations` (default)
- `my_custom_migrations`
-- `migrations_v2`
+- `migrations_v2` -
- `app_schema_versions`
Invalid examples:
@@ -298,21 +343,15 @@ 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
+- **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.
+ 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.