I am a beginner in Golang Migrate. I am trying to implement migration but have encountered the problem of naming my versions like 1.1.1. Currently, I can only name my version 1, 2, or 3 but not 1.1.1. So how do I name my versions in formats like 1.1.1, etc.?
Also, in Golang Migrate, we have 'schema_migrations' table which stores the latest migration version applied and not their history so I added 'schema_migrations_history' table. The rows get added in this history table when I am adding migration files one by one. But if I add 2 migration files together i.e. if I try to apply two or more versions of migrations together, then no row gets added to this history table. I think this is because 'm.Up' checks all outstanding migrations and applies them together. But I want to add the history of these migrations.
Any solution to both these problems will be much appreciated.
My code:- main.go ->
package main
import (
"fmt"
"log"
"my-migrate-project/db"
"github.com/golang-migrate/migrate/v4"
_ "github.com/golang-migrate/migrate/v4/source/file"
)
const (
dbURL = "postgres://postgres:password@localhost:5432/migrateDb?sslmode=disable"
dumpPath = "C:/Users/91790/Documents/Otino/my-migrate-project/backups/"
dbName = "migrateDb"
dbUsername = "postgres"
dbPassword = "password"
migrationsSource = "file://C:/Users/91790/Documents/Otino/my-migrate-project/db/migrations"
migrationsDir = "db/migrations"
customQueriesDir1 = "db/migrations/custom_queries_1.sql"
customQueriesDir2 = "db/migrations/custom_queries_2.sql"
)
func main() {
sqlDB, err := db.OpenDB(dbURL)
if err != nil {
log.Fatal("Error opening database connection:", err)
}
defer sqlDB.Close()
err = db.DumpDatabase(dbURL, dumpPath, dbUsername, dbPassword)
if err != nil {
fmt.Println("Error:", err)
}
m, err := migrate.New(migrationsSource, dbURL)
if err != nil {
log.Fatal("Error creating migrate instance:", err)
}
defer m.Close()
isDirty, err := db.IsDatabaseDirty(sqlDB, m)
if err != nil {
log.Fatal("Error checking if the database is dirty:", err)
return
}
if isDirty {
fmt.Println("Detected unexpected shutdown during migration. Rolling back...")
if err := db.Rollback(sqlDB, m); err != nil {
log.Fatal("Error during rollback:", err)
return
}
_, err := sqlDB.Exec("UPDATE schema_migrations SET dirty=false")
if err != nil {
log.Fatal("Error updating schema_migrations:", err)
return
}
return
}
if err := db.BeforeMigrationTxns(sqlDB, customQueriesDir1); err != nil {
fmt.Println("Error during transactions before migration:", err)
if rollbackErr := db.Rollback(sqlDB, m); rollbackErr != nil {
fmt.Println("Error during rollback:", rollbackErr)
}
return
}
if err := db.Migrate(sqlDB, migrationsDir); err != nil {
fmt.Println("Error during migration:", err)
if rollbackErr := db.Rollback(sqlDB, m); rollbackErr != nil {
fmt.Println("Error during rollback:", rollbackErr)
}
return
} else {
if err := db.AfterMigrationTxns(sqlDB, customQueriesDir2); err != nil {
fmt.Println("Error during transactions after migration:", err)
if rollbackErr := db.Rollback(sqlDB, m); rollbackErr != nil {
fmt.Println("Error during rollback:", rollbackErr)
}
return
}
}
fmt.Println("All operations completed successfully")
}
db.go ->
package db
import (
"bytes"
"database/sql"
"fmt"
"log"
"os"
"os/exec"
"path/filepath"
"time"
"github.com/golang-migrate/migrate/v4"
pgmigrate "github.com/golang-migrate/migrate/v4/database/postgres"
_ "github.com/golang-migrate/migrate/v4/source/file"
_ "github.com/lib/pq"
)
func OpenDB(dbURL string) (*sql.DB, error) {
db, err := sql.Open("postgres", dbURL)
if err != nil {
return nil, err
}
return db, nil
}
func Migrate(sqlDB *sql.DB, migrationsDir string) error {
driver, err := pgmigrate.WithInstance(sqlDB, &pgmigrate.Config{})
if err != nil {
return err
}
m, err := migrate.NewWithDatabaseInstance(
"file://"+migrationsDir,
"postgres", driver)
if err != nil {
return err
}
if err := m.Up(); err != nil && err != migrate.ErrNoChange {
return err
}
if err := UpdateMigrationHistory(sqlDB, m); err != nil {
fmt.Println("Error updating migration history:", err)
return err
}
fmt.Println("Migration successful")
return nil
}
func Rollback(sqlDB *sql.DB, m *migrate.Migrate) error {
var lastSuccessfulVersion uint
err := sqlDB.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations_history").Scan(&lastSuccessfulVersion)
log.Println("last successful version: ", lastSuccessfulVersion)
if err != nil && err != sql.ErrNoRows {
return fmt.Errorf("error getting last successful migration version: %w", err)
}
_, err = sqlDB.Exec("UPDATE schema_migrations SET version = $1, dirty = false", lastSuccessfulVersion)
if err != nil {
return fmt.Errorf("error updating schema_migrations table: %w", err)
}
log.Printf("Updated schema_migrations table: version = %d, dirty = false", lastSuccessfulVersion)
for {
version, _, err := m.Version()
if err != nil {
return fmt.Errorf("error getting current migration version: %w", err)
}
if version <= lastSuccessfulVersion {
break // Stop rolling back once we reach the last successful migration
}
if err := m.Steps(-1); err != nil {
if err == migrate.ErrNoChange {
break // No more migrations to roll back
}
return fmt.Errorf("error rolling back migrations: %w", err)
}
}
fmt.Println("Rollback successful")
return nil
}
func BeforeMigrationTxns(sqlDB *sql.DB, customQueriesDir1 string) error {
fmt.Println("Performing custom transactions before migration")
queries, err := readCustomQueries(customQueriesDir1)
if err != nil {
return err
}
if _, err := sqlDB.Exec(string(queries)); err != nil {
return err
}
return nil
}
func AfterMigrationTxns(sqlDB *sql.DB, customQueriesDir2 string) error {
fmt.Println("Performing custom transactions after migration")
queries, err := readCustomQueries(customQueriesDir2)
if err != nil {
return err
}
if _, err := sqlDB.Exec(string(queries)); err != nil {
return err
}
return nil
}
func readCustomQueries(queryFile string) ([]byte, error) {
queries, err := os.ReadFile(queryFile)
if err != nil {
return nil, err
}
return queries, nil
}
func RestoreDatabase(dbName string, backupPath string) error {
fmt.Println("Restoring database from backup")
cmd := exec.Command("pg_restore", "-d", dbName, "-U", "postgres", "-h", "localhost", backupPath)
cmd.Stderr = os.Stderr
// Run the command
err := cmd.Run()
if err != nil {
return fmt.Errorf("error restoring database from backup: %w", err)
}
fmt.Println("Database restored successfully")
return nil
}
func DumpDatabase(dbURL, dumpPath, dbUsername, dbPassword string) error {
dumpPath = dumpPath + "backup_" + time.Now().Format("2006-01-02T15_04_05") + ".dump"
fmt.Println("Taking database dump")
dumpDir := filepath.Dir(dumpPath)
if err := os.MkdirAll(dumpDir, os.ModePerm); err != nil {
return fmt.Errorf("error creating dump directory: %w", err)
}
if _, err := os.Stat(dumpPath); os.IsNotExist(err) {
file, err := os.Create(dumpPath)
if err != nil {
return fmt.Errorf("error creating dump file: %w", err)
}
defer file.Close()
}
cmd := exec.Command("pg_dump", "-U", dbUsername, "-p", "5432", "-F", "c", fmt.Sprintf("--file=%s", dumpPath), dbURL)
cmd.Env = []string{"PGPASSWORD=" + dbPassword}
var out bytes.Buffer
var stderr bytes.Buffer
cmd.Stdout = &out
cmd.Stderr = &stderr
err := cmd.Run()
if err != nil {
fmt.Printf("Error in DumpDatabase: %s\n", err)
fmt.Printf("stderr: %s\n", stderr.String())
return err
}
fmt.Println("Dump successful")
return nil
}
func IsDatabaseDirty(sqlDB *sql.DB, m *migrate.Migrate) (bool, error) {
version, dirty, err := m.Version()
if version == 0 {
return false, nil
} else if dirty {
return dirty, nil
} else if err != nil {
return false, err
}
return dirty, nil
}
func UpdateMigrationHistory(sqlDB *sql.DB, m *migrate.Migrate) error {
statements := `
BEGIN;
SET ROLE 'postgres';
CREATE TABLE IF NOT EXISTS schema_migrations_history (
id SERIAL PRIMARY KEY,
version BIGINT NOT NULL,
applied_at timestamptz NOT NULL DEFAULT NOW()
);
CREATE OR REPLACE FUNCTION track_applied_migration()
RETURNS TRIGGER AS $$
DECLARE _current_version integer;
BEGIN
SELECT COALESCE(MAX(version),0) FROM schema_migrations_history INTO _current_version;
IF NEW.dirty = 'f' AND NEW.version > _current_version THEN
INSERT INTO schema_migrations_history(version) VALUES (NEW.version);
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER track_applied_migrations
AFTER INSERT ON schema_migrations FOR EACH ROW EXECUTE PROCEDURE track_applied_migration();
COMMIT;`
version, _, err := m.Version()
if err != nil {
return err
}
if version == 1 {
statements += "\nINSERT INTO schema_migrations_history(version) VALUES (1);"
}
_, err = sqlDB.Exec(statements)
if err != nil {
log.Println("Error executing SQL statements:", err)
return err
}
return nil
}