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 | |
| parent | use docker compose to start database in workflow (diff) | |
| download | cli-9d2abbe510d1719bcfe31db901423376f79b7894.zip | |
Add MySQL support
Diffstat (limited to 'internal')
| -rw-r--r-- | internal/bolttest/db.go | 42 | ||||
| -rw-r--r-- | internal/commands/down.go | 7 | ||||
| -rw-r--r-- | internal/commands/status.go | 7 | ||||
| -rw-r--r-- | internal/commands/up.go | 7 | ||||
| -rw-r--r-- | internal/configloader/configloader.go | 1 | ||||
| -rw-r--r-- | internal/configloader/configloader_test.go | 11 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo.go | 102 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo_test.go | 271 | ||||
| -rw-r--r-- | internal/storage/db.go | 65 | ||||
| -rw-r--r-- | internal/storage/db_test.go | 49 |
10 files changed, 227 insertions, 335 deletions
diff --git a/internal/bolttest/db.go b/internal/bolttest/db.go index 6ebbabf..a4e1115 100644 --- a/internal/bolttest/db.go +++ b/internal/bolttest/db.go @@ -1,9 +1,8 @@ package bolttest import ( - "database/sql" + "fmt" "os" - "strconv" "testing" "github.com/eugenetriguba/bolt/internal/configloader" @@ -11,39 +10,28 @@ import ( "github.com/eugenetriguba/checkmate/assert" ) -func NewTestConnectionConfig(t *testing.T, driver string) *configloader.ConnectionConfig { - port, err := strconv.Atoi(os.Getenv("BOLT_DB_CONN_PORT")) +func NewTestDB(t *testing.T) storage.DB { + connectionConfig := NewTestConnectionConfig() + testdb, err := storage.DBConnect(connectionConfig) assert.Nil(t, err) + t.Cleanup(func() { + DropTable(t, testdb, "bolt_migrations") + assert.Nil(t, testdb.Session.Close()) + }) + return testdb +} - return &configloader.ConnectionConfig{ - Driver: driver, +func NewTestConnectionConfig() configloader.ConnectionConfig { + return configloader.ConnectionConfig{ + Driver: os.Getenv("BOLT_DB_CONN_DRIVER"), DBName: os.Getenv("BOLT_DB_CONN_DBNAME"), Host: os.Getenv("BOLT_DB_CONN_HOST"), - Port: port, User: os.Getenv("BOLT_DB_CONN_USER"), Password: os.Getenv("BOLT_DB_CONN_PASSWORD"), } } -func NewTestDB(t *testing.T, driver string) *sql.DB { - connectionConfig := NewTestConnectionConfig(t, driver) - connInfo := storage.DBConnectionString(connectionConfig) - db, err := storage.DBConnect(driver, connInfo) +func DropTable(t *testing.T, testdb storage.DB, tableName string) { + _, err := testdb.Session.SQL().Exec(fmt.Sprintf(`DROP TABLE IF EXISTS %s;`, tableName)) assert.Nil(t, err) - t.Cleanup(func() { - _, err = db.Exec(` - DO $$ DECLARE rec RECORD; - BEGIN FOR rec IN ( - SELECT table_name - FROM information_schema.tables - WHERE table_schema = 'public' - ) LOOP EXECUTE 'DROP TABLE IF EXISTS ' || rec.table_name || ' CASCADE'; - END LOOP; - END $$; - `) - assert.Nil(t, err) - err = db.Close() - assert.Nil(t, err) - }) - return db } diff --git a/internal/commands/down.go b/internal/commands/down.go index 116d9ce..0ba3abb 100644 --- a/internal/commands/down.go +++ b/internal/commands/down.go @@ -54,15 +54,12 @@ func (cmd *DownCmd) Execute( return subcommands.ExitFailure } - db, err := storage.DBConnect( - cfg.Connection.Driver, - storage.DBConnectionString(&cfg.Connection), - ) + db, err := storage.DBConnect(cfg.Connection) if err != nil { consoleOutputter.Error(fmt.Errorf("unable to connect to database: %w", err)) return subcommands.ExitFailure } - defer db.Close() + defer db.Session.Close() migrationDBRepo, err := repositories.NewMigrationDBRepo(db) if err != nil { diff --git a/internal/commands/status.go b/internal/commands/status.go index 76f7427..d44a132 100644 --- a/internal/commands/status.go +++ b/internal/commands/status.go @@ -44,15 +44,12 @@ func (m *StatusCmd) Execute( return subcommands.ExitFailure } - db, err := storage.DBConnect( - cfg.Connection.Driver, - storage.DBConnectionString(&cfg.Connection), - ) + db, err := storage.DBConnect(cfg.Connection) if err != nil { consoleOutputter.Error(fmt.Errorf("unable to connect to database: %w", err)) return subcommands.ExitFailure } - defer db.Close() + defer db.Session.Close() migrationDBRepo, err := repositories.NewMigrationDBRepo(db) if err != nil { diff --git a/internal/commands/up.go b/internal/commands/up.go index 330864f..a203d2e 100644 --- a/internal/commands/up.go +++ b/internal/commands/up.go @@ -54,15 +54,12 @@ func (cmd *UpCmd) Execute( return subcommands.ExitFailure } - db, err := storage.DBConnect( - cfg.Connection.Driver, - storage.DBConnectionString(&cfg.Connection), - ) + db, err := storage.DBConnect(cfg.Connection) if err != nil { consoleOutputter.Error(fmt.Errorf("unable to connect to database: %w", err)) return subcommands.ExitFailure } - defer db.Close() + defer db.Session.Close() migrationDBRepo, err := repositories.NewMigrationDBRepo(db) if err != nil { diff --git a/internal/configloader/configloader.go b/internal/configloader/configloader.go index c2c05e7..96bab00 100644 --- a/internal/configloader/configloader.go +++ b/internal/configloader/configloader.go @@ -55,7 +55,6 @@ type MigrationsConfig struct { type ConnectionConfig struct { Host string `toml:"host" envconfig:"BOLT_DB_CONN_HOST"` - Port int `toml:"port" envconfig:"BOLT_DB_CONN_PORT"` User string `toml:"user" envconfig:"BOLT_DB_CONN_USER"` Password string `toml:"password" envconfig:"BOLT_DB_CONN_PASSWORD"` DBName string `toml:"dbname" envconfig:"BOLT_DB_CONN_DBNAME"` diff --git a/internal/configloader/configloader_test.go b/internal/configloader/configloader_test.go index 6aa3b45..0c89672 100644 --- a/internal/configloader/configloader_test.go +++ b/internal/configloader/configloader_test.go @@ -1,7 +1,6 @@ package configloader_test import ( - "fmt" "os" "path/filepath" "testing" @@ -37,7 +36,6 @@ func TestNewConfigWithInvalidVersionStyle(t *testing.T) { func TestNewConfigFindsFileAndPopulatesConfigStruct(t *testing.T) { bolttest.UnsetEnv(t, "BOLT_DB_CONN_HOST") - bolttest.UnsetEnv(t, "BOLT_DB_CONN_PORT") bolttest.UnsetEnv(t, "BOLT_DB_CONN_USER") bolttest.UnsetEnv(t, "BOLT_DB_CONN_PASSWORD") bolttest.UnsetEnv(t, "BOLT_DB_CONN_DBNAME") @@ -51,11 +49,10 @@ func TestNewConfigFindsFileAndPopulatesConfigStruct(t *testing.T) { }, Connection: configloader.ConnectionConfig{ Host: "testhost", - Port: 1234, User: "testuser", Password: "testpassword", DBName: "testdb", - Driver: "postgres", + Driver: "postgresql", }, } tmpdir := t.TempDir() @@ -76,7 +73,6 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { }, Connection: configloader.ConnectionConfig{ Host: "testhost", - Port: 1234, User: "testuser", Password: "testpassword", DBName: "testdb", @@ -92,17 +88,15 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { }, Connection: configloader.ConnectionConfig{ Host: "envtesthost", - Port: 4321, User: "envtestuser", Password: "envtestpassword", DBName: "envtestdb", - Driver: "postgres", + Driver: "postgresql", }, } t.Setenv("BOLT_MIGRATIONS_VERSION_STYLE", string(envCfg.Migrations.VersionStyle)) t.Setenv("BOLT_MIGRATIONS_DIR_PATH", envCfg.Migrations.DirectoryPath) t.Setenv("BOLT_DB_CONN_HOST", envCfg.Connection.Host) - t.Setenv("BOLT_DB_CONN_PORT", fmt.Sprintf("%d", envCfg.Connection.Port)) t.Setenv("BOLT_DB_CONN_USER", envCfg.Connection.User) t.Setenv("BOLT_DB_CONN_PASSWORD", envCfg.Connection.Password) t.Setenv("BOLT_DB_CONN_DBNAME", envCfg.Connection.DBName) @@ -115,7 +109,6 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { func TestNewConfigSearchesParentDirectories(t *testing.T) { bolttest.UnsetEnv(t, "BOLT_DB_CONN_HOST") - bolttest.UnsetEnv(t, "BOLT_DB_CONN_PORT") bolttest.UnsetEnv(t, "BOLT_DB_CONN_USER") bolttest.UnsetEnv(t, "BOLT_DB_CONN_PASSWORD") bolttest.UnsetEnv(t, "BOLT_DB_CONN_DBNAME") 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)) } diff --git a/internal/storage/db.go b/internal/storage/db.go index d83e71d..7f1d93d 100644 --- a/internal/storage/db.go +++ b/internal/storage/db.go @@ -1,16 +1,18 @@ package storage import ( - "database/sql" "errors" "fmt" - "slices" "github.com/eugenetriguba/bolt/internal/configloader" - _ "github.com/jackc/pgx/v5/stdlib" + "github.com/upper/db/v4" + "github.com/upper/db/v4/adapter/mysql" + "github.com/upper/db/v4/adapter/postgresql" ) -var supportedDrivers = []string{"postgres"} +var postgresqlDriverName = "postgresql" +var mysqlDriverName = "mysql" +var supportedDrivers = []string{postgresqlDriverName, mysqlDriverName} var ( ErrMalformedConnectionString = errors.New( @@ -20,42 +22,43 @@ var ( ErrUnsupportedDriver = fmt.Errorf("unsupported driver, supported drivers are %s", supportedDrivers) ) -// DBConnect establishes a connection to the database using the driver -// and connection information. Only "postgres" is supported as the driver. +type DB struct { + Session db.Session +} + +// DBConnect establishes a connection to the database using the +// connection configuration. // // The following errors may be returned: -// - ErrMalformedConnectionString: The provided connection parameters are -// not in a valid format. // - ErrUnableToConnect: Unable to make a connection to the database with // the provided connection parameters. // - ErrUnsupportedDriver: The provided driver is not supported. -func DBConnect(driver string, connectionParams string) (*sql.DB, error) { - if !slices.Contains(supportedDrivers, driver) { - return nil, ErrUnsupportedDriver - } +func DBConnect(cfg configloader.ConnectionConfig) (DB, error) { + var db db.Session + var err error = nil - db, err := sql.Open("pgx", connectionParams) - if err != nil { - return nil, fmt.Errorf("%w: %v", ErrMalformedConnectionString, err) + switch cfg.Driver { + case postgresqlDriverName: + db, err = postgresql.Open(postgresql.ConnectionURL{ + User: cfg.User, + Password: cfg.Password, + Host: cfg.Host, + Database: cfg.DBName, + }) + case mysqlDriverName: + db, err = mysql.Open(mysql.ConnectionURL{ + User: cfg.User, + Password: cfg.Password, + Host: cfg.Host, + Database: cfg.DBName, + }) + default: + return DB{}, ErrUnsupportedDriver } - // Note: `sql.Open` only validates the connection string we provided is sane. - // It doesn't actually open up a connection to the database. For that, we ping - // the database to ensure the connection string is fully valid. - err = db.Ping() if err != nil { - return nil, fmt.Errorf("%w: %v", ErrUnableToConnect, err) + return DB{}, fmt.Errorf("%w: %v", ErrUnableToConnect, err) } - return db, nil -} - -// DBConnectionString generates a properly formatted connection -// string that can be used to establish a database connection. -// Note that it always creates a connection string with sslmode disabled. -func DBConnectionString(cfg *configloader.ConnectionConfig) string { - return fmt.Sprintf( - "host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", - cfg.Host, cfg.Port, cfg.User, cfg.Password, cfg.DBName, - ) + return DB{Session: db}, nil } diff --git a/internal/storage/db_test.go b/internal/storage/db_test.go index 9f87d4c..8162898 100644 --- a/internal/storage/db_test.go +++ b/internal/storage/db_test.go @@ -1,55 +1,38 @@ package storage_test import ( - "fmt" "testing" "github.com/eugenetriguba/bolt/internal/bolttest" - "github.com/eugenetriguba/bolt/internal/configloader" "github.com/eugenetriguba/bolt/internal/storage" "github.com/eugenetriguba/checkmate/assert" ) func TestDBConnect_Success(t *testing.T) { - cfg := bolttest.NewTestConnectionConfig(t, "postgres") - connParams := storage.DBConnectionString(cfg) - - conn, err := storage.DBConnect(cfg.Driver, connParams) + cfg := bolttest.NewTestConnectionConfig() + conn, err := storage.DBConnect(cfg) assert.Nil(t, err) - defer conn.Close() + defer conn.Session.Close() - _, err = conn.Exec("SELECT 1;") + _, err = conn.Session.SQL().Exec("SELECT 1;") assert.Nil(t, err) } -func TestDBConnect_BadConnectionString(t *testing.T) { - _, err := storage.DBConnect("postgres", "pizza=123") - assert.ErrorIs(t, err, storage.ErrUnableToConnect) - assert.ErrorContains(t, err, "unable to open connection to database") -} - func TestDBConnect_UnsupportedDriver(t *testing.T) { - cfg := bolttest.NewTestConnectionConfig(t, "redis") + t.Setenv("BOLT_DB_CONN_DRIVER", "abc123") + cfg := bolttest.NewTestConnectionConfig() - _, err := storage.DBConnect(cfg.Driver, storage.DBConnectionString(cfg)) + _, err := storage.DBConnect(cfg) assert.ErrorIs(t, err, storage.ErrUnsupportedDriver) } -func TestDBConnectionString(t *testing.T) { - cfg := configloader.ConnectionConfig{ - Driver: "postgres", - Host: "db1", - Port: 5432, - User: "testuser", - Password: "supersecretpassword", - DBName: "testdb", - } - - cs := storage.DBConnectionString(&cfg) - - expectedConnectionString := fmt.Sprintf( - "host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", - cfg.Host, cfg.Port, cfg.User, cfg.Password, cfg.DBName, - ) - assert.Equal(t, cs, expectedConnectionString) +func TestDBConnect_BadConnectionString(t *testing.T) { + t.Setenv("BOLT_DB_CONN_DRIVER", "mysql") + t.Setenv("BOLT_DB_CONN_HOST", "abc123") + cfg := bolttest.NewTestConnectionConfig() + + _, err := storage.DBConnect(cfg) + + assert.ErrorIs(t, err, storage.ErrUnableToConnect) + assert.ErrorContains(t, err, "unable to open connection to database") } |