The database — Postgres, goose migrations, sqlc

Code

Wire the database in — main, handlers, home

Now connect the pieces. Three modifications, as tabs, following the data from its source outward: main.go → handlers.go → home.templ (start the DB, give handlers the queries, show the count). The diffs show only what changed; everything from lessons 1–2 is carried forward.

1. cmd/signflow/main.go (modify) — two steps slot in between config and serving: run the migrations, then open the pool.

if err := runMigrations(cfg.DatabaseURL, log); err != nil {   // goose Up, embedded
	return err
}
pool, err := db.Connect(ctx, cfg.DatabaseURL)                 // pgx pool
// ...
queries := db.New(pool)
h := handlers.New(cfg, queries, log)                          // ← now takes queries

runMigrations uses goose's database/sql handle (via the pgx stdlib driver) to apply the embedded migrations, then the app runs on a pgx pool. The static/serving/shutdown code below is unchanged.

2. internal/handlers/handlers.go (modify) — the struct gains a Queries field, New takes it, and Home reads the count:

count, err := h.Queries.CountUsers(r.Context())
// ...
render(w, r, http.StatusOK, web.Home(count))

Health now pings the DB too — an unreachable database reports 503 unhealthy instead of silently serving 500s.

3. internal/web/home.templ (modify) — Home grows a parameter and a line:

templ Home(userCount int64) {          // ← was Home()
	...
	Registered users in the database: <strong>{ strconv.FormatInt(userCount, 10) }</strong>

Verify. Create the database, then run (remember: templ generate for the home change, sqlc generate if you haven't):

createdb signflow
templ generate
go run ./cmd/signflow

Startup now logs the migration applying:

level=INFO msg="starting signflow" env=dev port=8080 base_url=http://localhost:8080
level=INFO msg=goose msg="OK   00001_users.sql"
level=INFO msg=listening addr=:8080

Open http://localhost:8080 — the status card now reads "Registered users in the database: 0", live from Postgres. Insert one by hand and reload:

$ psql signflow -c "INSERT INTO users (email, password_hash) VALUES ('a@b.co', 'x');"
INSERT 0 1

Reload the page → "Registered users in the database: 1". The count came from the database, through your sqlc-generated CountUsers, into a type-safe template. The whole stack is wired.

Gotcha (migration already applied). Run go run twice and the second start logs no OK 00001_users.sql — goose records applied migrations in a goose_db_version table and skips them. That's the point: migrations are apply-once and safe to run on every boot. To start clean, dropdb signflow && createdb signflow.

main.go MODIFY 57 lines changed

Edit this file in cmd/signflow/

// Command signflow is the HTTP server entrypoint.
//
// On startup it: loads config, runs embedded goose migrations, opens a pgx
// pool, builds the Chi router, and serves with graceful shutdown.
package main

import (
	"context"
	"errors"
	"fmt"
	"io/fs"
	"log/slog"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

	"github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"

	migrations "github.com/programuoki/signflow/db"
	"github.com/programuoki/signflow/internal/config"
	"github.com/programuoki/signflow/internal/db"
	"github.com/programuoki/signflow/internal/handlers"
	"github.com/programuoki/signflow/static"
)

func main() {
	log := slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{Level: slog.LevelInfo}))
	if err := run(log); err != nil {
		log.Error("fatal", "err", err)
		os.Exit(1)
	}
}

func run(log *slog.Logger) error {
	cfg, err := config.Load()
	if err != nil {
		return err
	}
	log.Info("starting signflow", "env", cfg.Env, "port", cfg.Port, "base_url", cfg.BaseURL)

	ctx := context.Background()

	// 1. Migrate. We use a throwaway database/sql handle (pgx stdlib driver)
	//    because goose speaks database/sql, then hand the app a pgx pool.
	if err := runMigrations(cfg.DatabaseURL, log); err != nil {
		return err
	}

	// 2. Connect the application pool the sqlc queries run against.
	pool, err := db.Connect(ctx, cfg.DatabaseURL)
	if err != nil {
		return err
	}
	defer pool.Close()

	queries := db.New(pool)
	h := handlers.New(cfg, queries, log)

	staticFS, err := fs.Sub(static.FS, "assets")
	if err != nil {
		return err
	}

	srv := &http.Server{
		Addr:              ":" + cfg.Port,
		Handler:           h.Router(staticFS),
		ReadHeaderTimeout: 10 * time.Second,
	}

	// Graceful shutdown: stop accepting on SIGINT/SIGTERM, then drain in-flight
	// requests for up to 15s before exiting.
	shutdownCtx, stop := signal.NotifyContext(ctx, os.Interrupt, syscall.SIGTERM)
	defer stop()

	go func() {
		log.Info("listening", "addr", srv.Addr)
		if err := srv.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
			log.Error("server error", "err", err)
			stop()
		}
	}()

	<-shutdownCtx.Done()
	log.Info("shutting down")

	timeout, cancel := context.WithTimeout(context.Background(), 15*time.Second)
	defer cancel()
	return srv.Shutdown(timeout)
}

// runMigrations applies all pending goose migrations from the embedded FS.
func runMigrations(databaseURL string, log *slog.Logger) error {
	sqlDB, err := goose.OpenDBWithDriver("pgx", databaseURL)
	if err != nil {
		return err
	}
	defer sqlDB.Close()

	goose.SetBaseFS(migrations.FS)
	goose.SetLogger(gooseLogger{log})
	if err := goose.SetDialect("postgres"); err != nil {
		return err
	}
	return goose.Up(sqlDB, "migrations")
}

// gooseLogger adapts slog to goose's logger interface.
type gooseLogger struct{ log *slog.Logger }

func (g gooseLogger) Fatalf(format string, v ...interface{}) {
	g.log.Error("goose", "msg", fmt.Sprintf(format, v...))
}
func (g gooseLogger) Printf(format string, v ...interface{}) {
	g.log.Info("goose", "msg", fmt.Sprintf(format, v...))
}

// ensure the pgx stdlib driver is registered for goose.OpenDBWithDriver("pgx", ...)
var _ = stdlib.GetDefaultDriver

handlers.go MODIFY 23 lines changed

Edit this file in internal/handlers/

// Package handlers holds the HTTP handlers. In this skeleton there is only the
// home page and a health check; later lessons add auth, documents, and signing.
package handlers

import (
	"log/slog"
	"net/http"

	"github.com/programuoki/signflow/internal/config"
	"github.com/programuoki/signflow/internal/db"
	"github.com/programuoki/signflow/internal/web"
)

// Handlers bundles the dependencies every handler needs. Constructed once in
// main; its methods are registered on the router. It gains a Queries field here
// (the database); sessions and more arrive in later lessons.
type Handlers struct {
	Cfg     config.Config
	Queries *db.Queries
	Log     *slog.Logger
}

func New(cfg config.Config, q *db.Queries, log *slog.Logger) *Handlers {
	return &Handlers{Cfg: cfg, Queries: q, Log: log}
}

// Home renders the landing page, including a live user count read from Postgres
// — proof the whole stack is wired end to end.
func (h *Handlers) Home(w http.ResponseWriter, r *http.Request) {
	count, err := h.Queries.CountUsers(r.Context())
	if err != nil {
		h.Log.Error("count users", "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}
	render(w, r, http.StatusOK, web.Home(count))
}

// Health is a liveness probe for Railway / uptime checks. It also pings the DB,
// so an unreachable database reports unhealthy rather than silently serving 500s.
func (h *Handlers) Health(w http.ResponseWriter, r *http.Request) {
	if _, err := h.Queries.CountUsers(r.Context()); err != nil {
		http.Error(w, "db unavailable", http.StatusServiceUnavailable)
		return
	}
	w.Header().Set("Content-Type", "text/plain")
	_, _ = w.Write([]byte("ok"))
}

home.templ MODIFY 10 lines changed

Edit this file in internal/web/

package web

import "strconv"

// Home is the public landing page. It also proves the full stack is wired: the
// user count comes from Postgres via sqlc.
templ Home(userCount int64) {
	@Layout("Home") {
		<section class="hero">
			<h1>Sign documents. Keep the receipts.</h1>
			<p class="lead">
				Upload a document, invite signers by email, and collect typed
				signatures. Every action lands in an append-only audit trail.
			</p>
			<div class="cta-row">
				<a class="btn btn-primary" href="/register">Get started</a>
				<a class="btn btn-ghost" href="/login">Log in</a>
			</div>
		</section>
		<section class="status-card">
			<h2>Skeleton status</h2>
			<ul class="checklist">
				<li>✅ Chi router + middleware</li>
				<li>✅ Templ rendering this page</li>
				<li>✅ Postgres connected via pgx</li>
				<li>✅ goose migrations applied</li>
			</ul>
			<p class="muted">
				Registered users in the database: <strong>{ strconv.FormatInt(userCount, 10) }</strong>
			</p>
		</section>
	}
}