connection.go RawHistoryFolder

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	// Test the connection
37	if err := db.Ping(); err != nil {
38		_ = db.Close()
39		return nil, fmt.Errorf("failed to connect to database: %w", err)
40	}
41
42	// Apply performance pragmas
43	if err := applyPragmas(db); err != nil {
44		_ = db.Close()
45		return nil, err
46	}
47
48	// Run migrations
49	if err := RunMigrations(ctx, db); err != nil {
50		_ = db.Close()
51		return nil, fmt.Errorf("failed to run migrations: %w", err)
52	}
53
54	log.Info().Str("path", dbPath).Msg("database connection established")
55
56	return db, nil
57}
58
59// applyPragmas configures SQLite for optimal performance.
60func applyPragmas(db *sql.DB) error {
61	pragmas := []string{
62		"PRAGMA journal_mode = WAL",      // Write-Ahead Logging for concurrent access
63		"PRAGMA synchronous = NORMAL",    // Safe in WAL mode
64		"PRAGMA cache_size = -64000",     // 64MB cache
65		"PRAGMA temp_store = MEMORY",     // Temporary tables in RAM
66		"PRAGMA mmap_size = 30000000000", // 30GB memory-mapped I/O
67		"PRAGMA busy_timeout = 5000",     // Wait 5 seconds on lock contention
68	}
69
70	for _, pragma := range pragmas {
71		if _, err := db.Exec(pragma); err != nil {
72			return fmt.Errorf("failed to set pragma %q: %w", pragma, err)
73		}
74	}
75
76	return nil
77}
78
79// Close closes the database connection gracefully.
80func Close(db *sql.DB) error {
81	if db == nil {
82		return nil
83	}
84	return db.Close()
85}