aboutsummaryrefslogtreecommitdiffstats
path: root/internal
diff options
context:
space:
mode:
authorEugene Triguba <eugenetriguba@gmail.com>2024-03-10 15:54:35 -0400
committerEugene Triguba <eugenetriguba@gmail.com>2024-03-10 15:54:35 -0400
commit57689dfd7dfe2e3c6efea53b67ad57820683b3b9 (patch)
tree3e008fc4c0a0baa5d1c44ced357110be223de82a /internal
parentUpdate readme (diff)
downloadcli-57689dfd7dfe2e3c6efea53b67ad57820683b3b9.zip
Add adapters and remove upperdb
Diffstat (limited to 'internal')
-rw-r--r--internal/bolttest/db.go7
-rw-r--r--internal/commands/down.go4
-rw-r--r--internal/commands/status.go4
-rw-r--r--internal/commands/up.go4
-rw-r--r--internal/configloader/configloader.go1
-rw-r--r--internal/configloader/configloader_test.go6
-rw-r--r--internal/repositories/migration_db_repo.go36
-rw-r--r--internal/repositories/migration_db_repo_test.go62
-rw-r--r--internal/storage/adapter.go10
-rw-r--r--internal/storage/db.go131
-rw-r--r--internal/storage/db_test.go27
-rw-r--r--internal/storage/mysql.go77
-rw-r--r--internal/storage/postgresql.go71
13 files changed, 337 insertions, 103 deletions
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,
+ )
+}