Go - Sample CRUD API (with Migrations)

Overview

This sample extends the REST API with a PostgreSQL-backed CRUD layer and SQL migrations. It uses standard library database/sql for portability and clarity.

Project Layout

app/
  cmd/api/main.go
  internal/store/store.go
  internal/httpx/users.go
  db/migrations/
    0001_init.up.sql
    0001_init.down.sql
  go.mod

go.mod

module example.com/app

go 1.22.0

require (
  github.com/lib/pq v1.10.9 // postgres driver
)

db/migrations/0001_init.up.sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

docker-compose.yml (Postgres)

version: "3.9"
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: pass
      POSTGRES_DB: app
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U user -d app"]
      interval: 5s
      timeout: 3s
      retries: 5

Makefile (recommended)

DSN ?= postgres://user:pass@localhost:5432/app?sslmode=disable

.PHONY: compose-up compose-down migrate-up migrate-down seed run test lint

compose-up:
	docker compose up -d db

compose-down:
	docker compose down

migrate-up:
	docker run --rm --network host -v $$(pwd)/db/migrations:/migrations migrate/migrate -path=/migrations -database $(DSN) up

migrate-down:
	docker run --rm --network host -v $$(pwd)/db/migrations:/migrations migrate/migrate -path=/migrations -database $(DSN) down 1

seed:
	docker run --rm -i --network host -v $$(pwd)/db:/db postgres:16 psql $(DSN) -f /db/seed.sql

run:
	DSN=$(DSN) go run ./cmd/api

test:
	go test ./...

lint:
	go vet ./...

cmd/api/main.go

package main

import (
  "database/sql"
  "log"
  "net/http"
  "os"
  "time"
  "example.com/app/internal/httpx"
  "example.com/app/internal/store"
  _ "github.com/lib/pq"
)

func main(){
  dsn := getenv("DSN", "postgres://user:pass@localhost:5432/app?sslmode=disable")
  db, err := sql.Open("postgres", dsn)
  if err != nil { log.Fatal(err) }
  if err := db.Ping(); err != nil { log.Fatal(err) }

  st := store.New(db)
  mux := http.NewServeMux()
  mux.HandleFunc("/health", httpx.Health)
  mux.HandleFunc("/users", httpx.Users(st))

  srv := &http.Server{ Addr:":8080", Handler:mux, ReadHeaderTimeout:5*time.Second }
  log.Println("listening on", srv.Addr)
  log.Fatal(srv.ListenAndServe())
}

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

internal/store/store.go

package store

import (
  "context"
  "database/sql"
)

type Store struct{ DB *sql.DB }
func New(db *sql.DB) *Store { return &Store{DB: db} }

type User struct{ ID int; Name, Email string }

func (s *Store) ListUsers(ctx context.Context) ([]User, error) {
  rows, err := s.DB.QueryContext(ctx, `SELECT id, name, email FROM users ORDER BY id`)
  if err != nil { return nil, err }
  defer rows.Close()
  var out []User
  for rows.Next(){
    var u User
    if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil { return nil, err }
    out = append(out, u)
  }
  return out, rows.Err()
}

func (s *Store) CreateUser(ctx context.Context, name, email string) (int, error) {
  var id int
  err := s.DB.QueryRowContext(ctx, `INSERT INTO users(name,email) VALUES($1,$2) RETURNING id`, name, email).Scan(&id)
  return id, err
}

internal/httpx/users.go

package httpx

import (
  "encoding/json"
  "net/http"
  "example.com/app/internal/store"
)

type userCreateReq struct{ Name, Email string }

func Users(st *store.Store) http.HandlerFunc {
  return func(w http.ResponseWriter, r *http.Request){
    switch r.Method {
    case http.MethodGet:
      list, err := st.ListUsers(r.Context())
      if err != nil { http.Error(w, err.Error(), 500); return }
      writeJSON(w, 200, list)
    case http.MethodPost:
      var req userCreateReq
      if err := json.NewDecoder(r.Body).Decode(&req); err != nil { http.Error(w, "bad json", 400); return }
      id, err := st.CreateUser(r.Context(), req.Name, req.Email)
      if err != nil { http.Error(w, err.Error(), 500); return }
      writeJSON(w, 201, map[string]any{"id": id})
    default:
      http.Error(w, "method not allowed", 405)
    }
  }
}

func writeJSON(w http.ResponseWriter, code int, v any){
  w.Header().Set("Content-Type","application/json")
  w.WriteHeader(code)
  _ = json.NewEncoder(w).Encode(v)
}

db/seed.sql

INSERT INTO users(name,email) VALUES
  ('Ada Lovelace','[email protected]'),
  ('Alan Turing','[email protected]');

Migrate and Run

# start postgres
make compose-up

# run migrations
make migrate-up

# run the API (ensure DSN is set if different)
make run

# test it
curl -s localhost:8080/users | jq
curl -s -X POST localhost:8080/users -d '{"name":"Ada","email":"[email protected]"}' -H 'Content-Type: application/json' | jq