diff options
| author | Eugene Triguba <eugenetriguba@gmail.com> | 2024-01-13 23:21:39 -0500 |
|---|---|---|
| committer | Eugene Triguba <eugenetriguba@gmail.com> | 2024-01-13 23:21:39 -0500 |
| commit | 2212743dcf34fa34aeebbb2292152cc4d9d6dcdf (patch) | |
| tree | b3b2370325f62685102ba6be01ff985ddab30b73 | |
| parent | Update configuration format and env names (diff) | |
| download | cli-2212743dcf34fa34aeebbb2292152cc4d9d6dcdf.zip | |
Add support for turning off transaction execution
| -rw-r--r-- | README.md | 21 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo.go | 107 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo_test.go | 55 | ||||
| -rw-r--r-- | internal/sqlparse/sqlparse.go | 44 | ||||
| -rw-r--r-- | internal/sqlparse/sqlparse_test.go | 56 |
5 files changed, 251 insertions, 32 deletions
@@ -28,6 +28,8 @@ Bolt is a command-line tool designed to simplify and streamline your database mi - [Downgrading Your Migration](#downgrading-your-migration) - [Verifying Migration Reversion](#verifying-migration-reversion) - [Next Steps](#next-steps) +- [How-to](#how-to) + - [How to execute a migration script without a transaction](#how-to-execute-a-migration-script-without-a-transaction) - [Reference](#reference) - [Database Compatibility](#database-compatibility) - [Configuration](#configuration) @@ -38,6 +40,7 @@ Bolt is a command-line tool designed to simplify and streamline your database mi - [`bolt up`](#bolt-up) - [`bolt down`](#bolt-down) - [`bolt status`](#bolt-status) + - [Script Execution Options](#script-execution-options) - [Explanation](#explanation) - [How Are Migration Scripts Executed?](#how-are-migration-scripts-executed) - [How Does Bolt Know What Migrations Have Been Applied?](#how-does-bolt-know-what-migrations-have-been-applied) @@ -214,6 +217,16 @@ bolt_tutorial_db=# \dt Congrats! You've learned the core features of Bolt. If you want learn more, see the [Reference](#reference) or [Explanation](#explanation) section. +## How-to + +### How to execute a migration script without a transaction + +In your `upgrade.sql` or `downgrade.sql`, add the following line as the first line of your script: + +```sql +-- bolt: no-transaction +``` + ## Reference ### Database Compatibility @@ -315,6 +328,14 @@ status: List the database migrations and their statuses ``` +### Script Execution Options + +Bolt provides a way for you to customize how your migration scripts are executed if you need something different than the default behavior. + +You can do this by adding a comment to the top of your migration script. The comment must be in the following format: `-- bolt: <option>`. The following options are available: + +- `no-transaction`: Execute the migration script without a transaction. By default, every migration script will be attempted to be executed within a transaction, however, some SQL commands cannot be executed within a transaction so you'll need to opt out of that behavior in those cases. + ## Explanation ### How Are Migration Scripts Executed? diff --git a/internal/repositories/migration_db_repo.go b/internal/repositories/migration_db_repo.go index 6a37e7b..42cf0e8 100644 --- a/internal/repositories/migration_db_repo.go +++ b/internal/repositories/migration_db_repo.go @@ -6,6 +6,7 @@ import ( "strings" "github.com/eugenetriguba/bolt/internal/models" + "github.com/eugenetriguba/bolt/internal/sqlparse" ) type MigrationDBRepo struct { @@ -87,27 +88,48 @@ func (mr *MigrationDBRepo) Apply( upgradeScript string, migration *models.Migration, ) error { - tx, err := mr.db.Begin() + sqlParser := sqlparse.NewSqlParser(strings.NewReader(upgradeScript)) + execOptions, err := sqlParser.Parse() if err != nil { return err } - defer tx.Rollback() - _, err = tx.Exec(upgradeScript) - if err != nil { - return err - } + if execOptions.UseTransaction { + tx, err := mr.db.Begin() + if err != nil { + return err + } + defer tx.Rollback() - _, err = tx.Exec( - `INSERT INTO bolt_migrations(version) VALUES ($1);`, - migration.Version, - ) - if err != nil { - return err - } - err = tx.Commit() - if err != nil { - return err + _, err = tx.Exec(upgradeScript) + if err != nil { + return err + } + + _, err = tx.Exec( + `INSERT INTO bolt_migrations(version) VALUES ($1);`, + migration.Version, + ) + if err != nil { + return err + } + err = tx.Commit() + if err != nil { + return err + } + } else { + _, err = mr.db.Exec(upgradeScript) + if err != nil { + return err + } + + _, err = mr.db.Exec( + `INSERT INTO bolt_migrations(version) VALUES ($1);`, + migration.Version, + ) + if err != nil { + return err + } } migration.Applied = true @@ -121,28 +143,49 @@ func (mr *MigrationDBRepo) Revert( downgradeScript string, migration *models.Migration, ) error { - tx, err := mr.db.Begin() + sqlParser := sqlparse.NewSqlParser(strings.NewReader(downgradeScript)) + execOptions, err := sqlParser.Parse() if err != nil { return err } - defer tx.Rollback() - _, err = tx.Exec(downgradeScript) - if err != nil { - return err - } + if execOptions.UseTransaction { + tx, err := mr.db.Begin() + if err != nil { + return err + } + defer tx.Rollback() - _, err = tx.Exec( - `DELETE FROM bolt_migrations WHERE version = $1;`, - migration.Version, - ) - if err != nil { - return err - } + _, err = tx.Exec(downgradeScript) + if err != nil { + return err + } - err = tx.Commit() - if err != nil { - return err + _, err = tx.Exec( + `DELETE FROM bolt_migrations WHERE version = $1;`, + migration.Version, + ) + if err != nil { + return err + } + + err = tx.Commit() + if err != nil { + return err + } + } else { + _, err = mr.db.Exec(downgradeScript) + if err != nil { + return err + } + + _, err = mr.db.Exec( + `DELETE FROM bolt_migrations WHERE version = $1;`, + migration.Version, + ) + if err != nil { + return err + } } migration.Applied = false diff --git a/internal/repositories/migration_db_repo_test.go b/internal/repositories/migration_db_repo_test.go index a38875f..55b8eb2 100644 --- a/internal/repositories/migration_db_repo_test.go +++ b/internal/repositories/migration_db_repo_test.go @@ -186,3 +186,58 @@ func TestMigrationDBRepo_Revert(t *testing.T) { assert.NilError(t, err) assert.Equal(t, count, 0) } + +func TestMigrationDBRepo_ApplyAndRevertWithoutTransaction(t *testing.T) { + db := bolttest.NewTestDB(t, "postgres") + repo, err := repositories.NewMigrationDBRepo(db) + assert.NilError(t, err) + _, err = db.Exec("CREATE TABLE tmp(id INT PRIMARY KEY, id2 INT, id3 INT)") + assert.NilError(t, err) + + migration := models.NewMigration(time.Now(), "test") + // CREATE INDEX CONCURRENTLY cannot run inside a transaction. + err = repo.Apply( + `-- bolt: no-transaction\CREATE INDEX CONCURRENTLY i1 ON tmp(id2);`, + migration, + ) + assert.NilError(t, err) + assert.Equal(t, migration.Applied, true) + + err = repo.Revert( + "-- bolt: no-transaction\nCREATE INDEX CONCURRENTLY i2 ON tmp(id3);", + migration, + ) + assert.NilError(t, err) + assert.Equal(t, migration.Applied, false) +} + +func TestMigrationDBRepo_ApplyMalformedSql(t *testing.T) { + db := bolttest.NewTestDB(t, "postgres") + repo, err := repositories.NewMigrationDBRepo(db) + assert.NilError(t, err) + migration := models.NewMigration(time.Now(), "test") + + err = repo.Apply("this is not SQL", migration) + assert.ErrorContains(t, err, "syntax error") + assert.Equal(t, migration.Applied, false) + + err = repo.Apply("-- bolt: no-transaction\nthis is not SQL", migration) + assert.ErrorContains(t, err, "syntax error") + assert.Equal(t, migration.Applied, false) +} + +func TestMigrationDBRepo_RevertMalformedSql(t *testing.T) { + db := bolttest.NewTestDB(t, "postgres") + repo, err := repositories.NewMigrationDBRepo(db) + assert.NilError(t, err) + migration := models.NewMigration(time.Now(), "test") + migration.Applied = true + + err = repo.Revert("this is not SQL", migration) + assert.ErrorContains(t, err, "syntax error") + assert.Equal(t, migration.Applied, true) + + err = repo.Revert("-- bolt: no-transaction\nthis is not SQL", migration) + assert.ErrorContains(t, err, "syntax error") + assert.Equal(t, migration.Applied, true) +} diff --git a/internal/sqlparse/sqlparse.go b/internal/sqlparse/sqlparse.go new file mode 100644 index 0000000..9ded618 --- /dev/null +++ b/internal/sqlparse/sqlparse.go @@ -0,0 +1,44 @@ +package sqlparse + +import ( + "bufio" + "io" + "strings" +) + +type SqlParser struct { + reader io.Reader +} + +type ExecutionOptions struct { + UseTransaction bool +} + +func NewSqlParser(reader io.Reader) *SqlParser { + return &SqlParser{reader: reader} +} + +func (sp *SqlParser) Parse() (*ExecutionOptions, error) { + options := &ExecutionOptions{UseTransaction: true} + + scanner := bufio.NewScanner(sp.reader) + if scanner.Scan() { + firstLine := strings.ToLower(strings.TrimSpace(scanner.Text())) + if strings.HasPrefix(firstLine, "-- bolt:") { + parts := strings.Split(firstLine, " ") + // Skip the first two parts ("--" and "bolt:") + for _, part := range parts[2:] { + if part == "no-transaction" { + options.UseTransaction = false + break + } + } + } + } + + if err := scanner.Err(); err != nil { + return nil, err + } + + return options, nil +} diff --git a/internal/sqlparse/sqlparse_test.go b/internal/sqlparse/sqlparse_test.go new file mode 100644 index 0000000..51b93b1 --- /dev/null +++ b/internal/sqlparse/sqlparse_test.go @@ -0,0 +1,56 @@ +package sqlparse_test + +import ( + "strings" + "testing" + + "github.com/eugenetriguba/bolt/internal/sqlparse" + "gotest.tools/v3/assert" +) + +func TestSqlParser_Parse(t *testing.T) { + type test struct { + buffer string + expectedExecutionOptions *sqlparse.ExecutionOptions + } + + tests := []test{ + { + buffer: "-- bolt: no-transaction", + expectedExecutionOptions: &sqlparse.ExecutionOptions{UseTransaction: false}, + }, + { + buffer: "-- BOLT: no-transaction", + expectedExecutionOptions: &sqlparse.ExecutionOptions{UseTransaction: false}, + }, + { + buffer: "\n-- bolt: no-transaction", + expectedExecutionOptions: &sqlparse.ExecutionOptions{UseTransaction: true}, + }, + { + buffer: "", + expectedExecutionOptions: &sqlparse.ExecutionOptions{UseTransaction: true}, + }, + { + buffer: "--bolt: no-transaction", + expectedExecutionOptions: &sqlparse.ExecutionOptions{UseTransaction: true}, + }, + { + buffer: "-- bolt : no-transaction", + expectedExecutionOptions: &sqlparse.ExecutionOptions{UseTransaction: true}, + }, + { + buffer: "-- bolt: no-transaction no-transaction", + expectedExecutionOptions: &sqlparse.ExecutionOptions{UseTransaction: false}, + }, + } + + for _, tc := range tests { + sqlParser := sqlparse.NewSqlParser(strings.NewReader(tc.buffer)) + + execOptions, err := sqlParser.Parse() + assert.NilError(t, err) + + assert.DeepEqual(t, execOptions, tc.expectedExecutionOptions) + } +} |