3 files changed, 79 insertions, 0 deletions

diff --git a/backend/config.go b/backend/config.go
index e009b8a..b221978 100644
--- a/backend/config.go
+++ b/backend/config.go
@@ -35,6 +35,7 @@ type Config struct {
 	DefaultImage string   `toml:"default_image"`
 	Quotes       []string `toml:"quotes"`
 	Language     string   `toml:"language"`
+	Database     string   `toml:"database"`
 
 	Sections []Section `toml:"section"`
 
@@ -73,6 +74,7 @@ func (c *Config) DefaultValues() {
 	}}
 	c.RootFolder = "data"
 	c.PublicFolder = "public"
+	c.Database = "database.sqlite"
 	c.Quotes = []string{"Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do."}
 	c.Replacers = []Replacer{{"~", " "}}
 }
diff --git a/backend/db.go b/backend/db.go
new file mode 100644
index 0000000..3559b9c
--- /dev/null
+++ b/backend/db.go
@@ -0,0 +1,73 @@
+package backend
+
+import (
+	"context"
+	"database/sql"
+	"embed"
+	"fmt"
+	"log/slog"
+	"regexp"
+	"slices"
+	"strconv"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+//go:embed migrations
+var migrations embed.FS
+
+var nameReg = regexp.MustCompile(`(\d{3})_[a-zA-Z_-]+.sql`)
+
+func ConnectDatabase(cfg *Config) *sql.DB {
+	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?cache=shared", cfg.Database))
+	if err != nil {
+		panic(err)
+	}
+	db.SetMaxOpenConns(1)
+	return db
+}
+
+func RunMigration(ctx context.Context, db *sql.DB) error {
+	entries, err := migrations.ReadDir("migrations")
+	if err != nil {
+		return err
+	}
+	type dbConfig struct {
+		Id        int
+		Migration int
+	}
+	type runMig struct {
+		val string
+		n   int
+	}
+	var toRun []runMig
+	for _, e := range entries {
+		rawId := nameReg.FindStringSubmatch(e.Name())
+		id, err := strconv.Atoi(rawId[1])
+		if err != nil {
+			return err
+		}
+		b, err := migrations.ReadFile("migrations/" + e.Name())
+		if err != nil {
+			return err
+		}
+		slog.Debug("loading migration", "n", id, "file", e.Name(), "content", string(b))
+		toRun = append(toRun, runMig{
+			val: string(b), n: id,
+		})
+	}
+	if len(toRun) == 0 {
+		return nil
+	}
+	slices.SortFunc(toRun, func(a, b runMig) int {
+		return a.n - b.n
+	})
+	for _, m := range toRun {
+		slog.Info("migrating", "n", m.n)
+		_, err := db.ExecContext(ctx, m.val)
+		if err != nil {
+			return err
+		}
+	}
+	return nil
+}
diff --git a/backend/migrations/000_init.sql b/backend/migrations/000_init.sql
new file mode 100644
index 0000000..6bd9d3a
--- /dev/null
+++ b/backend/migrations/000_init.sql
@@ -0,0 +1,4 @@
+CREATE TABLE IF NOT EXISTS config(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    migration INTEGER
+);