commit 15e95722dcb0bf2b93123299e814dd4d9e4c1fdf
parent ee0fe62ca4f011b5e05365adec12964970e70e22
Author: brookjeynes <me@brookjeynes.dev>
Date: Sat, 2 May 2026 13:17:35 +1000
feat(db): add db
Signed-off-by: brookjeynes <me@brookjeynes.dev>
Diffstat:
11 files changed, 232 insertions(+), 28 deletions(-)
diff --git a/.gitignore b/.gitignore
@@ -1,3 +1,5 @@
.env
*/**/*templ.go
static/files
+*.db
+*.db-*
diff --git a/go.mod b/go.mod
@@ -7,6 +7,7 @@ require (
github.com/charmbracelet/log v1.0.0
github.com/go-chi/chi/v5 v5.2.5
github.com/gorilla/schema v1.4.1
+ github.com/mattn/go-sqlite3 v1.14.44
github.com/sethvargo/go-envconfig v1.3.0
)
diff --git a/go.sum b/go.sum
@@ -45,6 +45,8 @@ github.com/mattn/go-isatty v0.0.20 h1:xfD0iDuEKnDkl03q4limB+vH+GxLEtL/jb4xVJSWWE
github.com/mattn/go-isatty v0.0.20/go.mod h1:W+V8PltTTMOvKvAeJH7IuucS94S2C6jfK/D7dTCTo3Y=
github.com/mattn/go-runewidth v0.0.16 h1:E5ScNMtiwvlvB5paMFdw9p4kSQzbXFikJ5SQO6TULQc=
github.com/mattn/go-runewidth v0.0.16/go.mod h1:Jdepj2loyihRzMpdS35Xk/zdY8IAYHsh153qUoGf23w=
+github.com/mattn/go-sqlite3 v1.14.44 h1:3VSe+xafpbzsLbdr2AWlAZk9yRHiBhTBakioXaCKTF8=
+github.com/mattn/go-sqlite3 v1.14.44/go.mod h1:pjEuOr8IwzLJP2MfGeTb0A35jauH+C2kbHKBr7yXKVQ=
github.com/muesli/termenv v0.16.0 h1:S5AlUN9dENB57rsbnkPyfdGuWIlkmzJjbFf0Tf5FWUc=
github.com/muesli/termenv v0.16.0/go.mod h1:ZRfOIKPFDYQoDFF4Olj7/QJbW60Ol/kL1pU3VfY/Cnk=
github.com/natefinch/atomic v1.0.1 h1:ZPYKxkqQOx3KZ+RsbnP/YsgvxWQPGxjC0oBt2AhwV0A=
diff --git a/internal/config/config.go b/internal/config/config.go
@@ -9,6 +9,7 @@ import (
type CoreConfig struct {
Dev bool `env:"DEV, default=false"`
ListenAddr string `env:"PORT, default=0.0.0.0:8080"`
+ DbPath string `env:"DB_PATH, default=appview.db"`
}
type Config struct {
diff --git a/internal/db/db.go b/internal/db/db.go
@@ -0,0 +1,173 @@
+package db
+
+import (
+ "context"
+ "database/sql"
+ "fmt"
+ "log/slog"
+ "lyrics/internal/log"
+ "strings"
+
+ _ "github.com/mattn/go-sqlite3"
+)
+
+type DB struct {
+ *sql.DB
+ logger *slog.Logger
+}
+
+type Execer interface {
+ Query(query string, args ...any) (*sql.Rows, error)
+ QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
+ QueryRow(query string, args ...any) *sql.Row
+ QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
+ Exec(query string, args ...any) (sql.Result, error)
+ ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
+ Prepare(query string) (*sql.Stmt, error)
+ PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
+}
+
+func Make(ctx context.Context, dbPath string) (*DB, error) {
+ // https://github.com/mattn/go-sqlite3#connection-string
+ opts := []string{
+ "_foreign_keys=1",
+ "_journal_mode=WAL",
+ "_synchronous=NORMAL",
+ "_auto_vacuum=incremental",
+ }
+
+ logger := log.FromContext(ctx)
+ logger = log.SubLogger(logger, "db")
+
+ db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
+ if err != nil {
+ return nil, fmt.Errorf("failed to open db: %w", err)
+ }
+
+ conn, err := db.Conn(ctx)
+ if err != nil {
+ return nil, err
+ }
+ defer conn.Close()
+
+ logger.Debug("creating tables")
+
+ tx, err := conn.BeginTx(ctx, nil)
+ if err != nil {
+ return nil, err
+ }
+ defer tx.Rollback()
+
+ _, err = tx.ExecContext(ctx, `
+ create table if not exists songs (
+ id integer primary key autoincrement,
+ title text not null,
+ lyrics text not null,
+ release_date text not null,
+ created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
+ );
+ `)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create songs table: %w", err)
+ }
+
+ _, err = tx.ExecContext(ctx, `
+ create table if not exists artists (
+ id integer primary key autoincrement,
+ name text not null,
+ bio text not null,
+ created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
+ );
+ `)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create artists table: %w", err)
+ }
+
+ _, err = tx.ExecContext(ctx, `
+ create table if not exists albums (
+ id integer primary key autoincrement,
+ title text not null,
+ release_date text not null,
+ created_at text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
+
+ unique(title)
+ );
+ `)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create albums table: %w", err)
+ }
+
+ _, err = tx.ExecContext(ctx, `
+ create table if not exists album_artists (
+ album_id integer not null,
+ artist_id integer not null,
+ sort_order integer not null,
+
+ unique(album_id, sort_order),
+ foreign key (album_id) references albums(id) on delete cascade,
+ foreign key (artist_id) references artists(id) on delete cascade,
+ primary key (album_id, artist_id)
+ );
+ `)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create album_artists table: %w", err)
+ }
+
+ _, err = tx.ExecContext(ctx, `
+ create table if not exists song_artists (
+ song_id integer not null,
+ artist_id integer not null,
+ role text not null,
+ sort_order integer not null,
+
+ unique(song_id, sort_order),
+ foreign key (song_id) references songs(id) on delete cascade,
+ foreign key (artist_id) references artists(id) on delete cascade,
+ primary key (song_id, artist_id, role)
+ );
+ `)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create song_artists table: %w", err)
+ }
+
+ _, err = tx.ExecContext(ctx, `
+ create table if not exists song_genres (
+ song_id integer not null,
+ genre text not null,
+
+ foreign key (song_id) references songs(id) on delete cascade,
+ primary key (song_id, genre)
+ );
+ `)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create song_genres table: %w", err)
+ }
+
+ _, err = tx.ExecContext(ctx, `
+ create table if not exists song_albums (
+ song_id integer not null,
+ album_id integer not null,
+ track_number integer not null,
+
+ unique(album_id, track_number),
+ foreign key (song_id) references songs(id) on delete cascade,
+ foreign key (album_id) references albums(id) on delete cascade,
+ primary key (song_id, album_id)
+ );
+ `)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create song_albums table: %w", err)
+ }
+
+ if err = tx.Commit(); err != nil {
+ return nil, fmt.Errorf("failed to commit db create operations: %w", err)
+ }
+
+ logger.Debug("successfully created tables")
+
+ return &DB{db, logger}, nil
+}
+
+func (d *DB) Close() error {
+ return d.DB.Close()
+}
diff --git a/internal/domain/album.go b/internal/domain/album.go
@@ -0,0 +1,16 @@
+package domain
+
+import "time"
+
+type Album struct {
+ ID int64
+ Title string
+ ReleaseDate time.Time
+ Artists []AlbumArtist
+}
+
+type AlbumArtist struct {
+ AlbumID int64
+ ArtistID int64
+ SortOrder int
+}
diff --git a/internal/domain/artist.go b/internal/domain/artist.go
@@ -3,4 +3,5 @@ package domain
type Artist struct {
ID int64
Name string
+ Bio string
}
diff --git a/internal/domain/song-artist.go b/internal/domain/song-artist.go
@@ -1,15 +0,0 @@
-package domain
-
-type SongArtistRole string
-
-const (
- RolePrimary SongArtistRole = "primary"
- RoleFeatured SongArtistRole = "featured"
-)
-
-type SongArtist struct {
- SongID int64
- ArtistID int64
- Role SongArtistRole
- Order int
-}
diff --git a/internal/domain/song.go b/internal/domain/song.go
@@ -8,11 +8,25 @@ import (
var NoTrailingOrLeadingSpaceRegex = regexp.MustCompile(`^\S(\s*\S)*$`)
type Song struct {
- ID int64
- Title string
- Lyrics string
- Date time.Time
- Artists []SongArtist
- AlbumIDs []int64
- Genres []Genre
+ ID int64
+ Title string
+ Lyrics string
+ ReleaseDate time.Time
+ Artists []SongArtist
+ AlbumIDs []int64
+ Genres []Genre
+}
+
+type SongArtistRole string
+
+const (
+ RolePrimary SongArtistRole = "primary"
+ RoleFeatured SongArtistRole = "featured"
+)
+
+type SongArtist struct {
+ SongID int64
+ ArtistID int64
+ Role SongArtistRole
+ SortOrder int
}
diff --git a/internal/server/new-song.go b/internal/server/new-song.go
@@ -89,11 +89,11 @@ func parseNewSongForm(form NewSongForm) (domain.Song, error) {
}
song = domain.Song{
- Title: form.Title,
- Lyrics: form.Lyrics,
- Date: releaseDate,
- Genres: genres,
- AlbumIDs: albumIDs,
+ Title: form.Title,
+ Lyrics: form.Lyrics,
+ ReleaseDate: releaseDate,
+ Genres: genres,
+ AlbumIDs: albumIDs,
}
return song, nil
diff --git a/internal/server/server-state.go b/internal/server/server-state.go
@@ -2,27 +2,36 @@ package server
import (
"context"
+ "fmt"
"log/slog"
"lyrics/internal/config"
+ "lyrics/internal/db"
"lyrics/internal/log"
)
type ServerState struct {
config *config.Config
logger *slog.Logger
+ db *db.DB
}
func Make(ctx context.Context, config *config.Config) (*ServerState, error) {
logger := log.FromContext(ctx)
+ db, err := db.Make(ctx, config.Core.DbPath)
+ if err != nil {
+ return nil, fmt.Errorf("failed to create db: %w", err)
+ }
+
serverState := &ServerState{
config: config,
logger: logger,
+ db: db,
}
return serverState, nil
}
func (s *ServerState) Close() error {
- return nil
+ return s.db.Close()
}
