Added goose for migrations, refactored SQL schema, server and client

are broken

1 files changed, 20 insertions, 1 deletions

diff --git a/internal/server/api/database.go b/internal/server/api/database.go
index 0617515..00e8d4d 100644
--- a/internal/server/api/database.go
+++ b/internal/server/api/database.go
@@ -2,19 +2,26 @@ package api
 
 import (
 	"database/sql"
+	"embed"
 	"log"
 
 	_ "github.com/mattn/go-sqlite3"
+	"github.com/pressly/goose/v3"
 
 	"github.com/kyren223/eko/pkg/assert"
 )
 
+//go:embed migrations/*.sql
+var embedMigrations embed.FS
+
 var db *sql.DB
 
 func ConnectToDatabase() {
 	var err error
 	db, err = sql.Open("sqlite3", "file:server.db?cache=shared")
-	assert.NoError(err, "DB should always be accessible")
+	if err != nil {
+		log.Fatalln("unable to open db:", err)
+	}
 	assert.AddFlush(db)
 	log.Println("established connection with the database")
 
@@ -29,6 +36,18 @@ func ConnectToDatabase() {
 		assert.NoError(err, "DB pragmas should always execute with no errors")
 	}
 
+	log.Println("opened database, running up migrations...")
+
+	goose.SetBaseFS(embedMigrations)
+	if err := goose.SetDialect("sqlite3"); err != nil {
+		assert.NoError(err, "sqlite3 is a valid dialect that shouldn't error")
+	}
+	if err := goose.Up(db, "migrations"); err != nil {
+		db.Close()
+		log.Fatalln("error running up migrations:", err)
+	}
+	log.Println("database up migrations applied successfully")
+
 	log.Println("database connection ready to be used")
 }