1package db
 2
 3import (
 4	"context"
 5	"database/sql"
 6	"fmt"
 7	"log/slog"
 8	"os"
 9	"path/filepath"
10
11	_ "github.com/ncruces/go-sqlite3/driver"
12	_ "github.com/ncruces/go-sqlite3/embed"
13
14	"github.com/pressly/goose/v3"
15)
16
17func Connect(ctx context.Context, dataDir string) (*sql.DB, error) {
18	if dataDir == "" {
19		return nil, fmt.Errorf("data.dir is not set")
20	}
21	if err := os.MkdirAll(dataDir, 0o700); err != nil {
22		return nil, fmt.Errorf("failed to create data directory: %w", err)
23	}
24	dbPath := filepath.Join(dataDir, "crush.db")
25	// Open the SQLite database
26	db, err := sql.Open("sqlite3", dbPath)
27	if err != nil {
28		return nil, fmt.Errorf("failed to open database: %w", err)
29	}
30
31	// Verify connection
32	if err = db.PingContext(ctx); err != nil {
33		db.Close()
34		return nil, fmt.Errorf("failed to connect to database: %w", err)
35	}
36
37	// Set pragmas for better performance
38	pragmas := []string{
39		"PRAGMA foreign_keys = ON;",
40		"PRAGMA journal_mode = WAL;",
41		"PRAGMA page_size = 4096;",
42		"PRAGMA cache_size = -8000;",
43		"PRAGMA synchronous = NORMAL;",
44	}
45
46	for _, pragma := range pragmas {
47		if _, err = db.ExecContext(ctx, pragma); err != nil {
48			slog.Error("Failed to set pragma", pragma, err)
49		} else {
50			slog.Debug("Set pragma", "pragma", pragma)
51		}
52	}
53
54	goose.SetBaseFS(FS)
55
56	if err := goose.SetDialect("sqlite3"); err != nil {
57		slog.Error("Failed to set dialect", "error", err)
58		return nil, fmt.Errorf("failed to set dialect: %w", err)
59	}
60
61	if err := goose.Up(db, "migrations"); err != nil {
62		slog.Error("Failed to apply migrations", "error", err)
63		return nil, fmt.Errorf("failed to apply migrations: %w", err)
64	}
65
66	// Configure connection pool for SQLite to prevent lock contention
67	// SQLite only supports one writer at a time, so limit connections
68	db.SetMaxOpenConns(1)
69	db.SetMaxIdleConns(1)
70	db.SetConnMaxLifetime(0) // No limit on connection lifetime
71
72	return db, nil
73}