From 4cb6a5952f341741ef2728fb39acb7f19de61ba9 Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Sun, 31 May 2026 18:13:15 +0200
Subject: Add migration reading

This prepares the infrastructure to place migration SQL code in the
source repository with paths formatted as `sql/migrations/%04d.sqlite3`.
The numbers need to be counting up from 1.

They are embedded with Go's embed package and are parsed into a slice of
strings where the migration number minus one is the slice index and the
value is the migrations file as string.

This makes handling of migrations in code as easy as possible.

This commit adds also tests for this functionality.
---
 database.go                 | 45 +++++++++++++++++++++++++++++++++++++++++++++
 database_test.go            | 44 ++++++++++++++++++++++++++++++++++++++++++++
 meson.build                 |  1 +
 sql/migrations/0001.sqlite3 |  1 +
 4 files changed, 91 insertions(+)
 create mode 100644 database.go
 create mode 100644 database_test.go
 create mode 100644 sql/migrations/0001.sqlite3

diff --git a/database.go b/database.go
new file mode 100644
index 0000000..c2d69c1
--- /dev/null
+++ b/database.go
@@ -0,0 +1,45 @@
+package main
+
+import (
+	"embed"
+	"fmt"
+	"io/fs"
+	"log"
+	"path/filepath"
+)
+
+const (
+	migrationDir         = `sql/migrations`
+	migrationFilePattern = `%04d.sqlite3`
+)
+
+var migrations []string
+
+//go:embed sql/migrations/*.sqlite3
+var migrationsFS embed.FS
+
+func init() {
+	readMigrations()
+}
+
+func readMigrations() {
+	i := 1
+
+	_ = fs.WalkDir(migrationsFS, migrationDir, func(path string, d fs.DirEntry, err error) error {
+		if !d.IsDir() {
+			expected := fmt.Sprintf(migrationFilePattern, i)
+			if filepath.Base(path) == expected {
+				data, err := fs.ReadFile(migrationsFS, path)
+				if err != nil {
+					log.Fatalf("Could not read migration: %v", err)
+				}
+
+				migrations = append(migrations, string(data))
+
+				i += 1
+			}
+		}
+
+		return nil
+	})
+}
diff --git a/database_test.go b/database_test.go
new file mode 100644
index 0000000..fce0c02
--- /dev/null
+++ b/database_test.go
@@ -0,0 +1,44 @@
+package main
+
+import (
+	"fmt"
+	"os"
+	"path/filepath"
+	"strings"
+	"testing"
+)
+
+func TestMigrationNumber(t *testing.T) {
+	n := len(migrations)
+
+	entries, err := os.ReadDir(migrationDir)
+	if err != nil {
+		t.Fatalf("Failed to determine number of migration files: %v", err)
+	}
+	expectation := len(entries)
+
+	if n != expectation {
+		t.Fatalf("Expected %d migration files but got %d.", expectation, n)
+	}
+}
+
+func TestMigrationContent(t *testing.T) {
+	for i, migration := range migrations {
+		name := fmt.Sprintf(migrationFilePattern, i+1)
+		path := filepath.Join(migrationDir, name)
+
+		expectation := fmt.Sprintf("PRAGMA user_version = %d;", i+1)
+		if !strings.HasPrefix(migration, expectation) {
+			t.Fatalf("Expected '%s' at start of '%s'.", expectation, path)
+		}
+
+		n := strings.Count(migration, `user_version`)
+		if n != 1 {
+			t.Fatalf(
+				"'user_version' was mentioned more than once in %s. "+
+				"This is likely not intended.",
+				path,
+			)
+		}
+	}
+}
diff --git a/meson.build b/meson.build
index 0bd75d3..ba66106 100644
--- a/meson.build
+++ b/meson.build
@@ -11,6 +11,7 @@ finserv_linux_amd64 = custom_target(
   input : [
     meson.current_source_dir() / 'main.go',
     meson.current_source_dir() / 'handlers.go',
+    meson.current_source_dir() / 'database.go',
   ],
   output : 'finserv-linux-amd64',
   env : {'GOOS': 'linux', 'GOARCH': 'amd64'},
diff --git a/sql/migrations/0001.sqlite3 b/sql/migrations/0001.sqlite3
new file mode 100644
index 0000000..d0c13fc
--- /dev/null
+++ b/sql/migrations/0001.sqlite3
@@ -0,0 +1 @@
+PRAGMA user_version = 1;
-- 
cgit v1.3.1