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