feat: db things

5 files changed, 215 insertions, 3 deletions

diff --git a/.gitignore b/.gitignore
index ca41f40..39ac19a 100644
--- a/.gitignore
+++ b/.gitignore
@@ -30,8 +30,5 @@ go.work.sum
 # env file
 .env
 
-# Testing
-db/
-
 # MacOS
 .DS_STORE
diff --git a/db/docker-compose.yml b/db/docker-compose.yml
new file mode 100644
index 0000000..6104179
--- /dev/null
+++ b/db/docker-compose.yml
@@ -0,0 +1,22 @@
+services:
+  postgres:
+    image: postgres:16
+    container_name: rpserver_postgres
+    environment:
+      POSTGRES_DB: rpserver
+      POSTGRES_USER: ${DB_USER:-postgres}
+      POSTGRES_PASSWORD: ${DB_PASSWORD}
+    volumes:
+      - postgres_data:/var/lib/postgresql/data
+    ports:
+      - "5433:5432"
+    restart: unless-stopped
+    healthcheck:
+      test: ["CMD-SHELL", "pg_isready -U ${DB_USER:-postgres}"]
+      interval: 10s
+      timeout: 5s
+      retries: 5
+
+volumes:
+  postgres_data:
+    name: rpserver_postgres_data  # Named volume for Postgres data persistence
diff --git a/db/migrations/001_database.down.sql b/db/migrations/001_database.down.sql
new file mode 100644
index 0000000..9ce1fae
--- /dev/null
+++ b/db/migrations/001_database.down.sql
@@ -0,0 +1,3 @@
+DROP TABLE IF EXISTS users;
+DROP TABLE IF EXISTS refresh_tokens;
+DROP TABLE IF EXISTS users_sessions;
diff --git a/db/migrations/001_database.up.sql b/db/migrations/001_database.up.sql
new file mode 100644
index 0000000..6c613c2
--- /dev/null
+++ b/db/migrations/001_database.up.sql
@@ -0,0 +1,35 @@
+-- Database Schema
+
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
+
+-- Users Table
+CREATE TABLE IF NOT EXISTS users (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    name VARCHAR(255) UNIQUE NOT NULL,
+    email VARCHAR(255) UNIQUE NOT NULL,
+    password_hash VARCHAR(255) NOT NULL,
+    admin BOOLEAN NOT NULL DEFAULT false,
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Refresh Tokens Table
+CREATE TABLE IF NOT EXISTS refresh_tokens (
+    id SERIAL PRIMARY KEY,
+    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+    token_hash VARCHAR(255) NOT NULL UNIQUE,
+    expires_at TIMESTAMP NOT NULL,
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Users Sessions Table
+CREATE TABLE IF NOT EXISTS users_sessions (
+    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
+    user_id UUID NOT NULL,
+    ip TEXT NOT NULL,
+    ua TEXT NOT NULL,
+    expires_at TIMESTAMP NOT NULL,
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
+);
diff --git a/db/migrator.go b/db/migrator.go
new file mode 100644
index 0000000..26bacaa
--- /dev/null
+++ b/db/migrator.go
@@ -0,0 +1,155 @@
+package db
+
+import (
+	"database/sql"
+	"embed"
+	"errors"
+	"fmt"
+	"log"
+
+	"github.com/golang-migrate/migrate/v4"
+	"github.com/golang-migrate/migrate/v4/database"
+	"github.com/golang-migrate/migrate/v4/database/postgres"
+
+	"github.com/golang-migrate/migrate/v4/database/cockroachdb"
+	"github.com/golang-migrate/migrate/v4/database/sqlite3"
+
+	_ "github.com/golang-migrate/migrate/v4/source/github"
+	"github.com/golang-migrate/migrate/v4/source/iofs"
+	_ "github.com/lib/pq"
+)
+
+//go:embed migrations/*.sql
+var migrationFS embed.FS
+
+type Migrator struct {
+	db      *sql.DB
+	Migrate *migrate.Migrate
+}
+
+func NewMigrator(db *sql.DB, dbProvider string, dbName string) (*Migrator, error) {
+	// Verify migrations exist
+	entries, err := migrationFS.ReadDir("migrations")
+	if err != nil {
+		return nil, fmt.Errorf("failed to read migrations directory: %w", err)
+	}
+
+	if len(entries) == 0 {
+		return nil, fmt.Errorf("no migration files found in embedded filesystem")
+	}
+
+	log.Printf("Found %d migration files", len(entries))
+
+	source, err := iofs.New(migrationFS, "migrations")
+	if err != nil {
+		return nil, fmt.Errorf("failed to create migrate instance: %w", err)
+	}
+
+	var driver database.Driver
+
+	switch dbProvider {
+	case "postgres":
+		driver, err = postgres.WithInstance(db, &postgres.Config{})
+	case "cockroachdb":
+		driver, err = cockroachdb.WithInstance(db, &cockroachdb.Config{})
+	case "sqlite3":
+		driver, err = sqlite3.WithInstance(db, &sqlite3.Config{})
+	default:
+		return nil, fmt.Errorf("failed to initialize database driver: %s it may not be supported yet", dbProvider)
+	}
+
+	if err != nil {
+		log.Fatalf("Failed to create DB driver: %v", err)
+	}
+
+	m, err := migrate.NewWithInstance("iofs", source, dbName, driver)
+	if err != nil {
+		return nil, fmt.Errorf("failed to create migrate instance: %w", err)
+	}
+
+	return &Migrator{
+		db:      db,
+		Migrate: m,
+	}, nil
+}
+
+func (m *Migrator) MigrateIfNeeded() error {
+	curVersion, dirty, err := m.Migrate.Version()
+	if err != nil && !errors.Is(err, migrate.ErrNilVersion) {
+		return fmt.Errorf("failed to get current version: %w", err)
+	}
+
+	if dirty {
+		return fmt.Errorf("database is in dirty state, manual intervention is required")
+	}
+
+	hasPending, nextVersion, err := m.hasPendingMigrations(curVersion)
+	if err != nil {
+		return fmt.Errorf("failed to check pending migrations: %w", err)
+	}
+
+	if !hasPending {
+		log.Printf("Database is up to date (version: %d)", curVersion)
+	}
+
+	log.Printf("Migrating database from version %d to %d", curVersion, nextVersion)
+
+	if err := m.Migrate.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
+		return fmt.Errorf("failed to run migrations %w", err)
+	}
+
+	newVersion, _, err := m.Migrate.Version()
+	if err != nil {
+		return fmt.Errorf("failed to get new version: %w", err)
+	}
+
+	log.Printf("Database migrated successfully to version %d", newVersion)
+	return nil
+}
+
+func (m *Migrator) hasPendingMigrations(curVersion uint) (bool, uint, error) {
+	nextVersion, err := m.getNextVersion(curVersion)
+	if err != nil {
+		if errors.Is(err, migrate.ErrNoChange) {
+			return false, curVersion, nil
+		}
+		return false, 0, err
+	}
+
+	return nextVersion > curVersion, nextVersion, nil
+}
+
+func (m *Migrator) getNextVersion(curVersion uint) (uint, error) {
+	err := m.Migrate.Steps(1)
+	if err != nil {
+		if errors.Is(err, migrate.ErrNoChange) || errors.Is(err, migrate.ErrShortLimit{}) {
+			return curVersion, migrate.ErrNoChange
+		}
+		return 0, err
+	}
+
+	newVersion, _, err := m.Migrate.Version()
+	if err != nil {
+		return 0, err
+	}
+
+	if err := m.Migrate.Migrate(curVersion); err != nil {
+		return 0, fmt.Errorf("failed to rollback to original version: %w", err)
+	}
+
+	return newVersion, nil
+}
+
+// Close closes the source and the database.
+func (m *Migrator) Close() error {
+	sourceErr, dbErr := m.Migrate.Close()
+	if sourceErr != nil {
+		return fmt.Errorf("failed to close source: %w", sourceErr)
+	}
+
+	if dbErr != nil {
+		return fmt.Errorf("failed to close database: %w", dbErr)
+	}
+
+	return nil
+}