aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorEugene Triguba <eugenetriguba@gmail.com>2024-08-18 16:06:54 -0400
committerGitHub <noreply@github.com>2024-08-18 16:06:54 -0400
commit3ad5a6ab1ef53330f18014b710534d0ed0cd55b0 (patch)
tree5b5d1ed42e59314e65a4ddb8bff2dd7c059fedd9
parentAdd a couple helper scripts (diff)
downloadcli-3ad5a6ab1ef53330f18014b710534d0ed0cd55b0.zip
Fix using a schema with a custom migration table name (#7)
* Fix using a schema with a custom migration table name * Fix test
-rw-r--r--CHANGELOG.md6
-rw-r--r--README.md2
-rw-r--r--internal/repositories/migration_db_repo_mssql_test.go38
-rw-r--r--internal/repositories/migration_db_repo_postgresql_test.go36
-rw-r--r--internal/storage/mssql.go18
-rw-r--r--internal/storage/postgresql.go20
-rw-r--r--internal/storage/postgresql_test.go28
7 files changed, 135 insertions, 13 deletions
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 80280db..97b0b10 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -5,6 +5,12 @@ All notable changes to this project will be documented in this file.
The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.0.0/),
and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html).
+## [Unreleased]
+
+### Fixed
+
+- Using a database schema with a custom migration table (e.g. `my_schema.my_table`) would lead to the table attempting to get re-created each time bolt runs and failing because it already exists.
+
## [0.10.0] - 2024-08-18
### Added
diff --git a/README.md b/README.md
index f85a3d9..ea15e94 100644
--- a/README.md
+++ b/README.md
@@ -451,5 +451,5 @@ All migrations are created on the local filesystem in the format `<version>_<mes
### 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`).
+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`). However, do note that using a custom schema is only support with MSSQL and PostgreSQL since MySQL and SQlite3 both don't support schemas.
diff --git a/internal/repositories/migration_db_repo_mssql_test.go b/internal/repositories/migration_db_repo_mssql_test.go
new file mode 100644
index 0000000..5c79168
--- /dev/null
+++ b/internal/repositories/migration_db_repo_mssql_test.go
@@ -0,0 +1,38 @@
+//go:build mssql
+
+package repositories_test
+
+import (
+ "testing"
+
+ "github.com/eugenetriguba/bolt/internal/bolttest"
+ "github.com/eugenetriguba/bolt/internal/repositories"
+ "github.com/eugenetriguba/checkmate/assert"
+)
+
+func TestNewMigrationDBRepo_CreatesTableInSchema_MSSQL(t *testing.T) {
+ testdb := bolttest.NewTestDB(t)
+ schemaName := "custom_schema"
+ tableName := schemaName + ".bolt_migrations"
+
+ t.Cleanup(func() {
+ _, err := testdb.Exec("DROP TABLE IF EXISTS " + tableName)
+ assert.Nil(t, err)
+ _, err = testdb.Exec("DROP SCHEMA IF EXISTS " + schemaName)
+ assert.Nil(t, err)
+ })
+
+ _, err := testdb.Exec("CREATE SCHEMA " + schemaName)
+ assert.Nil(t, err)
+
+ exists, err := testdb.TableExists(tableName)
+ assert.Nil(t, err)
+ assert.False(t, exists)
+
+ _, err = repositories.NewMigrationDBRepo(tableName, testdb)
+ assert.Nil(t, err)
+
+ exists, err = testdb.TableExists(tableName)
+ assert.Nil(t, err)
+ assert.True(t, exists)
+}
diff --git a/internal/repositories/migration_db_repo_postgresql_test.go b/internal/repositories/migration_db_repo_postgresql_test.go
new file mode 100644
index 0000000..0df9c1d
--- /dev/null
+++ b/internal/repositories/migration_db_repo_postgresql_test.go
@@ -0,0 +1,36 @@
+//go:build postgresql
+
+package repositories_test
+
+import (
+ "testing"
+
+ "github.com/eugenetriguba/bolt/internal/bolttest"
+ "github.com/eugenetriguba/bolt/internal/repositories"
+ "github.com/eugenetriguba/checkmate/assert"
+)
+
+func TestNewMigrationDBRepo_CreatesTableInSchema_Postgresql(t *testing.T) {
+ testdb := bolttest.NewTestDB(t)
+ schemaName := "custom_schema"
+ tableName := schemaName + ".bolt_migrations"
+
+ t.Cleanup(func() {
+ _, err := testdb.Exec("DROP SCHEMA IF EXISTS " + schemaName + " CASCADE;")
+ assert.Nil(t, err)
+ })
+
+ _, err := testdb.Exec("CREATE SCHEMA IF NOT EXISTS " + schemaName)
+ assert.Nil(t, err)
+
+ exists, err := testdb.TableExists(tableName)
+ assert.Nil(t, err)
+ assert.False(t, exists)
+
+ _, err = repositories.NewMigrationDBRepo(tableName, testdb)
+ assert.Nil(t, err)
+
+ exists, err = testdb.TableExists(tableName)
+ assert.Nil(t, err)
+ assert.True(t, exists)
+}
diff --git a/internal/storage/mssql.go b/internal/storage/mssql.go
index a7fbead..4a084ca 100644
--- a/internal/storage/mssql.go
+++ b/internal/storage/mssql.go
@@ -31,17 +31,27 @@ func (m MSSQLAdapter) TableExists(
tableName string,
) (bool, error) {
var exists bool
+
+ schemaName := "dbo"
+ parts := strings.Split(tableName, ".")
+ if len(parts) == 2 {
+ schemaName = parts[0]
+ tableName = parts[1]
+ } else {
+ tableName = parts[0]
+ }
+
err := executor.QueryRow(`
SELECT CASE WHEN EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_SCHEMA = 'dbo'
- AND TABLE_NAME = @p1
+ WHERE TABLE_SCHEMA = @p1
+ AND TABLE_NAME = @p2
) THEN 1 ELSE 0 END
- `, tableName).Scan(&exists)
+ `, schemaName, tableName).Scan(&exists)
if err != nil {
return false, fmt.Errorf(
- "unable to check if dbo.%s exists: %w",
+ "unable to check if %s exists: %w",
tableName,
err,
)
diff --git a/internal/storage/postgresql.go b/internal/storage/postgresql.go
index dee0288..d93d014 100644
--- a/internal/storage/postgresql.go
+++ b/internal/storage/postgresql.go
@@ -29,19 +29,25 @@ func (p PostgresqlAdapter) TableExists(
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.
+
+ schemaName := "public"
+ parts := strings.Split(tableName, ".")
+ if len(parts) == 2 {
+ schemaName = parts[0]
+ tableName = parts[1]
+ } else {
+ tableName = parts[0]
+ }
+
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
+ WHERE n.nspname = $1
+ AND c.relname = $2
AND c.relkind = 'r' -- Only tables
);
- `, tableName).Scan(&exists)
+ `, schemaName, tableName).Scan(&exists)
if err != nil {
return false, fmt.Errorf(
"unable to check if %s exists: %w",
diff --git a/internal/storage/postgresql_test.go b/internal/storage/postgresql_test.go
index ef89cff..d244646 100644
--- a/internal/storage/postgresql_test.go
+++ b/internal/storage/postgresql_test.go
@@ -44,7 +44,7 @@ func TestPostgresql_ConvertGenericPlaceholders(t *testing.T) {
}
}
-func TestPostgresql_TableExists(t *testing.T) {
+func TestPostgresql_TableExistsDefaultSchema(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.PostgresqlAdapter{}
db, err := sql.Open("pgx", adapter.CreateDSN(cfg))
@@ -67,6 +67,32 @@ func TestPostgresql_TableExists(t *testing.T) {
assert.True(t, exists)
}
+func TestPostgresql_TableExistsCustomSchema(t *testing.T) {
+ cfg := bolttest.NewTestConnectionConfig()
+ adapter := storage.PostgresqlAdapter{}
+ db, err := sql.Open("pgx", adapter.CreateDSN(cfg))
+ assert.Nil(t, err)
+ t.Cleanup(func() {
+ _, err = db.Exec("DROP SCHEMA IF EXISTS custom_schema CASCADE;")
+ assert.Nil(t, err)
+ assert.Nil(t, db.Close())
+ })
+
+ exists, err := adapter.TableExists(db, "custom_table.tmp")
+ assert.Nil(t, err)
+ assert.False(t, exists)
+
+ _, err = db.Exec("CREATE SCHEMA custom_schema;")
+ assert.Nil(t, err)
+
+ _, err = db.Exec("CREATE TABLE custom_schema.tmp(id INT PRIMARY KEY);")
+ assert.Nil(t, err)
+
+ exists, err = adapter.TableExists(db, "custom_schema.tmp")
+ assert.Nil(t, err)
+ assert.True(t, exists)
+}
+
func TestPostgresql_DatabaseName(t *testing.T) {
cfg := bolttest.NewTestConnectionConfig()
adapter := storage.PostgresqlAdapter{}