Skip to content

Modern Docker Management, Designed for Everyone

18.2 KB
Raw Blame
package database

import (
	"cmp"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io/fs"
	"log/slog"
	"maps"
	"net/url"
	"os"
	"path/filepath"
	"slices"
	"strconv"
	"strings"
	"time"

	"github.com/libtnb/sqlite"
	"github.com/pressly/goose/v3"
	"github.com/pressly/goose/v3/lock"
	kit "go.getarcane.app/kit/pkg"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"gorm.io/plugin/opentelemetry/metrics"

	sqliteutil "github.com/getarcaneapp/arcane/backend/v2/pkg/utils/sqlite"
	"github.com/getarcaneapp/arcane/backend/v2/resources"
)

type DB struct {
	*gorm.DB
}

type MigrationOptions struct {
	AllowDowngrade bool
}

const (
	dbProviderSQLite             = "sqlite"
	dbProviderPostgres           = "postgres"
	gooseVersionTable            = "goose_db_version"
	legacyVersionTable           = "schema_migrations"
	latestMigrationVersion int64 = -1

	// Bound prepared statements so variable SQL does not retain an unbounded cache.
	preparedStmtMaxSize = 256
	preparedStmtTTL     = 15 * time.Minute
)

var customGormLogger logger.Interface

func SetGormLogger(l logger.Interface) {
	customGormLogger = l
}

func Initialize(ctx context.Context, databaseURL string, options MigrationOptions) (database *DB, err error) {
	var dialector gorm.Dialector
	var dbProvider string
	switch {
	case strings.HasPrefix(databaseURL, "file:"):
		dbProvider = dbProviderSQLite
		if err = sqliteutil.RegisterFunctions(); err != nil {
			err = fmt.Errorf("failed to register SQLite functions: %w", err)
			break
		}
		var connString string
		connString, err = ParseSQLiteConnectionString(databaseURL)
		if err != nil {
			err = fmt.Errorf("failed to parse SQLite connection string: %w", err)
			break
		}
		var path string
		path, err = kit.SQLitePathFromDSN(connString)
		if err != nil {
			err = fmt.Errorf("failed to prepare SQLite directory: failed to parse SQLite DSN: %w", err)
			break
		}
		dir := filepath.Dir(path)
		if path != "" && !strings.HasPrefix(path, ":memory:") && dir != "" && dir != "." {
			if err = os.MkdirAll(dir, 0o755); err != nil {
				err = fmt.Errorf("failed to prepare SQLite directory: %w", err)
				break
			}
		}
		dialector = sqlite.Open(connString)
	case strings.HasPrefix(databaseURL, "postgres"):
		dbProvider = dbProviderPostgres
		dialector = postgres.Open(databaseURL)
	default:
		err = fmt.Errorf("unsupported database type in URL: %s", databaseURL)
	}
	if err != nil {
		return nil, fmt.Errorf("failed to connect to database: %w", err)
	}

	var gormDB *gorm.DB
	connectStart := time.Now()
	for attempt := 1; ; attempt++ {
		if err = ctx.Err(); err != nil {
			return nil, fmt.Errorf("failed to connect to database: %w", err)
		}
		gormDB, err = gorm.Open(dialector, &gorm.Config{
			Logger: customGormLogger,
			NowFunc: func() time.Time {
				return time.Now().UTC()
			},
			PrepareStmt:                      true,
			PrepareStmtMaxSize:               preparedStmtMaxSize,
			PrepareStmtTTL:                   preparedStmtTTL,
			IgnoreRelationshipsWhenMigrating: true,
		})
		if err == nil {
			break
		}
		wait := min(time.Second<<min(attempt-1, 4), 15*time.Second)
		if time.Since(connectStart)+wait > time.Minute {
			err = fmt.Errorf("gave up after %d attempts: %w", attempt, err)
			break
		}
		slog.InfoContext(ctx, "Failed to initialize database", "attempt", attempt, "retryIn", wait, "error", err)
		select {
		case <-time.After(wait):
		case <-ctx.Done():
			return nil, fmt.Errorf("failed to connect to database: %w", ctx.Err())
		}
	}
	if err != nil {
		return nil, fmt.Errorf("failed to connect to database: %w", err)
	}
	db := &DB{DB: gormDB}
	// Close the pool if initialization fails after opening it.
	defer func() {
		if err == nil {
			return
		}
		if closeErr := db.Close(); closeErr != nil {
			slog.WarnContext(ctx, "Failed to close database after initialization failure", "error", closeErr)
		}
	}()
	if cancellationErr := ctx.Err(); cancellationErr != nil {
		return nil, cancellationErr
	}
	sqlDB, err := db.SQLDB()
	if err != nil {
		return nil, fmt.Errorf("failed to get sql.DB: %w", err)
	}
	// Statement spans and pool metrics are no-ops until telemetry is enabled.
	cb := gormDB.Callback()
	err = errors.Join(
		cb.Create().Before("gorm:create").Register("arcane:otel_before_create", startStatementSpan),
		cb.Create().After("gorm:create").Register("arcane:otel_after_create", endStatementSpan),
		cb.Query().Before("gorm:query").Register("arcane:otel_before_query", startStatementSpan),
		cb.Query().After("gorm:query").Register("arcane:otel_after_query", endStatementSpan),
		cb.Update().Before("gorm:update").Register("arcane:otel_before_update", startStatementSpan),
		cb.Update().After("gorm:update").Register("arcane:otel_after_update", endStatementSpan),
		cb.Delete().Before("gorm:delete").Register("arcane:otel_before_delete", startStatementSpan),
		cb.Delete().After("gorm:delete").Register("arcane:otel_after_delete", endStatementSpan),
		cb.Row().Before("gorm:row").Register("arcane:otel_before_row", startStatementSpan),
		cb.Row().After("gorm:row").Register("arcane:otel_after_row", endStatementSpan),
		cb.Raw().Before("gorm:raw").Register("arcane:otel_before_raw", startStatementSpan),
		cb.Raw().After("gorm:raw").Register("arcane:otel_after_raw", endStatementSpan),
	)
	if err != nil {
		return nil, fmt.Errorf("failed to register database instrumentation: %w", err)
	}
	if dbProvider == dbProviderPostgres {
		sqlDB.SetMaxIdleConns(15)
		sqlDB.SetMaxOpenConns(50)
		sqlDB.SetConnMaxLifetime(5 * time.Minute)
		sqlDB.SetConnMaxIdleTime(3 * time.Minute)
	} else {
		sqlDB.SetMaxIdleConns(10)
		sqlDB.SetMaxOpenConns(10)
		sqlDB.SetConnMaxLifetime(0)
		sqlDB.SetConnMaxIdleTime(0)
	}
	metrics.ReportDBStatsMetrics(sqlDB)
	if migrationErr := migrateDatabase(ctx, sqlDB, dbProvider, options, latestMigrationVersion); migrationErr != nil {
		slog.ErrorContext(ctx, "Failed to run migrations", "error", migrationErr)
		return nil, fmt.Errorf("failed to run migrations: %w", migrationErr)
	}
	return db, nil
}

// latestMigrationVersion selects the latest embedded migration.
func migrateDatabase(ctx context.Context, db *sql.DB, dbProvider string, options MigrationOptions, requiredVersion int64) error {
	if requiredVersion == latestMigrationVersion {
		versions, err := getEmbeddedMigrationVersions(dbProvider)
		if err != nil {
			return fmt.Errorf("failed to determine target migration version for %s: %w", dbProvider, err)
		}
		if len(versions) == 0 {
			return fmt.Errorf("failed to determine target migration version for %s: no embedded migrations found for %s", dbProvider, dbProvider)
		}
		requiredVersion = versions[len(versions)-1]
	}

	if err := adoptLegacyMigrationState(ctx, db, dbProvider, options); err != nil {
		return err
	}

	migrationsFS, err := fs.Sub(resources.FS, "migrations/"+dbProvider)
	if err != nil {
		return fmt.Errorf("failed to create goose provider for %s: failed to load embedded migrations for %s: %w", dbProvider, dbProvider, err)
	}
	var dialect goose.Dialect
	switch dbProvider {
	case dbProviderSQLite:
		dialect = goose.DialectSQLite3
	case dbProviderPostgres:
		dialect = goose.DialectPostgres
	default:
		return fmt.Errorf("failed to create goose provider for %s: unsupported database provider: %s", dbProvider, dbProvider)
	}
	// Serialize Postgres migrations across processes. SQLite already allows one writer.
	providerOptions := []goose.ProviderOption{}
	if dialect == goose.DialectPostgres {
		sessionLocker, lockerErr := lock.NewPostgresSessionLocker()
		if lockerErr != nil {
			return fmt.Errorf("failed to create goose provider for %s: failed to create Postgres migration session locker: %w", dbProvider, lockerErr)
		}
		providerOptions = append(providerOptions, goose.WithSessionLocker(sessionLocker))
	}
	provider, err := goose.NewProvider(dialect, db, migrationsFS, providerOptions...)
	if err != nil {
		return fmt.Errorf("failed to create goose provider for %s: %w", dbProvider, err)
	}

	currentVersion, err := provider.GetDBVersion(ctx)
	if err != nil {
		return fmt.Errorf("failed to determine current migration version for %s: %w", dbProvider, err)
	}

	slog.InfoContext(ctx, "Resolved database migration state", "provider", dbProvider, "currentVersion", currentVersion, "requiredVersion", requiredVersion)

	if currentVersion > requiredVersion {
		if !options.AllowDowngrade {
			return fmt.Errorf(
				"database schema version %d is newer than this Arcane binary supports (target %d for %s); downgrade "+
					"requires ALLOW_DOWNGRADE=true and a database backup before startup",
				currentVersion,
				requiredVersion,
				dbProvider,
			)
		}

		embeddedVersions, versionsErr := getEmbeddedMigrationVersions(dbProvider)
		if versionsErr != nil {
			return versionsErr
		}
		applied := kit.Ternary(dbProvider == dbProviderPostgres, "true", "1")
		placeholder := kit.Ternary(dbProvider == dbProviderPostgres, "$1", "?")
		query := "SELECT DISTINCT version_id FROM " + gooseVersionTable +
			" WHERE is_applied = " + applied + " AND version_id > " + placeholder + " ORDER BY version_id"
		rows, queryErr := db.QueryContext(ctx, query, requiredVersion)
		if queryErr != nil {
			return fmt.Errorf("failed to read applied migration versions for %s: %w", dbProvider, queryErr)
		}
		defer func() {
			_ = rows.Close()
		}()
		missingVersions := []int64{}
		for rows.Next() {
			var version int64
			if scanErr := rows.Scan(&version); scanErr != nil {
				return fmt.Errorf("failed to scan applied migration version for %s: %w", dbProvider, scanErr)
			}
			if !slices.Contains(embeddedVersions, version) {
				missingVersions = append(missingVersions, version)
			}
		}
		if iterateErr := rows.Err(); iterateErr != nil {
			return fmt.Errorf("failed to iterate applied migration versions for %s: %w", dbProvider, iterateErr)
		}
		if len(missingVersions) > 0 {
			return fmt.Errorf("cannot downgrade database from version %d to %d for %s: embedded Goose migrations are missing for "+
				"applied version(s) %v, so the rollback SQL is unavailable in this Arcane binary; "+
				"ALLOW_DOWNGRADE=true is not sufficient, restore the database from a backup taken before the newer "+
				"schema was applied",
				currentVersion,
				requiredVersion,
				dbProvider,
				missingVersions,
			)
		}

		if _, downToErr := provider.DownTo(ctx, requiredVersion); downToErr != nil {
			return fmt.Errorf("failed to downgrade database from version %d to %d for %s using embedded Goose migrations: %w", currentVersion, requiredVersion, dbProvider, downToErr)
		}

		slog.InfoContext(ctx, "Database downgrade completed successfully", "provider", dbProvider, "fromVersion", currentVersion, "toVersion", requiredVersion)
		return nil
	}

	if currentVersion == requiredVersion {
		slog.InfoContext(ctx, "Database schema is up to date", "provider", dbProvider, "migrationVersion", currentVersion)
		return nil
	}

	if _, upToErr := provider.UpTo(ctx, requiredVersion); upToErr != nil {
		return fmt.Errorf("failed to apply embedded Goose migrations for %s: %w", dbProvider, upToErr)
	}

	slog.InfoContext(ctx, "Database migrations completed successfully", "provider", dbProvider, "targetVersion", requiredVersion)
	return nil
}

// Remove in v3
func adoptLegacyMigrationState(ctx context.Context, db *sql.DB, dbProvider string, options MigrationOptions) error {
	if dbProvider != dbProviderSQLite && dbProvider != dbProviderPostgres {
		return fmt.Errorf("unsupported database provider: %s", dbProvider)
	}
	tableQuery := kit.Ternary(dbProvider == dbProviderPostgres,
		"SELECT to_regclass($1) IS NOT NULL",
		"SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?)",
	)
	var legacyVersion int64
	var dirty bool
	for _, table := range []string{legacyVersionTable, gooseVersionTable} {
		var exists bool
		if err := db.QueryRowContext(ctx, tableQuery, table).Scan(&exists); err != nil {
			label := kit.Ternary(table == legacyVersionTable, "legacy migration table", "Goose version table")
			return fmt.Errorf("failed to check %s for %s: %w", label, dbProvider, err)
		}
		if table == gooseVersionTable {
			if !exists {
				break
			}
			var version int64
			applied := kit.Ternary(dbProvider == dbProviderPostgres, "true", "1")
			query := fmt.Sprintf("SELECT COALESCE(MAX(version_id), 0) FROM %s WHERE is_applied = %s", table, applied)
			if err := db.QueryRowContext(ctx, query).Scan(&version); err != nil {
				return fmt.Errorf("failed to read Goose migration state for %s: %w", dbProvider, err)
			}
			if version > 0 {
				return nil
			}
			continue
		}
		if !exists {
			return nil
		}
		query := "SELECT version, dirty FROM " + table + " ORDER BY version DESC LIMIT 1"
		err := db.QueryRowContext(ctx, query).Scan(&legacyVersion, &dirty)
		if errors.Is(err, sql.ErrNoRows) {
			return nil
		}
		if err != nil {
			return fmt.Errorf("failed to read legacy migration state for %s: %w", dbProvider, err)
		}
		if dirty {
			if !options.AllowDowngrade {
				return fmt.Errorf(
					"database schema version %d is dirty in legacy %s table; resolve it manually or set "+
						"ALLOW_DOWNGRADE=true after verifying the database state",
					legacyVersion,
					legacyVersionTable,
				)
			}
			placeholder := kit.Ternary(dbProvider == dbProviderPostgres, "$1", "?")
			query = "UPDATE " + table + " SET dirty = false WHERE version = " + placeholder
			if _, err = db.ExecContext(ctx, query, legacyVersion); err != nil {
				return fmt.Errorf("failed to clear legacy dirty migration state for %s version %d: %w", dbProvider, legacyVersion, err)
			}
			slog.WarnContext(ctx, "Cleared dirty legacy migration state because ALLOW_DOWNGRADE=true", "provider", dbProvider, "version", legacyVersion)
		}
	}

	versions, err := getEmbeddedMigrationVersions(dbProvider)
	if err != nil {
		return err
	}
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("failed to start legacy migration adoption transaction for %s: %w", dbProvider, err)
	}
	defer func() {
		_ = tx.Rollback()
	}()
	versionDDL := fmt.Sprintf(kit.Ternary(dbProvider == dbProviderPostgres,
		`CREATE TABLE IF NOT EXISTS %s (
	id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	version_id bigint NOT NULL,
	is_applied boolean NOT NULL,
	tstamp timestamp NOT NULL DEFAULT now()
)`,
		`CREATE TABLE IF NOT EXISTS %s (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	version_id INTEGER NOT NULL,
	is_applied INTEGER NOT NULL,
	tstamp TIMESTAMP DEFAULT (datetime('now'))
)`,
	), gooseVersionTable)
	if _, createErr := tx.ExecContext(ctx, versionDDL); createErr != nil {
		return fmt.Errorf("failed to create Goose version table for %s: %w", dbProvider, createErr)
	}
	if _, err = tx.ExecContext(ctx, "DELETE FROM "+gooseVersionTable); err != nil {
		return fmt.Errorf("failed to clear Goose version table for %s: %w", dbProvider, err)
	}

	if end := slices.IndexFunc(versions, func(version int64) bool { return version > legacyVersion }); end >= 0 {
		versions = versions[:end]
	}
	appliedVersions := append([]int64{0}, versions...)
	if !slices.Contains(appliedVersions, legacyVersion) {
		appliedVersions = append(appliedVersions, legacyVersion)
	}
	first := kit.Ternary(dbProvider == dbProviderPostgres, "$1", "?")
	second := kit.Ternary(dbProvider == dbProviderPostgres, "$2", "?")
	insertQuery := fmt.Sprintf("INSERT INTO %s (version_id, is_applied) VALUES (%s, %s)", gooseVersionTable, first, second)
	for _, version := range appliedVersions {
		if _, insertErr := tx.ExecContext(ctx, insertQuery, version, true); insertErr != nil {
			return fmt.Errorf("failed to insert Goose migration version %d for %s: %w", version, dbProvider, insertErr)
		}
	}
	if err = tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit legacy migration adoption for %s: %w", dbProvider, err)
	}
	slog.InfoContext(ctx, "Adopted legacy migration state into Goose", "provider", dbProvider, "legacyVersion", legacyVersion)
	return nil
}

func getEmbeddedMigrationVersions(dbProvider string) ([]int64, error) {
	entries, err := resources.FS.ReadDir("migrations/" + dbProvider)
	if err != nil {
		return nil, fmt.Errorf("failed to read embedded migrations for %s: %w", dbProvider, err)
	}

	versionsMap := make(map[int64]struct{})
	for _, entry := range entries {
		if entry.IsDir() || !strings.HasSuffix(entry.Name(), ".sql") {
			continue
		}

		versionText, _, found := strings.Cut(entry.Name(), "_")
		if !found {
			continue
		}
		version, parseErr := strconv.ParseInt(versionText, 10, 64)
		if parseErr != nil {
			continue
		}

		versionsMap[version] = struct{}{}
	}

	return slices.Sorted(maps.Keys(versionsMap)), nil
}

func ParseSQLiteConnectionString(connString string) (string, error) {
	if !strings.HasPrefix(connString, "file:") {
		connString = "file:" + connString
	}

	connStringUrl, err := url.Parse(connString)
	if err != nil {
		return "", fmt.Errorf("failed to parse SQLite connection string: %w", err)
	}

	qs := sqliteutil.NormalizeQuery(connStringUrl.Query())
	// Foreign-key enforcement is required on every pooled connection.
	qs.Add("_pragma", "foreign_keys(1)")
	if !sqliteutil.HasPragma(qs, "busy_timeout") {
		qs.Add("_pragma", "busy_timeout(10000)")
	}
	if !slices.Contains([]string{"ro", "memory"}, qs.Get("mode")) && !strings.HasPrefix(cmp.Or(connStringUrl.Opaque, connStringUrl.Path), ":memory:") {
		if !sqliteutil.HasPragma(qs, "journal_mode") {
			qs.Add("_pragma", "journal_mode(WAL)")
		}
		if !sqliteutil.HasPragma(qs, "synchronous") {
			qs.Add("_pragma", "synchronous(NORMAL)")
		}
		if qs.Get("_txlock") == "" {
			qs.Set("_txlock", "immediate")
		}
	}
	return (&url.URL{
		Scheme:   connStringUrl.Scheme,
		Opaque:   connStringUrl.Opaque,
		User:     connStringUrl.User,
		Host:     connStringUrl.Host,
		Path:     connStringUrl.Path,
		RawPath:  connStringUrl.RawPath,
		RawQuery: qs.Encode(),
		Fragment: connStringUrl.Fragment,
	}).String(), nil
}

// FindEnvironmentIDByApiKey finds the environment ID that is associated with the given API key.
// It queries the api_keys table to validate the key and find the associated environment.
func (db *DB) FindEnvironmentIDByApiKey(ctx context.Context, apiKey string) (string, error) {
	var envID string
	err := db.WithContext(ctx).Table("environments").
		Select("environments.id").
		Joins("INNER JOIN api_keys ON api_keys.id = environments.api_key_id").
		Where("api_keys.key = ?", apiKey).
		Pluck("environments.id", &envID).Error
	if err != nil {
		return "", err
	}
	if envID == "" {
		return "", gorm.ErrRecordNotFound
	}
	return envID, nil
}

func (db *DB) Close() error {
	sqlDB, err := db.SQLDB()
	if err != nil {
		return err
	}
	return sqlDB.Close()
}

func (db *DB) SQLDB() (*sql.DB, error) {
	return db.DB.DB()
}