// Package storage opens the database (SQLite or PostgreSQL) and applies schema // migrations. Repositories live here too. // // This is the only place that should import the ORM (bun) and the database // drivers. Services depend on small interfaces, never on this package directly. package storage import ( "context" "database/sql" "fmt" "io/fs" "strings" "time" _ "github.com/jackc/pgx/v5/stdlib" // PostgreSQL driver ("pgx") "github.com/uptrace/bun" "github.com/uptrace/bun/dialect/pgdialect" "github.com/uptrace/bun/dialect/sqlitedialect" _ "modernc.org/sqlite" // pure-Go SQLite driver ("sqlite") ) // Supported database drivers. const ( DriverSQLite = "sqlite" DriverPostgres = "postgres" ) // pathEscaper protects characters that are special in a file: URI. var pathEscaper = strings.NewReplacer("%", "%25", "?", "%3F", "#", "%23") // Open connects to the database. For "sqlite" target is a file path (created if // needed); for "postgres" it is a connection string (DSN or URL). func Open(driver, target string) (*bun.DB, error) { switch driver { case DriverSQLite: return openSQLite(target) case DriverPostgres: return openPostgres(target) default: return nil, fmt.Errorf("unknown database driver %q", driver) } } // openSQLite enables WAL, foreign keys and a busy timeout on every connection. // Transactions start with BEGIN IMMEDIATE: one that reads and then writes would // otherwise fail at once with "database is locked" when another writer commits in // between, and the busy timeout does not apply to that case. func openSQLite(path string) (*bun.DB, error) { dsn := "file:" + pathEscaper.Replace(path) + "?_pragma=foreign_keys(1)&_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)&_txlock=immediate" sqldb, err := sql.Open("sqlite", dsn) if err != nil { return nil, fmt.Errorf("open sqlite: %w", err) } if err := sqldb.Ping(); err != nil { _ = sqldb.Close() return nil, fmt.Errorf("ping sqlite: %w", err) } return bun.NewDB(sqldb, sqlitedialect.New()), nil } func openPostgres(dsn string) (*bun.DB, error) { sqldb, err := sql.Open("pgx", dsn) if err != nil { return nil, fmt.Errorf("open postgres: %w", err) } if err := sqldb.Ping(); err != nil { _ = sqldb.Close() return nil, fmt.Errorf("ping postgres: %w", err) } return bun.NewDB(sqldb, pgdialect.New()), nil } // Migrate applies, in lexical order, every *.sql file in fsys that has not // been applied yet. Each file runs in its own transaction. func Migrate(ctx context.Context, db *bun.DB, fsys fs.FS) error { _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations ( version TEXT PRIMARY KEY, applied_at BIGINT NOT NULL )`) if err != nil { return fmt.Errorf("create schema_migrations: %w", err) } applied, err := appliedVersions(ctx, db) if err != nil { return err } entries, err := fs.ReadDir(fsys, ".") if err != nil { return fmt.Errorf("read migrations: %w", err) } for _, e := range entries { name := e.Name() if e.IsDir() || !strings.HasSuffix(name, ".sql") || applied[name] { continue } if err := applyOne(ctx, db, fsys, name); err != nil { return fmt.Errorf("migration %s: %w", name, err) } } return nil } func appliedVersions(ctx context.Context, db *bun.DB) (map[string]bool, error) { rows, err := db.QueryContext(ctx, "SELECT version FROM schema_migrations") if err != nil { return nil, fmt.Errorf("list applied migrations: %w", err) } defer rows.Close() applied := map[string]bool{} for rows.Next() { var v string if err := rows.Scan(&v); err != nil { return nil, err } applied[v] = true } return applied, rows.Err() } func applyOne(ctx context.Context, db *bun.DB, fsys fs.FS, name string) error { body, err := fs.ReadFile(fsys, name) if err != nil { return err } tx, err := db.BeginTx(ctx, nil) if err != nil { return err } defer func() { _ = tx.Rollback() }() // no-op after a successful Commit // The file body goes straight to the driver: it may contain several // statements and must not go through placeholder formatting. if _, err := tx.Tx.ExecContext(ctx, string(body)); err != nil { return err } _, err = tx.ExecContext(ctx, "INSERT INTO schema_migrations (version, applied_at) VALUES (?, ?)", name, time.Now().Unix()) if err != nil { return err } return tx.Commit() }