From e5658d1f61415054029b66cd2f703f1f40138460 Mon Sep 17 00:00:00 2001 From: Eugene Triguba Date: Sat, 18 Oct 2025 10:43:39 -0400 Subject: Add support for migration dependencies --- docs/contributing.md | 33 +++-- docs/explanation.md | 351 ++++++++++++++++++++++++++++----------------------- docs/how-to.md | 101 +++++++++++---- docs/index.md | 44 +++---- docs/installation.md | 9 +- docs/reference.md | 192 +++++++++++++++++++++------- docs/tutorial.md | 67 +++++++++- 7 files changed, 521 insertions(+), 276 deletions(-) (limited to 'docs') diff --git a/docs/contributing.md b/docs/contributing.md index 109b04a..418958f 100644 --- a/docs/contributing.md +++ b/docs/contributing.md @@ -1,8 +1,7 @@ # Contributing to Bolt -Thank you for your interest in contributing to Bolt! This document provides guidelines and instructions for contributing to the project. - -## Getting Started +Thank you for your interest in contributing to Bolt! This document provides +guidelines and instructions for contributing to the project. Bolt is hosted on [SourceHut](https://sr.ht/~eugenetriguba/Bolt/). If you're new to SourceHut, see the [git.sr.ht quickstart](https://man.sr.ht/git.sr.ht/). @@ -14,22 +13,16 @@ You do not need an account on SourceHut to contribute. Bolt uses an [email-based workflow](https://git-send-email.io/) for accepting contributions. -- Patches should be submitted via email to my [public-inbox mailing -list](https://lists.sr.ht/~eugenetriguba/public-inbox) with the subject -prefix `[PATCH bolt]`. -- General questions and discussions about the bolt project can be sent to my -[public-inbox mailing list](https://lists.sr.ht/~eugenetriguba/public-inbox). -- Bug reports can be submitted to my [public-inbox mailing -list](https://lists.sr.ht/~eugenetriguba/public-inbox). - -For general questions, discussions, or bug reports, please make it obvious in -the subject line it is for the bolt project. +- All questions, discussions, requests, or patches should be submitted via + email to my [public-inbox mailing list](https://lists.sr.ht/~eugenetriguba/public-inbox). + For patches, use the subject prefix `[PATCH bolt]`. For anything else, please + make it obvious it is for the bolt project. ## Development Setup ### Prerequisites -- Go 1.23.0 or later +- Go 1.24.0 or later - Docker (for running database integration tests) - git @@ -73,3 +66,15 @@ Run tests for a specific database: ./scripts/test.sh mssql ./scripts/test.sh sqlite3 ``` + +### Running Linters/Formatters + +To lint: +```bash +./scripts/lint.sh +``` + +To format: +```bash +./scripts/format.sh +``` 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 `: - -- 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 `, 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 `: - -- 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 `, 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: `_.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. diff --git a/docs/how-to.md b/docs/how-to.md index 5d59a39..0d5c370 100644 --- a/docs/how-to.md +++ b/docs/how-to.md @@ -4,9 +4,12 @@ This section contains guides for specific tasks you may want to accomplish with ## How to execute a migration script without a transaction -By default, Bolt executes all migrations within a transaction. However, some SQL commands cannot be executed within a transaction (for example, certain DDL operations in some databases). +By default, Bolt executes all migrations within a transaction. However, some +SQL commands cannot be executed within a transaction (for example, certain +DDL operations in some databases). -To execute a migration without a transaction, add a `transaction:false` option to the migration directives: +To execute a migration without a transaction, add a `transaction:false` option +to the migration directives: ```sql -- migrate:up transaction:false @@ -17,7 +20,65 @@ DROP INDEX CONCURRENTLY idx_users_email; ``` !!! note - The `transaction:false` option can be applied independently to `-- migrate:up` and `-- migrate:down`. You can have one run in a transaction and the other not, depending on your needs. + The `transaction:false` option can be applied independently to + `-- migrate:up` and `-- migrate:down`. You can have one run in + a transaction and the other not, depending on your needs. + +## How to make a migration depend on a specific migrations + +By default, Bolt automatically manages dependencies when you create a new +migration. To manually control which migrations must run first, add a +`-- depends:` directive at the top of the migration file. + +Single dependency: + +```sql +-- depends: 20240316145038 +-- migrate:up +CREATE TABLE posts ( + id SERIAL PRIMARY KEY, + user_id INTEGER NOT NULL REFERENCES users(id) +); + +-- migrate:down +DROP TABLE posts; +``` + +Multiple dependencies (comma-separated): + +```sql +-- depends: 20240316145038, 20240317120000 +-- migrate:up +CREATE TABLE comments ( + id SERIAL PRIMARY KEY, + post_id INTEGER NOT NULL REFERENCES posts(id), + user_id INTEGER NOT NULL REFERENCES users(id) +); + +-- migrate:down +DROP TABLE comments; +``` + +Bolt ensures all listed migrations are applied before this one runs. + +## How to determine migration execution order + +Use `bolt status` to see migrations in the order they'll be executed: + +```bash +bolt status +``` + +Output: +``` +Version Message Applied +20240316145038 create_users X +20240317120000 create_posts X +20240318093000 add_comments +``` + +Migrations are listed in dependency order. Applied migrations show an `X` in +the Applied column. ## How to jump to a specific migration version @@ -45,37 +106,29 @@ This will revert all applied migrations down to and including version `202403161 ## How to use environment variables for configuration -Instead of using a `bolt.toml` file, you can configure Bolt using environment variables. This is particularly useful for CI/CD environments or when you want to keep sensitive information out of configuration files. - -### Available environment variables - -- `BOLT_MIGRATIONS_DIR_PATH` - The directory where your migration scripts are located -- `BOLT_MIGRATIONS_VERSION_STYLE` - The migration versioning style (`timestamp` or `sequential`) -- `BOLT_DB_DSN` - The database connection string (DSN) -- `BOLT_DB_DRIVER` - The database driver (`postgresql`, `mysql`, `mssql`, or `sqlite3`) -- `BOLT_DB_MIGRATIONS_TABLE` - The name of the migrations tracking table - -### Example - ```bash export BOLT_DB_DSN="postgresql://user:password@localhost:5432/mydb?sslmode=disable" export BOLT_DB_DRIVER="postgresql" bolt up ``` +See the [reference](./reference.md#environment-variables) for the complete list +of environment variables supported. + !!! tip - Environment variables take precedence over `bolt.toml` settings. You can use a mix of both, with environment variables overriding file-based configuration when needed. + Environment variables take precedence over `bolt.toml` settings. You can + use a mix of both, with environment variables overriding file-based + configuration when needed. ## How to use a custom migrations table name -By default, Bolt uses a table named `bolt_migrations` to track applied migrations. You can customize this table name in your configuration. +By default, Bolt uses a table named `bolt_migrations` to track applied +migrations. You can customize this table name in your configuration. ### In bolt.toml ```toml [database] -dsn = "postgresql://user:password@localhost:5432/mydb" -driver = "postgresql" migrations_table = "my_custom_migrations" ``` @@ -92,12 +145,11 @@ export BOLT_DB_MIGRATIONS_TABLE="my_custom_migrations" migrations_table = "myschema.migrations" ``` -!!! warning "Restrictions" - The migrations table name must only contain alphanumeric characters or underscores (e.g., `my_custom_table`). It may contain one dot (`.`) if specifying a schema, but custom schemas are only supported with PostgreSQL and MSSQL. - ## How to use sequential versioning instead of timestamps -By default, Bolt uses timestamp-based versioning for migrations (e.g., `20240316145038`). You can switch to sequential versioning (e.g., `001`, `002`, `003`) for new projects. +By default, Bolt uses timestamp-based versioning for migrations +(e.g., `20240316145038`). You can switch to sequential versioning +(e.g., `001`, `002`, `003`) for new projects. ### In bolt.toml @@ -113,4 +165,5 @@ export BOLT_MIGRATIONS_VERSION_STYLE="sequential" ``` !!! danger "Important" - You cannot change version styles once you have existing migrations. Mixing timestamp and sequential versions is not supported. See the [Explanation](explanation.md#why-cant-i-change-between-version-styles) section for more details. + You shouldn't change version styles once you have existing migrations. + Mixing timestamp and sequential versions is not well supported. diff --git a/docs/index.md b/docs/index.md index 91b8891..b524074 100644 --- a/docs/index.md +++ b/docs/index.md @@ -5,37 +5,32 @@ [![GoDoc](https://godoc.org/git.sr.ht/~eugenetriguba/bolt?status.svg)](https://godoc.org/git.sr.ht/~eugenetriguba/bolt) [![Go Report Card](https://goreportcard.com/badge/git.sr.ht/~eugenetriguba/bolt)](https://goreportcard.com/report/git.sr.ht/~eugenetriguba/bolt) -Bolt is a command-line tool designed to simplify and streamline your database migration process. +Bolt is a command-line tool designed to simplify and streamline your database +migration process. -It is distributed as a standalone binary, making it easy to run anywhere without having to setup any tooling, and it is language independent. It doesn't matter what programming language you're using, you can use Bolt for any project you have. +It is distributed as a standalone binary, making it easy to run anywhere +without having to setup any tooling, and it is language independent. It +doesn't matter what programming language you're using, you can use Bolt +for any project you have. ## Features -- **Plain SQL migrations** - Schema migrations are written in plain SQL -- **Flexible versioning** - Migrations can be versioned sequentially or by creation timestamp -- **Transactional by default** - All migrations run in transactions by default -- **Bidirectional migrations** - Supports up and down migrations and jumping to any particular schema version -- **Multi-database support** - Supports PostgreSQL, MySQL, SQL Server, and SQLite3 +- Schema migrations are written in plain SQL +- Migrations can be versioned sequentially or by creation timestamp +- All migrations run in transactions by default +- Supports up and down migrations and jumping to any particular schema version +- Supports PostgreSQL, MySQL, SQL Server, and SQLite3 ## Quick Start ### Install Bolt -#### Go install - ```bash go install git.sr.ht/~eugenetriguba/bolt/cmd/bolt@v0.11.0 ``` This will install Bolt to your `$GOBIN` directory. Make sure that `$GOBIN` is in your `$PATH`. -#### Homebrew - -```bash -brew tap eugenetriguba/bolt https://git.sr.ht/~eugenetriguba/homebrew-bolt -brew install bolt -``` - ### Create your first migration ```bash @@ -78,13 +73,10 @@ bolt up ## Next Steps -- Follow the complete [Tutorial](tutorial.md) for a detailed walkthrough -- Check out [How-To Guides](how-to.md) for specific tasks -- Browse the [Reference](reference.md) for commands and configuration options -- Read [Explanations](explanation.md) to understand how Bolt works internally - -## Getting Help - -- Read the [documentation](https://bolt.eugenetriguba.com) -- Check the [issue tracker](https://todo.sr.ht/~eugenetriguba/bolt) -- View the [source code](https://git.sr.ht/~eugenetriguba/bolt) +- Read the [installation guide](./installation.md) for all installation options. +- Follow the [Tutorial](tutorial.md) for a detailed walkthrough. +- Check out [How-To Guides](how-to.md) for specific tasks. +- Browse the [Reference](reference.md) for commands and configuration options. +- Read [Explanations](explanation.md) to understand how Bolt works internally. +- See [Contributing](contributing.md) if you're interesting in contributing to + Bolt. diff --git a/docs/installation.md b/docs/installation.md index c45b439..24dc82a 100644 --- a/docs/installation.md +++ b/docs/installation.md @@ -1,6 +1,7 @@ # Installation -Bolt is distributed as a standalone binary that can be installed through several methods. +Bolt is distributed as a standalone binary that can be installed through +several methods. ## Go install @@ -10,7 +11,8 @@ If you have Go installed, you can install Bolt using `go install`: go install git.sr.ht/~eugenetriguba/bolt/cmd/bolt@v0.11.0 ``` -This will install Bolt to your `$GOBIN` directory. Make sure that `$GOBIN` is in your `$PATH`. +This will install Bolt to your `$GOBIN` directory. Make sure that `$GOBIN` is +in your `$PATH`. ## Homebrew @@ -23,7 +25,8 @@ brew install bolt ## Verifying Installation -After installation, verify that Bolt is correctly installed by checking the version: +After installation, verify that Bolt is correctly installed by checking the +version: ```bash bolt version diff --git a/docs/reference.md b/docs/reference.md index ac3147d..595a0a4 100644 --- a/docs/reference.md +++ b/docs/reference.md @@ -1,15 +1,17 @@ # Reference -This section provides detailed reference information for Bolt's commands, configuration options, and supported databases. +This section provides detailed reference information for Bolt's commands, +configuration options, and supported databases. ## Database Compatibility Bolt supports the following databases: -- **PostgreSQL** - All recent versions -- **MySQL** - All recent versions -- **Microsoft SQL Server** - All recent versions -- **SQLite3** - All recent versions +- **PostgreSQL** - 13+ +- **MySQL** - 5.7+ +- **Microsoft SQL Server** - 2017+ +- **SQLite3** - All recent versions. We explicitly test against the latest + version. ## Commands @@ -88,6 +90,9 @@ bolt status **Example output:** +The migrations will be ordered in the same in the same order that they would be +executed. + ``` Version Message Applied 20240316145038 my_first_migration X @@ -104,11 +109,14 @@ bolt version ## Configuration -Bolt can be configured using either a `bolt.toml` configuration file or environment variables. Environment variables take precedence over configuration file settings. +Bolt can be configured using a `bolt.toml` configuration file and environment +variables. Environment variables take precedence over configuration file +settings if both are utilized. ### Configuration File -Bolt searches for a `bolt.toml` file in your current working directory or any parent directory. +Bolt searches for a `bolt.toml` file in your current working directory or any +parent directory. **Complete example:** @@ -122,9 +130,6 @@ directory_path = "migrations" # The migration versioning style you prefer. Supported options # are "timestamp" and "sequential". Defaults to "timestamp". -# -# Note: It is not supported to change migration version styles -# i.e. you can't have a mix of sequential and timestamp migrations. version_style = "timestamp" [database] @@ -162,15 +167,20 @@ export BOLT_MIGRATIONS_DIR_PATH="db/migrations" ## Database Connection (DSN) -Bolt uses DSN (Data Source Name) for database connections. This allows you to use any connection format supported by the database driver. +Bolt uses DSN (Data Source Name) for database connections. This allows you to +use any connection format supported by the database driver. ### What is a DSN? -A DSN (Data Source Name) is a string that contains all the information needed to connect to a database. It includes the database type, connection parameters, and optional driver-specific options. +A DSN (Data Source Name) is a string that contains all the information needed +to connect to a database. It includes the database type, connection parameters, +and optional driver-specific options. ### PostgreSQL -**Driver:** [pgx](https://github.com/jackc/pgx) - [DSN Documentation](https://pkg.go.dev/github.com/jackc/pgx/v5#hdr-Connection_String_Parameters) +**Driver:** [pgx](https://github.com/jackc/pgx) + +[DSN Documentation](https://github.com/jackc/pgx/blob/v5.7.6/pgxpool/pool.go#L352) **Format:** ``` @@ -195,7 +205,9 @@ dsn = "postgresql://user:password@/mydb?host=/var/run/postgresql" ### MySQL -**Driver:** [go-sql-driver/mysql](https://github.com/go-sql-driver/mysql) - [DSN Documentation](https://github.com/go-sql-driver/mysql#dsn-data-source-name) +**Driver:** [go-sql-driver/mysql](https://github.com/go-sql-driver/mysql) + +[DSN Documentation](https://github.com/go-sql-driver/mysql#dsn-data-source-name) **Format:** ``` @@ -220,7 +232,9 @@ dsn = "user:password@unix(/tmp/mysql.sock)/mydb" ### SQL Server (MSSQL) -**Driver:** [microsoft/go-mssqldb](https://github.com/microsoft/go-mssqldb) - [DSN Documentation](https://github.com/microsoft/go-mssqldb#connection-parameters-and-dsn) +**Driver:** [microsoft/go-mssqldb](https://github.com/microsoft/go-mssqldb) + +[DSN Documentation](https://github.com/microsoft/go-mssqldb#connection-parameters-and-dsn) **Format:** ``` @@ -245,7 +259,9 @@ dsn = "sqlserver://localhost:1433?database=mydb&trusted_connection=yes" ### SQLite -**Driver:** [mattn/go-sqlite3](https://github.com/mattn/go-sqlite3) - [DSN Documentation](https://github.com/mattn/go-sqlite3#connection-string) +**Driver:** [mattn/go-sqlite3](https://github.com/mattn/go-sqlite3) + +[DSN Documentation](https://github.com/mattn/go-sqlite3#connection-string) **Format:** ``` @@ -268,30 +284,132 @@ dsn = "/path/to/mydatabase.db?mode=ro" dsn = "/path/to/mydatabase.db?_foreign_keys=on" ``` -### Security Notes +## Migration Script Directives + +Bolt supports several directives that can be added to migration files to +control behavior. + +### `-- depends:` + +Declares dependencies on other migrations. Dependencies determine execution order. + +**Syntax:** +```sql +-- depends: version1, version2, version3 +``` + +**Format Rules:** + +- Multiple versions are separated by commas +- Whitespace around commas is optional (`version1,version2` and `version1, version2` are equivalent) +- The directive is case-insensitive (`-- DEPENDS:`, `-- depends:`, `-- Depends:` all work) +- Version numbers must exactly match existing migration version numbers + +**Examples:** + +No dependencies (root migration): +```sql +-- migrate:up +CREATE TABLE users ( + id SERIAL PRIMARY KEY, + name VARCHAR(255) +); + +-- migrate:down +DROP TABLE users; +``` + +Single dependency: +```sql +-- depends: 20240316145038 +-- migrate:up +CREATE TABLE posts ( + id SERIAL PRIMARY KEY, + user_id INTEGER NOT NULL REFERENCES users(id) +); + +-- migrate:down +DROP TABLE posts; +``` + +Multiple dependencies: +```sql +-- depends: 20240316145038, 20240317120000, 20240318093000 +-- migrate:up +CREATE TABLE comments ( + id SERIAL PRIMARY KEY, + post_id INTEGER NOT NULL REFERENCES posts(id), + user_id INTEGER NOT NULL REFERENCES users(id) +); + +-- migrate:down +DROP TABLE comments; +``` + +**Execution Order:** + +Migrations are executed in topological order: +1. Migrations with no dependencies run first +2. Once a migration completes, migrations that depend only on completed + migrations become eligible +3. When multiple migrations are eligible simultaneously, they execute in + version order (timestamp or sequential) for deterministic behavior + +**Automatic Population:** + +When you create a new migration with `bolt new`, Bolt automatically populates +the `-- depends:` directive with current "leaf" migrations (migrations that +no other migration depends on). + +First migration in a project: +```bash +bolt new -m "create users" +# Generated file has no -- depends: line +``` + +Second migration: +```bash +bolt new -m "create posts" +# Generated file has: -- depends: 20240316145038 +``` -!!! warning "Security Best Practices" - - Never commit DSN strings containing passwords to version control - - Use environment variables for sensitive connection information - - For PostgreSQL and MySQL, always use SSL/TLS in production (`sslmode=require` for PostgreSQL, `tls=true` for MySQL) - - Use dedicated database users with minimal required permissions for migrations +After parallel migrations merge: +```bash +# If migrations A and B both exist and neither depends on the other +bolt new -m "create feature needing both" +# Generated file has: -- depends: , +``` -## Script Execution Options +### `-- migrate:up` and `-- migrate:down` -Bolt provides options to customize how migration scripts are executed. +These directives separate the upgrade and downgrade portions of your migration. -Options are added to the `-- migrate:up` or `-- migrate:down` comments: +**Syntax:** +```sql +-- migrate:up [option1] [option2] +-- migrate:down [option1] [option2] +``` +**Example:** ```sql --- migrate:up --- migrate:down +-- migrate:up +CREATE TABLE users ( + id SERIAL PRIMARY KEY, + name VARCHAR(255) NOT NULL +); + +-- migrate:down +DROP TABLE users; ``` ### Available Options +Options can be added to the `-- migrate:up` or `-- migrate:down` directives. + #### `transaction:false` -Execute the migration script without a transaction. By default, every migration script is executed within a transaction. +Execute the migration script without a transaction. By default, every +migration script is executed within a transaction. **Example:** @@ -303,11 +421,6 @@ CREATE INDEX CONCURRENTLY idx_users_email ON users(email); DROP INDEX CONCURRENTLY idx_users_email; ``` -**When to use:** -- PostgreSQL `CREATE INDEX CONCURRENTLY` operations -- Operations that cannot run within a transaction in your database -- When you need to handle transactions manually within your script - ## Version Styles Bolt supports two migration versioning styles: @@ -316,11 +429,6 @@ Bolt supports two migration versioning styles: Migrations are versioned using timestamps (e.g., `20240316145038`). -**Advantages:** -- Works well in teams where multiple developers create migrations simultaneously -- Timestamp indicates when the migration was created -- Less likely to have version conflicts - **Configuration:** ```toml [migrations] @@ -331,16 +439,8 @@ version_style = "timestamp" Migrations are versioned using incrementing integers (e.g., `001`, `002`, `003`). -**Advantages:** -- Simpler, more readable version numbers -- Easier to reference in conversation ("migration 5") -- Works well for solo developers or small teams with linear development - **Configuration:** ```toml [migrations] version_style = "sequential" ``` - -!!! danger "Important" - You cannot change version styles once you have existing migrations. Mixing version styles is not supported. See [Why can't I change between version styles?](explanation.md#why-cant-i-change-between-version-styles) for more details. diff --git a/docs/tutorial.md b/docs/tutorial.md index d163f23..147a6f6 100644 --- a/docs/tutorial.md +++ b/docs/tutorial.md @@ -1,6 +1,8 @@ # Tutorial -In this tutorial, we'll guide you through the basics of using Bolt by running through how you can create a migration, apply it, revert it, and see the status of your migrations. +In this tutorial, we'll guide you through the basics of using Bolt by running +through how you can create a migration, apply it, revert it, and see the +status of your migrations. ## Prerequisites @@ -22,7 +24,8 @@ Output: Created migration 20240316145038 - my first migration. ``` -Upon completion, a migrations directory will be created with the following structure: +Upon completion, a migrations directory will be created with the following +structure: ```bash migrations @@ -31,11 +34,14 @@ migrations 1 directory, 1 file ``` -Bolt automatically creates the migrations directory and your migration scripts inside it. +Bolt automatically creates the migrations directory and your migration scripts +inside it. ## Writing your migration scripts -Bolt utilizes plain SQL for migration scripts. Each migration file contains a comment for the upgrade script portion with `-- migrate:up` and a comment for the downgrade script with `-- migrate:down`. +Bolt utilizes plain SQL for migration scripts. Each migration file contains a +comment for the upgrade script portion with `-- migrate:up` and a comment for +the downgrade script with `-- migrate:down`. 1. Open up `20240316145038_my_first_migration.sql` @@ -86,7 +92,8 @@ docker run \ ## Configuring Bolt -Bolt can be configured with a `bolt.toml` file or environment variables. For simplicity, we'll use a `bolt.toml` file in this tutorial. +Bolt can be configured with a `bolt.toml` file and environment variables. For +simplicity, we'll use a `bolt.toml` file in this tutorial. 1. Create a `bolt.toml` file: @@ -130,7 +137,8 @@ Version Message Applied 20240316145038 my_first_migration X ``` -This command displays the migration's version, name, and whether the migration was applied or not. +This command displays the migration's version, name, and whether the migration +was applied or not. ## Verifying the Migration @@ -171,7 +179,8 @@ Successfully reverted migration 20240316145038_my_first_migration in 6.283667ms! ## Verifying Migration Reversion -Reconnect to the database container and check that the users table has been removed: +Reconnect to the database container and check that the users table has been +removed: ```bash docker exec -it bolt-postgres psql -U bolt_user -d bolt_tutorial_db @@ -186,6 +195,50 @@ bolt_tutorial_db=# \dt (1 row) ``` +## Creating a migration with dependencies + +Let's create another migration that depends on the first one: + +```bash +bolt new -m "create posts table" +``` + +Open the new migration file. You'll see that Bolt automatically added +a dependency on the previous migration: + +```sql +-- depends: 20240316145038 +-- migrate:up + +-- migrate:down +``` + +Add the migration SQL: + +```sql +-- depends: 20240316145038 +-- migrate:up +CREATE TABLE posts ( + id SERIAL PRIMARY KEY, + user_id INTEGER NOT NULL REFERENCES users(id), + title VARCHAR(255) NOT NULL, + content TEXT NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT NOW() +); + +-- migrate:down +DROP TABLE posts; +``` + +Apply the new migration: + +```bash +bolt up +``` + +The dependency ensures that the `users` table exists before creating the +`posts` table with its foreign key reference. + ## Next Steps Congratulations! You've learned the core features of Bolt. -- cgit v1.2.3