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 migrations, err := GetMigrations()
82 if err != nil {
83 return fmt.Errorf("failed to load migrations: %w", err)
84 }
85
86 for _, migration := range migrations {
87 if err := applyMigration(db, migration); err != nil {
88 return fmt.Errorf("failed to apply migration %d (%s): %w", migration.Version, migration.Name, err)
89 }
90 }
91
92 return nil
93}
94
95// createMigrationsTable creates the table for tracking applied migrations
96func createMigrationsTable(db *sql.DB) error {
97 schema := `
98 CREATE TABLE IF NOT EXISTS schema_migrations (
99 version INTEGER PRIMARY KEY,
100 name TEXT NOT NULL,
101 applied_at DATETIME DEFAULT CURRENT_TIMESTAMP
102 );`
103
104 _, err := db.Exec(schema)
105 return err
106}
107
108// applyMigration applies a single migration if it hasn't been applied yet
109func applyMigration(db *sql.DB, migration Migration) error {
110 // Check if migration has already been applied
111 var count int
112 err := db.QueryRow("SELECT COUNT(*) FROM schema_migrations WHERE version = ?", migration.Version).Scan(&count)
113 if err != nil {
114 return fmt.Errorf("failed to check migration status: %w", err)
115 }
116
117 if count > 0 {
118 // Migration already applied, skip
119 return nil
120 }
121
122 log.Printf("Applying migration %03d: %s", migration.Version, migration.Name)
123
124 // Use transaction for safety
125 tx, err := db.Begin()
126 if err != nil {
127 return fmt.Errorf("failed to begin transaction: %w", err)
128 }
129 defer func() {
130 if err != nil {
131 tx.Rollback()
132 }
133 }()
134
135 // Execute migration SQL
136 if _, err := tx.Exec(migration.SQL); err != nil {
137 return fmt.Errorf("failed to execute migration SQL: %w", err)
138 }
139
140 // Record migration as applied
141 if _, err := tx.Exec(
142 "INSERT INTO schema_migrations (version, name) VALUES (?, ?)",
143 migration.Version, migration.Name,
144 ); err != nil {
145 return fmt.Errorf("failed to record migration: %w", err)
146 }
147
148 if err := tx.Commit(); err != nil {
149 return fmt.Errorf("failed to commit migration transaction: %w", err)
150 }
151
152 log.Printf("Successfully applied migration %03d: %s", migration.Version, migration.Name)
153 return nil
154}
155
156// GetAppliedMigrations returns the list of applied migration versions
157func GetAppliedMigrations(db *sql.DB) ([]int, error) {
158 // First check if migrations table exists
159 var count int
160 err := db.QueryRow("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='schema_migrations'").Scan(&count)
161 if err != nil {
162 return nil, fmt.Errorf("failed to check if migrations table exists: %w", err)
163 }
164
165 if count == 0 {
166 // Migrations table doesn't exist, no migrations applied
167 return []int{}, nil
168 }
169
170 rows, err := db.Query("SELECT version FROM schema_migrations ORDER BY version")
171 if err != nil {
172 return nil, fmt.Errorf("failed to query applied migrations: %w", err)
173 }
174 defer rows.Close()
175
176 var versions []int
177 for rows.Next() {
178 var version int
179 if err := rows.Scan(&version); err != nil {
180 return nil, fmt.Errorf("failed to scan migration version: %w", err)
181 }
182 versions = append(versions, version)
183 }
184
185 if err := rows.Err(); err != nil {
186 return nil, fmt.Errorf("error iterating over migration rows: %w", err)
187 }
188
189 return versions, nil
190}