refactor: simplify SQLite connection handling and path management

1 files changed, 25 insertions, 5 deletions

diff --git a/internal/database/sql/main.go b/internal/database/sql/main.go
index 6e73ca3..1a29157 100644
--- a/internal/database/sql/main.go
+++ b/internal/database/sql/main.go
@@ -2,6 +2,7 @@ package database
 
 import (
 	"os"
+	"path/filepath"
 	"time"
 
 	"log/slog"
@@ -36,14 +37,23 @@ func closeConn(conn *sqlx.DB) error {
 	return conn.Close()
 }
 
-func Init(DBURI string) (*DB, error) {
+func Init(dbPath string) (*DB, error) {
 	var result DB
 	var err error
-	result.Conn, err = openDBConnection(DBURI, dbDriver)
+	
+	// Default to in-memory DB if no path specified
+	if dbPath == "" {
+		dbPath = ":memory:"
+	} else if !filepath.IsAbs(dbPath) {
+		// Convert relative paths to absolute
+		dbPath = filepath.Join(".", dbPath)
+	}
+
+	result.Conn, err = openDBConnection(dbPath, dbDriver)
 	if err != nil {
 		return nil, err
 	}
-	result.URI = DBURI
+	result.URI = dbPath
 	if err := testConnection(result.Conn); err != nil {
 		return nil, err
 	}
@@ -64,11 +74,21 @@ func Init(DBURI string) (*DB, error) {
 	return &result, nil
 }
 
-func openDBConnection(dbURI, driver string) (*sqlx.DB, error) {
-	conn, err := sqlx.Open(driver, dbURI)
+func openDBConnection(dbPath, driver string) (*sqlx.DB, error) {
+	// Ensure the directory exists
+	if err := os.MkdirAll(filepath.Dir(dbPath), 0755); err != nil {
+		return nil, errors.Wrap(err, "failed to create db directory")
+	}
+
+	conn, err := sqlx.Open(driver, dbPath)
 	if err != nil {
 		return nil, err
 	}
+
+	// SQLite-specific optimizations
+	conn.SetMaxOpenConns(1) // SQLite only supports one writer at a time
+	conn.SetConnMaxLifetime(0) // Connections don't need to be closed/reopened
+
 	return conn, nil
 }