diff options
| author | Eugene Triguba <eugenetriguba@gmail.com> | 2024-03-09 15:50:21 -0500 |
|---|---|---|
| committer | Eugene Triguba <eugenetriguba@gmail.com> | 2024-03-09 15:50:21 -0500 |
| commit | 9d2abbe510d1719bcfe31db901423376f79b7894 (patch) | |
| tree | 00f5a057571a3ca779e5bbe96655169c58a692f9 /internal/repositories | |
| parent | use docker compose to start database in workflow (diff) | |
| download | cli-9d2abbe510d1719bcfe31db901423376f79b7894.zip | |
Add MySQL support
Diffstat (limited to 'internal/repositories')
| -rw-r--r-- | internal/repositories/migration_db_repo.go | 102 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo_test.go | 271 |
2 files changed, 154 insertions, 219 deletions
diff --git a/internal/repositories/migration_db_repo.go b/internal/repositories/migration_db_repo.go index 7a90a2a..8373eda 100644 --- a/internal/repositories/migration_db_repo.go +++ b/internal/repositories/migration_db_repo.go @@ -7,19 +7,10 @@ import ( "strings" "github.com/eugenetriguba/bolt/internal/models" + "github.com/eugenetriguba/bolt/internal/storage" + "github.com/upper/db/v4" ) -type sqlExecutor interface { - Exec(query string, args ...interface{}) (sql.Result, error) -} - -type SqlDb interface { - Exec(query string, args ...any) (sql.Result, error) - Query(query string, args ...any) (*sql.Rows, error) - QueryRow(query string, args ...any) *sql.Row - Begin() (*sql.Tx, error) -} - type MigrationDBRepo interface { List() (map[string]*models.Migration, error) IsApplied(version string) (bool, error) @@ -30,15 +21,15 @@ type MigrationDBRepo interface { } type migrationDBRepo struct { - db SqlDb + db storage.DB } // NewMigrationDBRepo initializes the MigrationDBRepo with a // database. Furthermore, it ensures the migration table it // operates on exists. If it is unable to create or confirm // the table exists, an error is returned. -func NewMigrationDBRepo(db SqlDb) (MigrationDBRepo, error) { - _, err := db.Exec(` +func NewMigrationDBRepo(db storage.DB) (MigrationDBRepo, error) { + _, err := db.Session.SQL().Exec(` CREATE TABLE IF NOT EXISTS bolt_migrations( version CHARACTER(14) PRIMARY KEY NOT NULL ); @@ -58,7 +49,7 @@ func NewMigrationDBRepo(db SqlDb) (MigrationDBRepo, error) { // will be ones that have been applied, and their message // will always be an empty string. func (mr migrationDBRepo) List() (map[string]*models.Migration, error) { - rows, err := mr.db.Query(`SELECT version FROM bolt_migrations;`) + rows, err := mr.db.Session.SQL().Select("version").From("bolt_migrations").Query() if err != nil { return nil, fmt.Errorf( "unable to execute query to select versions from "+ @@ -98,9 +89,12 @@ func (mr migrationDBRepo) List() (map[string]*models.Migration, error) { // when the version might be applied, but there was an error. // Check err first before looking at whether the version is applied. func (mr migrationDBRepo) IsApplied(version string) (bool, error) { - row := mr.db.QueryRow(`SELECT 1 FROM bolt_migrations WHERE version = $1`, version) + row, err := mr.db.Session.SQL().Select(1).From("bolt_migrations").Where("version = ?", version).QueryRow() + if err != nil { + return false, fmt.Errorf("unable to execute query to check if version exists in bolt_migrations: %w", err) + } var scanResult int - err := row.Scan(&scanResult) + err = row.Scan(&scanResult) if errors.Is(err, sql.ErrNoRows) { return false, nil } @@ -122,7 +116,7 @@ func (mr migrationDBRepo) Apply( upgradeScript string, migration *models.Migration, ) error { - err := applyMigration(mr.db, upgradeScript, *migration) + err := applyMigration(mr.db.Session, upgradeScript, *migration) if err != nil { return err } @@ -137,49 +131,35 @@ func (mr migrationDBRepo) ApplyWithTx( upgradeScript string, migration *models.Migration, ) error { - tx, err := mr.db.Begin() - if err != nil { - return fmt.Errorf( - "unable to start transaction during upgrade script execution: %w", - err, - ) - } - defer tx.Rollback() - - err = applyMigration(tx, upgradeScript, *migration) + err := mr.db.Session.Tx(func(sess db.Session) error { + err := applyMigration(sess, upgradeScript, *migration) + if err != nil { + return err + } + return nil + }) if err != nil { return err } - err = tx.Commit() - if err != nil { - return fmt.Errorf( - "unable to commit transaction during upgrade script execution: %w", - err, - ) - } - migration.Applied = true return nil } func applyMigration( - executor sqlExecutor, + db db.Session, upgradeScript string, migration models.Migration, ) error { - _, err := executor.Exec(upgradeScript) + _, err := db.SQL().Exec(upgradeScript) if err != nil { return fmt.Errorf("unable to execute upgrade script: %w", err) } - _, err = executor.Exec( - `INSERT INTO bolt_migrations(version) VALUES ($1);`, - migration.Version, - ) + _, err = db.SQL().InsertInto("bolt_migrations").Columns("version").Values(migration.Version).Exec() if err != nil { return fmt.Errorf( - "unable to insert applied migration: %w", + "unable to insert migration: %w", err, ) } @@ -194,7 +174,7 @@ func (mr migrationDBRepo) Revert( downgradeScript string, migration *models.Migration, ) error { - err := revertMigration(mr.db, downgradeScript, *migration) + err := revertMigration(mr.db.Session, downgradeScript, *migration) if err != nil { return err } @@ -209,46 +189,32 @@ func (mr migrationDBRepo) RevertWithTx( downgradeScript string, migration *models.Migration, ) error { - tx, err := mr.db.Begin() - if err != nil { - return fmt.Errorf( - "unable to start transaction during downgrade script execution: %w", - err, - ) - } - defer tx.Rollback() - - err = revertMigration(tx, downgradeScript, *migration) + err := mr.db.Session.Tx(func(sess db.Session) error { + err := revertMigration(sess, downgradeScript, *migration) + if err != nil { + return err + } + return nil + }) if err != nil { return err } - err = tx.Commit() - if err != nil { - return fmt.Errorf( - "unable to commit transaction during downgrade script execution: %w", - err, - ) - } - migration.Applied = false return nil } func revertMigration( - executor sqlExecutor, + db db.Session, downgradeScript string, migration models.Migration, ) error { - _, err := executor.Exec(downgradeScript) + _, err := db.SQL().Exec(downgradeScript) if err != nil { return fmt.Errorf("unable to execute downgrade script: %w", err) } - _, err = executor.Exec( - `DELETE FROM bolt_migrations WHERE version = $1;`, - migration.Version, - ) + _, err = db.SQL().DeleteFrom("bolt_migrations").Where("version = ?", migration.Version).Exec() if err != nil { return fmt.Errorf( "unable to remove reverted migration from bolt_migrations table: %w", diff --git a/internal/repositories/migration_db_repo_test.go b/internal/repositories/migration_db_repo_test.go index 2775faa..0ab6348 100644 --- a/internal/repositories/migration_db_repo_test.go +++ b/internal/repositories/migration_db_repo_test.go @@ -1,8 +1,6 @@ package repositories_test import ( - "database/sql" - "errors" "testing" "time" @@ -10,65 +8,59 @@ import ( "github.com/eugenetriguba/bolt/internal/models" "github.com/eugenetriguba/bolt/internal/repositories" "github.com/eugenetriguba/checkmate/assert" + "github.com/upper/db/v4" ) func TestNewMigrationDBRepo_CreatesTable(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") - row := db.QueryRow(` - SELECT 1 - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA = 'public' - AND TABLE_NAME = 'bolt_migrations' - `) - var scanResult int - err := row.Scan(&scanResult) - assert.ErrorIs(t, err, sql.ErrNoRows) + testdb := bolttest.NewTestDB(t) + exists, err := testdb.Session.Collection("bolt_migrations").Exists() + assert.ErrorIs(t, err, db.ErrCollectionDoesNotExist) + assert.False(t, exists) - _, err = repositories.NewMigrationDBRepo(db) + _, err = repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) - row = db.QueryRow(` - SELECT 1 - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA = 'public' - AND TABLE_NAME = 'bolt_migrations' - `) - err = row.Scan(&scanResult) + exists, err = testdb.Session.Collection("bolt_migrations").Exists() assert.Nil(t, err) + assert.True(t, exists) } func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") - _, err := db.Exec(`CREATE TABLE bolt_migrations(id INT NOT NULL PRIMARY KEY)`) + testdb := bolttest.NewTestDB(t) + _, err := testdb.Session.SQL().Exec(`CREATE TABLE bolt_migrations(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) - _, err = db.Exec(`INSERT INTO bolt_migrations(id) VALUES (1);`) + _, err = testdb.Session.SQL().Exec(`INSERT INTO bolt_migrations(id) VALUES (1);`) assert.Nil(t, err) - _, err = repositories.NewMigrationDBRepo(db) + _, err = repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) - row := db.QueryRow(`SELECT id FROM bolt_migrations`) + count, err := testdb.Session.Collection("bolt_migrations").Find().Count() + assert.Nil(t, err) + assert.Equal(t, count, uint64(1)) + row, err := testdb.Session.SQL().Select("id").From("bolt_migrations").QueryRow() + assert.Nil(t, err) var scanResult int err = row.Scan(&scanResult) assert.Nil(t, err) assert.Equal(t, scanResult, 1) } -func TestNewMigrationDBRepo_TableCreationErr(t *testing.T) { - expectedErr := errors.New("error!") - db := bolttest.MockSqlDb{ - ExecReturnValue: bolttest.ExecReturnValue{ - Err: expectedErr, - }, - } +// func TestNewMigrationDBRepo_TableCreationErr(t *testing.T) { +// expectedErr := errors.New("error!") +// db := bolttest.MockSqlDb{ +// ExecReturnValue: bolttest.ExecReturnValue{ +// Err: expectedErr, +// }, +// } - _, err := repositories.NewMigrationDBRepo(&db) +// _, err := repositories.NewMigrationDBRepo(&db) - assert.ErrorIs(t, err, expectedErr) -} +// assert.ErrorIs(t, err, expectedErr) +// } func TestList_EmptyTable(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) @@ -77,28 +69,28 @@ func TestList_EmptyTable(t *testing.T) { assert.Equal(t, len(migrations), 0) } -func TestList_QueryErr(t *testing.T) { - expectedErr := errors.New("error!") - db := bolttest.MockSqlDb{ - QueryReturnValue: bolttest.QueryReturnValue{ - Err: expectedErr, - }, - } - repo, err := repositories.NewMigrationDBRepo(&db) - assert.Nil(t, err) +// func TestList_QueryErr(t *testing.T) { +// expectedErr := errors.New("error!") +// db := bolttest.MockSqlDb{ +// QueryReturnValue: bolttest.QueryReturnValue{ +// Err: expectedErr, +// }, +// } +// repo, err := repositories.NewMigrationDBRepo(&db) +// assert.Nil(t, err) - _, err = repo.List() +// _, err = repo.List() - assert.ErrorIs(t, err, expectedErr) -} +// assert.ErrorIs(t, err, expectedErr) +// } func TestList_SingleResult(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) version := "20230101000000" - _, err = db.Exec(`INSERT INTO bolt_migrations(version) VALUES ($1);`, version) + _, err = db.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(version).Exec() assert.Nil(t, err) migrations, err := repo.List() @@ -112,12 +104,12 @@ func TestList_SingleResult(t *testing.T) { } func TestList_ShortVersion(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) version := "20230101" - _, err = db.Exec(`INSERT INTO bolt_migrations(version) VALUES ($1);`, version) + _, err = db.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(version).Exec() assert.Nil(t, err) migrations, err := repo.List() @@ -131,7 +123,7 @@ func TestList_ShortVersion(t *testing.T) { } func TestIsApplied_WithNotApplied(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) @@ -142,12 +134,12 @@ func TestIsApplied_WithNotApplied(t *testing.T) { } func TestIsApplied_WithApplied(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) version := "20230101010101" - _, err = db.Exec(`INSERT INTO bolt_migrations(version) VALUES ($1);`, version) + _, err = db.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(version).Exec() assert.Nil(t, err) applied, err := repo.IsApplied(version) @@ -156,104 +148,99 @@ func TestIsApplied_WithApplied(t *testing.T) { } func TestApply(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") - repo, err := repositories.NewMigrationDBRepo(db) + testdb := bolttest.NewTestDB(t) + repo, err := repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) + t.Cleanup(func() { + bolttest.DropTable(t, testdb, "tmp") + }) migration := models.NewTimestampMigration(time.Now(), "test") err = repo.Apply(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`, migration) assert.Nil(t, err) assert.Equal(t, migration.Applied, true) - row := db.QueryRow(` - SELECT 1 - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA = 'public' - AND TABLE_NAME = 'tmp' - `) - var scanResult int - err = row.Scan(&scanResult) + exists, err := testdb.Session.Collection("tmp").Exists() assert.Nil(t, err) + assert.True(t, exists) applied, err := repo.IsApplied(migration.Version) assert.Nil(t, err) assert.Equal(t, applied, true) } func TestApply_MalformedSql(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") err = repo.Apply("this is not SQL", migration) - assert.ErrorContains(t, err, "syntax error") + + assert.NotNil(t, err) assert.Equal(t, migration.Applied, false) } -func TestApplyWithTx_BeginErr(t *testing.T) { - expectedErr := errors.New("error!") - db := bolttest.MockSqlDb{ - BeginReturnValue: bolttest.BeginReturnValue{ - Err: expectedErr, - }, - } - repo, err := repositories.NewMigrationDBRepo(&db) - assert.Nil(t, err) - migration := models.NewTimestampMigration(time.Now(), "test") +// func TestApplyWithTx_BeginErr(t *testing.T) { +// expectedErr := errors.New("error!") +// db := bolttest.MockSqlDb{ +// BeginReturnValue: bolttest.BeginReturnValue{ +// Err: expectedErr, +// }, +// } +// repo, err := repositories.NewMigrationDBRepo(&db) +// assert.Nil(t, err) +// migration := models.NewTimestampMigration(time.Now(), "test") - err = repo.ApplyWithTx("SELECT 1 FROM bolt_migrations;", migration) +// err = repo.ApplyWithTx("SELECT 1 FROM bolt_migrations;", migration) - assert.ErrorIs(t, err, expectedErr) -} +// assert.ErrorIs(t, err, expectedErr) +// } func TestApplyWithTx_ExecErr(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") err = repo.ApplyWithTx("SELECT 1 FROM abc123donotexist;", migration) - assert.ErrorContains(t, err, `relation "abc123donotexist" does not exist`) + assert.ErrorContains(t, err, `unable to execute upgrade script`) } func TestApplyWithTx_SuccessfullyApplied(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") - repo, err := repositories.NewMigrationDBRepo(db) + testdb := bolttest.NewTestDB(t) + repo, err := repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) + t.Cleanup(func() { + bolttest.DropTable(t, testdb, "tmp") + }) migration := models.NewTimestampMigration(time.Now(), "test") err = repo.ApplyWithTx(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`, migration) assert.Nil(t, err) assert.Equal(t, migration.Applied, true) - row := db.QueryRow(` - SELECT 1 - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA = 'public' - AND TABLE_NAME = 'tmp' - `) - var scanResult int - err = row.Scan(&scanResult) + exists, err := testdb.Session.Collection("tmp").Exists() assert.Nil(t, err) + assert.True(t, exists) applied, err := repo.IsApplied(migration.Version) assert.Nil(t, err) assert.Equal(t, applied, true) } func TestRevert(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") - repo, err := repositories.NewMigrationDBRepo(db) + testdb := bolttest.NewTestDB(t) + repo, err := repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) + t.Cleanup(func() { + bolttest.DropTable(t, testdb, "tmp") + }) - _, err = db.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) + _, err = testdb.Session.SQL().Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") - _, err = db.Exec( - `INSERT INTO bolt_migrations(version) VALUES ($1);`, - migration.Version, - ) + _, err = testdb.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(migration.Version).Exec() assert.Nil(t, err) migration.Applied = true @@ -261,75 +248,66 @@ func TestRevert(t *testing.T) { assert.Nil(t, err) assert.Equal(t, migration.Applied, false) - row := db.QueryRow(` - SELECT 1 - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA = 'public' - AND TABLE_NAME = 'tmp' - `) - var scanResult int - err = row.Scan(&scanResult) - assert.ErrorIs(t, err, sql.ErrNoRows) - - row = db.QueryRow(`SELECT count(*) FROM bolt_migrations`) - var count int - err = row.Scan(&count) + exists, err := testdb.Session.Collection("tmp").Exists() + assert.ErrorIs(t, err, db.ErrCollectionDoesNotExist) + assert.False(t, exists) + count, err := testdb.Session.Collection("bolt_migrations").Count() assert.Nil(t, err) - assert.Equal(t, count, 0) + assert.Equal(t, count, uint64(0)) } func TestRevert_MalformedSql(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") migration.Applied = true err = repo.Revert("this is not SQL", migration) - assert.ErrorContains(t, err, "syntax error") + assert.ErrorContains(t, err, "unable to execute downgrade script") assert.Equal(t, migration.Applied, true) } -func TestRevertWithTx_BeginErr(t *testing.T) { - expectedErr := errors.New("error!") - db := bolttest.MockSqlDb{ - BeginReturnValue: bolttest.BeginReturnValue{ - Err: expectedErr, - }, - } - repo, err := repositories.NewMigrationDBRepo(&db) - assert.Nil(t, err) - migration := models.NewTimestampMigration(time.Now(), "test") +// func TestRevertWithTx_BeginErr(t *testing.T) { +// expectedErr := errors.New("error!") +// db := bolttest.MockSqlDb{ +// BeginReturnValue: bolttest.BeginReturnValue{ +// Err: expectedErr, +// }, +// } +// repo, err := repositories.NewMigrationDBRepo(&db) +// assert.Nil(t, err) +// migration := models.NewTimestampMigration(time.Now(), "test") - err = repo.RevertWithTx("DROP TABLE bolt_migrations;", migration) +// err = repo.RevertWithTx("DROP TABLE bolt_migrations;", migration) - assert.ErrorIs(t, err, expectedErr) -} +// assert.ErrorIs(t, err, expectedErr) +// } func TestRevertWithTx_ExecErr(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") + db := bolttest.NewTestDB(t) repo, err := repositories.NewMigrationDBRepo(db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") err = repo.RevertWithTx("DROP TABLE abc123donotexist;", migration) - assert.ErrorContains(t, err, `table "abc123donotexist" does not exist`) + assert.ErrorContains(t, err, `unable to execute downgrade script`) } func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { - db := bolttest.NewTestDB(t, "postgres") - repo, err := repositories.NewMigrationDBRepo(db) + testdb := bolttest.NewTestDB(t) + repo, err := repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) + t.Cleanup(func() { + bolttest.DropTable(t, testdb, "tmp") + }) - _, err = db.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) + _, err = testdb.Session.SQL().Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") - _, err = db.Exec( - `INSERT INTO bolt_migrations(version) VALUES ($1);`, - migration.Version, - ) + _, err = testdb.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(migration.Version).Exec() assert.Nil(t, err) migration.Applied = true @@ -337,19 +315,10 @@ func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { assert.Nil(t, err) assert.Equal(t, migration.Applied, false) - row := db.QueryRow(` - SELECT 1 - FROM INFORMATION_SCHEMA.TABLES - WHERE TABLE_SCHEMA = 'public' - AND TABLE_NAME = 'tmp' - `) - var scanResult int - err = row.Scan(&scanResult) - assert.ErrorIs(t, err, sql.ErrNoRows) - - row = db.QueryRow(`SELECT count(*) FROM bolt_migrations`) - var count int - err = row.Scan(&count) + exists, err := testdb.Session.Collection("tmp").Exists() + assert.ErrorIs(t, err, db.ErrCollectionDoesNotExist) + assert.False(t, exists) + count, err := testdb.Session.Collection("bolt_migrations").Count() assert.Nil(t, err) - assert.Equal(t, count, 0) + assert.Equal(t, count, uint64(0)) } |