gohttpserver/internal/database/database.go

package database

import (
	"context"
	"database/sql"
	"log/slog"
	"time"

	"git.eeqj.de/sneak/gohttpserver/internal/config"
	"git.eeqj.de/sneak/gohttpserver/internal/logger"
	"git.eeqj.de/sneak/gohttpserver/internal/models"
	"go.uber.org/fx"

	// spooky action at a distance!
	// this populates the environment
	// from a ./.env file automatically
	// for development configuration.
	// .env contents should be things like
	// `DBURL=postgres://user:pass@.../`
	// (without the backticks, of course)
	_ "github.com/joho/godotenv/autoload"

	// pure Go SQLite driver
	_ "modernc.org/sqlite"
)

type DatabaseParams struct {
	fx.In
	Logger *logger.Logger
	Config *config.Config
}

type Database struct {
	db     *sql.DB
	log    *slog.Logger
	params *DatabaseParams
}

func New(lc fx.Lifecycle, params DatabaseParams) (*Database, error) {
	s := new(Database)
	s.params = &params
	s.log = params.Logger.Get()

	s.log.Info("Database instantiated")

	lc.Append(fx.Hook{
		OnStart: func(ctx context.Context) error {
			s.log.Info("Database OnStart Hook")
			return s.connect(ctx)
		},
		OnStop: func(ctx context.Context) error {
			s.log.Info("Database OnStop Hook")
			if s.db != nil {
				return s.db.Close()
			}
			return nil
		},
	})
	return s, nil
}

func (s *Database) connect(ctx context.Context) error {
	dbURL := s.params.Config.DBURL
	if dbURL == "" {
		dbURL = "file:./data.db?_journal_mode=WAL"
	}

	s.log.Info("connecting to database", "url", dbURL)

	db, err := sql.Open("sqlite", dbURL)
	if err != nil {
		s.log.Error("failed to open database", "error", err)
		return err
	}

	if err := db.PingContext(ctx); err != nil {
		s.log.Error("failed to ping database", "error", err)
		return err
	}

	s.db = db
	s.log.Info("database connected")

	return s.createSchema(ctx)
}

func (s *Database) createSchema(ctx context.Context) error {
	schema := `
	CREATE TABLE IF NOT EXISTS posts (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		title TEXT NOT NULL,
		body TEXT NOT NULL,
		author TEXT NOT NULL,
		published INTEGER DEFAULT 0,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
		updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
	)`

	_, err := s.db.ExecContext(ctx, schema)
	if err != nil {
		s.log.Error("failed to create schema", "error", err)
		return err
	}

	s.log.Info("database schema initialized")
	return nil
}

func (s *Database) CreatePost(ctx context.Context, req *models.CreatePostRequest) (*models.Post, error) {
	now := time.Now()
	result, err := s.db.ExecContext(ctx,
		`INSERT INTO posts (title, body, author, published, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)`,
		req.Title, req.Body, req.Author, req.Published, now, now,
	)
	if err != nil {
		return nil, err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return nil, err
	}

	return s.GetPost(ctx, id)
}

func (s *Database) GetPost(ctx context.Context, id int64) (*models.Post, error) {
	post := &models.Post{}
	err := s.db.QueryRowContext(ctx,
		`SELECT id, title, body, author, published, created_at, updated_at FROM posts WHERE id = ?`,
		id,
	).Scan(&post.ID, &post.Title, &post.Body, &post.Author, &post.Published, &post.CreatedAt, &post.UpdatedAt)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	return post, nil
}

func (s *Database) ListPosts(ctx context.Context) ([]*models.Post, error) {
	rows, err := s.db.QueryContext(ctx,
		`SELECT id, title, body, author, published, created_at, updated_at FROM posts ORDER BY created_at DESC`,
	)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var posts []*models.Post
	for rows.Next() {
		post := &models.Post{}
		if err := rows.Scan(&post.ID, &post.Title, &post.Body, &post.Author, &post.Published, &post.CreatedAt, &post.UpdatedAt); err != nil {
			return nil, err
		}
		posts = append(posts, post)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return posts, nil
}

func (s *Database) UpdatePost(ctx context.Context, id int64, req *models.UpdatePostRequest) (*models.Post, error) {
	post, err := s.GetPost(ctx, id)
	if err != nil {
		return nil, err
	}
	if post == nil {
		return nil, nil
	}

	if req.Title != nil {
		post.Title = *req.Title
	}
	if req.Body != nil {
		post.Body = *req.Body
	}
	if req.Author != nil {
		post.Author = *req.Author
	}
	if req.Published != nil {
		post.Published = *req.Published
	}
	post.UpdatedAt = time.Now()

	_, err = s.db.ExecContext(ctx,
		`UPDATE posts SET title = ?, body = ?, author = ?, published = ?, updated_at = ? WHERE id = ?`,
		post.Title, post.Body, post.Author, post.Published, post.UpdatedAt, id,
	)
	if err != nil {
		return nil, err
	}

	return post, nil
}

func (s *Database) DeletePost(ctx context.Context, id int64) error {
	_, err := s.db.ExecContext(ctx, `DELETE FROM posts WHERE id = ?`, id)
	return err
}