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/posts.go | 58 ++++++++++++++++++++++++------------------------- 1 file changed, 28 insertions(+), 30 deletions(-) (limited to 'internal/store/posts.go') diff --git a/internal/store/posts.go b/internal/store/posts.go index 9f3c4f9..94bc856 100644 --- a/internal/store/posts.go +++ b/internal/store/posts.go @@ -31,8 +31,8 @@ func scanPost(row interface{ Scan(...any) error }) (*Post, error) { return &p, nil } -func (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) { - rows, err := s.db.Query(ctx, q, args...) +func (bs *BlogStore) collectPosts(ctx context.Context, q string, args ...any) ([]Post, error) { + rows, err := bs.db.Query(ctx, q, args...) if err != nil { return nil, err } @@ -48,21 +48,21 @@ func (s *Store) collectPosts(ctx context.Context, q string, args ...any) ([]Post return out, rows.Err() } -// ListPosts returns all posts of a blog for the dashboard, optionally filtered by page. -func (s *Store) ListPosts(ctx context.Context, blogID int64, pageID int64) ([]Post, error) { - return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND ($2=0 OR p.page_id=$2) ORDER BY p.created_at DESC, p.id DESC`, blogID, pageID) +// ListPosts returns all posts of the blog for the dashboard, optionally filtered by page. +func (bs *BlogStore) ListPosts(ctx context.Context, pageID int64) ([]Post, error) { + return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id + WHERE ($1=0 OR p.page_id=$1) ORDER BY p.created_at DESC, p.id DESC`, pageID) } // PublishedPosts returns a page of published posts for the public site. -func (s *Store) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) { - posts, err := s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id +func (bs *BlogStore) PublishedPosts(ctx context.Context, pageID int64, limit, offset int) ([]Post, int, error) { + posts, err := bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.page_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2 OFFSET $3`, pageID, limit, offset) if err != nil { return nil, 0, err } var total int - err = s.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total) + err = bs.db.QueryRow(ctx, `SELECT count(*) FROM posts WHERE page_id=$1 AND published`, pageID).Scan(&total) return posts, total, err } @@ -74,10 +74,10 @@ type PostRef struct { CreatedAt time.Time } -// PublishedPostIndex lists every published post of a blog, newest first, without bodies. -func (s *Store) PublishedPostIndex(ctx context.Context, blogID int64) ([]PostRef, error) { - rows, err := s.db.Query(ctx, `SELECT p.title, p.slug, g.slug, p.created_at FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC`, blogID) +// PublishedPostIndex lists every published post of the blog, newest first, without bodies. +func (bs *BlogStore) PublishedPostIndex(ctx context.Context) ([]PostRef, error) { + rows, err := bs.db.Query(ctx, `SELECT p.title, p.slug, g.slug, p.created_at FROM posts p JOIN pages g ON g.id=p.page_id + WHERE p.published ORDER BY p.created_at DESC, p.id DESC`) if err != nil { return nil, err } @@ -93,40 +93,38 @@ func (s *Store) PublishedPostIndex(ctx context.Context, blogID int64) ([]PostRef return out, rows.Err() } -// RecentPublishedPosts returns the newest published posts across a whole blog (for feeds). -func (s *Store) RecentPublishedPosts(ctx context.Context, blogID int64, limit int) ([]Post, error) { - return s.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $2`, blogID, limit) +// RecentPublishedPosts returns the newest published posts across the whole blog (for feeds). +func (bs *BlogStore) RecentPublishedPosts(ctx context.Context, limit int) ([]Post, error) { + return bs.collectPosts(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id + WHERE p.published ORDER BY p.created_at DESC, p.id DESC LIMIT $1`, limit) } -func (s *Store) PostByID(ctx context.Context, blogID, id int64) (*Post, error) { - return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id - WHERE g.blog_id=$1 AND p.id=$2`, blogID, id)) +func (bs *BlogStore) PostByID(ctx context.Context, id int64) (*Post, error) { + return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id)) } -func (s *Store) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) { - return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id +func (bs *BlogStore) PublishedPostBySlug(ctx context.Context, pageID int64, slug string) (*Post, error) { + return scanPost(bs.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.page_id=$1 AND p.slug=$2 AND p.published`, pageID, slug)) } -func (s *Store) CreatePost(ctx context.Context, p *Post) (*Post, error) { +func (bs *BlogStore) CreatePost(ctx context.Context, p *Post) (*Post, error) { var id int64 - err := s.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`, + err := bs.db.QueryRow(ctx, `INSERT INTO posts (page_id, slug, title, body_md, body_html, published) VALUES ($1,$2,$3,$4,$5,$6) RETURNING id`, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published).Scan(&id) if err != nil { return nil, wrap(err) } - return scanPost(s.db.QueryRow(ctx, `SELECT `+postCols+` FROM posts p JOIN pages g ON g.id=p.page_id WHERE p.id=$1`, id)) + return bs.PostByID(ctx, id) } -// UpdatePost updates a post; the page must belong to the same blog (checked by handler). -func (s *Store) UpdatePost(ctx context.Context, p *Post) error { - _, err := s.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, published=$7, updated_at=now() WHERE id=$1`, +func (bs *BlogStore) UpdatePost(ctx context.Context, p *Post) error { + _, err := bs.db.Exec(ctx, `UPDATE posts SET page_id=$2, slug=$3, title=$4, body_md=$5, body_html=$6, published=$7, updated_at=now() WHERE id=$1`, p.ID, p.PageID, p.Slug, p.Title, p.BodyMD, p.BodyHTML, p.Published) return wrap(err) } -func (s *Store) DeletePost(ctx context.Context, blogID, id int64) error { - _, err := s.db.Exec(ctx, `DELETE FROM posts p USING pages g WHERE g.id=p.page_id AND g.blog_id=$1 AND p.id=$2`, blogID, id) +func (bs *BlogStore) DeletePost(ctx context.Context, id int64) error { + _, err := bs.db.Exec(ctx, `DELETE FROM posts WHERE id=$1`, id) return err } -- cgit v1.2.3