The database — Postgres, goose migrations, sqlc

Code

Schema and migrations — the users table, embedding, config

First, the new dependencies:

go get github.com/jackc/pgx/v5 github.com/pressly/goose/v3 github.com/joho/godotenv

Three files as tabs, in dependency order: 00001_users.sql → db/embed.go → config.go (the schema, then embedding it, then teaching config where the database lives). The last is a modify of your lesson-1 config.

1. db/migrations/00001_users.sql — the first migration. The -- +goose markers tell goose where each direction begins:

-- +goose Up
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE users (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT        NOT NULL UNIQUE,
    password_hash TEXT        NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- +goose Down
DROP TABLE users;

Up builds it, Down reverses it. gen_random_uuid() (from pgcrypto) gives every row a UUID primary key — no guessable sequential IDs, which matters once documents belong to users. The email is UNIQUE; password_hash is filled in lesson 4.

2. db/embed.go — bake the migrations into the binary:

//go:embed migrations/*.sql
var FS embed.FS

This is what makes deployment self-migrating: the .sql files travel inside the compiled binary, and startup runs any that haven't been applied.

3. internal/config/config.go (modify) — config learns where the database is. The change: a DatabaseURL field, godotenv to read a local .env, and a production guard.

DatabaseURL: getenv("DATABASE_URL", "postgres://postgres@localhost:5432/signflow?sslmode=disable"),

The dev default connects to a local Postgres with no password (trust auth), so go run works with zero config. Everything from lesson 1 — Port, Env, BaseURL, IsProd — is carried forward unchanged; the diff is just the new field and the .env loading.

.env in dev. godotenv.Load() reads a .env file if present (and shrugs if not), so you can keep DATABASE_URL in a gitignored .env instead of exporting it every shell. In prod, Railway sets the real environment.

00001_users.sql CREATE

Create this file in db/migrations/

-- +goose Up
-- +goose StatementBegin
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT        NOT NULL UNIQUE,
    password_hash TEXT        NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE users;
-- +goose StatementEnd

embed.go CREATE

Create this file in db/

// Package migrations embeds the goose SQL migration files so they can be run at
// application startup without shipping the .sql files alongside the binary. This
// is what makes `railway up` self-migrating: a fresh deploy migrates itself.
package migrations

import "embed"

//go:embed migrations/*.sql
var FS embed.FS

config.go MODIFY 19 lines changed

Edit this file in internal/config/

// Package config loads runtime configuration from the environment.
//
// In development we lean on sensible defaults so you can `go run` the app with
// zero setup. In production every value is expected to come from the
// environment (Railway injects them). The struct GROWS as the app does — the
// database URL arrives in lesson 3, the session secret in lesson 4, and so on.
package config

import (
	"fmt"
	"os"

	"github.com/joho/godotenv"
)

type Config struct {
	// DatabaseURL is a standard libpq/pgx connection string.
	DatabaseURL string
	// Port is the TCP port the HTTP server listens on. Railway sets $PORT.
	Port string
	// Env is "dev" or "prod". Later it flips cookie Secure flags and the email
	// sender; for now it is just a label.
	Env string
	// BaseURL is the public origin used to build absolute links (in emails,
	// later). In dev it is http://localhost:PORT.
	BaseURL string
}

// IsProd reports whether we are running in production. One flag, read in many
// places later (Secure cookies, strict CSRF).
func (c Config) IsProd() bool { return c.Env == "prod" }

// Load reads configuration, applying dev-friendly defaults.
func Load() (Config, error) {
	_ = godotenv.Load() // best-effort; load a local .env if present, else shrug

	cfg := Config{
		DatabaseURL: getenv("DATABASE_URL", "postgres://postgres@localhost:5432/signflow?sslmode=disable"),
		Port:        getenv("PORT", "8080"),
		Env:         getenv("APP_ENV", "dev"),
		BaseURL:     os.Getenv("BASE_URL"),
	}
	if cfg.BaseURL == "" {
		cfg.BaseURL = "http://localhost:" + cfg.Port
	}

	if cfg.IsProd() && os.Getenv("BASE_URL") == "" {
		return Config{}, fmt.Errorf("BASE_URL is required in production")
	}

	return cfg, nil
}

func getenv(key, fallback string) string {
	if v := os.Getenv(key); v != "" {
		return v
	}
	return fallback
}