db.go

package main

import (
	"crypto/rand"
	"embed"
	"fmt"
	"io/fs"
	"log"
	"sort"

	"github.com/jmoiron/sqlx"
)

//go:embed migrations/*.sql
var migrationFS embed.FS

func initDB(db *sqlx.DB) error {
	// Create table tracking migration state
	query := `CREATE TABLE IF NOT EXISTS migrations (name TEXT UNIQUE);`

	_, err := db.Exec(query)
	if err != nil {
		return err
	}

	entries, err := migrationFS.ReadDir("migrations")
	if err != nil {
		return err
	}

	sort.Slice(entries, func(i, j int) bool {
		return entries[i].Name() < entries[j].Name()
	})

	for _, e := range entries {
		data, err := fs.ReadFile(migrationFS, "migrations/"+e.Name())
		if err != nil {
			return err
		}

		tx, err := db.Begin()
		if err != nil {
			return err
		}

		row := tx.QueryRow("SELECT count(*) FROM migrations WHERE name = ?", e.Name())

		var howMany int
		err = row.Scan(&howMany)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("checking status for migration %s: %w", e.Name(), err)
		}

		switch howMany {
		case 0:
			// not yet applied
		case 1:
			// applied, no need to do anything
			log.Printf("skipping migration %s: already applied", e.Name())
			tx.Rollback()
			continue
		default:
			// very weird
			tx.Rollback()
			return fmt.Errorf("unexpected migration count for %s: %d", e.Name(), howMany)
		}

		log.Println("applying migration", e.Name())

		_, err = tx.Exec(string(data))
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("applying migration %s: %w", e.Name(), err)
		}

		// Record migration as applied
		_, err = tx.Exec("INSERT INTO migrations (name) VALUES (?)", e.Name())
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("recording migration %s: %w", e.Name(), err)
		}

		err = tx.Commit()
		if err != nil {
			return err
		}

		log.Println("it has", len(data), "bytes")
	}

	// Initialize persistent state like password salt
	buf := make([]byte, 16)
	_, err = rand.Read(buf)
	if err != nil {
		return err
	}

	query = `INSERT OR IGNORE INTO state VALUES ('pwsalt', ?)`
	_, err = db.Exec(query, buf)
	if err != nil {
		return err
	}

	return nil
}