aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--README.md21
-rw-r--r--internal/repositories/migration_db_repo.go107
-rw-r--r--internal/repositories/migration_db_repo_test.go55
-rw-r--r--internal/sqlparse/sqlparse.go44
-rw-r--r--internal/sqlparse/sqlparse_test.go56
5 files changed, 251 insertions, 32 deletions
diff --git a/README.md b/README.md
index e89e4f4..68da9f7 100644
--- a/README.md
+++ b/README.md
@@ -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)
+ }
+}