commit 03318522417a694906ea318071a8cb211a4f38ea
parent 361f2f91fa0b4c044bf122e7ae3f28ef0733f7c9
Author: David Eisinger <[email protected]>
Date: Mon, 6 Oct 2025 12:55:33 -0400
database
Diffstat:
3 files changed, 313 insertions(+), 0 deletions(-)
diff --git a/go.mod b/go.mod
@@ -1,3 +1,5 @@
module marky
go 1.24.5
+
+require modernc.org/sqlite v1.33.0
diff --git a/internal/database.go b/internal/database.go
@@ -0,0 +1,294 @@
+package internal
+
+import (
+ "context"
+ "database/sql"
+ "embed"
+ "errors"
+ "fmt"
+ "io/fs"
+ "os"
+ "path/filepath"
+ "sort"
+ "strings"
+ "time"
+
+ _ "modernc.org/sqlite"
+)
+
+//go:embed migrations/*.sql
+var migrationsFS embed.FS
+
+// Bookmark represents a single bookmark row.
+type Bookmark struct {
+ ID int64
+ URL string
+ Title string
+ Description sql.NullString
+ CreatedAt time.Time
+ UpdatedAt time.Time
+}
+
+// DB wraps *sql.DB with helpers.
+type DB struct {
+ sql *sql.DB
+ dsn string
+}
+
+// DefaultPath returns the default SQLite file path: ~/.marky/bookmarks.sql
+func DefaultPath() (string, error) {
+ home, err := os.UserHomeDir()
+ if err != nil {
+ return "", fmt.Errorf("resolve home dir: %w", err)
+ }
+ dir := filepath.Join(home, ".marky")
+ if err := os.MkdirAll(dir, 0o755); err != nil {
+ return "", fmt.Errorf("mkdir %s: %w", dir, err)
+ }
+ return filepath.Join(dir, "bookmarks.sql"), nil
+}
+
+// Open opens (and initializes) the database at the default path.
+func Open(ctx context.Context) (*DB, error) {
+ p, err := DefaultPath()
+ if err != nil {
+ return nil, err
+ }
+ return OpenAtPath(ctx, p)
+}
+
+// OpenAtPath opens (and initializes) the database at a specific path.
+func OpenAtPath(ctx context.Context, path string) (*DB, error) {
+ // Use a file: DSN and apply sensible pragmas after open.
+ dsn := fmt.Sprintf("file:%s", filepath.ToSlash(path))
+
+ sqldb, err := sql.Open("sqlite", dsn)
+ if err != nil {
+ return nil, fmt.Errorf("open sqlite: %w", err)
+ }
+
+ // Connection settings.
+ // WAL improves concurrency; busy_timeout makes writes friendlier.
+ pragmas := []string{
+ "PRAGMA journal_mode=WAL",
+ "PRAGMA busy_timeout=5000",
+ "PRAGMA foreign_keys=ON",
+ }
+ for _, p := range pragmas {
+ if _, execErr := sqldb.ExecContext(ctx, p); execErr != nil {
+ _ = sqldb.Close()
+ return nil, fmt.Errorf("apply pragma %q: %w", p, execErr)
+ }
+ }
+
+ // Migrate schema.
+ if err := applyMigrations(ctx, sqldb); err != nil {
+ _ = sqldb.Close()
+ return nil, err
+ }
+
+ return &DB{sql: sqldb, dsn: dsn}, nil
+}
+
+// Close closes the database.
+func (db *DB) Close() error { return db.sql.Close() }
+
+// applyMigrations executes embedded SQL migration files in order.
+func applyMigrations(ctx context.Context, sqldb *sql.DB) error {
+ if _, err := sqldb.ExecContext(ctx, `
+ CREATE TABLE IF NOT EXISTS schema_migrations (
+ version TEXT PRIMARY KEY,
+ applied_at TEXT NOT NULL
+ );
+ `); err != nil {
+ return fmt.Errorf("create schema_migrations: %w", err)
+ }
+
+ entries, err := fs.ReadDir(migrationsFS, "migrations")
+ if err != nil {
+ return fmt.Errorf("read migrations: %w", err)
+ }
+
+ // Sort by filename ascending.
+ sort.Slice(entries, func(i, j int) bool { return entries[i].Name() < entries[j].Name() })
+
+ for _, e := range entries {
+ name := e.Name()
+ if e.IsDir() || !strings.HasSuffix(name, ".sql") {
+ continue
+ }
+
+ // Skip if applied.
+ var exists int
+ if err := sqldb.QueryRowContext(ctx, `SELECT 1 FROM schema_migrations WHERE version = ?`, name).Scan(&exists); err != nil {
+ if !errors.Is(err, sql.ErrNoRows) {
+ return fmt.Errorf("check migration %s: %w", name, err)
+ }
+ } else {
+ // already applied
+ continue
+ }
+
+ // Read SQL.
+ b, err := migrationsFS.ReadFile(filepath.Join("migrations", name))
+ if err != nil {
+ return fmt.Errorf("read migration %s: %w", name, err)
+ }
+
+ tx, err := sqldb.BeginTx(ctx, nil)
+ if err != nil {
+ return fmt.Errorf("begin tx for %s: %w", name, err)
+ }
+ if _, err := tx.ExecContext(ctx, string(b)); err != nil {
+ _ = tx.Rollback()
+ return fmt.Errorf("exec migration %s: %w", name, err)
+ }
+ if _, err := tx.ExecContext(ctx, `INSERT INTO schema_migrations(version, applied_at) VALUES(?, ?)`, name, time.Now().UTC().Format(time.RFC3339Nano)); err != nil {
+ _ = tx.Rollback()
+ return fmt.Errorf("record migration %s: %w", name, err)
+ }
+ if err := tx.Commit(); err != nil {
+ return fmt.Errorf("commit migration %s: %w", name, err)
+ }
+ }
+ return nil
+}
+
+// CreateBookmark inserts a new bookmark and returns it.
+func (db *DB) CreateBookmark(ctx context.Context, url, title string, description *string) (*Bookmark, error) {
+ if strings.TrimSpace(url) == "" || strings.TrimSpace(title) == "" {
+ return nil, fmt.Errorf("url and title are required")
+ }
+ now := time.Now().UTC()
+ var desc sql.NullString
+ if description != nil {
+ desc = sql.NullString{String: *description, Valid: true}
+ }
+ res, err := db.sql.ExecContext(ctx, `
+ INSERT INTO bookmarks(url, title, description, created_at, updated_at)
+ VALUES(?, ?, ?, ?, ?)
+ `, url, title, nullString(desc), now.Format(time.RFC3339Nano), now.Format(time.RFC3339Nano))
+ if err != nil {
+ return nil, fmt.Errorf("insert bookmark: %w", err)
+ }
+ id, err := res.LastInsertId()
+ if err != nil {
+ return nil, fmt.Errorf("last insert id: %w", err)
+ }
+ return &Bookmark{
+ ID: id,
+ URL: url,
+ Title: title,
+ Description: desc,
+ CreatedAt: now,
+ UpdatedAt: now,
+ }, nil
+}
+
+// GetBookmark returns a bookmark by ID.
+func (db *DB) GetBookmark(ctx context.Context, id int64) (*Bookmark, error) {
+ var b Bookmark
+ var created, updated string
+ if err := db.sql.QueryRowContext(ctx, `
+ SELECT id, url, title, description, created_at, updated_at
+ FROM bookmarks WHERE id = ?
+ `, id).Scan(&b.ID, &b.URL, &b.Title, &b.Description, &created, &updated); err != nil {
+ if errors.Is(err, sql.ErrNoRows) {
+ return nil, nil
+ }
+ return nil, fmt.Errorf("select bookmark: %w", err)
+ }
+ var err error
+ if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
+ return nil, fmt.Errorf("parse created_at: %w", err)
+ }
+ if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
+ return nil, fmt.Errorf("parse updated_at: %w", err)
+ }
+ return &b, nil
+}
+
+// ListBookmarks returns bookmarks ordered by created_at desc.
+func (db *DB) ListBookmarks(ctx context.Context, limit, offset int) ([]Bookmark, error) {
+ if limit <= 0 {
+ limit = 50
+ }
+ rows, err := db.sql.QueryContext(ctx, `
+ SELECT id, url, title, description, created_at, updated_at
+ FROM bookmarks
+ ORDER BY created_at DESC
+ LIMIT ? OFFSET ?
+ `, limit, offset)
+ if err != nil {
+ return nil, fmt.Errorf("query bookmarks: %w", err)
+ }
+ defer rows.Close()
+ out := make([]Bookmark, 0, limit)
+ for rows.Next() {
+ var b Bookmark
+ var created, updated string
+ if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &created, &updated); err != nil {
+ return nil, fmt.Errorf("scan bookmark: %w", err)
+ }
+ if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
+ return nil, fmt.Errorf("parse created_at: %w", err)
+ }
+ if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
+ return nil, fmt.Errorf("parse updated_at: %w", err)
+ }
+ out = append(out, b)
+ }
+ if err := rows.Err(); err != nil {
+ return nil, fmt.Errorf("iterate bookmarks: %w", err)
+ }
+ return out, nil
+}
+
+// UpdateBookmark updates fields for a given bookmark.
+func (db *DB) UpdateBookmark(ctx context.Context, id int64, url, title string, description *string) (*Bookmark, error) {
+ if strings.TrimSpace(url) == "" || strings.TrimSpace(title) == "" {
+ return nil, fmt.Errorf("url and title are required")
+ }
+ now := time.Now().UTC()
+ var desc sql.NullString
+ if description != nil {
+ desc = sql.NullString{String: *description, Valid: true}
+ }
+ res, err := db.sql.ExecContext(ctx, `
+ UPDATE bookmarks
+ SET url = ?, title = ?, description = ?, updated_at = ?
+ WHERE id = ?
+ `, url, title, nullString(desc), now.Format(time.RFC3339Nano), id)
+ if err != nil {
+ return nil, fmt.Errorf("update bookmark: %w", err)
+ }
+ n, err := res.RowsAffected()
+ if err != nil {
+ return nil, fmt.Errorf("rows affected: %w", err)
+ }
+ if n == 0 {
+ return nil, nil
+ }
+ return db.GetBookmark(ctx, id)
+}
+
+// DeleteBookmark deletes a bookmark by ID. Returns true if a row was deleted.
+func (db *DB) DeleteBookmark(ctx context.Context, id int64) (bool, error) {
+ res, err := db.sql.ExecContext(ctx, `DELETE FROM bookmarks WHERE id = ?`, id)
+ if err != nil {
+ return false, fmt.Errorf("delete bookmark: %w", err)
+ }
+ n, err := res.RowsAffected()
+ if err != nil {
+ return false, fmt.Errorf("rows affected: %w", err)
+ }
+ return n > 0, nil
+}
+
+func nullString(ns sql.NullString) any {
+ if ns.Valid {
+ return ns.String
+ }
+ return nil
+}
+
diff --git a/internal/migrations/0001_init.sql b/internal/migrations/0001_init.sql
@@ -0,0 +1,17 @@
+-- Enable foreign key support
+PRAGMA foreign_keys = ON;
+
+-- Create bookmarks table
+CREATE TABLE IF NOT EXISTS bookmarks (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ url TEXT NOT NULL,
+ title TEXT NOT NULL,
+ description TEXT,
+ created_at TEXT NOT NULL,
+ updated_at TEXT NOT NULL
+);
+
+-- Optional indexes for common lookups
+CREATE INDEX IF NOT EXISTS idx_bookmarks_url ON bookmarks(url);
+CREATE INDEX IF NOT EXISTS idx_bookmarks_created_at ON bookmarks(created_at);
+
