aboutsummaryrefslogtreecommitdiffstats
path: root/internal/storage
diff options
context:
space:
mode:
Diffstat (limited to 'internal/storage')
-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
5 files changed, 270 insertions, 46 deletions
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,
+ )
+}