diff options
| author | Eugene Triguba <eugenetriguba@gmail.com> | 2024-08-18 14:51:00 -0400 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-08-18 14:51:00 -0400 |
| commit | d2186ed58d907a81648aa62ffa17ec20fb2d213e (patch) | |
| tree | a5b2e26f1f96f333d48a8b20126ecc9ead697029 | |
| parent | Multiple issue fixes in readme (#3) (diff) | |
| download | cli-d2186ed58d907a81648aa62ffa17ec20fb2d213e.zip | |
GH-4: Add configurable migrations table name (#6)
* Add configurable migrations table name
* Update codecov to be informational
* Add mockdb and couple of extra tests
* remove extra files
* Add list query error test
* Add readme update for custom migration table
| -rw-r--r-- | .env.mssql | 1 | ||||
| -rw-r--r-- | .env.mysql | 1 | ||||
| -rw-r--r-- | .env.postgresql | 1 | ||||
| -rw-r--r-- | .env.sqlite3 | 1 | ||||
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | README.md | 13 | ||||
| -rw-r--r-- | codecov.yml | 11 | ||||
| -rw-r--r-- | internal/bolttest/db.go | 51 | ||||
| -rw-r--r-- | internal/commands/down.go | 2 | ||||
| -rw-r--r-- | internal/commands/status.go | 2 | ||||
| -rw-r--r-- | internal/commands/up.go | 2 | ||||
| -rw-r--r-- | internal/configloader/configloader.go | 16 | ||||
| -rw-r--r-- | internal/configloader/configloader_test.go | 46 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo.go | 73 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo_test.go | 112 | ||||
| -rw-r--r-- | internal/storage/db.go | 43 |
16 files changed, 282 insertions, 94 deletions
@@ -4,3 +4,4 @@ BOLT_DB_PORT=4003 BOLT_DB_USER=sa BOLT_DB_PASSWORD=testPASS123.? BOLT_DB_DRIVER=mssql +BOLT_DB_MIGRATIONS_TABLE=bolt_migrations @@ -4,3 +4,4 @@ BOLT_DB_PORT=4002 BOLT_DB_USER=test_user BOLT_DB_PASSWORD=testpassword BOLT_DB_DRIVER=mysql +BOLT_DB_MIGRATIONS_TABLE=bolt_migrations diff --git a/.env.postgresql b/.env.postgresql index b62d4bc..31e1bfb 100644 --- a/.env.postgresql +++ b/.env.postgresql @@ -4,3 +4,4 @@ BOLT_DB_PORT=4001 BOLT_DB_USER=test_user BOLT_DB_PASSWORD=testpassword BOLT_DB_DRIVER=postgresql +BOLT_DB_MIGRATIONS_TABLE=bolt_migrations diff --git a/.env.sqlite3 b/.env.sqlite3 index f735af9..53856ee 100644 --- a/.env.sqlite3 +++ b/.env.sqlite3 @@ -1,2 +1,3 @@ BOLT_DB_NAME=/tmp/tmp.db BOLT_DB_DRIVER=sqlite3 +BOLT_DB_MIGRATIONS_TABLE=bolt_migrations @@ -3,6 +3,7 @@ # Editors /.vscode +/tags # Bolt CLI config/output /migrations @@ -195,7 +195,7 @@ Apply your migration, which will execute the `upgrade.sql` script: ```bash $ bolt up Applying migration 20240316145038_my_first_migration.. -Successfully applied migration 20240316145038_my_first_migration! +Successfully applied migration 20240316145038_my_first_migration in 3.217875ms! ``` ### Checking Migration Status @@ -237,7 +237,7 @@ To revert your migration: ```bash $ bolt down Reverting migration 20240316145038_my_first_migration.. -Successfully reverted migration 20240316145038_my_first_migration! +Successfully reverted migration 20240316145038_my_first_migration in 6.283667ms! ``` ### Verifying Migration Reversion @@ -323,6 +323,9 @@ dbname = # The name of the database driver to use to connect to # the database. Either "postgresql", "mysql", "mssql", or "sqlite3". driver = +# The name of the database table to create for managing +# the applied migration versions. Defaults to "bolt_migrations". +migrations_table = "bolt_migrations" ``` #### Environment Variables @@ -337,6 +340,7 @@ All configuration file settings have corresponding environment variables. - `BOLT_DB_PASSWORD` - `BOLT_DB_NAME` - `BOLT_DB_DRIVER` +- `BOLT_DB_MIGRATIONS_TABLE` ### Commands @@ -443,3 +447,8 @@ This means that if you wanted to switch version styles, you could do it manually ### How is the migration message used? All migrations are created on the local filesystem in the format `<version>_<message>`. Bolt uses the `<version>` part of the name for keeping track of the migrations and how they should be applied or reverted. The `<message>` part is purely informational so you know what that migration is for. + +### What restrictions are there on the custom migration table? + +While you can configure the database table that bolt uses for keeping track of applied migrations, there are restrictions on the name that can be used. It must only contain alphanumeric or underscore characters (e.g. `my_custom_table`). Furthermore, it _may_ contain one dot (`.`) if you would like to specify a different schema (e.g. `different_schema.my_custom_table`). + diff --git a/codecov.yml b/codecov.yml new file mode 100644 index 0000000..e3ae2cc --- /dev/null +++ b/codecov.yml @@ -0,0 +1,11 @@ +coverage: + status: + project: + default: + informational: true + patch: + default: + informational: true + changes: + default: + informational: true diff --git a/internal/bolttest/db.go b/internal/bolttest/db.go index d03012f..2d21762 100644 --- a/internal/bolttest/db.go +++ b/internal/bolttest/db.go @@ -1,6 +1,7 @@ package bolttest import ( + "database/sql" "fmt" "os" "testing" @@ -15,10 +16,10 @@ func NewTestDB(t *testing.T) storage.DB { db, err := storage.NewDB(connectionConfig) assert.Nil(t, err) - DropTable(t, db, "bolt_migrations") + DropTable(t, db, connectionConfig.MigrationsTable) DropTable(t, db, "tmp") t.Cleanup(func() { - DropTable(t, db, "bolt_migrations") + DropTable(t, db, connectionConfig.MigrationsTable) DropTable(t, db, "tmp") assert.Nil(t, db.Close()) }) @@ -28,12 +29,13 @@ func NewTestDB(t *testing.T) storage.DB { func NewTestConnectionConfig() configloader.ConnectionConfig { return configloader.ConnectionConfig{ - Driver: os.Getenv("BOLT_DB_DRIVER"), - DBName: os.Getenv("BOLT_DB_NAME"), - Host: os.Getenv("BOLT_DB_HOST"), - Port: os.Getenv("BOLT_DB_PORT"), - User: os.Getenv("BOLT_DB_USER"), - Password: os.Getenv("BOLT_DB_PASSWORD"), + Driver: os.Getenv("BOLT_DB_DRIVER"), + DBName: os.Getenv("BOLT_DB_NAME"), + Host: os.Getenv("BOLT_DB_HOST"), + Port: os.Getenv("BOLT_DB_PORT"), + User: os.Getenv("BOLT_DB_USER"), + Password: os.Getenv("BOLT_DB_PASSWORD"), + MigrationsTable: os.Getenv("BOLT_DB_MIGRATIONS_TABLE"), } } @@ -41,3 +43,36 @@ func DropTable(t *testing.T, db storage.DB, tableName string) { _, err := db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS %s;", tableName)) assert.Nil(t, err) } + +type MockDB struct { + ExecFunc func(query string, args ...interface{}) (sql.Result, error) + QueryFunc func(query string, args ...interface{}) (*sql.Rows, error) + QueryRowFunc func(query string, args ...interface{}) *sql.Row + TxFunc func(fn storage.TxFunc) error + CloseFunc func() error + TableExistsFunc func(tableName string) (bool, error) +} + +func (m *MockDB) Exec(query string, args ...interface{}) (sql.Result, error) { + return m.ExecFunc(query, args...) +} + +func (m *MockDB) Query(query string, args ...interface{}) (*sql.Rows, error) { + return m.QueryFunc(query, args...) +} + +func (m *MockDB) QueryRow(query string, args ...interface{}) *sql.Row { + return m.QueryRowFunc(query, args...) +} + +func (m *MockDB) Tx(fn storage.TxFunc) error { + return m.TxFunc(fn) +} + +func (m *MockDB) Close() error { + return m.CloseFunc() +} + +func (m *MockDB) TableExists(tableName string) (bool, error) { + return m.TableExistsFunc(tableName) +} diff --git a/internal/commands/down.go b/internal/commands/down.go index f991c69..b537b17 100644 --- a/internal/commands/down.go +++ b/internal/commands/down.go @@ -61,7 +61,7 @@ func (cmd *DownCmd) Execute( } defer db.Close() - migrationDBRepo, err := repositories.NewMigrationDBRepo(db) + migrationDBRepo, err := repositories.NewMigrationDBRepo(cfg.Connection.MigrationsTable, db) if err != nil { consoleOutputter.Error(err) return subcommands.ExitFailure diff --git a/internal/commands/status.go b/internal/commands/status.go index 1e176b9..6c305fe 100644 --- a/internal/commands/status.go +++ b/internal/commands/status.go @@ -51,7 +51,7 @@ func (m *StatusCmd) Execute( } defer db.Close() - migrationDBRepo, err := repositories.NewMigrationDBRepo(db) + migrationDBRepo, err := repositories.NewMigrationDBRepo(cfg.Connection.MigrationsTable, db) if err != nil { consoleOutputter.Error(err) return subcommands.ExitFailure diff --git a/internal/commands/up.go b/internal/commands/up.go index 77e0b8d..c2cd3ef 100644 --- a/internal/commands/up.go +++ b/internal/commands/up.go @@ -61,7 +61,7 @@ func (cmd *UpCmd) Execute( } defer db.Close() - migrationDBRepo, err := repositories.NewMigrationDBRepo(db) + migrationDBRepo, err := repositories.NewMigrationDBRepo(cfg.Connection.MigrationsTable, db) if err != nil { consoleOutputter.Error(err) return subcommands.ExitFailure diff --git a/internal/configloader/configloader.go b/internal/configloader/configloader.go index c13977a..0f724a6 100644 --- a/internal/configloader/configloader.go +++ b/internal/configloader/configloader.go @@ -54,12 +54,13 @@ type MigrationsConfig struct { } type ConnectionConfig struct { - Host string `toml:"host" envconfig:"BOLT_DB_HOST"` - Port string `toml:"port" envconfig:"BOLT_DB_PORT"` - User string `toml:"user" envconfig:"BOLT_DB_USER"` - Password string `toml:"password" envconfig:"BOLT_DB_PASSWORD"` - DBName string `toml:"dbname" envconfig:"BOLT_DB_NAME"` - Driver string `toml:"driver" envconfig:"BOLT_DB_DRIVER"` + Host string `toml:"host" envconfig:"BOLT_DB_HOST"` + Port string `toml:"port" envconfig:"BOLT_DB_PORT"` + User string `toml:"user" envconfig:"BOLT_DB_USER"` + Password string `toml:"password" envconfig:"BOLT_DB_PASSWORD"` + DBName string `toml:"dbname" envconfig:"BOLT_DB_NAME"` + Driver string `toml:"driver" envconfig:"BOLT_DB_DRIVER"` + MigrationsTable string `toml:"migrations_table" envconfig:"BOLT_DB_MIGRATIONS_TABLE"` } func NewConfig() (*Config, error) { @@ -73,6 +74,9 @@ func NewConfig() (*Config, error) { DirectoryPath: "migrations", VersionStyle: VersionStyleTimestamp, }, + Connection: ConnectionConfig{ + MigrationsTable: "bolt_migrations", + }, } if !errors.Is(err, ErrConfigFileNotFound) { _, err = toml.DecodeFile(filePath, &cfg) diff --git a/internal/configloader/configloader_test.go b/internal/configloader/configloader_test.go index 89c4368..352f243 100644 --- a/internal/configloader/configloader_test.go +++ b/internal/configloader/configloader_test.go @@ -19,6 +19,7 @@ func TestNewConfigDefaults(t *testing.T) { check.Equal(t, cfg.Migrations.DirectoryPath, "migrations") check.Equal(t, cfg.Migrations.VersionStyle, configloader.VersionStyleTimestamp) + check.Equal(t, cfg.Connection.MigrationsTable, "bolt_migrations") } func TestNewConfigWithInvalidVersionStyle(t *testing.T) { @@ -41,6 +42,7 @@ func TestNewConfigFindsFileAndPopulatesConfigStruct(t *testing.T) { bolttest.UnsetEnv(t, "BOLT_DB_PASSWORD") bolttest.UnsetEnv(t, "BOLT_DB_NAME") bolttest.UnsetEnv(t, "BOLT_DB_DRIVER") + bolttest.UnsetEnv(t, "BOLT_DB_MIGRATIONS_TABLE") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_DIR_PATH") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_VERSION_STYLE") expectedCfg := configloader.Config{ @@ -49,12 +51,13 @@ func TestNewConfigFindsFileAndPopulatesConfigStruct(t *testing.T) { VersionStyle: configloader.VersionStyleSequential, }, Connection: configloader.ConnectionConfig{ - Host: "testhost", - Port: "1234", - User: "testuser", - Password: "testpassword", - DBName: "testdb", - Driver: "postgresql", + Host: "testhost", + Port: "1234", + User: "testuser", + Password: "testpassword", + DBName: "testdb", + Driver: "postgresql", + MigrationsTable: "test_table", }, } tmpdir := t.TempDir() @@ -74,12 +77,13 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { VersionStyle: configloader.VersionStyleSequential, }, Connection: configloader.ConnectionConfig{ - Host: "testhost", - Port: "1234", - User: "testuser", - Password: "testpassword", - DBName: "testdb", - Driver: "mysql", + Host: "testhost", + Port: "1234", + User: "testuser", + Password: "testpassword", + DBName: "testdb", + Driver: "mysql", + MigrationsTable: "test_table", }, } bolttest.CreateConfigFile(t, &fileCfg, "bolt.toml") @@ -90,12 +94,13 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { VersionStyle: configloader.VersionStyleTimestamp, }, Connection: configloader.ConnectionConfig{ - Host: "envtesthost", - Port: "4321", - User: "envtestuser", - Password: "envtestpassword", - DBName: "envtestdb", - Driver: "postgresql", + Host: "envtesthost", + Port: "4321", + User: "envtestuser", + Password: "envtestpassword", + DBName: "envtestdb", + Driver: "postgresql", + MigrationsTable: "different_table", }, } t.Setenv("BOLT_MIGRATIONS_VERSION_STYLE", string(envCfg.Migrations.VersionStyle)) @@ -106,6 +111,7 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { t.Setenv("BOLT_DB_PASSWORD", envCfg.Connection.Password) t.Setenv("BOLT_DB_NAME", envCfg.Connection.DBName) t.Setenv("BOLT_DB_DRIVER", envCfg.Connection.Driver) + t.Setenv("BOLT_DB_MIGRATIONS_TABLE", envCfg.Connection.MigrationsTable) cfg, err := configloader.NewConfig() assert.Nil(t, err) @@ -119,6 +125,7 @@ func TestNewConfigSearchesParentDirectories(t *testing.T) { bolttest.UnsetEnv(t, "BOLT_DB_PASSWORD") bolttest.UnsetEnv(t, "BOLT_DB_NAME") bolttest.UnsetEnv(t, "BOLT_DB_DRIVER") + bolttest.UnsetEnv(t, "BOLT_DB_MIGRATIONS_TABLE") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_DIR_PATH") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_VERSION_STYLE") expectedCfg := configloader.Config{ @@ -126,6 +133,9 @@ func TestNewConfigSearchesParentDirectories(t *testing.T) { DirectoryPath: "differentmigrationsdir", VersionStyle: configloader.VersionStyleSequential, }, + Connection: configloader.ConnectionConfig{ + MigrationsTable: "migration_table", + }, } tmpdir := t.TempDir() bolttest.CreateConfigFile(t, &expectedCfg, filepath.Join(tmpdir, "bolt.toml")) diff --git a/internal/repositories/migration_db_repo.go b/internal/repositories/migration_db_repo.go index 26cc2b3..9e069f4 100644 --- a/internal/repositories/migration_db_repo.go +++ b/internal/repositories/migration_db_repo.go @@ -4,6 +4,7 @@ import ( "database/sql" "errors" "fmt" + "regexp" "strings" "github.com/eugenetriguba/bolt/internal/models" @@ -20,37 +21,59 @@ type MigrationDBRepo interface { } type migrationDBRepo struct { - db storage.DB + migrationTableName string + 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 storage.DB) (MigrationDBRepo, error) { - migrationTableExists, err := db.TableExists("bolt_migrations") +func NewMigrationDBRepo(migrationTableName string, db storage.DB) (MigrationDBRepo, error) { + err := sanitizeTableName(migrationTableName) if err != nil { return nil, fmt.Errorf( - "unable to confirm bolt_migrations database table exists: %w", + "invalid migration table name: %w", + err, + ) + } + + migrationTableExists, err := db.TableExists(migrationTableName) + if err != nil { + return nil, fmt.Errorf( + "unable to confirm '%s' database table exists: %w", + migrationTableName, err, ) } if !migrationTableExists { - _, err := db.Exec(` - CREATE TABLE bolt_migrations( + _, err := db.Exec(fmt.Sprintf(` + CREATE TABLE %s ( version VARCHAR(255) PRIMARY KEY NOT NULL ); - `) + `, migrationTableName)) if err != nil { return nil, fmt.Errorf( - "unable to create bolt_migrations database table: %w", + "unable to create '%s' database table: %w", + migrationTableName, err, ) } } - return &migrationDBRepo{db: db}, nil + return &migrationDBRepo{migrationTableName: migrationTableName, db: db}, nil +} + +func sanitizeTableName(tableName string) error { + // Allow alphanumeric characters, underscores, and a single dot for schema.table + validTableName := regexp.MustCompile(`^[a-zA-Z0-9_]+(\.[a-zA-Z0-9_]+)?$`) + if !validTableName.MatchString(tableName) { + return errors.New( + "a migration table name must only contain alphanumeric or underscore characters " + + "and optionally a single dot for schema-qualified names") + } + return nil } // List retrieves a map of migration models that can be @@ -58,11 +81,12 @@ 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.Query("SELECT version FROM bolt_migrations;") + rows, err := mr.db.Query(fmt.Sprintf("SELECT version FROM %s;", mr.migrationTableName)) if err != nil { return nil, fmt.Errorf( "unable to execute query to select versions from "+ - "bolt_migrations database table: %w", + "'%s' database table: %w", + mr.migrationTableName, err, ) } @@ -99,7 +123,7 @@ func (mr migrationDBRepo) List() (map[string]*models.Migration, error) { // Check err first before looking at whether the version is applied. func (mr migrationDBRepo) IsApplied(version string) (bool, error) { var scanResult int - err := mr.db.QueryRow("SELECT 1 FROM bolt_migrations WHERE version = ?", version). + err := mr.db.QueryRow(fmt.Sprintf("SELECT 1 FROM %s WHERE version = ?", mr.migrationTableName), version). Scan(&scanResult) if errors.Is(err, sql.ErrNoRows) { return false, nil @@ -122,7 +146,7 @@ func (mr migrationDBRepo) Apply( upgradeScript string, migration *models.Migration, ) error { - err := applyMigration(mr.db, upgradeScript, *migration) + err := mr.applyMigration(upgradeScript, *migration) if err != nil { return err } @@ -138,7 +162,7 @@ func (mr migrationDBRepo) ApplyWithTx( migration *models.Migration, ) error { err := mr.db.Tx(func(db storage.DB) error { - err := applyMigration(db, upgradeScript, *migration) + err := mr.applyMigration(upgradeScript, *migration) if err != nil { return err } @@ -152,17 +176,16 @@ func (mr migrationDBRepo) ApplyWithTx( return nil } -func applyMigration( - db storage.DB, +func (mr migrationDBRepo) applyMigration( upgradeScript string, migration models.Migration, ) error { - _, err := db.Exec(upgradeScript) + _, err := mr.db.Exec(upgradeScript) if err != nil { return fmt.Errorf("unable to execute upgrade script: %w", err) } - _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", migration.Version) + _, err = mr.db.Exec(fmt.Sprintf("INSERT INTO %s(version) VALUES(?)", mr.migrationTableName), migration.Version) if err != nil { return fmt.Errorf( "unable to insert migration: %w", @@ -180,7 +203,7 @@ func (mr migrationDBRepo) Revert( downgradeScript string, migration *models.Migration, ) error { - err := revertMigration(mr.db, downgradeScript, *migration) + err := mr.revertMigration(downgradeScript, *migration) if err != nil { return err } @@ -196,7 +219,7 @@ func (mr migrationDBRepo) RevertWithTx( migration *models.Migration, ) error { err := mr.db.Tx(func(db storage.DB) error { - err := revertMigration(db, downgradeScript, *migration) + err := mr.revertMigration(downgradeScript, *migration) if err != nil { return err } @@ -210,20 +233,20 @@ func (mr migrationDBRepo) RevertWithTx( return nil } -func revertMigration( - db storage.DB, +func (mr migrationDBRepo) revertMigration( downgradeScript string, migration models.Migration, ) error { - _, err := db.Exec(downgradeScript) + _, err := mr.db.Exec(downgradeScript) if err != nil { return fmt.Errorf("unable to execute downgrade script: %w", err) } - _, err = db.Exec("DELETE FROM bolt_migrations WHERE version = ?", migration.Version) + _, err = mr.db.Exec(fmt.Sprintf("DELETE FROM %s WHERE version = ?", mr.migrationTableName), migration.Version) if err != nil { return fmt.Errorf( - "unable to remove reverted migration from bolt_migrations table: %w", + "unable to remove reverted migration from %s table: %w", + mr.migrationTableName, err, ) } diff --git a/internal/repositories/migration_db_repo_test.go b/internal/repositories/migration_db_repo_test.go index 37efe37..1163b9c 100644 --- a/internal/repositories/migration_db_repo_test.go +++ b/internal/repositories/migration_db_repo_test.go @@ -1,6 +1,8 @@ package repositories_test import ( + "database/sql" + "errors" "testing" "time" @@ -16,7 +18,7 @@ func TestNewMigrationDBRepo_CreatesTable(t *testing.T) { assert.Nil(t, err) assert.False(t, exists) - _, err = repositories.NewMigrationDBRepo(testdb) + _, err = repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) exists, err = testdb.TableExists("bolt_migrations") @@ -24,6 +26,31 @@ func TestNewMigrationDBRepo_CreatesTable(t *testing.T) { assert.True(t, exists) } +func TestNewMigrationDBRepo_CreateTableExecError(t *testing.T) { + mockDB := &bolttest.MockDB{ + TableExistsFunc: func(tableName string) (bool, error) { + return false, nil + }, + ExecFunc: func(query string, args ...interface{}) (sql.Result, error) { + return nil, errors.New("exec error") + }, + } + _, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) + assert.NotNil(t, err) + assert.ErrorContains(t, err, "unable to create 'bolt_migrations' database table: exec error") +} + +func TestNewMigrationDBRepo_TableExistsError(t *testing.T) { + mockDB := &bolttest.MockDB{ + TableExistsFunc: func(tableName string) (bool, error) { + return false, errors.New("table exists failed") + }, + } + _, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) + assert.NotNil(t, err) + assert.ErrorContains(t, err, "unable to confirm 'bolt_migrations' database table exists: table exists failed") +} + func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { testdb := bolttest.NewTestDB(t) _, err := testdb.Exec(`CREATE TABLE bolt_migrations(id INT NOT NULL PRIMARY KEY)`) @@ -31,7 +58,7 @@ func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { _, err = testdb.Exec(`INSERT INTO bolt_migrations(id) VALUES (1);`) assert.Nil(t, err) - _, err = repositories.NewMigrationDBRepo(testdb) + _, err = repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) var count int @@ -46,7 +73,7 @@ func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { func TestList_EmptyTable(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migrations, err := repo.List() @@ -54,9 +81,26 @@ func TestList_EmptyTable(t *testing.T) { assert.Equal(t, len(migrations), 0) } +func TestList_QueryError(t *testing.T) { + mockDB := &bolttest.MockDB{ + TableExistsFunc: func(tableName string) (bool, error) { + return true, nil + }, + QueryFunc: func(query string, args ...interface{}) (*sql.Rows, error) { + return nil, errors.New("query error") + }, + } + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) + assert.Nil(t, err) + + _, err = repo.List() + assert.NotNil(t, err) + assert.ErrorContains(t, err, "unable to execute query to select versions from 'bolt_migrations' database table: query error") +} + func TestList_SingleResult(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101000000" @@ -75,7 +119,7 @@ func TestList_SingleResult(t *testing.T) { func TestList_ShortVersion(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101" @@ -94,7 +138,7 @@ func TestList_ShortVersion(t *testing.T) { func TestIsApplied_WithNotApplied(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101010101" @@ -105,7 +149,7 @@ func TestIsApplied_WithNotApplied(t *testing.T) { func TestIsApplied_WithApplied(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101010101" @@ -117,9 +161,24 @@ func TestIsApplied_WithApplied(t *testing.T) { assert.Equal(t, applied, true) } +func TestIsApplied_SQLInjectionAttempt(t *testing.T) { + db := bolttest.NewTestDB(t) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) + assert.Nil(t, err) + + version := "20230101010101'; DROP TABLE bolt_migrations; --" + applied, err := repo.IsApplied(version) + assert.Nil(t, err) + assert.Equal(t, applied, false) + + exists, err := db.TableExists("bolt_migrations") + assert.Nil(t, err) + assert.True(t, exists) +} + func TestApply(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -137,7 +196,7 @@ func TestApply(t *testing.T) { func TestApply_MalformedSql(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -149,7 +208,7 @@ func TestApply_MalformedSql(t *testing.T) { func TestApplyWithTx_ExecErr(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -160,7 +219,7 @@ func TestApplyWithTx_ExecErr(t *testing.T) { func TestApplyWithTx_SuccessfullyApplied(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -178,7 +237,7 @@ func TestApplyWithTx_SuccessfullyApplied(t *testing.T) { func TestRevert(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) @@ -207,7 +266,7 @@ func TestRevert(t *testing.T) { func TestRevert_MalformedSql(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") migration.Applied = true @@ -219,7 +278,7 @@ func TestRevert_MalformedSql(t *testing.T) { func TestRevertWithTx_ExecErr(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -230,7 +289,7 @@ func TestRevertWithTx_ExecErr(t *testing.T) { func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) @@ -256,3 +315,26 @@ func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { assert.Nil(t, err) assert.Equal(t, count, 0) } + +func TestNewMigrationDBRepo_InvalidTableName(t *testing.T) { + db := bolttest.NewTestDB(t) + invalidTableNames := []string{ + "bolt_migrations; DROP TABLE users; --", // SQL injection attempt + "bolt_migrations`", // Backtick is not allowed + "bolt_migrations'", // Single quote is not allowed + "bolt_migrations\"", // Double quote is not allowed + "bolt_migrations;", // Semicolon could terminate the SQL statement + "bolt_migrations\\", // Backslash is not allowed + "myschema..migrations_table", // Double dot, which is invalid for schema.table format + "myschema.migrations_table.", // Trailing dot is not allowed + "myschema.migrations_table;", // Semicolon in schema-qualified name is not allowed + ".migrations_table", // Leading dot is not allowed + "myschema.migrations_table$", // Dollar sign is not allowed + } + + for _, tableName := range invalidTableNames { + _, err := repositories.NewMigrationDBRepo(tableName, db) + assert.NotNil(t, err) + assert.ErrorContains(t, err, "invalid migration table name") + } +} diff --git a/internal/storage/db.go b/internal/storage/db.go index 1d4cada..b73d250 100644 --- a/internal/storage/db.go +++ b/internal/storage/db.go @@ -51,9 +51,20 @@ type sqlExecutor interface { QueryRow(query string, args ...interface{}) *sql.Row } -type DB struct { +type TxFunc func(db DB) error + +type DB interface { + Exec(query string, args ...interface{}) (sql.Result, error) + Query(query string, args ...interface{}) (*sql.Rows, error) + QueryRow(query string, args ...interface{}) *sql.Row + Tx(fn TxFunc) error + Close() error + TableExists(tableName string) (bool, error) +} + +type SqlDB struct { executor sqlExecutor - sqlDB *sql.DB + conn *sql.DB adapter DBAdapter } @@ -69,12 +80,12 @@ type DB struct { func NewDB(cfg configloader.ConnectionConfig) (DB, error) { driver, exists := supportedDrivers[cfg.Driver] if !exists { - return DB{}, ErrUnsupportedDriver + return SqlDB{}, ErrUnsupportedDriver } db, err := sql.Open(driver.name, driver.adapter.CreateDSN(cfg)) if err != nil { - return DB{}, fmt.Errorf("%w: %v", ErrMalformedConnectionString, err) + return SqlDB{}, fmt.Errorf("%w: %v", ErrMalformedConnectionString, err) } // Note: `sql.Open` only validates the connection string we provided is sane. @@ -82,50 +93,48 @@ func NewDB(cfg configloader.ConnectionConfig) (DB, error) { // 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 SqlDB{}, fmt.Errorf("%w: %v", ErrUnableToConnect, err) } - return DB{executor: db, sqlDB: db, adapter: driver.adapter}, nil + return SqlDB{executor: db, conn: db, adapter: driver.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 SqlDB) Close() error { + return db.conn.Close() } // Exec is a wrapper around the sql.DB Exec. -func (db DB) Exec(query string, args ...any) (sql.Result, error) { +func (db SqlDB) Exec(query string, args ...any) (sql.Result, error) { newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) return db.executor.Exec(newQuery, args...) } // Query is a wrapper around the sql.DB Query. -func (db DB) Query(query string, args ...any) (*sql.Rows, error) { +func (db SqlDB) Query(query string, args ...any) (*sql.Rows, error) { newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) return db.executor.Query(newQuery, args...) } // QueryRow is a wrapper around the sql.DB QueryRow. -func (db DB) QueryRow(query string, args ...any) *sql.Row { +func (db SqlDB) QueryRow(query string, args ...any) *sql.Row { newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) return db.executor.QueryRow(newQuery, args...) } // TableExists checks if the tableName exists within the // database currently connected to. -func (db DB) TableExists(tableName string) (bool, error) { +func (db SqlDB) TableExists(tableName string) (bool, error) { return db.adapter.TableExists(db.executor, tableName) } -type txFunc func(db DB) error - // Tx executes fn within a transaction block. If // fn returns an error, the transaction will be rolled // back. Otherwise, it will be committed. -func (db *DB) Tx(fn txFunc) error { - tx, err := db.sqlDB.Begin() +func (db SqlDB) Tx(fn TxFunc) error { + tx, err := db.conn.Begin() if err != nil { return fmt.Errorf( "unable to start transaction: %w", @@ -136,7 +145,7 @@ func (db *DB) Tx(fn txFunc) error { // Create a shallow clone of the DB instance for // the transaction scope with a tx executor. - txDB := *db + txDB := db txDB.executor = tx err = fn(txDB) |