From aeb19df4222269c585de55be5568326222df879d Mon Sep 17 00:00:00 2001 From: grm Date: Sun, 13 Sep 2026 23:52:41 +0300 Subject: Give every blog its own Postgres database A blog is now a database of its own (blog_) on the same server: one pg_dump is a complete backup of a blog, one psql restores it, and nothing a blog's queries do can reach another blog's rows. The control database (DATABASE_URL) keeps only users and the blog registry (id, owner, subdomain, db_name); title, tagline and theme move into a one-row settings table next to the content so the dump really is everything. db.Cluster holds the control pool plus small, lazily opened per-blog pools. store.Store (control) hands out a store.BlogStore per blog; every blog_id parameter and column is gone, the database is the scope. Handlers reach it through blogStore(r), which resolveBlog puts in the context next to the blog. Existing data is moved in place by control migration 00006, a Go migration that runs inside the control transaction: it creates and migrates each blog database, copies the rows preserving ids, and marks the registry; 00007 then drops the old tables. Either every blog is moved or the control database is untouched. /media/{id} now serves the host's blog only, so dashboard previews on the root domain use /b/{sub}/media/{id}. Subdomains are capped at 58 chars so "blog_" + name fits a Postgres identifier. Deleting a user drops their database. Store.Open resets a blog's pool and retries once so a database restored under a running app (dropdb --force, createdb, psql) just works. Co-Authored-By: Claude Opus 5 Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A --- internal/store/blogs.go | 118 ++++++++++++++++++++++++++++++++++++++---------- 1 file changed, 94 insertions(+), 24 deletions(-) (limited to 'internal/store/blogs.go') diff --git a/internal/store/blogs.go b/internal/store/blogs.go index b7edee0..a2e88a8 100644 --- a/internal/store/blogs.go +++ b/internal/store/blogs.go @@ -3,34 +3,52 @@ package store import ( "context" "encoding/json" + "errors" + "fmt" "time" + "github.com/gramanas/blogspace/internal/db" "github.com/jackc/pgx/v5" ) +// Blog is a registry row (control database) plus the settings row of the +// blog's own database, which Store.Open fills in. type Blog struct { ID int64 OwnerID int64 Subdomain string + DBName string + CreatedAt time.Time + // from the blog database Title string Tagline string ThemeJSON json.RawMessage - CreatedAt time.Time UpdatedAt time.Time } -const blogCols = `id, owner_id, subdomain, title, tagline, theme, created_at, updated_at` +const blogCols = `id, owner_id, subdomain, db_name, created_at` func scanBlog(row interface{ Scan(...any) error }) (*Blog, error) { var b Blog - err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.Title, &b.Tagline, &b.ThemeJSON, &b.CreatedAt, &b.UpdatedAt) + err := row.Scan(&b.ID, &b.OwnerID, &b.Subdomain, &b.DBName, &b.CreatedAt) if err != nil { return nil, wrap(err) } return &b, nil } -// CreateBlogger creates a user, their blog and a default home page in one transaction. +func (bs *BlogStore) loadSettings(ctx context.Context, b *Blog) error { + err := bs.db.QueryRow(ctx, `SELECT title, tagline, theme, updated_at FROM settings`).Scan(&b.Title, &b.Tagline, &b.ThemeJSON, &b.UpdatedAt) + if errors.Is(err, pgx.ErrNoRows) { // registered, but the database is empty: not a 404 + return fmt.Errorf("database %s of blog %q has no settings row (empty or wrong dump?)", b.DBName, b.Subdomain) + } + if err != nil { + return fmt.Errorf("settings of %s: %w", b.DBName, err) + } + return nil +} + +// CreateBlogger creates a user, their blog and a default home page. func (s *Store) CreateBlogger(ctx context.Context, username, passwordHash, subdomain, title string) (*User, *Blog, error) { tx, err := s.db.Begin(ctx) if err != nil { @@ -43,13 +61,10 @@ func (s *Store) CreateBlogger(ctx context.Context, username, passwordHash, subdo if err != nil { return nil, nil, err } - b, err := createBlog(ctx, tx, u.ID, subdomain, title) + b, err := s.createBlog(ctx, tx, u.ID, subdomain, title) if err != nil { return nil, nil, err } - if err := tx.Commit(ctx); err != nil { - return nil, nil, err - } return u, b, nil } @@ -60,30 +75,64 @@ func (s *Store) CreateBlog(ctx context.Context, ownerID int64, subdomain, title return nil, err } defer tx.Rollback(ctx) - b, err := createBlog(ctx, tx, ownerID, subdomain, title) + return s.createBlog(ctx, tx, ownerID, subdomain, title) +} + +// createBlog registers the blog in tx, then creates and fills its database and +// commits. The registry insert goes first so a taken subdomain fails before +// any DDL; if anything after that fails the transaction rolls back and the new +// database is dropped again. +func (s *Store) createBlog(ctx context.Context, tx pgx.Tx, ownerID int64, subdomain, title string) (*Blog, error) { + name := db.DBName(subdomain) + b, err := scanBlog(tx.QueryRow(ctx, `INSERT INTO blogs (owner_id, subdomain, db_name) VALUES ($1,$2,$3) RETURNING `+blogCols, + ownerID, subdomain, name)) + if err != nil { + return nil, err + } + if err := s.cluster.CreateBlogDB(ctx, name); err != nil { + return nil, err + } + bs, err := s.fillNewBlog(ctx, b, title) + if err == nil { + err = tx.Commit(ctx) + } if err != nil { + s.cluster.DropBlogDB(ctx, name) return nil, err } - return b, tx.Commit(ctx) + if err := bs.loadSettings(ctx, b); err != nil { + return nil, err + } + return b, nil } -func createBlog(ctx context.Context, tx pgx.Tx, ownerID int64, subdomain, title string) (*Blog, error) { - b, err := scanBlog(tx.QueryRow(ctx, `INSERT INTO blogs (owner_id, subdomain, title) VALUES ($1,$2,$3) RETURNING `+blogCols, - ownerID, subdomain, title)) +// fillNewBlog writes what every new blog starts with: its settings, a home +// page in the menu and the default layout. +func (s *Store) fillNewBlog(ctx context.Context, b *Blog, title string) (*BlogStore, error) { + pool, err := s.cluster.Blog(ctx, b.DBName) if err != nil { return nil, err } + bs := &BlogStore{db: pool} + btx, err := pool.Begin(ctx) + if err != nil { + return nil, err + } + defer btx.Rollback(ctx) + if _, err := btx.Exec(ctx, `INSERT INTO settings (title, created_at, updated_at) VALUES ($1,$2,$2)`, title, b.CreatedAt); err != nil { + return nil, err + } var homeID int64 - if err := tx.QueryRow(ctx, `INSERT INTO pages (blog_id, slug, title, nav_order, is_home) VALUES ($1,'home','Home',0,true) RETURNING id`, b.ID).Scan(&homeID); err != nil { + if err := btx.QueryRow(ctx, `INSERT INTO pages (slug, title, nav_order, is_home) VALUES ('home','Home',0,true) RETURNING id`).Scan(&homeID); err != nil { return nil, wrap(err) } - if err := addMenuPage(ctx, tx, b.ID, homeID); err != nil { + if err := addMenuPage(ctx, btx, homeID); err != nil { return nil, err } - if err := insertDefaultModules(ctx, tx, b.ID); err != nil { + if err := insertDefaultModules(ctx, btx); err != nil { return nil, err } - return b, nil + return bs, btx.Commit(ctx) } func (s *Store) BlogByID(ctx context.Context, id int64) (*Blog, error) { @@ -98,17 +147,38 @@ func (s *Store) BlogByOwner(ctx context.Context, ownerID int64) (*Blog, error) { return scanBlog(s.db.QueryRow(ctx, `SELECT `+blogCols+` FROM blogs WHERE owner_id=$1`, ownerID)) } -func (s *Store) UpdateBlogSettings(ctx context.Context, id int64, title, tagline string) error { - _, err := s.db.Exec(ctx, `UPDATE blogs SET title=$2, tagline=$3, updated_at=now() WHERE id=$1`, id, title, tagline) - return err +// ListBlogs returns every registered blog, oldest first. +func (s *Store) ListBlogs(ctx context.Context) ([]Blog, error) { + rows, err := s.db.Query(ctx, `SELECT `+blogCols+` FROM blogs ORDER BY id`) + if err != nil { + return nil, err + } + defer rows.Close() + var out []Blog + for rows.Next() { + b, err := scanBlog(rows) + if err != nil { + return nil, err + } + out = append(out, *b) + } + return out, rows.Err() +} + +// DeleteBlog unregisters the blog and drops its database. +func (s *Store) DeleteBlog(ctx context.Context, b *Blog) error { + if _, err := s.db.Exec(ctx, `DELETE FROM blogs WHERE id=$1`, b.ID); err != nil { + return err + } + return s.cluster.DropBlogDB(ctx, b.DBName) } -func (s *Store) UpdateBlogTheme(ctx context.Context, id int64, theme json.RawMessage) error { - _, err := s.db.Exec(ctx, `UPDATE blogs SET theme=$2, updated_at=now() WHERE id=$1`, id, theme) +func (bs *BlogStore) UpdateSettings(ctx context.Context, title, tagline string) error { + _, err := bs.db.Exec(ctx, `UPDATE settings SET title=$1, tagline=$2, updated_at=now()`, title, tagline) return err } -func (s *Store) DeleteBlog(ctx context.Context, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM blogs WHERE id=$1`, id) +func (bs *BlogStore) UpdateTheme(ctx context.Context, theme json.RawMessage) error { + _, err := bs.db.Exec(ctx, `UPDATE settings SET theme=$1, updated_at=now()`, theme) return err } -- cgit v1.2.3