1package sqlite
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "os"
8 "path/filepath"
9
10 "github.com/bnema/dumber/internal/logging"
11 _ "github.com/bnema/purego-sqlite/driver" // SQLite driver (purego, no WASM/CGo)
12)
13
14// NewConnection creates a new SQLite database connection with optimized settings.
15// It creates the database directory if it doesn't exist and applies performance pragmas.
16func NewConnection(ctx context.Context, dbPath string) (*sql.DB, error) {
17 const dbDirPerm = 0o750
18 log := logging.FromContext(ctx)
19
20 if dbPath == "" {
21 return nil, fmt.Errorf("database path cannot be empty")
22 }
23
24 // Ensure database directory exists
25 if err := os.MkdirAll(filepath.Dir(dbPath), dbDirPerm); err != nil {
26 return nil, fmt.Errorf("failed to create database directory: %w", err)
27 }
28
29 // Open database connection
30 db, err := sql.Open("sqlite3", dbPath)
31 if err != nil {
32 return nil, fmt.Errorf("failed to open database: %w", err)
33 }
34
35 // Configure connection pool (must be done before any queries)
36 configurePool(db)
37
38 // Test the connection
39 if err := db.Ping(); err != nil {
40 _ = db.Close()
41 return nil, fmt.Errorf("failed to connect to database: %w", err)
42 }
43
44 // Apply performance pragmas
45 if err := applyPragmas(db); err != nil {
46 _ = db.Close()
47 return nil, err
48 }
49
50 // Run migrations
51 if err := RunMigrations(ctx, db); err != nil {
52 _ = db.Close()
53 return nil, fmt.Errorf("failed to run migrations: %w", err)
54 }
55
56 log.Info().Str("path", dbPath).Msg("database connection established")
57
58 return db, nil
59}
60
61// applyPragmas configures SQLite for optimal performance.
62func applyPragmas(db *sql.DB) error {
63 pragmas := []string{
64 "PRAGMA journal_mode = WAL", // Write-Ahead Logging for concurrent access
65 "PRAGMA synchronous = NORMAL", // Safe in WAL mode
66 "PRAGMA cache_size = -64000", // 64MB cache
67 "PRAGMA temp_store = MEMORY", // Temporary tables in RAM
68 "PRAGMA mmap_size = 268435456", // 256MB memory-mapped I/O (reasonable for browser history)
69 "PRAGMA busy_timeout = 5000", // Wait 5 seconds on lock contention
70 "PRAGMA foreign_keys = ON", // Enable referential integrity
71 }
72
73 for _, pragma := range pragmas {
74 if _, err := db.Exec(pragma); err != nil {
75 return fmt.Errorf("failed to set pragma %q: %w", pragma, err)
76 }
77 }
78
79 return nil
80}
81
82// configurePool sets connection pool parameters optimized for SQLite.
83// SQLite only supports one writer at a time, so we limit connections.
84//
85// Connections are configured to never expire or close while idle. This is safe
86// because the application's lifecycle matches the database connection's lifecycle:
87// the process terminates before the underlying database file is rotated or replaced.
88func configurePool(db *sql.DB) {
89 db.SetMaxOpenConns(1) // SQLite is single-writer
90 db.SetMaxIdleConns(1) // Keep one connection alive
91 db.SetConnMaxLifetime(0) // Never expire connections
92 db.SetConnMaxIdleTime(0) // Never close idle connections
93}
94
95// Close closes the database connection gracefully.
96func Close(db *sql.DB) error {
97 if db == nil {
98 return nil
99 }
100 return db.Close()
101}