7 files changed, 94 insertions, 0 deletions

diff --git a/.gitignore b/.gitignore
index 15031c6..81516fb 100644
--- a/.gitignore
+++ b/.gitignore
@@ -161,3 +161,4 @@ config.toml
 data
 small-web
 *.tar.gz
+*.sqlite
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
+);
diff --git a/go.mod b/go.mod
index 65f2039..9c84422 100644
--- a/go.mod
+++ b/go.mod
@@ -8,3 +8,5 @@ require (
 	github.com/joho/godotenv v1.5.1
 	github.com/pelletier/go-toml/v2 v2.2.4
 )
+
+require github.com/mattn/go-sqlite3 v1.14.32 // indirect
diff --git a/go.sum b/go.sum
index e8af854..6f9f35f 100644
--- a/go.sum
+++ b/go.sum
@@ -4,5 +4,7 @@ github.com/go-chi/httplog/v3 v3.2.2 h1:G0oYv3YYcikNjijArHFUlqfR78cQNh9fGT43i6Stq
 github.com/go-chi/httplog/v3 v3.2.2/go.mod h1:N/J1l5l1fozUrqIVuT8Z/HzNeSy8TF2EFyokPLe6y2w=
 github.com/joho/godotenv v1.5.1 h1:7eLL/+HRGLY0ldzfGMeQkb7vMd0as4CfYvUVzLqw0N0=
 github.com/joho/godotenv v1.5.1/go.mod h1:f4LDr5Voq0i2e/R5DDNOoa2zzDfwtkZa6DnEwAbqwq4=
+github.com/mattn/go-sqlite3 v1.14.32 h1:JD12Ag3oLy1zQA+BNn74xRgaBbdhbNIDYvQUEuuErjs=
+github.com/mattn/go-sqlite3 v1.14.32/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y=
 github.com/pelletier/go-toml/v2 v2.2.4 h1:mye9XuhQ6gvn5h28+VilKrrPoQVanw5PMw/TB0t5Ec4=
 github.com/pelletier/go-toml/v2 v2.2.4/go.mod h1:2gIqNv+qfxSVS7cM2xJQKtLSTLUE9V8t9Stt+h56mCY=
diff --git a/main.go b/main.go
index 480762d..6b5571e 100644
--- a/main.go
+++ b/main.go
@@ -12,6 +12,7 @@ import (
 	"os/signal"
 	"strconv"
 	"syscall"
+	"time"
 
 	"git.anhgelus.world/anhgelus/small-web/backend"
 	"github.com/joho/godotenv"
@@ -58,6 +59,15 @@ func main() {
 		os.Exit(1)
 	}
 
+	ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second)
+	defer cancel()
+	db := backend.ConnectDatabase(cfg)
+	defer db.Close()
+	err := backend.RunMigration(ctx, db)
+	if err != nil {
+		panic(err)
+	}
+
 	for _, sec := range cfg.Sections {
 		if ok = sec.Load(cfg); !ok {
 			slog.Info("exiting")