Skip to content

armgit / lyftr

Public Imported from GitHub
0

Self-hosted fitness tracker. Log workouts, track nutrition, and monitor progress. Docker deployment, mobile-friendly.

2.5 KB
Raw Blame
package db

import (
	"database/sql"
	"log"
	"os"
	"path/filepath"

	"github.com/Cawlumm/lyftr-backend/config"
	_ "modernc.org/sqlite"
)

var DB *sql.DB

// BuildSchema brings an already-open DB to the current schema: base tables, then
// every migration, in the same order Connect uses.
//
// Exists so tests outside this package build their database the way production does
// instead of keeping a copy of the DDL. The controller tests used to carry a
// hand-maintained 138-line schema, which had already drifted — it created
// program_days (a migration artifact, not a base table) and omitted active_sessions
// entirely — so a column added to a migration compiled fine and failed at runtime.
func BuildSchema() error {
	if err := migrate(); err != nil {
		return err
	}
	alterMigrations()
	return nil
}

func Connect() {
	dbPath := config.C.DBPath

	// Ensure the directory exists
	if err := os.MkdirAll(filepath.Dir(dbPath), 0755); err != nil {
		log.Fatalf("failed to create db directory: %v", err)
	}

	var err error
	// modernc.org/sqlite uses _pragma=NAME(VALUE) DSN syntax. The old mattn-style
	// params (_journal_mode=…&_busy_timeout=…) were silently ignored, leaving
	// busy_timeout at 0 — so any contended lock failed instantly (a write racing
	// other requests would 500). Fix:
	//   busy_timeout(5000): wait up to 5s for a lock instead of erroring.
	//   journal_mode(WAL): readers don't block the writer; fewer locks.
	//   synchronous(NORMAL): the safe, faster durability setting under WAL.
	//   foreign_keys(on): keep cascade deletes working (DeleteAccount relies on it).
	DB, err = sql.Open("sqlite", dbPath+"?_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(on)")
	if err != nil {
		log.Fatalf("failed to open database: %v", err)
	}

	// SQLite is a single writer — it does not do concurrent writes. Rather than
	// fight that with a larger pool (which only turns contention into lock errors
	// to retry), serialize all DB access through ONE connection. With the
	// nested-cursor fix (no request needs a second connection mid-query), one
	// connection is sufficient and leaves no in-process lock contention to fail on.
	// The busy_timeout/WAL pragmas above remain only as cheap cross-process defense.
	DB.SetMaxOpenConns(1)
	DB.SetMaxIdleConns(1)

	if err = DB.Ping(); err != nil {
		log.Fatalf("failed to ping database: %v", err)
	}

	if err = migrate(); err != nil {
		log.Fatalf("migration failed: %v", err)
	}

	alterMigrations()

	log.Printf("SQLite database ready at %s", dbPath)
}