fn_registry/functions/infra/sqlite_apply_migrations.go

package infra

import (
	"database/sql"
	"fmt"
	"io/fs"
	"sort"
	"strings"
)

// ApplyMigrations reads SQL files matching glob from fsys, sorts them
// lexicographically (NNN_name.sql order), and executes every statement
// found in each file against conn.
//
// If glob is empty, it defaults to "migrations/*.sql".
//
// Each statement is executed individually. Errors that look like
// idempotent SQLite errors (duplicate column, already exists) are
// silently ignored so that migrations can be replayed safely against
// a database that was partially migrated.
//
// NOTE: the internal statement parser splits on ";" at the end of a
// trimmed line. This is intentionally simple and will break if SQL
// strings contain a literal semicolon at end-of-line. Avoid using
// such patterns in migration files.
func ApplyMigrations(conn *sql.DB, fsys fs.FS, glob string) error {
	if glob == "" {
		glob = "migrations/*.sql"
	}
	files, err := fs.Glob(fsys, glob)
	if err != nil {
		return err
	}
	sort.Strings(files)
	for _, f := range files {
		b, err := fs.ReadFile(fsys, f)
		if err != nil {
			return err
		}
		for _, stmt := range splitSQLStatements(string(b)) {
			s := strings.TrimSpace(stmt)
			if s == "" {
				continue
			}
			if _, err := conn.Exec(s); err != nil {
				if isIdempotentMigrationError(err) {
					continue
				}
				return fmt.Errorf("%s: %w", f, err)
			}
		}
	}
	return nil
}

// splitSQLStatements splits a SQL script into individual statements.
// Lines starting with "--" (comments) and blank lines are skipped.
// A statement ends when a trimmed line ends with ";".
func splitSQLStatements(s string) []string {
	out := []string{}
	cur := strings.Builder{}
	for _, line := range strings.Split(s, "\n") {
		trim := strings.TrimSpace(line)
		if strings.HasPrefix(trim, "--") || trim == "" {
			continue
		}
		cur.WriteString(line)
		cur.WriteString("\n")
		if strings.HasSuffix(trim, ";") {
			out = append(out, cur.String())
			cur.Reset()
		}
	}
	if cur.Len() > 0 {
		out = append(out, cur.String())
	}
	return out
}

// isIdempotentMigrationError returns true for SQLite errors that arise
// from re-applying a migration that was already applied (duplicate
// column, table/index already exists).
func isIdempotentMigrationError(err error) bool {
	msg := err.Error()
	return strings.Contains(msg, "duplicate column") ||
		strings.Contains(msg, "already exists")
}