diff options
Diffstat (limited to 'internal/storage')
| -rw-r--r-- | internal/storage/adapter.go | 10 | ||||
| -rw-r--r-- | internal/storage/db.go | 131 | ||||
| -rw-r--r-- | internal/storage/db_test.go | 27 | ||||
| -rw-r--r-- | internal/storage/mysql.go | 77 | ||||
| -rw-r--r-- | internal/storage/postgresql.go | 71 |
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, + ) +} |