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