gomail/internal/db/db.go

// Package db wraps database/sql with the gomail schema. No ORM — raw SQL with
// prepared statements only, per the project's minimal-dependency principle.
package db

import (
	"database/sql"
	"fmt"
	"log/slog"
	"strings"

	_ "github.com/mattn/go-sqlite3"
)

// DB wraps *sql.DB with the driver name (some queries need driver-specific SQL,
// e.g. placeholder syntax differs between sqlite/postgres/mysql).
type DB struct {
	*sql.DB
	Driver string
}

// Open connects to the database using the configured driver.
// SQLite is always available; postgres and mysql require build tags:
//
//	go build -tags postgres .
//	go build -tags mysql .
func Open(driver, dsn string) (*DB, error) {
	d := strings.ToLower(driver)

	var sqlDriverName string
	switch d {
	case "sqlite", "":
		sqlDriverName = "sqlite3"
		d = "sqlite"
	default:
		name, ok := driverRegistry[d]
		if !ok {
			available := []string{"sqlite"}
			for k := range driverRegistry {
				available = append(available, k)
			}
			return nil, fmt.Errorf("driver %q not compiled in; rebuild with -tags %s. Available: %v", driver, driver, available)
		}
		sqlDriverName = name
	}

	sqlDB, err := sql.Open(sqlDriverName, dsn)
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}

	if d == "sqlite" {
		// SQLite doesn't handle concurrent writers well — serialize via single conn.
		sqlDB.SetMaxOpenConns(1)
		if _, err := sqlDB.Exec("PRAGMA journal_mode=WAL"); err != nil {
			return nil, fmt.Errorf("enabling WAL mode: %w", err)
		}
		if _, err := sqlDB.Exec("PRAGMA foreign_keys=ON"); err != nil {
			return nil, fmt.Errorf("enabling foreign keys: %w", err)
		}
		if _, err := sqlDB.Exec("PRAGMA busy_timeout=5000"); err != nil {
			return nil, fmt.Errorf("setting busy timeout: %w", err)
		}
	}

	if err := sqlDB.Ping(); err != nil {
		return nil, fmt.Errorf("ping database: %w", err)
	}

	return &DB{DB: sqlDB, Driver: d}, nil
}

// driverRegistry is populated by build-tag-gated driver_*.go files
// (driver_postgres.go, driver_mysql.go) via init().
var driverRegistry = map[string]string{}

func registerDriver(name, sqlDriverName string) {
	driverRegistry[strings.ToLower(name)] = sqlDriverName
}

// Migrate runs all pending schema migrations in order. Migrations are
// idempotent (CREATE TABLE IF NOT EXISTS) so this is always safe to call at
// startup.
func (db *DB) Migrate() error {
	slog.Info("running database migrations")

	if _, err := db.Exec(migrationsTableSQL[db.Driver]); err != nil {
		return fmt.Errorf("creating migrations table: %w", err)
	}

	for _, m := range migrations {
		applied, err := db.migrationApplied(m.name)
		if err != nil {
			return fmt.Errorf("checking migration %s: %w", m.name, err)
		}
		if applied {
			continue
		}

		stmt := m.sql[db.Driver]
		if stmt == "" {
			stmt = m.sql["sqlite"] // fall back — most DDL is portable enough via driver quirks handled per-migration
		}

		slog.Info("applying migration", "name", m.name)
		tx, err := db.Begin()
		if err != nil {
			return fmt.Errorf("begin tx for %s: %w", m.name, err)
		}
		if _, err := tx.Exec(stmt); err != nil {
			tx.Rollback()
			return fmt.Errorf("applying migration %s: %w", m.name, err)
		}
		if _, err := tx.Exec(db.insertMigrationSQL(), m.name); err != nil {
			tx.Rollback()
			return fmt.Errorf("recording migration %s: %w", m.name, err)
		}
		if err := tx.Commit(); err != nil {
			return fmt.Errorf("commit migration %s: %w", m.name, err)
		}
	}

	slog.Info("migrations complete", "count", len(migrations))
	return nil
}

func (db *DB) migrationApplied(name string) (bool, error) {
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM schema_migrations WHERE name = "+db.placeholder(1), name).Scan(&count)
	return count > 0, err
}

func (db *DB) insertMigrationSQL() string {
	return "INSERT INTO schema_migrations (name, applied_at) VALUES (" + db.placeholder(1) + ", CURRENT_TIMESTAMP)"
}

// placeholder returns the driver-appropriate positional parameter syntax.
// sqlite/mysql use "?", postgres uses "$1", "$2", ...
func (db *DB) placeholder(n int) string {
	if db.Driver == "postgres" {
		return fmt.Sprintf("$%d", n)
	}
	return "?"
}

var migrationsTableSQL = map[string]string{
	"sqlite": `CREATE TABLE IF NOT EXISTS schema_migrations (
		name TEXT PRIMARY KEY,
		applied_at DATETIME NOT NULL
	)`,
	"postgres": `CREATE TABLE IF NOT EXISTS schema_migrations (
		name TEXT PRIMARY KEY,
		applied_at TIMESTAMPTZ NOT NULL
	)`,
	"mysql": `CREATE TABLE IF NOT EXISTS schema_migrations (
		name VARCHAR(255) PRIMARY KEY,
		applied_at DATETIME NOT NULL
	)`,
}