1package sqlite
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "os"
9 "path/filepath"
10 "strings"
11 "time"
12
13 "golang.org/x/sys/unix"
14
15 "github.com/bnema/dumber/internal/logging"
16 _ "github.com/bnema/purego-sqlite/driver" // SQLite driver (purego, no WASM/CGo)
17)
18
19// NewConnection creates a new SQLite database connection with optimized settings.
20// It creates the database directory if it doesn't exist and applies performance pragmas.
21func NewConnection(ctx context.Context, dbPath string) (*sql.DB, error) {
22 const dbDirPerm = 0o750
23 log := logging.FromContext(ctx)
24
25 if dbPath == "" {
26 return nil, fmt.Errorf("database path cannot be empty")
27 }
28
29 // Ensure database directory exists
30 if err := os.MkdirAll(filepath.Dir(dbPath), dbDirPerm); err != nil {
31 return nil, fmt.Errorf("failed to create database directory: %w", err)
32 }
33
34 lock, err := lockDatabaseStartup(ctx, dbPath+".startup.lock")
35 if err != nil {
36 return nil, err
37 }
38 defer unlockDatabaseStartup(lock)
39
40 // Open database connection
41 db, err := sql.Open("sqlite3", dbPath)
42 if err != nil {
43 return nil, fmt.Errorf("failed to open database: %w", err)
44 }
45
46 // Configure connection pool (must be done before any queries)
47 configurePool(db)
48
49 // Test the connection
50 if err := db.Ping(); err != nil {
51 _ = db.Close()
52 return nil, fmt.Errorf("failed to connect to database: %w", err)
53 }
54
55 // Apply performance pragmas
56 if err := applyPragmas(ctx, db); err != nil {
57 _ = db.Close()
58 return nil, err
59 }
60
61 // Run migrations
62 if err := RunMigrations(ctx, db); err != nil {
63 _ = db.Close()
64 return nil, fmt.Errorf("failed to run migrations: %w", err)
65 }
66
67 log.Info().Str("path", dbPath).Msg("database connection established")
68
69 return db, nil
70}
71
72const (
73 pragmaRetryTimeout = 5 * time.Second
74 lockRetryInterval = 25 * time.Millisecond
75)
76
77// applyPragmas configures SQLite for optimal performance.
78func applyPragmas(ctx context.Context, db *sql.DB) error {
79 pragmas := []string{
80 "PRAGMA synchronous = NORMAL", // Safe in WAL mode
81 "PRAGMA cache_size = -64000", // 64MB cache
82 "PRAGMA temp_store = MEMORY", // Temporary tables in RAM
83 "PRAGMA mmap_size = 268435456", // 256MB memory-mapped I/O (reasonable for browser history)
84 "PRAGMA busy_timeout = 5000", // Wait 5 seconds on lock contention
85 "PRAGMA foreign_keys = ON", // Enable referential integrity
86 }
87
88 var journalMode string
89 if err := retryPragma(ctx, func() error {
90 return db.QueryRowContext(ctx, "PRAGMA journal_mode = WAL").Scan(&journalMode)
91 }); err != nil {
92 return fmt.Errorf("failed to set pragma %q: %w", "PRAGMA journal_mode = WAL", err)
93 }
94 if !strings.EqualFold(journalMode, "wal") {
95 return fmt.Errorf("failed to set pragma %q: journal mode is %q", "PRAGMA journal_mode = WAL", journalMode)
96 }
97
98 for _, pragma := range pragmas {
99 if err := retryPragma(ctx, func() error {
100 _, err := db.ExecContext(ctx, pragma)
101 return err
102 }); err != nil {
103 return fmt.Errorf("failed to set pragma %q: %w", pragma, err)
104 }
105 }
106 return nil
107}
108
109func retryPragma(ctx context.Context, run func() error) error {
110 deadline := time.Now().Add(pragmaRetryTimeout)
111 for {
112 err := run()
113 if err == nil || !isSQLiteBusy(err) || !time.Now().Before(deadline) {
114 return err
115 }
116 timer := time.NewTimer(lockRetryInterval)
117 select {
118 case <-ctx.Done():
119 if !timer.Stop() {
120 <-timer.C
121 }
122 return ctx.Err()
123 case <-timer.C:
124 }
125 }
126}
127
128func isSQLiteBusy(err error) bool {
129 message := strings.ToLower(err.Error())
130 return strings.Contains(message, "database is locked") || strings.Contains(message, "database is busy") ||
131 strings.Contains(message, "sqlite_busy")
132}
133
134func lockDatabaseStartup(ctx context.Context, path string) (*os.File, error) {
135 const (
136 lockRetryInterval = 25 * time.Millisecond
137 ownerOnlyFileMode = 0o600
138 )
139 file, err := os.OpenFile(path, os.O_CREATE|os.O_RDWR|os.O_TRUNC, ownerOnlyFileMode)
140 if err != nil {
141 return nil, fmt.Errorf("open database startup lock: %w", err)
142 }
143 for {
144 if err := unix.Flock(int(file.Fd()), unix.LOCK_EX|unix.LOCK_NB); err == nil {
145 return file, nil
146 } else if !errors.Is(err, unix.EWOULDBLOCK) {
147 _ = file.Close()
148 return nil, fmt.Errorf("lock database startup: %w", err)
149 }
150 select {
151 case <-ctx.Done():
152 _ = file.Close()
153 return nil, fmt.Errorf("lock database startup: %w", ctx.Err())
154 case <-time.After(lockRetryInterval):
155 }
156 }
157}
158
159func unlockDatabaseStartup(file *os.File) {
160 if file == nil {
161 return
162 }
163 _ = unix.Flock(int(file.Fd()), unix.LOCK_UN)
164 _ = file.Close()
165}
166
167// configurePool sets connection pool parameters optimized for SQLite.
168// SQLite only supports one writer at a time, so we limit connections.
169//
170// Connections are configured to never expire or close while idle. This is safe
171// because the application's lifecycle matches the database connection's lifecycle:
172// the process terminates before the underlying database file is rotated or replaced.
173func configurePool(db *sql.DB) {
174 db.SetMaxOpenConns(1) // SQLite is single-writer
175 db.SetMaxIdleConns(1) // Keep one connection alive
176 db.SetConnMaxLifetime(0) // Never expire connections
177 db.SetConnMaxIdleTime(0) // Never close idle connections
178}
179
180// Close closes the database connection gracefully.
181func Close(db *sql.DB) error {
182 if db == nil {
183 return nil
184 }
185 return db.Close()
186}