diff options
| author | Eugene Triguba <eugenetriguba@gmail.com> | 2024-03-16 15:06:30 -0400 |
|---|---|---|
| committer | Eugene Triguba <eugenetriguba@gmail.com> | 2024-03-16 15:06:30 -0400 |
| commit | 65ded7c0133ae011a30546fb4fd1e00fc3179874 (patch) | |
| tree | 305c9655c581ba5c959d56cf801a96bf80683dd7 | |
| parent | Update README (diff) | |
| download | cli-65ded7c0133ae011a30546fb4fd1e00fc3179874.zip | |
Update migration file format
| -rw-r--r-- | CHANGELOG.md | 10 | ||||
| -rw-r--r-- | README.md | 72 | ||||
| -rw-r--r-- | internal/bolttest/mock_migration_fs_repo.go | 17 | ||||
| -rw-r--r-- | internal/configloader/configloader.go | 2 | ||||
| -rw-r--r-- | internal/configloader/configloader_test.go | 2 | ||||
| -rw-r--r-- | internal/models/migration.go | 13 | ||||
| -rw-r--r-- | internal/models/migration_test.go | 32 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo.go | 2 | ||||
| -rw-r--r-- | internal/repositories/migration_fs_repo.go | 73 | ||||
| -rw-r--r-- | internal/repositories/migration_fs_repo_test.go | 41 | ||||
| -rw-r--r-- | internal/services/migration.go | 34 | ||||
| -rw-r--r-- | internal/services/migration_test.go | 37 | ||||
| -rw-r--r-- | internal/sqlparse/sqlparse.go | 78 | ||||
| -rw-r--r-- | internal/sqlparse/sqlparse_test.go | 175 |
14 files changed, 403 insertions, 185 deletions
diff --git a/CHANGELOG.md b/CHANGELOG.md index 5b131cf..38eb98a 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -5,6 +5,16 @@ All notable changes to this project will be documented in this file. The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/), and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html). +## Unreleased + +### Changed + +- Migrations are now a single file with an upgrade and downgrade script delineated by a `-- migrate:up` or `--migrate:down` comment instead of the prior approach where the migration was a directory with a `upgrade.sql` and `downgrade.sql` file within it. + +- Default version style is now timestamp. + +- `bolt_migrations` `version` column is now a `VARCHAR(255)` instead of `CHARACTER(14)`. + ## [0.7.0] - 2024-03-14 ### Changed @@ -61,7 +61,7 @@ Features: - [How Are Migrations Reverted?](#how-are-migrations-reverted) - [What are Migration Version Styles?](#what-are-migration-version-styles) - [Why can't I change between version styles?](#why-cant-i-change-between-version-styles) - - [How is the migration message used by Bolt?](#how-is-the-migration-message-used-by-bolt) + - [How is the migration message used?](#how-is-the-migration-message-used) ## Installation @@ -83,30 +83,38 @@ Run the following command to create your first migration: ```bash $ bolt new -m "my first migration" +Created migration 20240316145038 - my first migration. ``` Upon completion, a migrations directory will be created with the following structure: ```bash -migrations/ -└── 001_my_first_migration - ├── downgrade.sql - └── upgrade.sql +migrations +└── 20240316145038_my_first_migration.sql -1 directories, 2 files +1 directory, 1 file ``` 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 directory contains `upgrade.sql` for applying migrations and `downgrade.sql` for reverting them. +Bolt utilizes plain SQL for migration scripts. Each migration directory contains a comment for the upgrade script portion with `-- migrate:up` and a comment for the downgrade script with `-- migrate:down`. -1. Write the Upgrade Script +1. Open up `20240316145038_my_first_migration.sql` -Edit `upgrade.sql` with the following SQL command to create a new table: +You'll see the following template in the file for your migration: ```sql +-- migrate:up + +-- migrate:down +``` + +2. Write your migration script + +```sql +-- migrate:up CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, @@ -114,13 +122,7 @@ CREATE TABLE users ( created_at TIMESTAMP NOT NULL DEFAULT NOW(), updated_at TIMESTAMP NOT NULL DEFAULT NOW() ); -``` - -2. Write the Downgrade Script - -Edit `downgrade.sql` with the following SQL command to remove the table: - -```sql +-- migrate:down DROP TABLE users; ``` @@ -166,8 +168,8 @@ Apply your migration, which will execute the `upgrade.sql` script: ```bash $ bolt up -Applying migration 001_my_first_migration.. -Successfully applied migration 001_my_first_migration! +Applying migration 20240316145038_my_first_migration.. +Successfully applied migration 20240316145038_my_first_migration! ``` ### Checking Migration Status @@ -177,7 +179,7 @@ To view the status of your migration: ```bash $ bolt status Version Message Applied -001 my_first_migration X +20240316145038 my_first_migration X ``` This command displays the migration's version, name, and whether the migration was applied or not. @@ -208,8 +210,8 @@ To revert your migration: ```bash $ bolt down -Reverting migration 001_my_first_migration.. -Successfully reverted migration 001_my_first_migration! +Reverting migration 20240316145038_my_first_migration.. +Successfully reverted migration 20240316145038_my_first_migration! ``` ### Verifying Migration Reversion @@ -231,16 +233,18 @@ bolt_tutorial_db=# \dt ### Next Steps -Congrats! You've learned the core features of Bolt. If you want learn more, see the [Reference](#reference) or [Explanation](#explanation) section. +Congrats! You've learned the core features of Bolt. If you want learn more, see the [How-to](#how-to), [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: +In your migration script, add a `transaction:false` option: ```sql --- bolt: no-transaction +-- migrate:up transaction:false + +-- migrate:down transaction:false ``` ## Reference @@ -269,11 +273,11 @@ or in any parent directory. # path. directory_path = "migrations" # The migration versioning style you prefer. Supported options -# are "timestamp" and "sequential". Defaults to "sequential". +# 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 = "sequential" +version_style = "timestamp" # Connection parameters for the database Bolt will be # applying migrations to. All connection parameters are @@ -366,9 +370,9 @@ version: 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: +You can do this by adding onto the `-- migrate:up` or `-- migrate:down` comments with your own execution option. The options must be in the following format: `-- migrate:up <option1> <option2> <...>` or `-- migrate:down <option1> <option2> <...>`. 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. +- `transaction:false`: 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. ### Version Styles @@ -380,7 +384,7 @@ The following version styles are supported: ### How Are Migration Scripts Executed? -Bolt executes upgrade and downgrade migration scripts in a transaction. This ensures that if any errors occur during the execution of any 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 what error has occurred to standard error. +Bolt executes upgrade and downgrade migration scripts in a transaction. This ensures that if any errors occur during the execution of any 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 what error has occurred to standard error. However, do note that some databases, like MySQL, commit certain DDL statements immediately even if you're in a transaction. ### How Does Bolt Know What Migrations Have Been Applied? @@ -388,11 +392,11 @@ Bolt keeps track of which migrations have been applied to your database by creat ### How Are Migrations Applied? -When you run `bolt up`, Bolt will look at your local migration scripts and compares the version part of the migration directory names to the versions that have been inserted into the `bolt_migrations` table. Any versions that aren't in the table will be applied in order, starting with the oldest migration. Applying a migration entails executing the `upgrade.sql` script in a transaction and inserting in the migration's version into the `bolt_migrations` table. +When you run `bolt up`, Bolt will look at your local migration scripts and compares the version part of the migration directory names to the versions that have been inserted into the `bolt_migrations` table. Any versions that aren't in the table will be applied in order, starting with the oldest migration. Applying a migration entails executing the `-- migrate:up` portion of the script in a transaction and inserting in the migration's version into the `bolt_migrations` table. ### How Are Migrations Reverted? -When you run `bolt down`, Bolt will look at the `bolt_migrations` table and compare the versions to the versions of your local migration scripts. Any versions that are in the table but not in your local migration scripts will be reverted in order, starting with the newest migration. Reverting a migration entails executing the `downgrade.sql` script in a transaction and removing the migration's version from the `bolt_migrations` table. +When you run `bolt down`, Bolt will look at the `bolt_migrations` table and compare the versions to the versions of your local migration scripts. Any versions that are in the table but not in your local migration scripts will be reverted in order, starting with the newest migration. Reverting a migration entails executing the `-- migrate:down` portion of the script in a transaction and removing the migration's version from the `bolt_migrations` table. ### What are Migration Version Styles? @@ -408,6 +412,8 @@ Sequential versions are incrementing integers. When you create a migration, it'l The main reason that switching back and forth between "sequential" and "timestamp" versions is not supported is because Bolt will no longer know how to properly sort your migrations in the right order (and therefore, apply or revert them in the right order). If you've been using sequential migrations, such as "001" and "002", and then move over to timestamp migrations, such as "20200101000000", Bolt won't know how to order these correctly when both exist. Furthermore, because the "version" is parsed and sorted according to the version style that is configured, the parsing will fail if a mix of version styles are used. -### How is the migration message used by Bolt? +This means that if you wanted to switch version styles, you could do it manually by updating your local migrations to use the new version style and update the entries in `bolt_migrations` to match, but there is no tooling support for doing this change. + +### How is the migration message used? -All migrations are created on the local filesystem in the format `<version>_<message>`. Bolt uses the `<version>` part of the name for keeping track of the migrations and how they should be applied or reverted. The `<message>` part is purely informational so you know what that migration is for. Bolt does not use or store it anywhere. You may alter this message whenever you'd like as long as the format is kept consistent (`_` after the `<version>`). However, the version should not be directly manipulated. +All migrations are created on the local filesystem in the format `<version>_<message>`. Bolt uses the `<version>` part of the name for keeping track of the migrations and how they should be applied or reverted. The `<message>` part is purely informational so you know what that migration is for. diff --git a/internal/bolttest/mock_migration_fs_repo.go b/internal/bolttest/mock_migration_fs_repo.go index d392732..b2507a0 100644 --- a/internal/bolttest/mock_migration_fs_repo.go +++ b/internal/bolttest/mock_migration_fs_repo.go @@ -1,6 +1,9 @@ package bolttest -import "github.com/eugenetriguba/bolt/internal/models" +import ( + "github.com/eugenetriguba/bolt/internal/models" + "github.com/eugenetriguba/bolt/internal/sqlparse" +) type MockMigrationFsRepo struct { CreateReturnValue CreateReturnValue @@ -18,8 +21,8 @@ type CreateReturnValue struct { } type ReadUpgradeScriptReturnValue struct { - ScriptContents string - Err error + Script sqlparse.MigrationScript + Err error } type ReadDowngradeScriptReturnValue = ReadUpgradeScriptReturnValue @@ -36,14 +39,14 @@ func (repo *MockMigrationFsRepo) List() (map[string]*models.Migration, error) { func (repo *MockMigrationFsRepo) ReadUpgradeScript( migration *models.Migration, -) (string, error) { +) (sqlparse.MigrationScript, error) { repo.ReadUpgradeScriptCallCount += 1 - return repo.ReadUpgradeScriptReturnValue.ScriptContents, repo.ReadUpgradeScriptReturnValue.Err + return repo.ReadUpgradeScriptReturnValue.Script, repo.ReadUpgradeScriptReturnValue.Err } func (repo *MockMigrationFsRepo) ReadDowngradeScript( migration *models.Migration, -) (string, error) { +) (sqlparse.MigrationScript, error) { repo.ReadDowngradeScriptCallCount += 1 - return repo.ReadDowngradeScriptReturnValue.ScriptContents, repo.ReadDowngradeScriptReturnValue.Err + return repo.ReadDowngradeScriptReturnValue.Script, repo.ReadDowngradeScriptReturnValue.Err } diff --git a/internal/configloader/configloader.go b/internal/configloader/configloader.go index eed150f..c13977a 100644 --- a/internal/configloader/configloader.go +++ b/internal/configloader/configloader.go @@ -71,7 +71,7 @@ func NewConfig() (*Config, error) { cfg := Config{ Migrations: MigrationsConfig{ DirectoryPath: "migrations", - VersionStyle: VersionStyleSequential, + VersionStyle: VersionStyleTimestamp, }, } if !errors.Is(err, ErrConfigFileNotFound) { diff --git a/internal/configloader/configloader_test.go b/internal/configloader/configloader_test.go index 7684436..89c4368 100644 --- a/internal/configloader/configloader_test.go +++ b/internal/configloader/configloader_test.go @@ -18,7 +18,7 @@ func TestNewConfigDefaults(t *testing.T) { assert.Nil(t, err) check.Equal(t, cfg.Migrations.DirectoryPath, "migrations") - check.Equal(t, cfg.Migrations.VersionStyle, configloader.VersionStyleSequential) + check.Equal(t, cfg.Migrations.VersionStyle, configloader.VersionStyleTimestamp) } func TestNewConfigWithInvalidVersionStyle(t *testing.T) { diff --git a/internal/models/migration.go b/internal/models/migration.go index b63fec9..ed48073 100644 --- a/internal/models/migration.go +++ b/internal/models/migration.go @@ -2,6 +2,7 @@ package models import ( "fmt" + "strings" "time" ) @@ -31,6 +32,16 @@ func NewSequentialMigration(version uint64, message string) *Migration { } } +// NormalizedMessage gives back the Message in lowercase, +// leading and trailing whitespace removed, and spaces +// replaced with underscores. +func (m Migration) NormalizedMessage() string { + message := strings.ToLower(m.Message) + message = strings.TrimSpace(message) + message = strings.ReplaceAll(message, " ", "_") + return message +} + func (m *Migration) Name() string { - return fmt.Sprintf("%s_%s", m.Version, m.Message) + return fmt.Sprintf("%s_%s", m.Version, m.NormalizedMessage()) } diff --git a/internal/models/migration_test.go b/internal/models/migration_test.go index b6d3205..809887f 100644 --- a/internal/models/migration_test.go +++ b/internal/models/migration_test.go @@ -1,7 +1,6 @@ package models_test import ( - "fmt" "testing" "time" @@ -34,6 +33,33 @@ func TestNewSequentialMigration(t *testing.T) { } func TestName(t *testing.T) { - m := models.NewSequentialMigration(1, "test message") - check.Equal(t, m.Name(), fmt.Sprintf("%s_%s", m.Version, m.Message)) + type test struct { + migration *models.Migration + expectedName string + } + + testCases := []test{ + // Underscores are used to separate version and message. + { + migration: models.NewSequentialMigration(1, "test_message"), + expectedName: "001_test_message", + }, + { + migration: models.NewTimestampMigration(time.Date(1234, 1, 2, 3, 4, 5, 6, time.UTC), "test_message"), + expectedName: "12340102030405_test_message", + }, + // Leading/Trailing spaces trimmed, lowercased, and spaces converted to underscores. + { + migration: models.NewSequentialMigration(1, " teST mesSAGE "), + expectedName: "001_test_message", + }, + { + migration: models.NewTimestampMigration(time.Date(1234, 1, 2, 3, 4, 5, 6, time.UTC), " teST mesSAGE "), + expectedName: "12340102030405_test_message", + }, + } + + for _, tc := range testCases { + check.Equal(t, tc.migration.Name(), tc.expectedName) + } } diff --git a/internal/repositories/migration_db_repo.go b/internal/repositories/migration_db_repo.go index e97349a..26cc2b3 100644 --- a/internal/repositories/migration_db_repo.go +++ b/internal/repositories/migration_db_repo.go @@ -39,7 +39,7 @@ func NewMigrationDBRepo(db storage.DB) (MigrationDBRepo, error) { if !migrationTableExists { _, err := db.Exec(` CREATE TABLE bolt_migrations( - version CHARACTER(14) PRIMARY KEY NOT NULL + version VARCHAR(255) PRIMARY KEY NOT NULL ); `) if err != nil { diff --git a/internal/repositories/migration_fs_repo.go b/internal/repositories/migration_fs_repo.go index e535013..0ded9d0 100644 --- a/internal/repositories/migration_fs_repo.go +++ b/internal/repositories/migration_fs_repo.go @@ -10,6 +10,7 @@ import ( "github.com/eugenetriguba/bolt/internal/configloader" "github.com/eugenetriguba/bolt/internal/models" + "github.com/eugenetriguba/bolt/internal/sqlparse" ) type ErrIsNotDir struct { @@ -26,8 +27,8 @@ func (e *ErrIsNotDir) Error() string { type MigrationFsRepo interface { Create(migration *models.Migration) error List() (map[string]*models.Migration, error) - ReadUpgradeScript(migration *models.Migration) (string, error) - ReadDowngradeScript(migration *models.Migration) (string, error) + ReadUpgradeScript(migration *models.Migration) (sqlparse.MigrationScript, error) + ReadDowngradeScript(migration *models.Migration) (sqlparse.MigrationScript, error) } type migrationFsRepo struct { @@ -61,23 +62,15 @@ func NewMigrationFsRepo( } func (mr migrationFsRepo) Create(migration *models.Migration) error { - newMigrationDir := filepath.Join(mr.migrationsDirPath, mr.migrationDirname(migration)) - - err := os.Mkdir(newMigrationDir, 0755) + newMigrationPath := filepath.Join(mr.migrationsDirPath, migration.Name()+".sql") + file, err := os.Create(newMigrationPath) if err != nil { - return err + return fmt.Errorf("unable to create file at %s: %w", newMigrationPath, err) } - - _, err = os.Create(filepath.Join(newMigrationDir, "upgrade.sql")) + _, err = file.WriteString("-- migrate:up\n\n-- migrate:down\n") if err != nil { - return err + return fmt.Errorf("unable to write template to new migration script: %w", err) } - - _, err = os.Create(filepath.Join(newMigrationDir, "downgrade.sql")) - if err != nil { - return err - } - return nil } @@ -108,31 +101,42 @@ func dirEntryToMigration(entry fs.DirEntry) (*models.Migration, error) { entry.Name(), ) } + version := parts[0] + message := strings.TrimSuffix(parts[1], filepath.Ext(parts[1])) // Remove .sql return &models.Migration{ - Version: parts[0], - Message: parts[1], + Version: version, + Message: message, Applied: false, }, nil } -func (mr migrationFsRepo) ReadUpgradeScript(migration *models.Migration) (string, error) { - upgradeScriptPath := filepath.Join( +func (mr migrationFsRepo) ReadUpgradeScript(migration *models.Migration) (sqlparse.MigrationScript, error) { + scriptPath := filepath.Join( mr.migrationsDirPath, - mr.migrationDirname(migration), - "upgrade.sql", + migration.Name()+".sql", ) - return mr.readScriptContents(upgradeScriptPath) + upgradeScript, _, err := mr.getMigrationScripts(scriptPath) + return upgradeScript, err } func (mr migrationFsRepo) ReadDowngradeScript( migration *models.Migration, -) (string, error) { - downgradeScriptPath := filepath.Join( +) (sqlparse.MigrationScript, error) { + scriptPath := filepath.Join( mr.migrationsDirPath, - mr.migrationDirname(migration), - "downgrade.sql", + migration.Name()+".sql", ) - return mr.readScriptContents(downgradeScriptPath) + _, downgradeScript, err := mr.getMigrationScripts(scriptPath) + return downgradeScript, err +} + +func (mr migrationFsRepo) getMigrationScripts(scriptPath string) (sqlparse.MigrationScript, sqlparse.MigrationScript, error) { + scriptContents, err := mr.readScriptContents(scriptPath) + if err != nil { + return sqlparse.MigrationScript{}, sqlparse.MigrationScript{}, fmt.Errorf("unable to read %s script: %w", scriptPath, err) + } + sqlParser := sqlparse.NewSqlParser() + return sqlParser.Parse(strings.NewReader(scriptContents)) } func (mr migrationFsRepo) readScriptContents(scriptPath string) (string, error) { @@ -143,18 +147,3 @@ func (mr migrationFsRepo) readScriptContents(scriptPath string) (string, error) return string(contents), nil } - -// migrationDirname creates the directory name that -// should be used for this migration. -func (mr migrationFsRepo) migrationDirname(migration *models.Migration) string { - return fmt.Sprintf("%s_%s", migration.Version, mr.normalizeMessage(migration)) -} - -// normalizeMessage normalizes the Message of a migration -// to be filesystem friendly. -func (mr migrationFsRepo) normalizeMessage(migration *models.Migration) string { - message := strings.ToLower(migration.Message) - message = strings.TrimSpace(message) - message = strings.ReplaceAll(message, " ", "_") - return message -} diff --git a/internal/repositories/migration_fs_repo_test.go b/internal/repositories/migration_fs_repo_test.go index 32de193..96d48a0 100644 --- a/internal/repositories/migration_fs_repo_test.go +++ b/internal/repositories/migration_fs_repo_test.go @@ -59,26 +59,23 @@ func TestCreate_SuccessfullyCreated(t *testing.T) { err = repo.Create(migration) assert.Nil(t, err) - migrationDirName := fmt.Sprintf("%s_add_users_table", migration.Version) - assertFileExists(t, filepath.Join(tempDir, migrationDirName)) - assertFileExists(t, filepath.Join(tempDir, migrationDirName, "upgrade.sql")) - assertFileExists(t, filepath.Join(tempDir, migrationDirName, "downgrade.sql")) + assertFileExists(t, filepath.Join(tempDir, fmt.Sprintf("%s.sql", migration.Name()))) } -func TestCreate_FailsToCreateDir(t *testing.T) { +func TestCreate_FailsToCreateMigration(t *testing.T) { tempDir := t.TempDir() - migrationsConfig := configloader.MigrationsConfig{DirectoryPath: tempDir} - repo, err := repositories.NewMigrationFsRepo(&migrationsConfig) + repo, err := repositories.NewMigrationFsRepo( + &configloader.MigrationsConfig{DirectoryPath: tempDir}, + ) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "add users table") - migrationDirName := fmt.Sprintf("%s_add_users_table", migration.Version) - _, err = os.Create(filepath.Join(tempDir, migrationDirName)) + _, err = os.OpenFile(filepath.Join(tempDir, fmt.Sprintf("%s.sql", migration.Name())), os.O_CREATE, 0000) assert.Nil(t, err) err = repo.Create(migration) assert.NotNil(t, err) - assert.ErrorContains(t, err, "file exists") + assert.ErrorContains(t, err, "unable to create file") } func TestReadUpgradeScript_SuccessfullyRead(t *testing.T) { @@ -91,17 +88,17 @@ func TestReadUpgradeScript_SuccessfullyRead(t *testing.T) { err = repo.Create(migration) assert.Nil(t, err) - migrationDirName := fmt.Sprintf("%s_add_users_table", migration.Version) - expectedUpgradeScriptContents := []byte("CREATE TABLE users(id int PRIMARY KEY);") + migrationName := fmt.Sprintf("%s.sql", migration.Name()) + expectedUpgradeScriptContents := "CREATE TABLE users(id int PRIMARY KEY);\n" os.WriteFile( - filepath.Join(tempDir, migrationDirName, "upgrade.sql"), - expectedUpgradeScriptContents, + filepath.Join(tempDir, migrationName), + []byte("-- migrate:up\n"+expectedUpgradeScriptContents), 0755, ) - upgradeScriptContents, err := repo.ReadUpgradeScript(migration) + upgradeScript, err := repo.ReadUpgradeScript(migration) assert.Nil(t, err) - assert.Equal(t, upgradeScriptContents, string(expectedUpgradeScriptContents)) + assert.Equal(t, upgradeScript.Contents, string(expectedUpgradeScriptContents)) } func TestReadUpgradeScript_FileDoesNotExist(t *testing.T) { @@ -126,17 +123,17 @@ func TestReadDowngradeScript_SuccessfullyRead(t *testing.T) { err = repo.Create(migration) assert.Nil(t, err) - migrationDirName := fmt.Sprintf("%s_add_users_table", migration.Version) - expectedDowngradeScriptContents := []byte("DROP TABLE users;") + migrationName := fmt.Sprintf("%s.sql", migration.Name()) + expectedDowngradeScriptContents := "DROP TABLE users;\n" os.WriteFile( - filepath.Join(tempDir, migrationDirName, "downgrade.sql"), - expectedDowngradeScriptContents, + filepath.Join(tempDir, migrationName), + []byte("-- migrate:down\n"+expectedDowngradeScriptContents), 0755, ) - downgradeScriptContents, err := repo.ReadDowngradeScript(migration) + downgradeScript, err := repo.ReadDowngradeScript(migration) assert.Nil(t, err) - assert.Equal(t, downgradeScriptContents, string(expectedDowngradeScriptContents)) + assert.Equal(t, downgradeScript.Contents, string(expectedDowngradeScriptContents)) } func TestReadDowngradeScript_FileDoesNotExist(t *testing.T) { diff --git a/internal/services/migration.go b/internal/services/migration.go index 10cc1b2..3b0f297 100644 --- a/internal/services/migration.go +++ b/internal/services/migration.go @@ -4,14 +4,12 @@ import ( "fmt" "sort" "strconv" - "strings" "time" "github.com/eugenetriguba/bolt/internal/configloader" "github.com/eugenetriguba/bolt/internal/models" "github.com/eugenetriguba/bolt/internal/output" "github.com/eugenetriguba/bolt/internal/repositories" - "github.com/eugenetriguba/bolt/internal/sqlparse" ) type MigrationService struct { @@ -118,21 +116,15 @@ func (ms MigrationService) ApplyMigration(migration *models.Migration) error { ), ) - scriptContents, err := ms.fsRepo.ReadUpgradeScript(migration) + upgradeScript, err := ms.fsRepo.ReadUpgradeScript(migration) if err != nil { - return err - } - - sqlParser := sqlparse.NewSqlParser() - execOptions, err := sqlParser.Parse(strings.NewReader(scriptContents)) - if err != nil { - return fmt.Errorf("unable to parse sql file for upgrade script: %w", err) + return fmt.Errorf("unable to read upgrade script: %w", err) } - if execOptions.UseTransaction { - err = ms.dbRepo.ApplyWithTx(scriptContents, migration) + if upgradeScript.Options.UseTransaction { + err = ms.dbRepo.ApplyWithTx(upgradeScript.Contents, migration) } else { - err = ms.dbRepo.Apply(scriptContents, migration) + err = ms.dbRepo.Apply(upgradeScript.Contents, migration) } if err != nil { @@ -225,21 +217,15 @@ func (ms MigrationService) RevertMigration(migration *models.Migration) error { migration.Message, ), ) - scriptContents, err := ms.fsRepo.ReadDowngradeScript(migration) - if err != nil { - return err - } - - sqlParser := sqlparse.NewSqlParser() - execOptions, err := sqlParser.Parse(strings.NewReader(scriptContents)) + downgradeScript, err := ms.fsRepo.ReadDowngradeScript(migration) if err != nil { - return fmt.Errorf("unable to parse sql file for downgrade script: %w", err) + return fmt.Errorf("unable to read downgrade script: %w", err) } - if execOptions.UseTransaction { - err = ms.dbRepo.RevertWithTx(scriptContents, migration) + if downgradeScript.Options.UseTransaction { + err = ms.dbRepo.RevertWithTx(downgradeScript.Contents, migration) } else { - err = ms.dbRepo.Revert(scriptContents, migration) + err = ms.dbRepo.Revert(downgradeScript.Contents, migration) } if err != nil { diff --git a/internal/services/migration_test.go b/internal/services/migration_test.go index db9448f..6828d28 100644 --- a/internal/services/migration_test.go +++ b/internal/services/migration_test.go @@ -8,6 +8,7 @@ import ( "github.com/eugenetriguba/bolt/internal/bolttest" "github.com/eugenetriguba/bolt/internal/configloader" "github.com/eugenetriguba/bolt/internal/models" + "github.com/eugenetriguba/bolt/internal/sqlparse" "github.com/eugenetriguba/checkmate/assert" "github.com/eugenetriguba/checkmate/check" ) @@ -582,8 +583,11 @@ func TestCreateMigration_SequentialVersionIncrementsParsingErr(t *testing.T) { func TestApplyMigration_WithTransaction(t *testing.T) { migrationFsRepo := &bolttest.MockMigrationFsRepo{ ReadUpgradeScriptReturnValue: bolttest.ReadUpgradeScriptReturnValue{ - ScriptContents: "CREATE TABLE tmp(id INT PRIMARY KEY, id2 INT, id3 INT);", - Err: nil, + Script: sqlparse.MigrationScript{ + Contents: "CREATE TABLE tmp(id INT PRIMARY KEY, id2 INT, id3 INT);", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, + Err: nil, }, } @@ -609,8 +613,11 @@ func TestApplyMigration_WithTransaction(t *testing.T) { func TestApplyMigration_WithoutTransaction(t *testing.T) { migrationFsRepo := &bolttest.MockMigrationFsRepo{ ReadUpgradeScriptReturnValue: bolttest.ReadUpgradeScriptReturnValue{ - ScriptContents: "-- bolt: no-transaction\nCREATE TABLE tmp(id INT PRIMARY KEY, id2 INT, id3 INT);", - Err: nil, + Script: sqlparse.MigrationScript{ + Contents: "CREATE TABLE tmp(id INT PRIMARY KEY, id2 INT, id3 INT);", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, + Err: nil, }, } @@ -637,8 +644,8 @@ func TestApplyMigration_ReadUpgradeScriptErr(t *testing.T) { expectedErr := errors.New("error!") migrationFsRepo := &bolttest.MockMigrationFsRepo{ ReadUpgradeScriptReturnValue: bolttest.ReadUpgradeScriptReturnValue{ - ScriptContents: "", - Err: expectedErr, + Script: sqlparse.MigrationScript{}, + Err: expectedErr, }, } @@ -860,8 +867,11 @@ func TestApplyUpToVersion_AppliesMigrations(t *testing.T) { func TestRevertMigration_WithTransaction(t *testing.T) { migrationFsRepo := &bolttest.MockMigrationFsRepo{ ReadDowngradeScriptReturnValue: bolttest.ReadDowngradeScriptReturnValue{ - ScriptContents: "DROP TABLE tmp;", - Err: nil, + Script: sqlparse.MigrationScript{ + Contents: "DROP TABLE tmp;", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, + Err: nil, }, } @@ -887,8 +897,11 @@ func TestRevertMigration_WithTransaction(t *testing.T) { func TestRevertMigration_WithoutTransaction(t *testing.T) { migrationFsRepo := &bolttest.MockMigrationFsRepo{ ReadDowngradeScriptReturnValue: bolttest.ReadDowngradeScriptReturnValue{ - ScriptContents: "-- bolt: no-transaction\nDROP TABLE tmp;", - Err: nil, + Script: sqlparse.MigrationScript{ + Contents: "DROP TABLE tmp;", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, + Err: nil, }, } migrationDbRepo := &bolttest.MockMigrationDBRepo{ @@ -914,8 +927,8 @@ func TestRevertMigration_ReadUpgradeScriptErr(t *testing.T) { expectedErr := errors.New("error!") migrationFsRepo := &bolttest.MockMigrationFsRepo{ ReadDowngradeScriptReturnValue: bolttest.ReadDowngradeScriptReturnValue{ - ScriptContents: "", - Err: expectedErr, + Script: sqlparse.MigrationScript{}, + Err: expectedErr, }, } migrationDbRepo := &bolttest.MockMigrationDBRepo{} diff --git a/internal/sqlparse/sqlparse.go b/internal/sqlparse/sqlparse.go index e986068..fa9fa96 100644 --- a/internal/sqlparse/sqlparse.go +++ b/internal/sqlparse/sqlparse.go @@ -8,40 +8,84 @@ import ( ) type SqlParser interface { - Parse(reader io.Reader) (ExecutionOptions, error) + Parse(reader io.Reader) (MigrationScript, MigrationScript, error) +} + +type MigrationScript struct { + Contents string + Options ExecutionOptions } type ExecutionOptions struct { UseTransaction bool } +const ( + unknownSection = iota + upgradeScriptSection + downgradeScriptSection +) + +const ( + upgradeScriptDeliminator = "-- migrate:up" + downgradeScriptDeliminator = "-- migrate:down" + transactionOptionName = "transaction" +) + type sqlParser struct{} +// NewSqlParser creates a new SqlParser which can +// parse out a migration script. func NewSqlParser() SqlParser { - return sqlParser{} + return &sqlParser{} } -func (sp sqlParser) Parse(reader io.Reader) (ExecutionOptions, error) { - options := ExecutionOptions{UseTransaction: true} +// Parse parses out the migration's upgrade and downgrade scripts +// and any custom execution options with those scripts. It returns +// the upgrade script, downgrade script, and any error if one occurred. +func (sp *sqlParser) Parse(reader io.Reader) (MigrationScript, MigrationScript, error) { + upgradeScript := MigrationScript{} + downgradeScript := MigrationScript{} scanner := bufio.NewScanner(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 - } - } + currentSection := unknownSection + for scanner.Scan() { + line := strings.TrimSpace(scanner.Text()) + loweredLine := strings.ToLower(line) + + if strings.HasPrefix(loweredLine, upgradeScriptDeliminator) { + currentSection = upgradeScriptSection + upgradeScript.Options = parseExecutionOptions(line) + } else if strings.HasPrefix(loweredLine, downgradeScriptDeliminator) { + currentSection = downgradeScriptSection + downgradeScript.Options = parseExecutionOptions(line) + } else if currentSection == upgradeScriptSection { + upgradeScript.Contents += line + "\n" + } else if currentSection == downgradeScriptSection { + downgradeScript.Contents += line + "\n" } } if err := scanner.Err(); err != nil { - return options, fmt.Errorf("parsing sql file encountered an error: %w", err) + return MigrationScript{}, MigrationScript{}, fmt.Errorf("parsing sql file encountered an error: %w", err) + } + + return upgradeScript, downgradeScript, nil +} + +// parseExecutionOptions extracts the execution options for a +// upgrade or downgrade migration script. +func parseExecutionOptions(line string) ExecutionOptions { + options := ExecutionOptions{UseTransaction: true} + + parts := strings.Split(line, " ") + for _, part := range parts { + part = strings.ToLower(part) + if strings.Contains(part, "transaction:") { + option := strings.Split(part, ":")[1] + options.UseTransaction = option != "false" + } } - return options, nil + return options } diff --git a/internal/sqlparse/sqlparse_test.go b/internal/sqlparse/sqlparse_test.go index 12a435d..b5d2e93 100644 --- a/internal/sqlparse/sqlparse_test.go +++ b/internal/sqlparse/sqlparse_test.go @@ -9,50 +9,183 @@ import ( "github.com/eugenetriguba/bolt/internal/sqlparse" "github.com/eugenetriguba/checkmate/assert" + "github.com/eugenetriguba/checkmate/check" ) func TestSqlParser_Parse(t *testing.T) { testCases := []struct { - buffer string - expectedExecutionOptions sqlparse.ExecutionOptions + migration string + expectedUpgradeScript sqlparse.MigrationScript + expectedDowngradeScript sqlparse.MigrationScript }{ { - buffer: "-- bolt: no-transaction", - expectedExecutionOptions: sqlparse.ExecutionOptions{UseTransaction: false}, + migration: "", + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, }, { - buffer: "-- BOLT: no-transaction", - expectedExecutionOptions: sqlparse.ExecutionOptions{UseTransaction: false}, + migration: ` + -- migrate:up + CREATE TABLE users(id int PRIMARY KEY);`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "CREATE TABLE users(id int PRIMARY KEY);\n", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, }, { - buffer: "\n-- bolt: no-transaction", - expectedExecutionOptions: sqlparse.ExecutionOptions{UseTransaction: true}, + migration: ` + -- migrate:up transaction:false + CREATE TABLE users(id int PRIMARY KEY);`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "CREATE TABLE users(id int PRIMARY KEY);\n", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, }, { - buffer: "", - expectedExecutionOptions: sqlparse.ExecutionOptions{UseTransaction: true}, + migration: ` + -- migrate:up transaction:true + CREATE TABLE users(id int PRIMARY KEY);`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "CREATE TABLE users(id int PRIMARY KEY);\n", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, }, { - buffer: "--bolt: no-transaction", - expectedExecutionOptions: sqlparse.ExecutionOptions{UseTransaction: true}, + migration: ` + -- transaction:true migrate:up + CREATE TABLE users(id int PRIMARY KEY);`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, }, { - buffer: "-- bolt : no-transaction", - expectedExecutionOptions: sqlparse.ExecutionOptions{UseTransaction: true}, + migration: ` + -- migrate:down + DROP TABLE users;`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "DROP TABLE users;\n", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, }, { - buffer: "-- bolt: no-transaction no-transaction", - expectedExecutionOptions: sqlparse.ExecutionOptions{UseTransaction: false}, + migration: ` + -- migrate:down transaction:false + DROP TABLE users;`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "DROP TABLE users;\n", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, + }, + { + migration: ` + -- migrate:down transaction:true + DROP TABLE users;`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "DROP TABLE users;\n", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, + }, + { + migration: ` + -- transaction:true migrate:down + DROP TABLE users;`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "", + Options: sqlparse.ExecutionOptions{}, + }, + }, + { + migration: ` + -- migrate:up + CREATE TABLE users(id int PRIMARY KEY); + -- migrate:down + DROP TABLE users;`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "CREATE TABLE users(id int PRIMARY KEY);\n", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "DROP TABLE users;\n", + Options: sqlparse.ExecutionOptions{UseTransaction: true}, + }, + }, + { + migration: ` + -- migrate:up transaction:false + CREATE TABLE users(id int PRIMARY KEY); + -- migrate:down transaction:false + DROP TABLE users;`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "CREATE TABLE users(id int PRIMARY KEY);\n", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "DROP TABLE users;\n", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, + }, + { + migration: ` + -- MIGRATE:UP TRANSACTION:FALSE + CREATE TABLE users(id int PRIMARY KEY); + -- MIGRATE:DOWN TRANSACTION:FALSE + DROP TABLE users;`, + expectedUpgradeScript: sqlparse.MigrationScript{ + Contents: "CREATE TABLE users(id int PRIMARY KEY);\n", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, + expectedDowngradeScript: sqlparse.MigrationScript{ + Contents: "DROP TABLE users;\n", + Options: sqlparse.ExecutionOptions{UseTransaction: false}, + }, }, } for _, tc := range testCases { sqlParser := sqlparse.NewSqlParser() - - execOptions, err := sqlParser.Parse(strings.NewReader(tc.buffer)) + upgradeScript, downgradeScript, err := sqlParser.Parse(strings.NewReader(tc.migration)) assert.Nil(t, err) - - assert.DeepEqual(t, execOptions, tc.expectedExecutionOptions) + check.DeepEqual(t, upgradeScript, tc.expectedUpgradeScript) + check.DeepEqual(t, downgradeScript, tc.expectedDowngradeScript) } } @@ -80,7 +213,7 @@ func TestSqlParserParseError(t *testing.T) { ErrCond: " ", } sqlParser := sqlparse.NewSqlParser() - _, err := sqlParser.Parse(reader) + _, _, err := sqlParser.Parse(reader) assert.NotNil(t, err) assert.ErrorContains(t, err, "unwanted input encountered") } |