Exercise: Database Migrations
Difficulty - Intermediate
Learning Objectives
- Understand database schema versioning
- Implement migration up/down operations
- Practice SQL transaction management
- Build migration tracking system
- Handle migration rollbacks
Problem Statement
Create a migrator package that manages database schema migrations.
Implementation
1package migrator
2
3import (
4 "database/sql"
5 "fmt"
6 "sort"
7 "time"
8)
9
10type Migration struct {
11 Version int
12 Name string
13 Up string
14 Down string
15}
16
17type Migrator struct {
18 db *sql.DB
19 migrations []Migration
20}
21
22func New(db *sql.DB) *Migrator {
23 return &Migrator{
24 db: db,
25 migrations: make([]Migration, 0),
26 }
27}
28
29func AddMigration(migration Migration) {
30 m.migrations = append(m.migrations, migration)
31 sort.Slice(m.migrations, func(i, j int) bool {
32 return m.migrations[i].Version < m.migrations[j].Version
33 })
34}
35
36func createMigrationsTable() error {
37 query := `
38 CREATE TABLE IF NOT EXISTS schema_migrations (
39 version INTEGER PRIMARY KEY,
40 name TEXT NOT NULL,
41 applied_at TIMESTAMP NOT NULL
42 )
43 `
44 _, err := m.db.Exec(query)
45 return err
46}
47
48func getCurrentVersion() {
49 var version int
50 err := m.db.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&version)
51 return version, err
52}
53
54func Up() error {
55 if err := m.createMigrationsTable(); err != nil {
56 return fmt.Errorf("failed to create migrations table: %w", err)
57 }
58
59 currentVersion, err := m.getCurrentVersion()
60 if err != nil {
61 return fmt.Errorf("failed to get current version: %w", err)
62 }
63
64 for _, migration := range m.migrations {
65 if migration.Version <= currentVersion {
66 continue
67 }
68
69 tx, err := m.db.Begin()
70 if err != nil {
71 return fmt.Errorf("failed to begin transaction: %w", err)
72 }
73
74 if _, err := tx.Exec(migration.Up); err != nil {
75 tx.Rollback()
76 return fmt.Errorf("failed to execute migration %d: %w", migration.Version, err)
77 }
78
79 if _, err := tx.Exec(
80 "INSERT INTO schema_migrations VALUES",
81 migration.Version, migration.Name, time.Now(),
82 ); err != nil {
83 tx.Rollback()
84 return fmt.Errorf("failed to record migration: %w", err)
85 }
86
87 if err := tx.Commit(); err != nil {
88 return fmt.Errorf("failed to commit transaction: %w", err)
89 }
90
91 fmt.Printf("Applied migration %d: %s\n", migration.Version, migration.Name)
92 }
93
94 return nil
95}
96
97func Down() error {
98 currentVersion, err := m.getCurrentVersion()
99 if err != nil {
100 return fmt.Errorf("failed to get current version: %w", err)
101 }
102
103 if currentVersion == 0 {
104 return nil
105 }
106
107 var migration *Migration
108 for i := range m.migrations {
109 if m.migrations[i].Version == currentVersion {
110 migration = &m.migrations[i]
111 break
112 }
113 }
114
115 if migration == nil {
116 return fmt.Errorf("migration %d not found", currentVersion)
117 }
118
119 tx, err := m.db.Begin()
120 if err != nil {
121 return fmt.Errorf("failed to begin transaction: %w", err)
122 }
123
124 if _, err := tx.Exec(migration.Down); err != nil {
125 tx.Rollback()
126 return fmt.Errorf("failed to execute rollback: %w", err)
127 }
128
129 if _, err := tx.Exec("DELETE FROM schema_migrations WHERE version = ?", migration.Version); err != nil {
130 tx.Rollback()
131 return fmt.Errorf("failed to remove migration record: %w", err)
132 }
133
134 if err := tx.Commit(); err != nil {
135 return fmt.Errorf("failed to commit transaction: %w", err)
136 }
137
138 fmt.Printf("Rolled back migration %d: %s\n", migration.Version, migration.Name)
139 return nil
140}
Example Usage
1package main
2
3import (
4 "database/sql"
5 "log"
6 _ "github.com/mattn/go-sqlite3"
7 "migrator"
8)
9
10func main() {
11 db, err := sql.Open("sqlite3", "test.db")
12 if err != nil {
13 log.Fatal(err)
14 }
15 defer db.Close()
16
17 m := migrator.New(db)
18
19 m.AddMigration(migrator.Migration{
20 Version: 1,
21 Name: "create_users_table",
22 Up: `
23 CREATE TABLE users (
24 id INTEGER PRIMARY KEY AUTOINCREMENT,
25 name TEXT NOT NULL,
26 email TEXT UNIQUE NOT NULL
27 )
28 `,
29 Down: "DROP TABLE users",
30 })
31
32 m.AddMigration(migrator.Migration{
33 Version: 2,
34 Name: "add_users_created_at",
35 Up: "ALTER TABLE users ADD COLUMN created_at TIMESTAMP",
36 Down: "ALTER TABLE users DROP COLUMN created_at",
37 })
38
39 if err := m.Up(); err != nil {
40 log.Fatal(err)
41 }
42}
Solution
Click to see the complete solution with best practices
The implementation above demonstrates a production-ready database migration system. Here's the complete enhanced version with additional features:
1package migrator
2
3import (
4 "database/sql"
5 "fmt"
6 "sort"
7 "time"
8)
9
10type Migration struct {
11 Version int
12 Name string
13 Up string
14 Down string
15}
16
17type Migrator struct {
18 db *sql.DB
19 migrations []Migration
20}
21
22func New(db *sql.DB) *Migrator {
23 return &Migrator{
24 db: db,
25 migrations: make([]Migration, 0),
26 }
27}
28
29func AddMigration(migration Migration) {
30 m.migrations = append(m.migrations, migration)
31 // Sort migrations by version to ensure correct order
32 sort.Slice(m.migrations, func(i, j int) bool {
33 return m.migrations[i].Version < m.migrations[j].Version
34 })
35}
36
37func createMigrationsTable() error {
38 query := `
39 CREATE TABLE IF NOT EXISTS schema_migrations (
40 version INTEGER PRIMARY KEY,
41 name TEXT NOT NULL,
42 applied_at TIMESTAMP NOT NULL
43 )
44 `
45 _, err := m.db.Exec(query)
46 return err
47}
48
49func getCurrentVersion() {
50 var version int
51 err := m.db.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&version)
52 return version, err
53}
54
55func Up() error {
56 if err := m.createMigrationsTable(); err != nil {
57 return fmt.Errorf("failed to create migrations table: %w", err)
58 }
59
60 currentVersion, err := m.getCurrentVersion()
61 if err != nil {
62 return fmt.Errorf("failed to get current version: %w", err)
63 }
64
65 for _, migration := range m.migrations {
66 if migration.Version <= currentVersion {
67 continue
68 }
69
70 if err := m.applyMigration(migration); err != nil {
71 return err
72 }
73 }
74
75 return nil
76}
77
78func applyMigration(migration Migration) error {
79 tx, err := m.db.Begin()
80 if err != nil {
81 return fmt.Errorf("failed to begin transaction: %w", err)
82 }
83
84 // Execute migration SQL
85 if _, err := tx.Exec(migration.Up); err != nil {
86 tx.Rollback()
87 return fmt.Errorf("failed to execute migration %d: %w", migration.Version, err)
88 }
89
90 // Record migration
91 if _, err := tx.Exec(
92 "INSERT INTO schema_migrations VALUES",
93 migration.Version, migration.Name, time.Now(),
94 ); err != nil {
95 tx.Rollback()
96 return fmt.Errorf("failed to record migration: %w", err)
97 }
98
99 if err := tx.Commit(); err != nil {
100 return fmt.Errorf("failed to commit transaction: %w", err)
101 }
102
103 fmt.Printf("✓ Applied migration %d: %s\n", migration.Version, migration.Name)
104 return nil
105}
106
107func Down() error {
108 currentVersion, err := m.getCurrentVersion()
109 if err != nil {
110 return fmt.Errorf("failed to get current version: %w", err)
111 }
112
113 if currentVersion == 0 {
114 fmt.Println("No migrations to roll back")
115 return nil
116 }
117
118 var migration *Migration
119 for i := range m.migrations {
120 if m.migrations[i].Version == currentVersion {
121 migration = &m.migrations[i]
122 break
123 }
124 }
125
126 if migration == nil {
127 return fmt.Errorf("migration %d not found", currentVersion)
128 }
129
130 return m.rollbackMigration(*migration)
131}
132
133func rollbackMigration(migration Migration) error {
134 tx, err := m.db.Begin()
135 if err != nil {
136 return fmt.Errorf("failed to begin transaction: %w", err)
137 }
138
139 // Execute rollback SQL
140 if _, err := tx.Exec(migration.Down); err != nil {
141 tx.Rollback()
142 return fmt.Errorf("failed to execute rollback: %w", err)
143 }
144
145 // Remove migration record
146 if _, err := tx.Exec("DELETE FROM schema_migrations WHERE version = ?", migration.Version); err != nil {
147 tx.Rollback()
148 return fmt.Errorf("failed to remove migration record: %w", err)
149 }
150
151 if err := tx.Commit(); err != nil {
152 return fmt.Errorf("failed to commit transaction: %w", err)
153 }
154
155 fmt.Printf("✓ Rolled back migration %d: %s\n", migration.Version, migration.Name)
156 return nil
157}
158
159// Status shows current migration status
160func Status() error {
161 currentVersion, err := m.getCurrentVersion()
162 if err != nil {
163 return err
164 }
165
166 fmt.Printf("Current version: %d\n", currentVersion)
167 fmt.Println("\nMigrations:")
168 for _, mig := range m.migrations {
169 status := "pending"
170 if mig.Version <= currentVersion {
171 status = "applied"
172 }
173 fmt.Printf(" %d: %s [%s]\n", mig.Version, mig.Name, status)
174 }
175
176 return nil
177}
Best Practices
1. Use Transactions:
1tx, err := m.db.Begin()
2defer tx.Rollback() // Safe to call even after Commit()
3
4// All or nothing - if any step fails, entire migration rolls back
5tx.Exec(migration.Up)
6tx.Exec("INSERT INTO schema_migrations...")
7tx.Commit()
2. Version Numbering:
1// Use timestamps for version numbers to avoid conflicts
2// Format: YYYYMMDDHHMMSS
3// Example: 20240119120000_create_users_table
4
5migration := Migration{
6 Version: 20240119120000,
7 Name: "create_users_table",
8}
3. Idempotent Migrations:
1// Always use IF NOT EXISTS / IF EXISTS
2Up: `
3 CREATE TABLE IF NOT EXISTS users (
4 id INTEGER PRIMARY KEY,
5 name TEXT NOT NULL
6 )
7`,
8Down: `
9 DROP TABLE IF EXISTS users
10`,
4. Data Migrations:
1// Separate schema and data migrations
2Migration{
3 Version: 3,
4 Name: "migrate_user_data",
5 Up: `
6 UPDATE users
7 SET email = LOWER(email)
8 WHERE email IS NOT NULL
9 `,
10 Down: `
11 -- Data migrations are often irreversible
12 -- Consider taking backups
13 `,
14}
5. Add Safety Checks:
1func validateMigrations() error {
2 versions := make(map[int]bool)
3 for _, mig := range m.migrations {
4 if versions[mig.Version] {
5 return fmt.Errorf("duplicate version: %d", mig.Version)
6 }
7 versions[mig.Version] = true
8
9 if mig.Up == "" {
10 return fmt.Errorf("migration %d has empty Up SQL", mig.Version)
11 }
12 }
13 return nil
14}
Production Considerations
Backup Before Migration:
1func BackupBeforeMigrate() error {
2 timestamp := time.Now().Format("20060102_150405")
3 backupFile := fmt.Sprintf("backup_%s.sql", timestamp)
4
5 // Create backup
6 // For PostgreSQL: pg_dump
7 // For MySQL: mysqldump
8
9 fmt.Printf("Created backup: %s\n", backupFile)
10 return nil
11}
Dry Run Mode:
1func DryRun() error {
2 currentVersion, _ := m.getCurrentVersion()
3
4 fmt.Println("Would apply the following migrations:")
5 for _, mig := range m.migrations {
6 if mig.Version > currentVersion {
7 fmt.Printf(" %d: %s\n", mig.Version, mig.Name)
8 }
9 }
10 return nil
11}
Lock Migrations:
1func acquireLock() error {
2 // Use advisory locks or a dedicated lock table
3 _, err := m.db.Exec("INSERT INTO migration_locks VALUES", time.Now())
4 return err
5}
6
7func releaseLock() error {
8 _, err := m.db.Exec("DELETE FROM migration_locks WHERE id = 1")
9 return err
10}
Key Takeaways
- Version Control: Track schema versions in database
- Transactions: Use transactions for atomic migrations
- Rollback Support: Always provide Down migrations
- Ordering: Sort migrations by version number
- Idempotency: Migrations should be safely re-runnable
- Safety: Validate migrations before applying
- Backups: Create backups before running migrations
- Locking: Prevent concurrent migration runs
Related Topics
- Database Operations & Patterns - Database operations from basic to advanced