Repository: amacneil/dbmate
Pull request #242 (2021-10-06 13:49:28)

Make Status read only
So we have this use case where our developers have read-only access to our databases, and want to be able to run `dbmate status` before running a migration as part of our CI/CD pipeline.

Surprisingly, `dbmate status` has the potential side-effect of creating the migrations table if it's not there, which fails for a read only user.

This change makes the Status command read only by checking if the table exists, and returning all migrations as pending if it doesn't (and works as expected otherwise).

Jell: anything I could do to help move this forward?
Enrico204: Interesting. I agree, status should be R/O, there is no need for it to create a table
amacneil: Would be good to add a test making sure the table doesn’t get created before we merge 
Jell: > Would be good to add a test making sure the table doesn’t get created before we merge

Done! https://github.com/amacneil/dbmate/pull/242/commits/7c76b9b3b15ef1ea30c0dd3c90ffb6b9745003ca
amacneil: Thanks!

Patch:
diff --git a/pkg/dbmate/db.go b/pkg/dbmate/db.go
index f64dd88..cb70ed7 100644
--- a/pkg/dbmate/db.go
+++ b/pkg/dbmate/db.go
@@ -576,17 +576,26 @@ func (db *DB) CheckMigrationsStatus(drv Driver) ([]StatusResult, error) {
 		return nil, fmt.Errorf("no migration files found")
 	}
 
-	sqlDB, err := db.openDatabaseForMigration(drv)
+	sqlDB, err := drv.Open()
 	if err != nil {
 		return nil, err
 	}
 	defer dbutil.MustClose(sqlDB)
 
-	applied, err := drv.SelectMigrations(sqlDB, -1)
+	applied := map[string]bool{}
+
+	migrationsTableExists, err := drv.MigrationsTableExists(sqlDB)
 	if err != nil {
 		return nil, err
 	}
 
+	if migrationsTableExists {
+		applied, err = drv.SelectMigrations(sqlDB, -1)
+		if err != nil {
+			return nil, err
+		}
+	}
+
 	var results []StatusResult
 
 	for _, filename := range files {
diff --git a/pkg/dbmate/driver.go b/pkg/dbmate/driver.go
index e79d60d..730a29b 100644
--- a/pkg/dbmate/driver.go
+++ b/pkg/dbmate/driver.go
@@ -15,6 +15,7 @@ type Driver interface {
 	CreateDatabase() error
 	DropDatabase() error
 	DumpSchema(*sql.DB) ([]byte, error)
+	MigrationsTableExists(*sql.DB) (bool, error)
 	CreateMigrationsTable(*sql.DB) error
 	SelectMigrations(*sql.DB, int) (map[string]bool, error)
 	InsertMigration(dbutil.Transaction, string) error
diff --git a/pkg/driver/clickhouse/clickhouse.go b/pkg/driver/clickhouse/clickhouse.go
index 83571a8..4bd8aab 100644
--- a/pkg/driver/clickhouse/clickhouse.go
+++ b/pkg/driver/clickhouse/clickhouse.go
@@ -230,6 +230,18 @@ func (drv *Driver) DatabaseExists() (bool, error) {
 	return exists, err
 }
 
+// MigrationsTableExists checks if the schema_migrations table exists
+func (drv *Driver) MigrationsTableExists(db *sql.DB) (bool, error) {
+	exists := false
+	err := db.QueryRow("EXISTS TABLE $1", drv.quotedMigrationsTableName()).
+		Scan(&exists)
+	if err == sql.ErrNoRows {
+		return false, nil
+	}
+
+	return exists, err
+}
+
 // CreateMigrationsTable creates the schema migrations table
 func (drv *Driver) CreateMigrationsTable(db *sql.DB) error {
 	_, err := db.Exec(fmt.Sprintf(`
diff --git a/pkg/driver/mysql/mysql.go b/pkg/driver/mysql/mysql.go
index fd25747..ab042c8 100644
--- a/pkg/driver/mysql/mysql.go
+++ b/pkg/driver/mysql/mysql.go
@@ -215,6 +215,19 @@ func (drv *Driver) DatabaseExists() (bool, error) {
 	return exists, err
 }
 
+// MigrationsTableExists checks if the schema_migrations table exists
+func (drv *Driver) MigrationsTableExists(db *sql.DB) (bool, error) {
+	match := ""
+	err := db.QueryRow(fmt.Sprintf("SHOW TABLES LIKE \"%s\"",
+		drv.migrationsTableName)).
+		Scan(&match)
+	if err == sql.ErrNoRows {
+		return false, nil
+	}
+
+	return match != "", err
+}
+
 // CreateMigrationsTable creates the schema_migrations table
 func (drv *Driver) CreateMigrationsTable(db *sql.DB) error {
 	_, err := db.Exec(fmt.Sprintf("create table if not exists %s "+
diff --git a/pkg/driver/postgres/postgres.go b/pkg/driver/postgres/postgres.go
index 60b1333..93dcf06 100644
--- a/pkg/driver/postgres/postgres.go
+++ b/pkg/driver/postgres/postgres.go
@@ -219,6 +219,26 @@ func (drv *Driver) DatabaseExists() (bool, error) {
 	return exists, err
 }
 
+// MigrationsTableExists checks if the schema_migrations table exists
+func (drv *Driver) MigrationsTableExists(db *sql.DB) (bool, error) {
+	schema, migrationsTable, err := drv.quotedMigrationsTableNameParts(db)
+	if err != nil {
+		return false, err
+	}
+
+	exists := false
+	err = db.QueryRow("SELECT 1 FROM information_schema.tables "+
+		"WHERE  table_schema = $1 "+
+		"AND    table_name   = $2",
+		schema, migrationsTable).
+		Scan(&exists)
+	if err == sql.ErrNoRows {
+		return false, nil
+	}
+
+	return exists, err
+}
+
 // CreateMigrationsTable creates the schema_migrations table
 func (drv *Driver) CreateMigrationsTable(db *sql.DB) error {
 	schema, migrationsTable, err := drv.quotedMigrationsTableNameParts(db)
diff --git a/pkg/driver/sqlite/sqlite.go b/pkg/driver/sqlite/sqlite.go
index be2c5f1..c3f6878 100644
--- a/pkg/driver/sqlite/sqlite.go
+++ b/pkg/driver/sqlite/sqlite.go
@@ -141,6 +141,20 @@ func (drv *Driver) DatabaseExists() (bool, error) {
 	return true, nil
 }
 
+// MigrationsTableExists checks if the schema_migrations table exists
+func (drv *Driver) MigrationsTableExists(db *sql.DB) (bool, error) {
+	exists := false
+	err := db.QueryRow("SELECT 1 FROM sqlite_master "+
+		"WHERE type='table' AND name=$1",
+		drv.migrationsTableName).
+		Scan(&exists)
+	if err == sql.ErrNoRows {
+		return false, nil
+	}
+
+	return exists, err
+}
+
 // CreateMigrationsTable creates the schema migrations table
 func (drv *Driver) CreateMigrationsTable(db *sql.DB) error {
 	_, err := db.Exec(


Test patch:
diff --git a/pkg/dbmate/db_test.go b/pkg/dbmate/db_test.go
index d2e42e5..dcfb0d6 100644
--- a/pkg/dbmate/db_test.go
+++ b/pkg/dbmate/db_test.go
@@ -393,6 +393,9 @@ func TestStatus(t *testing.T) {
 			require.Len(t, results, 2)
 			require.False(t, results[0].Applied)
 			require.False(t, results[1].Applied)
+			migrationsTableExists, err := drv.MigrationsTableExists(sqlDB)
+			require.NoError(t, err)
+			require.False(t, migrationsTableExists)
 
 			// run migrations
 			err = db.Migrate()