Give every blog its own Postgres database

A blog is now a database of its own (blog_<sub>) 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 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Sd8UPWrvyYCLj97JexNw3A

1 files changed, 170 insertions, 12 deletions

diff --git a/internal/db/db.go b/internal/db/db.go
index 44d489b..601680d 100644
--- a/internal/db/db.go
+++ b/internal/db/db.go
@@ -1,22 +1,45 @@
-// Package db opens the Postgres pool and applies embedded migrations.
+// Package db opens the Postgres pools and applies the embedded migrations.
+//
+// There are two kinds of database: the control database (users and the blog
+// registry, named by DATABASE_URL) and one database per blog on the same
+// server, created by the app. A Cluster hands out pools for both.
 package db
 
 import (
 	"context"
 	"database/sql"
 	"embed"
+	"errors"
 	"fmt"
+	"io/fs"
+	"net/url"
+	"regexp"
+	"strings"
+	"sync"
+	"time"
 
+	"github.com/jackc/pgx/v5"
+	"github.com/jackc/pgx/v5/pgconn"
 	"github.com/jackc/pgx/v5/pgxpool"
 	_ "github.com/jackc/pgx/v5/stdlib"
 	"github.com/pressly/goose/v3"
 )
 
-//go:embed migrations/*.sql
+//go:embed migrations/control/*.sql migrations/blog/*.sql
 var migrations embed.FS
 
-func Open(ctx context.Context, url string) (*pgxpool.Pool, error) {
-	pool, err := pgxpool.New(ctx, url)
+// Cluster is the connection to one Postgres server: the control pool plus a
+// lazily opened, cached pool per blog database.
+type Cluster struct {
+	controlURL string
+	control    *pgxpool.Pool
+	mu         sync.Mutex
+	blogs      map[string]*pgxpool.Pool
+}
+
+// Open connects to the control database.
+func Open(ctx context.Context, controlURL string) (*Cluster, error) {
+	pool, err := pgxpool.New(ctx, controlURL)
 	if err != nil {
 		return nil, fmt.Errorf("connect: %w", err)
 	}
@@ -24,20 +47,155 @@ func Open(ctx context.Context, url string) (*pgxpool.Pool, error) {
 		pool.Close()
 		return nil, fmt.Errorf("ping: %w", err)
 	}
-	return pool, nil
+	return &Cluster{controlURL: controlURL, control: pool, blogs: map[string]*pgxpool.Pool{}}, nil
 }
 
-// Migrate applies all pending migrations using goose over database/sql.
-func Migrate(ctx context.Context, url string) error {
-	sqldb, err := sql.Open("pgx", url)
+func (c *Cluster) Control() *pgxpool.Pool { return c.control }
+
+// Blog returns the pool for a blog database, opening it on first use. Blog
+// pools are small and drop idle connections quickly, so a hundred quiet blogs
+// cost nothing; only the busy ones hold connections.
+func (c *Cluster) Blog(ctx context.Context, dbName string) (*pgxpool.Pool, error) {
+	c.mu.Lock()
+	defer c.mu.Unlock()
+	if p, ok := c.blogs[dbName]; ok {
+		return p, nil
+	}
+	cfg, err := pgxpool.ParseConfig(c.BlogURL(dbName))
+	if err != nil {
+		return nil, err
+	}
+	cfg.MaxConns = 4
+	cfg.MinConns = 0
+	cfg.MaxConnIdleTime = 2 * time.Minute
+	p, err := pgxpool.NewWithConfig(ctx, cfg)
+	if err != nil {
+		return nil, err
+	}
+	c.blogs[dbName] = p
+	return p, nil
+}
+
+// forget closes and drops the cached pool for a blog database, if any.
+func (c *Cluster) forget(dbName string) {
+	c.mu.Lock()
+	p, ok := c.blogs[dbName]
+	delete(c.blogs, dbName)
+	c.mu.Unlock()
+	if ok {
+		p.Close()
+	}
+}
+
+// Close closes every pool.
+func (c *Cluster) Close() {
+	c.mu.Lock()
+	defer c.mu.Unlock()
+	for name, p := range c.blogs {
+		p.Close()
+		delete(c.blogs, name)
+	}
+	c.control.Close()
+}
+
+// BlogURL is the control URL pointed at another database on the same server.
+func (c *Cluster) BlogURL(dbName string) string { return withDatabase(c.controlURL, dbName) }
+
+func withDatabase(dsn, dbName string) string {
+	u, err := url.Parse(dsn)
+	if err != nil {
+		return dsn
+	}
+	u.Path = "/" + dbName
+	return u.String()
+}
+
+var dbNameRe = regexp.MustCompile(`^[a-z0-9_]{1,63}$`)
+
+// DBName is the database a blog lives in: "blog_" + subdomain, dashes as
+// underscores so the name needs no quoting in psql or pg_dump. Subdomains only
+// allow [a-z0-9-], so the mapping is one-to-one.
+func DBName(subdomain string) string { return "blog_" + strings.ReplaceAll(subdomain, "-", "_") }
+
+// ErrDatabaseExists is returned by CreateBlogDB when the name is taken — a
+// leftover from a deleted blog or a failed attempt that must be dropped by hand.
+var ErrDatabaseExists = errors.New("database already exists")
+
+// CreateBlogDB creates an empty blog database and applies the blog migrations.
+// CREATE DATABASE cannot run inside a transaction, so this always uses its own
+// connection; callers doing registry work in a transaction must order it so a
+// failure here rolls the transaction back.
+func (c *Cluster) CreateBlogDB(ctx context.Context, dbName string) error {
+	if !dbNameRe.MatchString(dbName) {
+		return fmt.Errorf("invalid database name %q", dbName)
+	}
+	if err := createDatabase(ctx, c.control, dbName); err != nil {
+		return err
+	}
+	if err := MigrateBlog(ctx, c.BlogURL(dbName)); err != nil {
+		c.control.Exec(ctx, `DROP DATABASE IF EXISTS `+pgx.Identifier{dbName}.Sanitize()+` WITH (FORCE)`)
+		return fmt.Errorf("migrate %s: %w", dbName, err)
+	}
+	return nil
+}
+
+// execer is the pool or connection createDatabase runs on.
+type execer interface {
+	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
+}
+
+func createDatabase(ctx context.Context, db execer, dbName string) error {
+	_, err := db.Exec(ctx, `CREATE DATABASE `+pgx.Identifier{dbName}.Sanitize())
+	var pgErr *pgconn.PgError
+	if errors.As(err, &pgErr) && pgErr.Code == "42P04" { // duplicate_database
+		return fmt.Errorf("%w: %s", ErrDatabaseExists, dbName)
+	}
+	if err != nil {
+		return fmt.Errorf("create database %s: %w", dbName, err)
+	}
+	return nil
+}
+
+// DropBlogDB closes the blog's pool and drops its database, kicking any other
+// session still connected to it.
+func (c *Cluster) DropBlogDB(ctx context.Context, dbName string) error {
+	if !dbNameRe.MatchString(dbName) {
+		return fmt.Errorf("invalid database name %q", dbName)
+	}
+	c.forget(dbName)
+	_, err := c.control.Exec(ctx, `DROP DATABASE IF EXISTS `+pgx.Identifier{dbName}.Sanitize()+` WITH (FORCE)`)
+	return err
+}
+
+// ---- migrations ------------------------------------------------------------
+
+// MigrateControl applies the control database migrations, including the Go
+// migration that moves content out into the blog databases.
+func MigrateControl(ctx context.Context, controlURL string) error {
+	return migrate(ctx, controlURL, "migrations/control", goose.WithGoMigrations(splitMigration(controlURL)))
+}
+
+// MigrateBlog applies the blog schema migrations to one blog database.
+func MigrateBlog(ctx context.Context, blogURL string) error {
+	return migrate(ctx, blogURL, "migrations/blog")
+}
+
+// migrate runs goose over database/sql. A Provider (rather than the package
+// globals) keeps the two migration sets, and the Go migration, apart.
+func migrate(ctx context.Context, dsn, dir string, opts ...goose.ProviderOption) error {
+	sqldb, err := sql.Open("pgx", dsn)
 	if err != nil {
 		return err
 	}
 	defer sqldb.Close()
-	goose.SetBaseFS(migrations)
-	goose.SetLogger(goose.NopLogger())
-	if err := goose.SetDialect("postgres"); err != nil {
+	fsys, err := fs.Sub(migrations, dir)
+	if err != nil {
+		return err
+	}
+	p, err := goose.NewProvider(goose.DialectPostgres, sqldb, fsys, opts...)
+	if err != nil {
 		return err
 	}
-	return goose.UpContext(ctx, sqldb, "migrations")
+	_, err = p.Up(ctx)
+	return err
 }