1package migrations
2
3import (
4 "database/sql"
5 "embed"
6 "fmt"
7 "log"
8 "sort"
9 "strconv"
10 "strings"
11)
12
13//go:embed *.sql
14var migrationFiles embed.FS
15
16// Migration represents a single database migration
17type Migration struct {
18 Version int
19 Name string
20 SQL string
21}
22
23// GetMigrations returns all embedded migrations sorted by version
24func GetMigrations() ([]Migration, error) {
25 entries, err := migrationFiles.ReadDir(".")
26 if err != nil {
27 return nil, fmt.Errorf("failed to read embedded migrations directory: %w", err)
28 }
29
30 var migrations []Migration
31 for _, entry := range entries {
32 if !strings.HasSuffix(entry.Name(), ".sql") {
33 continue
34 }
35
36 // Parse version from filename (e.g., "001_initial.sql" -> version 1)
37 parts := strings.SplitN(entry.Name(), "_", 2)
38 if len(parts) != 2 {
39 log.Printf("Warning: skipping migration file with invalid name format: %s", entry.Name())
40 continue
41 }
42
43 versionStr := parts[0]
44 version, err := strconv.Atoi(versionStr)
45 if err != nil {
46 log.Printf("Warning: skipping migration file with invalid version: %s", entry.Name())
47 continue
48 }
49
50 // Extract name without extension
51 name := strings.TrimSuffix(parts[1], ".sql")
52
53 // Read SQL content
54 content, err := migrationFiles.ReadFile(entry.Name())
55 if err != nil {
56 return nil, fmt.Errorf("failed to read migration file %s: %w", entry.Name(), err)
57 }
58
59 migrations = append(migrations, Migration{
60 Version: version,
61 Name: name,
62 SQL: string(content),
63 })
64 }
65
66 // Sort by version
67 sort.Slice(migrations, func(i, j int) bool {
68 return migrations[i].Version < migrations[j].Version
69 })
70
71 return migrations, nil
72}
73
74// RunEmbeddedMigrations applies all pending embedded migrations to the database
75func RunEmbeddedMigrations(db *sql.DB) error {
76 // Create migrations tracking table if it doesn't exist
77 if err := createMigrationsTable(db); err != nil {
78 return fmt.Errorf("failed to create migrations table: %w", err)
79 }
80
81 // NOTE: Legacy databases without migration tracking should be deleted and recreated.
82 // All new databases will have migration tracking from the start.
83 // Backfill logic was removed to maintain idempotency - migrations are the single source of truth.
84
85 migrations, err := GetMigrations()
86 if err != nil {
87 return fmt.Errorf("failed to load migrations: %w", err)
88 }
89
90 for _, migration := range migrations {
91 if err := applyMigration(db, migration); err != nil {
92 return fmt.Errorf("failed to apply migration %d (%s): %w", migration.Version, migration.Name, err)
93 }
94 }
95
96 return nil
97}
98
99// createMigrationsTable creates the table for tracking applied migrations
100func createMigrationsTable(db *sql.DB) error {
101 schema := `
102 CREATE TABLE IF NOT EXISTS schema_migrations (
103 version INTEGER PRIMARY KEY,
104 name TEXT NOT NULL,
105 applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
106 );`
107
108 _, err := db.Exec(schema)
109 return err
110}
111
112// applyMigration applies a single migration if it hasn't been applied yet
113func applyMigration(db *sql.DB, migration Migration) error {
114 // Check if migration has already been applied
115 var count int
116 err := db.QueryRow("SELECT COUNT(*) FROM schema_migrations WHERE version = ?", migration.Version).Scan(&count)
117 if err != nil {
118 return fmt.Errorf("failed to check migration status: %w", err)
119 }
120
121 if count > 0 {
122 // Migration already applied, skip
123 return nil
124 }
125
126 log.Printf("Applying migration %03d: %s", migration.Version, migration.Name)
127
128 // Use transaction for safety
129 tx, err := db.Begin()
130 if err != nil {
131 return fmt.Errorf("failed to begin transaction: %w", err)
132 }
133 defer func() {
134 if err != nil {
135 if rollbackErr := tx.Rollback(); rollbackErr != nil {
136 log.Printf("failed to rollback migration transaction: %v", rollbackErr)
137 }
138 }
139 }()
140
141 // Execute migration SQL
142 if _, err := tx.Exec(migration.SQL); err != nil {
143 return fmt.Errorf("failed to execute migration SQL: %w", err)
144 }
145
146 // Record migration as applied
147 if _, err := tx.Exec(
148 "INSERT INTO schema_migrations (version, name) VALUES (?, ?)",
149 migration.Version, migration.Name,
150 ); err != nil {
151 return fmt.Errorf("failed to record migration: %w", err)
152 }
153
154 if err := tx.Commit(); err != nil {
155 return fmt.Errorf("failed to commit migration transaction: %w", err)
156 }
157
158 log.Printf("Successfully applied migration %03d: %s", migration.Version, migration.Name)
159 return nil
160}
161
162// GetAppliedMigrations returns the list of applied migration versions
163func GetAppliedMigrations(db *sql.DB) ([]int, error) {
164 // First check if migrations table exists
165 var count int
166 err := db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='schema_migrations'").Scan(&count)
167 if err != nil {
168 return nil, fmt.Errorf("failed to check if migrations table exists: %w", err)
169 }
170
171 if count == 0 {
172 // Migrations table doesn't exist, no migrations applied
173 return []int{}, nil
174 }
175
176 rows, err := db.Query("SELECT version FROM schema_migrations ORDER BY version")
177 if err != nil {
178 return nil, fmt.Errorf("failed to query applied migrations: %w", err)
179 }
180 defer func() {
181 if err := rows.Close(); err != nil {
182 log.Printf("failed to close migration query rows: %v", err)
183 }
184 }()
185
186 var versions []int
187 for rows.Next() {
188 var version int
189 if err := rows.Scan(&version); err != nil {
190 return nil, fmt.Errorf("failed to scan migration version: %w", err)
191 }
192 versions = append(versions, version)
193 }
194
195 if err := rows.Err(); err != nil {
196 return nil, fmt.Errorf("error iterating over migration rows: %w", err)
197 }
198
199 return versions, nil
200}
201
202// VerifyAllMigrationsApplied scans all embedded migrations and verifies they have been applied
203// Returns an error if any migrations are missing or if verification fails
204func VerifyAllMigrationsApplied(db *sql.DB) error {
205 // Get all embedded migrations
206 allMigrations, err := GetMigrations()
207 if err != nil {
208 return fmt.Errorf("failed to get embedded migrations: %w", err)
209 }
210
211 // Get applied migrations from database
212 appliedVersions, err := GetAppliedMigrations(db)
213 if err != nil {
214 return fmt.Errorf("failed to get applied migrations: %w", err)
215 }
216
217 // Create map for quick lookup
218 appliedMap := make(map[int]bool)
219 for _, version := range appliedVersions {
220 appliedMap[version] = true
221 }
222
223 // Check each migration is applied
224 var missingMigrations []Migration
225 for _, migration := range allMigrations {
226 if !appliedMap[migration.Version] {
227 missingMigrations = append(missingMigrations, migration)
228 }
229 }
230
231 // Report results
232 if len(missingMigrations) > 0 {
233 log.Printf("WARNING: %d migrations are not applied:", len(missingMigrations))
234 for _, migration := range missingMigrations {
235 log.Printf(" - Migration %03d: %s", migration.Version, migration.Name)
236 }
237 return fmt.Errorf("%d migrations are not applied", len(missingMigrations))
238 }
239
240 log.Printf("Migration verification: All %d migrations are applied ✓", len(allMigrations))
241 return nil
242}