1package db
2
3import (
4 "database/sql"
5 "fmt"
6 "os"
7 "path/filepath"
8
9 "github.com/bnema/dumber/internal/logging"
10 "github.com/bnema/dumber/internal/migrations"
11 _ "github.com/ncruces/go-sqlite3/driver" // SQLite driver
12 _ "github.com/ncruces/go-sqlite3/embed" // Embed SQLite
13)
14
15// InitDB initializes the database connection and schema
16func InitDB(dbPath string) (*sql.DB, error) {
17 if dbPath == "" {
18 return nil, fmt.Errorf("database path cannot be empty")
19 }
20
21 // Ensure database directory exists
22 if err := os.MkdirAll(filepath.Dir(dbPath), 0750); err != nil {
23 return nil, fmt.Errorf("failed to create database directory: %w", err)
24 }
25
26 // Open database connection
27 database, err := sql.Open("sqlite3", dbPath)
28 if err != nil {
29 return nil, fmt.Errorf("failed to open database: %w", err)
30 }
31
32 // Test the connection
33 if err := database.Ping(); err != nil {
34 if err := database.Close(); err != nil {
35 logging.Warn(fmt.Sprintf("Warning: failed to close database: %v", err))
36 }
37 return nil, fmt.Errorf("failed to connect to database: %w", err)
38 }
39
40 // Configure SQLite for optimal performance
41 pragmas := []string{
42 "PRAGMA journal_mode = WAL", // Write-Ahead Logging: 70k reads/sec, 3.6k writes/sec, concurrent access
43 "PRAGMA synchronous = NORMAL", // Safe in WAL mode, only checkpoints wait for FSYNC
44 "PRAGMA cache_size = -64000", // 64MB cache (default is only ~2MB)
45 "PRAGMA temp_store = MEMORY", // Temporary tables in RAM
46 "PRAGMA mmap_size = 30000000000", // 30GB memory-mapped I/O (virtual memory, OS-managed)
47 "PRAGMA busy_timeout = 5000", // Wait 5 seconds on lock contention
48 }
49
50 for _, pragma := range pragmas {
51 if _, err := database.Exec(pragma); err != nil {
52 if err := database.Close(); err != nil {
53 logging.Warn(fmt.Sprintf("Warning: failed to close database: %v", err))
54 }
55 return nil, fmt.Errorf("failed to set pragma %q: %w", pragma, err)
56 }
57 }
58
59 logging.Info(fmt.Sprintf("SQLite configured with WAL mode and performance optimizations"))
60
61 // Run embedded migrations - single source of truth for schema initialization
62 // RunEmbeddedMigrations is idempotent and checks if migrations are already applied
63 if err := migrations.RunEmbeddedMigrations(database); err != nil {
64 if err := database.Close(); err != nil {
65 logging.Warn(fmt.Sprintf("Warning: failed to close database: %v", err))
66 }
67 return nil, fmt.Errorf("failed to run database migrations: %w", err)
68 }
69
70 return database, nil
71}