Repository: amacneil/dbmate (go)

Issue:
DATABASE_URL doesn't support whitespaces
I was testing the tool with a path containing a whitespace and this happened

```
Error: unable to open database file: no such file or directory
```

## Reproduce

`dbmate-x86_64-apple-darwin -u "sqlite:/Users/USERNAME/Library/Application Support/store.sqlite3" status`

Pull request:
Fix whitespace issues in SQLite absolute paths in DATABASE_URL
When the DSN is in the form `sqlite:/absolute/path` or `sqlite://absolute/path` or `sqlite:///absolute/path`, url.Parse
will consider the file path as:
 - `absolute` as the hostname
 - `path` (and the rest until `?`) as the URL path.
Instead, when the DSN is in the form "sqlite:", the (relative) file path is stored in the "Opaque" field.
See: https://pkg.go.dev/net/url#URL

While Opaque is not escaped, the URL Path is. So, in absolute paths the full file path is being escaped as if it was an URL path.
This commit works around this behavior in `ConnectionString()` (`sqlite.go`) so that the return string is always a correct (url-unescaped) file path.

Fixes #264 .

Patch:
diff --git a/README.md b/README.md
index 826f2ce..a66967e 100644
--- a/README.md
+++ b/README.md
@@ -64,6 +64,14 @@ $ sudo curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/re
 $ sudo chmod +x /usr/local/bin/dbmate
 ```
 
+**Windows**
+
+Download using [scoop](https://scoop.sh)
+
+```pwsh
+scoop install dbmate
+```
+
 **Docker**
 
 Docker images are published to both Docker Hub ([`amacneil/dbmate`](https://hub.docker.com/r/amacneil/dbmate)) and Github Container Registry ([`ghcr.io/amacneil/dbmate`](https://ghcr.io/amacneil/dbmate)).
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/dbutil/dbutil.go b/pkg/dbutil/dbutil.go
index e0bb13a..ac15ee0 100644
--- a/pkg/dbutil/dbutil.go
+++ b/pkg/dbutil/dbutil.go
@@ -151,3 +151,18 @@ func MustParseURL(s string) *url.URL {
 
 	return u
 }
+
+// MustUnescapePath unescapes a URL path, and panics if it fails.
+// It is used during in cases where we are parsing a generated path.
+func MustUnescapePath(s string) string {
+	if s == "" {
+		panic("missing path")
+	}
+
+	path, err := url.PathUnescape(s)
+	if err != nil {
+		panic(err)
+	}
+
+	return path
+}
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..0009710 100644
--- a/pkg/driver/sqlite/sqlite.go
+++ b/pkg/driver/sqlite/sqlite.go
@@ -47,6 +47,23 @@ func ConnectionString(u *url.URL) string {
 	newURL := *u
 	newURL.Scheme = ""
 
+	if newURL.Opaque == "" && newURL.Path != "" {
+		// When the DSN is in the form "scheme:/absolute/path" or
+		// "scheme://absolute/path" or "scheme:///absolute/path", url.Parse
+		// will consider the file path as :
+		// - "absolute" as the hostname
+		// - "path" (and the rest until "?") as the URL path.
+		// Instead, when the DSN is in the form "scheme:", the (relative) file
+		// path is stored in the "Opaque" field.
+		// See: https://pkg.go.dev/net/url#URL
+		//
+		// While Opaque is not escaped, the URL Path is. So, if .Path contains
+		// the file path, we need to un-escape it, and rebuild the full path.
+
+		newURL.Opaque = "//" + newURL.Host + dbutil.MustUnescapePath(newURL.Path)
+		newURL.Path = ""
+	}
+
 	// trim duplicate leading slashes
 	str := regexp.MustCompile("^//+").ReplaceAllString(newURL.String(), "/")
 
@@ -141,6 +158,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()
diff --git a/pkg/driver/sqlite/sqlite_test.go b/pkg/driver/sqlite/sqlite_test.go
index 8473e61..bb0c480 100644
--- a/pkg/driver/sqlite/sqlite_test.go
+++ b/pkg/driver/sqlite/sqlite_test.go
@@ -58,6 +58,16 @@ func TestConnectionString(t *testing.T) {
 		require.Equal(t, "foo/bar.sqlite3?mode=ro", ConnectionString(u))
 	})
 
+	t.Run("relative with dot", func(t *testing.T) {
+		u := dbutil.MustParseURL("sqlite:./foo/bar.sqlite3?mode=ro")
+		require.Equal(t, "./foo/bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
+	t.Run("relative with double dot", func(t *testing.T) {
+		u := dbutil.MustParseURL("sqlite:../foo/bar.sqlite3?mode=ro")
+		require.Equal(t, "../foo/bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
 	t.Run("absolute", func(t *testing.T) {
 		u := dbutil.MustParseURL("sqlite:/tmp/foo.sqlite3?mode=ro")
 		require.Equal(t, "/tmp/foo.sqlite3?mode=ro", ConnectionString(u))
@@ -75,6 +85,45 @@ func TestConnectionString(t *testing.T) {
 		u := dbutil.MustParseURL("sqlite:////tmp/foo.sqlite3?mode=ro")
 		require.Equal(t, "/tmp/foo.sqlite3?mode=ro", ConnectionString(u))
 	})
+
+	t.Run("relative with space", func(t *testing.T) {
+		u := dbutil.MustParseURL("sqlite:foo bar.sqlite3?mode=ro")
+		require.Equal(t, "foo bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
+	t.Run("relative with space and dot", func(t *testing.T) {
+		u := dbutil.MustParseURL("sqlite:./foo bar.sqlite3?mode=ro")
+		require.Equal(t, "./foo bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
+	t.Run("relative with space and double dot", func(t *testing.T) {
+		u := dbutil.MustParseURL("sqlite:../foo bar.sqlite3?mode=ro")
+		require.Equal(t, "../foo bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
+	t.Run("absolute with space", func(t *testing.T) {
+		u := dbutil.MustParseURL("sqlite:/foo bar.sqlite3?mode=ro")
+		require.Equal(t, "/foo bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
+	t.Run("three slashes with space in path", func(t *testing.T) {
+		// interpreted as absolute path
+		u := dbutil.MustParseURL("sqlite:///tmp/foo bar.sqlite3?mode=ro")
+		require.Equal(t, "/tmp/foo bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
+	t.Run("three slashes with space in path (1st dir)", func(t *testing.T) {
+		// interpreted as absolute path
+		u := dbutil.MustParseURL("sqlite:///tm p/foo bar.sqlite3?mode=ro")
+		require.Equal(t, "/tm p/foo bar.sqlite3?mode=ro", ConnectionString(u))
+	})
+
+	t.Run("four slashes with space", func(t *testing.T) {
+		// interpreted as absolute path
+		// supported for backwards compatibility
+		u := dbutil.MustParseURL("sqlite:////tmp/foo bar.sqlite3?mode=ro")
+		require.Equal(t, "/tmp/foo bar.sqlite3?mode=ro", ConnectionString(u))
+	})
 }
 
 func TestSQLiteCreateDropDatabase(t *testing.T) {
@@ -133,7 +182,7 @@ func TestSQLiteDumpSchema(t *testing.T) {
 	schema, err = drv.DumpSchema(db)
 	require.Nil(t, schema)
 	require.Error(t, err)
-	require.EqualError(t, err, "Error: unable to open database \".\": "+
+	require.EqualError(t, err, "Error: unable to open database \"/.\": "+
 		"unable to open database file")
 }