From 57689dfd7dfe2e3c6efea53b67ad57820683b3b9 Mon Sep 17 00:00:00 2001 From: Eugene Triguba Date: Sun, 10 Mar 2024 15:54:35 -0400 Subject: Add adapters and remove upperdb --- internal/bolttest/db.go | 7 +- internal/commands/down.go | 4 +- internal/commands/status.go | 4 +- internal/commands/up.go | 4 +- internal/configloader/configloader.go | 1 + internal/configloader/configloader_test.go | 6 ++ internal/repositories/migration_db_repo.go | 36 +++---- internal/repositories/migration_db_repo_test.go | 62 ++++++----- internal/storage/adapter.go | 10 ++ internal/storage/db.go | 131 +++++++++++++++++++----- internal/storage/db_test.go | 27 ++--- internal/storage/mysql.go | 77 ++++++++++++++ internal/storage/postgresql.go | 71 +++++++++++++ 13 files changed, 337 insertions(+), 103 deletions(-) create mode 100644 internal/storage/adapter.go create mode 100644 internal/storage/mysql.go create mode 100644 internal/storage/postgresql.go (limited to 'internal') diff --git a/internal/bolttest/db.go b/internal/bolttest/db.go index a4e1115..639adb8 100644 --- a/internal/bolttest/db.go +++ b/internal/bolttest/db.go @@ -12,11 +12,11 @@ import ( func NewTestDB(t *testing.T) storage.DB { connectionConfig := NewTestConnectionConfig() - testdb, err := storage.DBConnect(connectionConfig) + testdb, err := storage.NewDB(connectionConfig) assert.Nil(t, err) t.Cleanup(func() { DropTable(t, testdb, "bolt_migrations") - assert.Nil(t, testdb.Session.Close()) + assert.Nil(t, testdb.Close()) }) return testdb } @@ -26,12 +26,13 @@ func NewTestConnectionConfig() configloader.ConnectionConfig { Driver: os.Getenv("BOLT_DB_CONN_DRIVER"), DBName: os.Getenv("BOLT_DB_CONN_DBNAME"), Host: os.Getenv("BOLT_DB_CONN_HOST"), + Port: os.Getenv("BOLT_DB_CONN_PORT"), User: os.Getenv("BOLT_DB_CONN_USER"), Password: os.Getenv("BOLT_DB_CONN_PASSWORD"), } } func DropTable(t *testing.T, testdb storage.DB, tableName string) { - _, err := testdb.Session.SQL().Exec(fmt.Sprintf(`DROP TABLE IF EXISTS %s;`, tableName)) + _, err := testdb.Exec(fmt.Sprintf("DROP TABLE IF EXISTS %s;", tableName)) assert.Nil(t, err) } diff --git a/internal/commands/down.go b/internal/commands/down.go index 0ba3abb..f991c69 100644 --- a/internal/commands/down.go +++ b/internal/commands/down.go @@ -54,12 +54,12 @@ func (cmd *DownCmd) Execute( return subcommands.ExitFailure } - db, err := storage.DBConnect(cfg.Connection) + db, err := storage.NewDB(cfg.Connection) if err != nil { consoleOutputter.Error(fmt.Errorf("unable to connect to database: %w", err)) return subcommands.ExitFailure } - defer db.Session.Close() + defer db.Close() migrationDBRepo, err := repositories.NewMigrationDBRepo(db) if err != nil { diff --git a/internal/commands/status.go b/internal/commands/status.go index d44a132..1e176b9 100644 --- a/internal/commands/status.go +++ b/internal/commands/status.go @@ -44,12 +44,12 @@ func (m *StatusCmd) Execute( return subcommands.ExitFailure } - db, err := storage.DBConnect(cfg.Connection) + db, err := storage.NewDB(cfg.Connection) if err != nil { consoleOutputter.Error(fmt.Errorf("unable to connect to database: %w", err)) return subcommands.ExitFailure } - defer db.Session.Close() + defer db.Close() migrationDBRepo, err := repositories.NewMigrationDBRepo(db) if err != nil { diff --git a/internal/commands/up.go b/internal/commands/up.go index a203d2e..77e0b8d 100644 --- a/internal/commands/up.go +++ b/internal/commands/up.go @@ -54,12 +54,12 @@ func (cmd *UpCmd) Execute( return subcommands.ExitFailure } - db, err := storage.DBConnect(cfg.Connection) + db, err := storage.NewDB(cfg.Connection) if err != nil { consoleOutputter.Error(fmt.Errorf("unable to connect to database: %w", err)) return subcommands.ExitFailure } - defer db.Session.Close() + defer db.Close() migrationDBRepo, err := repositories.NewMigrationDBRepo(db) if err != nil { diff --git a/internal/configloader/configloader.go b/internal/configloader/configloader.go index 96bab00..8057ee2 100644 --- a/internal/configloader/configloader.go +++ b/internal/configloader/configloader.go @@ -55,6 +55,7 @@ type MigrationsConfig struct { type ConnectionConfig struct { Host string `toml:"host" envconfig:"BOLT_DB_CONN_HOST"` + Port string `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 0c89672..6a27d86 100644 --- a/internal/configloader/configloader_test.go +++ b/internal/configloader/configloader_test.go @@ -36,6 +36,7 @@ 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") @@ -49,6 +50,7 @@ func TestNewConfigFindsFileAndPopulatesConfigStruct(t *testing.T) { }, Connection: configloader.ConnectionConfig{ Host: "testhost", + Port: "1234", User: "testuser", Password: "testpassword", DBName: "testdb", @@ -73,6 +75,7 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { }, Connection: configloader.ConnectionConfig{ Host: "testhost", + Port: "1234", User: "testuser", Password: "testpassword", DBName: "testdb", @@ -88,6 +91,7 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { }, Connection: configloader.ConnectionConfig{ Host: "envtesthost", + Port: "4321", User: "envtestuser", Password: "envtestpassword", DBName: "envtestdb", @@ -97,6 +101,7 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { 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", 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) @@ -109,6 +114,7 @@ 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 56bff0c..27644ae 100644 --- a/internal/repositories/migration_db_repo.go +++ b/internal/repositories/migration_db_repo.go @@ -8,7 +8,6 @@ import ( "github.com/eugenetriguba/bolt/internal/models" "github.com/eugenetriguba/bolt/internal/storage" - "github.com/upper/db/v4" ) type MigrationDBRepo interface { @@ -29,7 +28,7 @@ type migrationDBRepo struct { // operates on exists. If it is unable to create or confirm // the table exists, an error is returned. func NewMigrationDBRepo(db storage.DB) (MigrationDBRepo, error) { - _, err := db.Session.SQL().Exec(` + _, err := db.Exec(` CREATE TABLE IF NOT EXISTS bolt_migrations( version CHARACTER(14) PRIMARY KEY NOT NULL ); @@ -49,7 +48,7 @@ func NewMigrationDBRepo(db storage.DB) (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.Session.SQL().Select("version").From("bolt_migrations").Query() + rows, err := mr.db.Query("SELECT version FROM bolt_migrations;") if err != nil { return nil, fmt.Errorf( "unable to execute query to select versions from "+ @@ -89,12 +88,9 @@ 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, 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 := mr.db.QueryRow("SELECT 1 FROM bolt_migrations WHERE version = ?", version). + Scan(&scanResult) if errors.Is(err, sql.ErrNoRows) { return false, nil } @@ -116,7 +112,7 @@ func (mr migrationDBRepo) Apply( upgradeScript string, migration *models.Migration, ) error { - err := applyMigration(mr.db.Session, upgradeScript, *migration) + err := applyMigration(mr.db, upgradeScript, *migration) if err != nil { return err } @@ -131,8 +127,8 @@ func (mr migrationDBRepo) ApplyWithTx( upgradeScript string, migration *models.Migration, ) error { - err := mr.db.Session.Tx(func(sess db.Session) error { - err := applyMigration(sess, upgradeScript, *migration) + err := mr.db.Tx(func(db storage.DB) error { + err := applyMigration(db, upgradeScript, *migration) if err != nil { return err } @@ -147,16 +143,16 @@ func (mr migrationDBRepo) ApplyWithTx( } func applyMigration( - db db.Session, + db storage.DB, upgradeScript string, migration models.Migration, ) error { - _, err := db.SQL().Exec(upgradeScript) + _, err := db.Exec(upgradeScript) if err != nil { return fmt.Errorf("unable to execute upgrade script: %w", err) } - _, err = db.SQL().InsertInto("bolt_migrations").Columns("version").Values(migration.Version).Exec() + _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", migration.Version) if err != nil { return fmt.Errorf( "unable to insert migration: %w", @@ -174,7 +170,7 @@ func (mr migrationDBRepo) Revert( downgradeScript string, migration *models.Migration, ) error { - err := revertMigration(mr.db.Session, downgradeScript, *migration) + err := revertMigration(mr.db, downgradeScript, *migration) if err != nil { return err } @@ -189,8 +185,8 @@ func (mr migrationDBRepo) RevertWithTx( downgradeScript string, migration *models.Migration, ) error { - err := mr.db.Session.Tx(func(sess db.Session) error { - err := revertMigration(sess, downgradeScript, *migration) + err := mr.db.Tx(func(db storage.DB) error { + err := revertMigration(db, downgradeScript, *migration) if err != nil { return err } @@ -205,16 +201,16 @@ func (mr migrationDBRepo) RevertWithTx( } func revertMigration( - db db.Session, + db storage.DB, downgradeScript string, migration models.Migration, ) error { - _, err := db.SQL().Exec(downgradeScript) + _, err := db.Exec(downgradeScript) if err != nil { return fmt.Errorf("unable to execute downgrade script: %w", err) } - _, err = db.SQL().DeleteFrom("bolt_migrations").Where("version = ?", migration.Version).Exec() + _, err = db.Exec("DELETE FROM bolt_migrations WHERE version = ?", migration.Version) 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 b9fb16e..d54fb24 100644 --- a/internal/repositories/migration_db_repo_test.go +++ b/internal/repositories/migration_db_repo_test.go @@ -8,40 +8,38 @@ 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) { testdb := bolttest.NewTestDB(t) - exists, err := testdb.Session.Collection("bolt_migrations").Exists() - assert.ErrorIs(t, err, db.ErrCollectionDoesNotExist) + exists, err := testdb.TableExists("bolt_migrations") + assert.Nil(t, err) assert.False(t, exists) _, err = repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) - exists, err = testdb.Session.Collection("bolt_migrations").Exists() + exists, err = testdb.TableExists("bolt_migrations") assert.Nil(t, err) assert.True(t, exists) } func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { testdb := bolttest.NewTestDB(t) - _, err := testdb.Session.SQL().Exec(`CREATE TABLE bolt_migrations(id INT NOT NULL PRIMARY KEY)`) + _, err := testdb.Exec(`CREATE TABLE bolt_migrations(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) - _, err = testdb.Session.SQL().Exec(`INSERT INTO bolt_migrations(id) VALUES (1);`) + _, err = testdb.Exec(`INSERT INTO bolt_migrations(id) VALUES (1);`) assert.Nil(t, err) _, err = repositories.NewMigrationDBRepo(testdb) assert.Nil(t, err) - 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() + var count int + err = testdb.QueryRow("SELECT count(*) FROM bolt_migrations;").Scan(&count) assert.Nil(t, err) + assert.Equal(t, count, 1) var scanResult int - err = row.Scan(&scanResult) + err = testdb.QueryRow("SELECT id FROM bolt_migrations;").Scan(&scanResult) assert.Nil(t, err) assert.Equal(t, scanResult, 1) } @@ -62,7 +60,7 @@ func TestList_SingleResult(t *testing.T) { assert.Nil(t, err) version := "20230101000000" - _, err = db.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(version).Exec() + _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", version) assert.Nil(t, err) migrations, err := repo.List() @@ -81,7 +79,7 @@ func TestList_ShortVersion(t *testing.T) { assert.Nil(t, err) version := "20230101" - _, err = db.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(version).Exec() + _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", version) assert.Nil(t, err) migrations, err := repo.List() @@ -111,7 +109,7 @@ func TestIsApplied_WithApplied(t *testing.T) { assert.Nil(t, err) version := "20230101010101" - _, err = db.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(version).Exec() + _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", version) assert.Nil(t, err) applied, err := repo.IsApplied(version) @@ -132,7 +130,7 @@ func TestApply(t *testing.T) { assert.Nil(t, err) assert.Equal(t, migration.Applied, true) - exists, err := testdb.Session.Collection("tmp").Exists() + exists, err := testdb.TableExists("tmp") assert.Nil(t, err) assert.True(t, exists) applied, err := repo.IsApplied(migration.Version) @@ -176,7 +174,7 @@ func TestApplyWithTx_SuccessfullyApplied(t *testing.T) { assert.Nil(t, err) assert.Equal(t, migration.Applied, true) - exists, err := testdb.Session.Collection("tmp").Exists() + exists, err := testdb.TableExists("tmp") assert.Nil(t, err) assert.True(t, exists) applied, err := repo.IsApplied(migration.Version) @@ -192,11 +190,14 @@ func TestRevert(t *testing.T) { bolttest.DropTable(t, testdb, "tmp") }) - _, err = testdb.Session.SQL().Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) + _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") - _, err = testdb.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(migration.Version).Exec() + _, err = testdb.Exec( + "INSERT INTO bolt_migrations(version) VALUES(?)", + migration.Version, + ) assert.Nil(t, err) migration.Applied = true @@ -204,12 +205,13 @@ func TestRevert(t *testing.T) { assert.Nil(t, err) assert.Equal(t, migration.Applied, false) - exists, err := testdb.Session.Collection("tmp").Exists() - assert.ErrorIs(t, err, db.ErrCollectionDoesNotExist) + exists, err := testdb.TableExists("tmp") + assert.Nil(t, err) assert.False(t, exists) - count, err := testdb.Session.Collection("bolt_migrations").Count() + var count int + err = testdb.QueryRow("SELECT count(*) FROM bolt_migrations;").Scan(&count) assert.Nil(t, err) - assert.Equal(t, count, uint64(0)) + assert.Equal(t, count, 0) } func TestRevert_MalformedSql(t *testing.T) { @@ -243,11 +245,14 @@ func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { bolttest.DropTable(t, testdb, "tmp") }) - _, err = testdb.Session.SQL().Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) + _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") - _, err = testdb.Session.SQL().InsertInto("bolt_migrations").Columns("version").Values(migration.Version).Exec() + _, err = testdb.Exec( + "INSERT INTO bolt_migrations(version) VALUES(?)", + migration.Version, + ) assert.Nil(t, err) migration.Applied = true @@ -255,10 +260,11 @@ func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { assert.Nil(t, err) assert.Equal(t, migration.Applied, false) - exists, err := testdb.Session.Collection("tmp").Exists() - assert.ErrorIs(t, err, db.ErrCollectionDoesNotExist) + exists, err := testdb.TableExists("tmp") + assert.Nil(t, err) assert.False(t, exists) - count, err := testdb.Session.Collection("bolt_migrations").Count() + var count int + err = testdb.QueryRow("SELECT count(*) FROM bolt_migrations;").Scan(&count) assert.Nil(t, err) - assert.Equal(t, count, uint64(0)) + assert.Equal(t, count, 0) } diff --git a/internal/storage/adapter.go b/internal/storage/adapter.go new file mode 100644 index 0000000..ce01989 --- /dev/null +++ b/internal/storage/adapter.go @@ -0,0 +1,10 @@ +package storage + +import "github.com/eugenetriguba/bolt/internal/configloader" + +type DBAdapter interface { + ConvertGenericPlaceholders(query string, argsCount int) string + TableExists(executor sqlExecutor, tableName string) (bool, error) + DatabaseName(executor sqlExecutor) (string, error) + CreateDSN(cfg configloader.ConnectionConfig) string +} diff --git a/internal/storage/db.go b/internal/storage/db.go index 7f1d93d..6a7650e 100644 --- a/internal/storage/db.go +++ b/internal/storage/db.go @@ -1,64 +1,139 @@ package storage import ( + "database/sql" "errors" "fmt" "github.com/eugenetriguba/bolt/internal/configloader" - "github.com/upper/db/v4" - "github.com/upper/db/v4/adapter/mysql" - "github.com/upper/db/v4/adapter/postgresql" + _ "github.com/go-sql-driver/mysql" + _ "github.com/jackc/pgx/v4/stdlib" ) var postgresqlDriverName = "postgresql" var mysqlDriverName = "mysql" -var supportedDrivers = []string{postgresqlDriverName, mysqlDriverName} + +var supportedDrivers = map[string]string{ + postgresqlDriverName: "pgx", + mysqlDriverName: "mysql", +} + +var driverAdapters = map[string]DBAdapter{ + postgresqlDriverName: PostgresqlAdapter{}, + mysqlDriverName: MySQLAdapter{}, +} var ( ErrMalformedConnectionString = errors.New( "malformed database connection parameters provided", ) ErrUnableToConnect = errors.New("unable to open connection to database") - ErrUnsupportedDriver = fmt.Errorf("unsupported driver, supported drivers are %s", supportedDrivers) + ErrUnsupportedDriver = fmt.Errorf( + "unsupported driver, supported drivers are %s", + []string{postgresqlDriverName, mysqlDriverName}, + ) ) +type sqlExecutor interface { + Exec(query string, args ...interface{}) (sql.Result, error) + Query(query string, args ...interface{}) (*sql.Rows, error) + QueryRow(query string, args ...interface{}) *sql.Row +} + type DB struct { - Session db.Session + executor sqlExecutor + sqlDB *sql.DB + adapter DBAdapter } -// DBConnect establishes a connection to the database using the +// NewDB 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(cfg configloader.ConnectionConfig) (DB, error) { - var db db.Session - var err error = nil - - 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: +func NewDB(cfg configloader.ConnectionConfig) (DB, error) { + driverName, exists := supportedDrivers[cfg.Driver] + if !exists { + return DB{}, ErrUnsupportedDriver + } + + adapter, exists := driverAdapters[cfg.Driver] + if !exists { return DB{}, ErrUnsupportedDriver } + db, err := sql.Open(driverName, adapter.CreateDSN(cfg)) + if err != nil { + return DB{}, fmt.Errorf("%w: %v", ErrMalformedConnectionString, err) + } + + // Note: `sql.Open` only validates the connection string we provided is sane. + // It doesn't 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 DB{}, fmt.Errorf("%w: %v", ErrUnableToConnect, err) } - return DB{Session: db}, nil + return DB{executor: db, sqlDB: db, adapter: adapter}, nil +} + +// Close closes the database connection. Any further +// queries will result in errors, and you should call +// NewDB again after if you'd like to run more. +func (db DB) Close() error { + return db.sqlDB.Close() +} + +func (db DB) Exec(query string, args ...any) (sql.Result, error) { + newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) + return db.executor.Exec(newQuery, args...) +} + +func (db DB) Query(query string, args ...any) (*sql.Rows, error) { + newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) + return db.executor.Query(newQuery, args...) +} + +func (db DB) QueryRow(query string, args ...any) *sql.Row { + newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) + return db.executor.QueryRow(newQuery, args...) +} + +func (db DB) TableExists(tableName string) (bool, error) { + return db.adapter.TableExists(db.executor, tableName) +} + +type txFunc func(db DB) error + +func (db *DB) Tx(fn txFunc) error { + tx, err := db.sqlDB.Begin() + if err != nil { + return fmt.Errorf( + "unable to start transaction: %w", + err, + ) + } + defer tx.Rollback() + + // Create a shallow clone of the DB instance for + // the transaction scope with a tx executor. + txDB := *db + txDB.executor = tx + + err = fn(txDB) + if err != nil { + return err + } + + err = tx.Commit() + if err != nil { + return err + } + + return nil } diff --git a/internal/storage/db_test.go b/internal/storage/db_test.go index 8162898..0eacd23 100644 --- a/internal/storage/db_test.go +++ b/internal/storage/db_test.go @@ -8,31 +8,22 @@ import ( "github.com/eugenetriguba/checkmate/assert" ) -func TestDBConnect_Success(t *testing.T) { +func TestNewDB_Success(t *testing.T) { cfg := bolttest.NewTestConnectionConfig() - conn, err := storage.DBConnect(cfg) - assert.Nil(t, err) - defer conn.Session.Close() - _, err = conn.Session.SQL().Exec("SELECT 1;") + db, err := storage.NewDB(cfg) + + assert.Nil(t, err) + defer db.Close() + _, err = db.Exec("SELECT 1;") assert.Nil(t, err) } -func TestDBConnect_UnsupportedDriver(t *testing.T) { +func TestNewDB_UnsupportedDriver(t *testing.T) { t.Setenv("BOLT_DB_CONN_DRIVER", "abc123") cfg := bolttest.NewTestConnectionConfig() - _, err := storage.DBConnect(cfg) - assert.ErrorIs(t, err, storage.ErrUnsupportedDriver) -} + _, err := storage.NewDB(cfg) -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") + assert.ErrorIs(t, err, storage.ErrUnsupportedDriver) } diff --git a/internal/storage/mysql.go b/internal/storage/mysql.go new file mode 100644 index 0000000..19514fb --- /dev/null +++ b/internal/storage/mysql.go @@ -0,0 +1,77 @@ +package storage + +import ( + "fmt" + + "github.com/eugenetriguba/bolt/internal/configloader" + "github.com/go-sql-driver/mysql" +) + +type MySQLAdapter struct{} + +func (m MySQLAdapter) ConvertGenericPlaceholders(query string, argsCount int) string { + // MySQL uses ? as its query argument placeholder, which we're using + // as our generic placeholder so no transformation is necessary. + return query +} + +func (m MySQLAdapter) TableExists( + executor sqlExecutor, + tableName string, +) (bool, error) { + // Note: MySQL doesn't have schemas. So the "table_schema" + // should be the currently selected database. + databaseName, err := m.DatabaseName(executor) + if err != nil { + return false, fmt.Errorf("unable to retrieve database name: %w", err) + } + + var exists bool + err = executor.QueryRow(` + SELECT EXISTS ( + SELECT 1 + FROM INFORMATION_SCHEMA.TABLES + WHERE TABLE_SCHEMA = ? + AND TABLE_NAME = ? + ); + `, databaseName, tableName).Scan(&exists) + if err != nil { + return false, fmt.Errorf( + "unable to check if %s exists: %w", + tableName, + err, + ) + } + + return exists, nil +} + +func (m MySQLAdapter) DatabaseName(executor sqlExecutor) (string, error) { + var name string + err := executor.QueryRow("SELECT DATABASE();").Scan(&name) + if err != nil { + return "", fmt.Errorf("unable to retrieve database name: %w", err) + } + + // MySQL allows connecting without a database selected. + if name == "" { + return "", fmt.Errorf("no database is currently selected") + } + + return name, nil +} + +func (m MySQLAdapter) CreateDSN(cfg configloader.ConnectionConfig) string { + addr := cfg.Host + if cfg.Port != "" { + addr += fmt.Sprintf(":%s", cfg.Port) + } + dsnCfg := mysql.Config{ + Net: "tcp", + Addr: addr, + User: cfg.User, + Passwd: cfg.Password, + DBName: cfg.DBName, + } + return dsnCfg.FormatDSN() +} diff --git a/internal/storage/postgresql.go b/internal/storage/postgresql.go new file mode 100644 index 0000000..f728514 --- /dev/null +++ b/internal/storage/postgresql.go @@ -0,0 +1,71 @@ +package storage + +import ( + "fmt" + "strings" + + "github.com/eugenetriguba/bolt/internal/configloader" +) + +type PostgresqlAdapter struct{} + +func (p PostgresqlAdapter) ConvertGenericPlaceholders( + query string, + argsCount int, +) string { + for i := 0; i <= argsCount; i++ { + placeholder := p.createPlaceholder(i + 1) + query = strings.Replace(query, "?", placeholder, 1) + } + return query +} + +func (p PostgresqlAdapter) createPlaceholder(index int) string { + return fmt.Sprintf("$%d", index) +} + +func (p PostgresqlAdapter) TableExists( + executor sqlExecutor, + tableName string, +) (bool, error) { + var exists bool + // Assumption: Anytime we check if a table exists, it will be + // for the 'public' schema. If someone wants to have bolt_migrations + // table outside of the 'public' schema on postgresql, this would be + // an issue. + err := executor.QueryRow(` + SELECT EXISTS ( + SELECT FROM pg_catalog.pg_class c + JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace + WHERE n.nspname = 'public' + AND c.relname = $1 + AND c.relkind = 'r' -- Only tables + ); + `, tableName).Scan(&exists) + if err != nil { + return false, fmt.Errorf( + "unable to check if %s exists: %w", + tableName, + err, + ) + } + + return exists, nil +} + +func (p PostgresqlAdapter) DatabaseName(executor sqlExecutor) (string, error) { + var name string + err := executor.QueryRow("SELECT database_name();").Scan(&name) + if err != nil { + return "", fmt.Errorf("unable to retrieve database name: %w", err) + } + + return name, nil +} + +func (p PostgresqlAdapter) CreateDSN(cfg configloader.ConnectionConfig) string { + return fmt.Sprintf( + "host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", + cfg.Host, cfg.Port, cfg.User, cfg.Password, cfg.DBName, + ) +} -- cgit v1.2.3