skeps/backend/db.go

package main

import (
	"database/sql"
	"fmt"
	"strconv"
	"strings"

	_ "github.com/go-sql-driver/mysql"
	_ "github.com/lib/pq"
	_ "modernc.org/sqlite"
)

const sqliteSchema = `
CREATE TABLE IF NOT EXISTS lists (
	id TEXT PRIMARY KEY,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS sublists (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	list_id TEXT NOT NULL REFERENCES lists(id),
	name TEXT NOT NULL,
	color TEXT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS items (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	list_id TEXT NOT NULL REFERENCES lists(id),
	name TEXT NOT NULL,
	quantity INTEGER NOT NULL DEFAULT 1,
	done INTEGER NOT NULL DEFAULT 0,
	position REAL NOT NULL DEFAULT 0,
	sublist_id INTEGER REFERENCES sublists(id),
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS recurring_items (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	list_id TEXT NOT NULL REFERENCES lists(id),
	name TEXT NOT NULL,
	sublist_id INTEGER REFERENCES sublists(id),
	position REAL NOT NULL DEFAULT 0,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`

const mysqlSchema = `
CREATE TABLE IF NOT EXISTS lists (
	id VARCHAR(64) PRIMARY KEY,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS sublists (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	list_id VARCHAR(64) NOT NULL,
	name VARCHAR(255) NOT NULL,
	color VARCHAR(32) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (list_id) REFERENCES lists(id)
);
CREATE TABLE IF NOT EXISTS items (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	list_id VARCHAR(64) NOT NULL,
	name VARCHAR(255) NOT NULL,
	quantity INTEGER NOT NULL DEFAULT 1,
	done BOOLEAN NOT NULL DEFAULT FALSE,
	position DOUBLE NOT NULL DEFAULT 0,
	sublist_id INTEGER,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (list_id) REFERENCES lists(id),
	FOREIGN KEY (sublist_id) REFERENCES sublists(id)
);
CREATE TABLE IF NOT EXISTS recurring_items (
	id INTEGER PRIMARY KEY AUTO_INCREMENT,
	list_id VARCHAR(64) NOT NULL,
	name VARCHAR(255) NOT NULL,
	sublist_id INTEGER,
	position DOUBLE NOT NULL DEFAULT 0,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (list_id) REFERENCES lists(id),
	FOREIGN KEY (sublist_id) REFERENCES sublists(id)
);`

const postgresSchema = `
CREATE TABLE IF NOT EXISTS lists (
	id TEXT PRIMARY KEY,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS sublists (
	id SERIAL PRIMARY KEY,
	list_id TEXT NOT NULL REFERENCES lists(id),
	name TEXT NOT NULL,
	color TEXT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS items (
	id SERIAL PRIMARY KEY,
	list_id TEXT NOT NULL REFERENCES lists(id),
	name TEXT NOT NULL,
	quantity INTEGER NOT NULL DEFAULT 1,
	done BOOLEAN NOT NULL DEFAULT FALSE,
	position DOUBLE PRECISION NOT NULL DEFAULT 0,
	sublist_id INTEGER REFERENCES sublists(id),
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS recurring_items (
	id SERIAL PRIMARY KEY,
	list_id TEXT NOT NULL REFERENCES lists(id),
	name TEXT NOT NULL,
	sublist_id INTEGER REFERENCES sublists(id),
	position DOUBLE PRECISION NOT NULL DEFAULT 0,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);`

// querier is the subset of *sql.DB every handler uses. Postgres needs its own
// implementation since, unlike sqlite and mysql, it takes $1-style positional
// placeholders instead of "?" and has no LastInsertId support.
type querier interface {
	Exec(query string, args ...any) (sql.Result, error)
	Query(query string, args ...any) (*sql.Rows, error)
	QueryRow(query string, args ...any) *sql.Row
	insertReturningID(query string, args ...any) (int64, error)
}

type stdDB struct{ *sql.DB }

func (d stdDB) insertReturningID(query string, args ...any) (int64, error) {
	res, err := d.DB.Exec(query, args...)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

type pgDB struct{ *sql.DB }

func rebindPositional(query string) string {
	var b strings.Builder
	n := 0
	for _, r := range query {
		if r == '?' {
			n++
			b.WriteByte('$')
			b.WriteString(strconv.Itoa(n))
		} else {
			b.WriteRune(r)
		}
	}
	return b.String()
}

func (d pgDB) Exec(query string, args ...any) (sql.Result, error) {
	return d.DB.Exec(rebindPositional(query), args...)
}

func (d pgDB) Query(query string, args ...any) (*sql.Rows, error) {
	return d.DB.Query(rebindPositional(query), args...)
}

func (d pgDB) QueryRow(query string, args ...any) *sql.Row {
	return d.DB.QueryRow(rebindPositional(query), args...)
}

func (d pgDB) insertReturningID(query string, args ...any) (int64, error) {
	var id int64
	err := d.DB.QueryRow(rebindPositional(query)+" RETURNING id", args...).Scan(&id)
	return id, err
}

func openDB(databaseURL string) (*sql.DB, querier, error) {
	driver, dsn, found := strings.Cut(databaseURL, "://")
	if !found {
		return nil, nil, fmt.Errorf("DATABASE_URL must be in the form driver://dsn, got %q", databaseURL)
	}

	var schema string
	switch driver {
	case "sqlite":
		schema = sqliteSchema
	case "mysql":
		schema = mysqlSchema
	case "postgres", "postgresql":
		driver = "postgres"
		dsn = databaseURL // lib/pq expects the full URL, scheme included
		schema = postgresSchema
	default:
		return nil, nil, fmt.Errorf("unsupported database driver %q, expected sqlite, mysql or postgres", driver)
	}

	db, err := sql.Open(driver, dsn)
	if err != nil {
		return nil, nil, fmt.Errorf("opening database: %w", err)
	}

	if err := db.Ping(); err != nil {
		return nil, nil, fmt.Errorf("connecting to database: %w", err)
	}

	if _, err := db.Exec(schema); err != nil {
		return nil, nil, fmt.Errorf("running migration: %w", err)
	}

	// Best-effort: adds columns for databases created before they existed.
	// Fails harmlessly if a column is already there.
	db.Exec("ALTER TABLE items ADD COLUMN position REAL NOT NULL DEFAULT 0")
	db.Exec("ALTER TABLE items ADD COLUMN sublist_id INTEGER")

	if driver == "postgres" {
		return db, pgDB{db}, nil
	}
	return db, stdDB{db}, nil
}