Connect a standard library Go API built on http.ServeMux to PostgreSQL with migrations and ETag-based optimistic locking, using Encore locally and on RDS or Cloud SQL, or pgx and Amazon RDS by hand.

The Go standard library got good enough at routing in Go 1.22 that a new API can start with http.NewServeMux() and nothing else, using patterns like PATCH /tasks/{id} and r.PathValue("id").
The database is where the standard library stops, and the parts around one pgxpool.New call are a Postgres on every laptop, a migration step that runs before the server, an RDS or Cloud SQL instance per environment, and a connection string that has to reach the process without landing in the repo.
This guide adds Postgres to a net/http task API where two people editing the same task must not silently overwrite each other. It starts with Encore, which declares the database in Go next to the ServeMux and runs it locally and in your own AWS or GCP account, then shows the same handler with pgx and a hand-provisioned RDS instance.
Encore works alongside a plain net/http app: the ServeMux and its handlers stay standard library code, and the database they query is a Go declaration with a migrations folder next to it. A raw endpoint is an ordinary http.HandlerFunc, so the mux is mounted in one fallback route that receives every request no Encore endpoint matches, and handlers use the Encore database API, which runs on a pgx pool.
Run in your terminal to get started locally.
The schema keeps a version on each task that goes up by one on every write, and CHECK constraints reject an empty title or an unknown status in the database itself:
-- tasks/migrations/1_create_tasks.up.sql
CREATE TABLE tasks (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL CHECK (title <> ''),
status TEXT NOT NULL DEFAULT 'todo' CHECK (status IN ('todo', 'doing', 'done')),
assignee TEXT,
version INT NOT NULL DEFAULT 1,
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX tasks_status_idx ON tasks (status, updated_at DESC);
The service declares the database, builds the ServeMux in initService and hands it every unmatched request. PATCH /tasks/{id} only applies the change when the If-Match header carries the version the client last read, and the version check and the increment happen in one UPDATE, so two clients that loaded version 3 cannot both write:
// tasks/tasks.go
package tasks
import (
"encoding/json"
"errors"
"fmt"
"net/http"
"strconv"
"strings"
"time"
"encore.dev/storage/sqldb"
"encore.dev/storage/sqldb/sqlerr"
)
// `encore run` starts Postgres locally and applies the migrations.
// Deployed, this is RDS on AWS or Cloud SQL on GCP in your own account.
var db = sqldb.NewDatabase("tasks", sqldb.DatabaseConfig{
Migrations: "./migrations",
})
//encore:service
type Service struct {
mux *http.ServeMux
}
func initService() (*Service, error) {
mux := http.NewServeMux()
mux.HandleFunc("POST /tasks", createTask)
mux.HandleFunc("GET /tasks/{id}", getTask)
mux.HandleFunc("PATCH /tasks/{id}", updateTask)
return &Service{mux: mux}, nil
}
// Fallback hands every request that no Encore endpoint matches to the ServeMux.
//
//encore:api public raw path=/!fallback
func (s *Service) Fallback(w http.ResponseWriter, req *http.Request) {
s.mux.ServeHTTP(w, req)
}
type Task struct {
ID int64 `json:"id"`
Title string `json:"title"`
Status string `json:"status"`
Assignee *string `json:"assignee"`
Version int `json:"version"`
UpdatedAt time.Time `json:"updated_at"`
}
const taskCols = `id, title, status, assignee, version, updated_at`
func scanTask(row *sqldb.Row) (Task, error) {
var t Task
err := row.Scan(&t.ID, &t.Title, &t.Status, &t.Assignee, &t.Version, &t.UpdatedAt)
return t, err
}
func createTask(w http.ResponseWriter, r *http.Request) {
var in struct {
Title string `json:"title"`
Assignee *string `json:"assignee"`
}
r.Body = http.MaxBytesReader(w, r.Body, 1<<20)
if err := json.NewDecoder(r.Body).Decode(&in); err != nil || strings.TrimSpace(in.Title) == "" {
writeError(w, http.StatusBadRequest, "title is required")
return
}
t, err := scanTask(db.QueryRow(r.Context(), `
INSERT INTO tasks (title, assignee) VALUES ($1, $2)
RETURNING `+taskCols, in.Title, in.Assignee))
if err != nil {
writeError(w, http.StatusInternalServerError, "could not create task")
return
}
writeTask(w, http.StatusCreated, t)
}
func getTask(w http.ResponseWriter, r *http.Request) {
id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
if err != nil {
http.NotFound(w, r)
return
}
t, err := scanTask(db.QueryRow(r.Context(), `SELECT `+taskCols+` FROM tasks WHERE id = $1`, id))
if errors.Is(err, sqldb.ErrNoRows) {
http.NotFound(w, r)
return
} else if err != nil {
writeError(w, http.StatusInternalServerError, "could not load task")
return
}
writeTask(w, http.StatusOK, t)
}
// updateTask only applies the change if the client saw the latest version,
// sent back as If-Match with the ETag from its last read.
func updateTask(w http.ResponseWriter, r *http.Request) {
id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
if err != nil {
http.NotFound(w, r)
return
}
ifMatch := r.Header.Get("If-Match")
if ifMatch == "" {
writeError(w, http.StatusPreconditionRequired, "send If-Match with the task's ETag")
return
}
version, err := strconv.Atoi(strings.Trim(ifMatch, `"`))
if err != nil {
writeError(w, http.StatusPreconditionFailed, "If-Match is not a task ETag")
return
}
var in struct {
Title *string `json:"title"`
Status *string `json:"status"`
Assignee *string `json:"assignee"`
}
r.Body = http.MaxBytesReader(w, r.Body, 1<<20)
if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
writeError(w, http.StatusBadRequest, "invalid JSON body")
return
}
t, err := scanTask(db.QueryRow(r.Context(), `
UPDATE tasks
SET title = COALESCE($3, title),
status = COALESCE($4, status),
assignee = COALESCE($5, assignee),
version = version + 1,
updated_at = now()
WHERE id = $1 AND version = $2
RETURNING `+taskCols, id, version, in.Title, in.Status, in.Assignee))
switch {
case err == nil:
writeTask(w, http.StatusOK, t)
case sqldb.ErrCode(err) == sqlerr.CheckViolation:
writeError(w, http.StatusBadRequest, "status must be todo, doing or done, and title cannot be empty")
case errors.Is(err, sqldb.ErrNoRows):
// Either the task does not exist or someone else changed it first.
var current int
err := db.QueryRow(r.Context(), `SELECT version FROM tasks WHERE id = $1`, id).Scan(¤t)
if errors.Is(err, sqldb.ErrNoRows) {
http.NotFound(w, r)
return
} else if err != nil {
writeError(w, http.StatusInternalServerError, "could not load task")
return
}
w.Header().Set("ETag", etag(current))
writeError(w, http.StatusPreconditionFailed, "task changed since you loaded it, reload and retry")
default:
writeError(w, http.StatusInternalServerError, "could not update task")
}
}
func etag(version int) string { return fmt.Sprintf(`"%d"`, version) }
func writeTask(w http.ResponseWriter, status int, t Task) {
w.Header().Set("ETag", etag(t.Version))
writeJSON(w, status, t)
}
func writeJSON(w http.ResponseWriter, status int, v any) {
w.Header().Set("Content-Type", "application/json")
w.WriteHeader(status)
_ = json.NewEncoder(w).Encode(v)
}
func writeError(w http.ResponseWriter, status int, msg string) {
writeJSON(w, status, map[string]string{"error": msg})
}
encore run starts Postgres locally, creates the tasks database and applies the migration before the server takes traffic, so a fresh clone works without a .env file. The SQL was run against Postgres 17: an update with If-Match: "1" returned version 2, a second update that still sent "1" matched no row, and a status of blocked hit the check constraint. When the conditional UPDATE finds nothing, the handler looks up the current version to tell a missing task (404) from a stale one (412), and returns the fresh ETag so the client can reload and retry. Constraint errors come back through sqldb.ErrCode(err) == sqlerr.CheckViolation.
If part of the code needs database/sql, for example for sqlc, db.Stdlib() returns a *sql.DB backed by the same database.
On deploy, Encore provisions RDS on AWS or Cloud SQL on GCP in your own account, runs the migrations and wires the connection into the service. Best fit: standard library Go APIs that want Postgres with migrations set up the same way on every laptop and in their own AWS or GCP account.
The hand-built route uses pgx directly with pgxpool and a server struct that carries the pool into the handlers. The conditional UPDATE is the same, and a check violation is detected with errors.As on *pgconn.PgError and code 23514:
package main
import (
"context"
"encoding/json"
"errors"
"fmt"
"log"
"net/http"
"os"
"strconv"
"strings"
"time"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgconn"
"github.com/jackc/pgx/v5/pgxpool"
)
type server struct {
pool *pgxpool.Pool
}
func main() {
// DATABASE_URL points at RDS, e.g. postgres://app:...@host:5432/tasks?sslmode=verify-full
pool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer pool.Close()
s := &server{pool: pool}
mux := http.NewServeMux()
mux.HandleFunc("PATCH /tasks/{id}", s.updateTask)
srv := &http.Server{
Addr: ":8080",
Handler: mux,
ReadHeaderTimeout: 5 * time.Second,
ReadTimeout: 15 * time.Second,
}
log.Fatal(srv.ListenAndServe())
}
func (s *server) updateTask(w http.ResponseWriter, r *http.Request) {
id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
if err != nil {
http.NotFound(w, r)
return
}
version, err := strconv.Atoi(strings.Trim(r.Header.Get("If-Match"), `"`))
if err != nil {
http.Error(w, "send If-Match with the task's ETag", http.StatusPreconditionRequired)
return
}
var in struct {
Status *string `json:"status"`
}
r.Body = http.MaxBytesReader(w, r.Body, 1<<20)
if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
http.Error(w, "invalid JSON body", http.StatusBadRequest)
return
}
var newVersion int
err = s.pool.QueryRow(r.Context(), `
UPDATE tasks SET status = COALESCE($3, status), version = version + 1, updated_at = now()
WHERE id = $1 AND version = $2
RETURNING version`, id, version, in.Status).Scan(&newVersion)
var pgErr *pgconn.PgError
switch {
case err == nil:
w.Header().Set("ETag", fmt.Sprintf(`"%d"`, newVersion))
w.WriteHeader(http.StatusNoContent)
case errors.As(err, &pgErr) && pgErr.Code == "23514": // check_violation
http.Error(w, "status must be todo, doing or done", http.StatusBadRequest)
case errors.Is(err, pgx.ErrNoRows):
http.Error(w, "task changed or does not exist", http.StatusPreconditionFailed)
default:
http.Error(w, "could not update task", http.StatusInternalServerError)
}
}
The pool defaults to the greater of 4 and the number of CPUs per process, so set pool_max_conns in the URL when several replicas share one instance. http.ListenAndServe has no timeouts, which is why the example builds an http.Server with ReadHeaderTimeout and ReadTimeout, and wraps the JSON body in http.MaxBytesReader. Migrations need their own step, usually golang-migrate run from CI or a one-off task before the new version starts. In AWS you create the RDS instance in private subnets with a DB subnet group and a security group that allows port 5432 from the app, keep the password in Secrets Manager and inject it into the task, and use sslmode=verify-full with the RDS certificate bundle so the client checks the server certificate.
A compact Terraform shape for the instance itself looks like this:
resource "aws_db_instance" "tasks" {
identifier = "tasks"
engine = "postgres"
engine_version = "17"
instance_class = "db.t4g.small"
allocated_storage = 20
db_name = "tasks"
username = "app"
manage_master_user_password = true
db_subnet_group_name = aws_db_subnet_group.tasks.name
vpc_security_group_ids = [aws_security_group.rds.id]
storage_encrypted = true
backup_retention_period = 7
skip_final_snapshot = true
}
Setting manage_master_user_password stores the master password in Secrets Manager, so the value does not land in Terraform state. The API task builds DATABASE_URL from the instance endpoint and that secret. The RDS security group should allow 5432 only from the API security group, not from the internet. For local development, run Postgres 17 in a container, apply the same migration the API expects, and point DATABASE_URL at localhost. A typical first migration creates the table the handler updates:
CREATE TABLE tasks (
id bigserial PRIMARY KEY,
status text NOT NULL DEFAULT 'todo',
version integer NOT NULL DEFAULT 1,
updated_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT tasks_status_check CHECK (status IN ('todo', 'doing', 'done'))
);
Run that migration, and the ones that follow, from CI or a one-off task before the new version starts, for example with golang-migrate. On a laptop the same pieces look like this:
docker run --rm -d --name tasks-pg -e POSTGRES_PASSWORD=dev -e POSTGRES_DB=tasks -p 5432:5432 postgres:17
export DATABASE_URL='postgres://postgres:dev@127.0.0.1:5432/tasks?sslmode=disable'
migrate -path migrations -database "$DATABASE_URL" up
go run .
sslmode=disable is only for that local container. In AWS, keep sslmode=verify-full and the RDS certificate bundle as above, and inject the password from Secrets Manager rather than exporting it in a shell profile.
Best fit: teams that already run RDS through Terraform and want pgx with no layer between the handler and the driver.
Teams that want every dependency to look like the standard library can open the pool with sql.Open("pgx", url) after importing github.com/jackc/pgx/v5/stdlib, and get a *sql.DB that works with sqlc, sqlx and most migration tools. The same optimistic update with database/sql looks like this:
package main
import (
"database/sql"
"encoding/json"
"errors"
"fmt"
"log"
"net/http"
"os"
"strconv"
"strings"
"time"
_ "github.com/jackc/pgx/v5/stdlib"
)
type server struct {
db *sql.DB
}
func main() {
db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer db.Close()
db.SetMaxOpenConns(20)
db.SetConnMaxIdleTime(5 * time.Minute)
s := &server{db: db}
mux := http.NewServeMux()
mux.HandleFunc("PATCH /tasks/{id}", s.updateTask)
srv := &http.Server{Addr: ":8080", Handler: mux, ReadHeaderTimeout: 5 * time.Second, ReadTimeout: 15 * time.Second}
log.Fatal(srv.ListenAndServe())
}
func (s *server) updateTask(w http.ResponseWriter, r *http.Request) {
id, err := strconv.ParseInt(r.PathValue("id"), 10, 64)
if err != nil {
http.NotFound(w, r)
return
}
version, err := strconv.Atoi(strings.Trim(r.Header.Get("If-Match"), `"`))
if err != nil {
http.Error(w, "send If-Match with the task's ETag", http.StatusPreconditionRequired)
return
}
var in struct {
Status *string `json:"status"`
}
r.Body = http.MaxBytesReader(w, r.Body, 1<<20)
if err := json.NewDecoder(r.Body).Decode(&in); err != nil {
http.Error(w, "invalid JSON body", http.StatusBadRequest)
return
}
var newVersion int
err = s.db.QueryRowContext(r.Context(), `
UPDATE tasks SET status = COALESCE($3, status), version = version + 1, updated_at = now()
WHERE id = $1 AND version = $2
RETURNING version`, id, version, in.Status).Scan(&newVersion)
switch {
case err == nil:
w.Header().Set("ETag", fmt.Sprintf(`"%d"`, newVersion))
w.WriteHeader(http.StatusNoContent)
case errors.Is(err, sql.ErrNoRows):
http.Error(w, "task changed or does not exist", http.StatusPreconditionFailed)
default:
http.Error(w, "could not update task", http.StatusInternalServerError)
}
}
SetMaxOpenConns and SetConnMaxIdleTime take the place of pool_max_conns in the pgx URL. Check violations still have to be detected from the driver error, which is why teams that want those codes without extra work often stay on pgxpool directly. Provisioning, migrations, sslmode=verify-full and the Secrets Manager injection are the same work as the pgx route.
Best fit: codebases that already use database/sql interfaces or generate queries with sqlc.
Pick Encore when you want the database declared next to the ServeMux, running locally with encore run and provisioned as RDS or Cloud SQL in your own account with migrations applied on deploy. Pick pgx with RDS when the database is already managed in Terraform and you want direct control of the pool and driver. database/sql suits code that is already written against it, with the same provisioning work as pgx.
The optimistic locking pattern itself does not depend on the choice: a version column, an ETag on reads, If-Match on writes and a conditional UPDATE, with 412 Precondition Failed for a stale copy and 428 Precondition Required when the header is missing. The Go 1.22 routing changes are what make PATCH /tasks/{id} possible without a router library.
Create one pgxpool.Pool at startup from a connection string, keep it on a struct your handlers can reach, and pass r.Context() to every query so it stops when the client goes away. Register handlers on an http.ServeMux with method and wildcard patterns such as PATCH /tasks/{id}, and read the wildcard with r.PathValue.
Declare the database with sqldb.NewDatabase and a migrations directory, build your http.ServeMux in initService, and forward requests to it from a raw endpoint with path=/!fallback. Handlers query the database with db.QueryRow and db.Exec, encore run starts Postgres locally and applies the migrations, and deploys provision RDS on AWS or Cloud SQL on GCP in your own account.
Keep a version column on the row, return it as an ETag, and require the client to send it back in If-Match. Run the update with WHERE id = $1 AND version = $2 and increment the version in the same statement; if no row comes back, the client had a stale copy and gets 412 Precondition Failed.
Not for most JSON APIs, as http.ServeMux has matched methods and path wildcards such as GET /tasks/{id} since Go 1.22. The database side is the same either way: one pool created at startup and the request context passed to each query.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.