How to Add Postgres to a chi App
Back a chi router with PostgreSQL, migrations and a local database, using Encore or pgx with Amazon RDS.
chi is the Go router people pick when they want to stay close to net/http: handlers are ordinary http.HandlerFunc values, middleware is func(http.Handler) http.Handler, and nothing in the package knows about databases. That keeps the code small, and it also means the first Postgres table pulls in a driver, a pool, a migration tool, a database on every laptop and a separate one in each cloud environment.
This guide adds Postgres to a small chi bookmarks API with tags, first with Encore, which declares the database in Go and runs it locally and in your own AWS or GCP account, and then with pgx, golang-migrate and Amazon RDS set up by hand.
Postgres options for a chi app at a glance
How do you add Postgres to chi with Encore?
Encore is an open source backend framework for Go and TypeScript that also provisions what the code declares in your own AWS or GCP account, and you can bring it into a chi app without rewriting the router. The chi router stays where it is and gets mounted in a single raw endpoint with a fallback route, while the database it queries is declared a few lines above the handlers.
Set up the app
Run in your terminal to get started locally.
The first migration creates both tables, and Encore applies migration files in the order of their number prefix:
-- bookmarks/migrations/1_create_bookmarks.up.sql
CREATE TABLE bookmarks (
id BIGSERIAL PRIMARY KEY,
url TEXT NOT NULL UNIQUE,
title TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE bookmark_tags (
bookmark_id BIGINT NOT NULL REFERENCES bookmarks (id) ON DELETE CASCADE,
tag TEXT NOT NULL,
PRIMARY KEY (bookmark_id, tag)
);
The service declares the database, builds the chi router in initService, and hands every request that no Encore endpoint matches to chi. Creating a bookmark writes the row and its tags in one transaction, so a failed tag insert never leaves a bookmark without its tags:
// bookmarks/bookmarks.go
package bookmarks
import (
"encoding/json"
"errors"
"net/http"
"strconv"
"encore.dev/storage/sqldb"
"github.com/go-chi/chi/v5"
"github.com/go-chi/chi/v5/middleware"
)
// Postgres locally with `encore run`, Amazon RDS or Cloud SQL when deployed.
var db = sqldb.NewDatabase("bookmarks", sqldb.DatabaseConfig{
Migrations: "./migrations",
})
//encore:service
type Service struct {
router chi.Router
}
func initService() (*Service, error) {
r := chi.NewRouter()
r.Use(middleware.Recoverer)
r.Post("/bookmarks", createBookmark)
r.Get("/bookmarks/{id}", getBookmark)
r.Get("/tags/{tag}/bookmarks", listByTag)
return &Service{router: r}, nil
}
// Fallback sends every request that no Encore endpoint matches to chi.
//
//encore:api public raw path=/!fallback
func (s *Service) Fallback(w http.ResponseWriter, req *http.Request) {
s.router.ServeHTTP(w, req)
}
type Bookmark struct {
ID int64 `json:"id"`
URL string `json:"url"`
Title string `json:"title"`
Tags []string `json:"tags"`
}
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 createBookmark(w http.ResponseWriter, r *http.Request) {
var in struct {
URL string `json:"url"`
Title string `json:"title"`
Tags []string `json:"tags"`
}
if err := json.NewDecoder(r.Body).Decode(&in); err != nil || in.URL == "" || in.Title == "" {
writeJSON(w, http.StatusBadRequest, map[string]string{"error": "url and title are required"})
return
}
ctx := r.Context()
tx, err := db.Begin(ctx)
if err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not start transaction"})
return
}
defer tx.Rollback() // no-op after Commit
b := Bookmark{URL: in.URL, Title: in.Title, Tags: []string{}}
err = tx.QueryRow(ctx, `
INSERT INTO bookmarks (url, title) VALUES ($1, $2)
ON CONFLICT (url) DO UPDATE SET title = EXCLUDED.title
RETURNING id
`, in.URL, in.Title).Scan(&b.ID)
if err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not save bookmark"})
return
}
for _, tag := range in.Tags {
if _, err := tx.Exec(ctx, `
INSERT INTO bookmark_tags (bookmark_id, tag) VALUES ($1, $2)
ON CONFLICT DO NOTHING
`, b.ID, tag); err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not save tags"})
return
}
b.Tags = append(b.Tags, tag)
}
if err := tx.Commit(); err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not commit"})
return
}
writeJSON(w, http.StatusCreated, b)
}
func getBookmark(w http.ResponseWriter, r *http.Request) {
id, err := strconv.ParseInt(chi.URLParam(r, "id"), 10, 64)
if err != nil {
writeJSON(w, http.StatusBadRequest, map[string]string{"error": "invalid id"})
return
}
b := Bookmark{ID: id}
err = db.QueryRow(r.Context(), `
SELECT b.url, b.title, COALESCE(array_agg(t.tag) FILTER (WHERE t.tag IS NOT NULL), '{}')
FROM bookmarks b LEFT JOIN bookmark_tags t ON t.bookmark_id = b.id
WHERE b.id = $1
GROUP BY b.id
`, id).Scan(&b.URL, &b.Title, &b.Tags)
if errors.Is(err, sqldb.ErrNoRows) {
writeJSON(w, http.StatusNotFound, map[string]string{"error": "bookmark not found"})
return
} else if err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not load bookmark"})
return
}
writeJSON(w, http.StatusOK, b)
}
func listByTag(w http.ResponseWriter, r *http.Request) {
rows, err := db.Query(r.Context(), `
SELECT b.id, b.url, b.title
FROM bookmarks b JOIN bookmark_tags t ON t.bookmark_id = b.id
WHERE t.tag = $1
ORDER BY b.created_at DESC
LIMIT 50
`, chi.URLParam(r, "tag"))
if err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not list bookmarks"})
return
}
defer rows.Close()
out := []Bookmark{}
for rows.Next() {
var b Bookmark
if err := rows.Scan(&b.ID, &b.URL, &b.Title); err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not read row"})
return
}
out = append(out, b)
}
if err := rows.Err(); err != nil {
writeJSON(w, http.StatusInternalServerError, map[string]string{"error": "could not list bookmarks"})
return
}
writeJSON(w, http.StatusOK, out)
}
encore run starts Postgres locally, creates the bookmarks database and applies the migration, so POST localhost:4000/bookmarks with a URL, title and tags works on a fresh clone with nothing to put in a .env file. The handlers are still plain chi code with chi.URLParam and http.ResponseWriter, and db is a package variable that Encore connects in every environment, with db.Begin giving you a transaction when a write touches more than one table. The SQL database docs cover sharing a database between services and connecting with encore db shell.
When you deploy, Encore provisions the database on Amazon RDS or Cloud SQL in your own account, keeps it in private subnets, gives each service its own database role and runs the migrations as part of the deploy. A coding agent asked to add a notes column can write 2_add_notes.up.sql and the handler change in the same commit and try both against the local database before anything reaches the cloud.
Routes can leave chi one at a time when you want them to. An Encore endpoint such as //encore:api public method=GET path=/bookmarks/:id takes precedence over the fallback, and Encore traces and documents the routes you move, while everything else keeps going through s.router.
Best fit: chi services that should keep their router and middleware and get a Postgres database that behaves the same on a laptop, in preview environments and in your own AWS or GCP account.
pgx, golang-migrate and Amazon RDS
The usual hand-wired setup is pgx for the driver and pool, golang-migrate for schema changes, and an RDS instance created in Terraform or the console. The pool is created in main and reaches the handlers through a struct, which chi supports through method values:
package main
import (
"context"
"encoding/json"
"log"
"net/http"
"os"
"github.com/go-chi/chi/v5"
"github.com/go-chi/chi/v5/middleware"
"github.com/jackc/pgx/v5/pgxpool"
)
type server struct{ db *pgxpool.Pool }
func main() {
pool, err := pgxpool.New(context.Background(), os.Getenv("DATABASE_URL"))
if err != nil {
log.Fatal(err)
}
defer pool.Close()
s := &server{db: pool}
r := chi.NewRouter()
r.Use(middleware.Recoverer)
r.Post("/bookmarks", s.createBookmark)
log.Fatal(http.ListenAndServe(":8080", r))
}
func (s *server) createBookmark(w http.ResponseWriter, r *http.Request) {
var in struct {
URL string `json:"url"`
Title string `json:"title"`
}
if err := json.NewDecoder(r.Body).Decode(&in); err != nil || in.URL == "" {
http.Error(w, "url is required", http.StatusBadRequest)
return
}
var id int64
err := s.db.QueryRow(r.Context(), `
INSERT INTO bookmarks (url, title) VALUES ($1, $2)
ON CONFLICT (url) DO UPDATE SET title = EXCLUDED.title
RETURNING id`, in.URL, in.Title).Scan(&id)
if err != nil {
http.Error(w, "could not save bookmark", http.StatusInternalServerError)
return
}
w.Header().Set("Content-Type", "application/json")
w.WriteHeader(http.StatusCreated)
json.NewEncoder(w).Encode(map[string]any{"id": id, "url": in.URL})
}
On a laptop this means a Postgres container in Docker Compose, a DATABASE_URL in .env, and migrate -path ./migrations -database "$DATABASE_URL" up before go run. In AWS it means an RDS for PostgreSQL instance in private subnets with a DB subnet group, a security group that only accepts port 5432 from the app, the master password in Secrets Manager, and a way for CI to reach that private database to run migrations, usually a one-off task inside the VPC. The pgxpool defaults open up to four connections or one per CPU, whichever is larger, per process, so the pool size has to be checked against the instance's max_connections once you run several replicas.
This route gives you every RDS setting directly and suits teams that already manage their databases in Terraform. You also repeat the setup for staging and production, and the Compose database on each laptop drifts from the RDS version and parameter group unless someone keeps them in step.
Best fit: chi apps whose database is owned by a platform team, or that depend on RDS settings and extensions managed outside the application.
sqlc on top of either
sqlc generates typed Go functions from SQL files, and it pairs well with chi because the generated code only needs a context and a database handle. It works with pgx and RDS directly, and with an Encore database through db.Stdlib(), which returns a *sql.DB connected to the same database that sqlc's default database/sql output can use, the same way the Atlas and GORM guide hands it to GORM. The trade-off is a code generation step in your build, which pays off once the number of queries grows past a handful.
How to choose
Pick Encore when you want to keep chi and get a database declared next to the handlers, a local Postgres that starts with the app, migrations that run on every deploy, and RDS or Cloud SQL provisioned in your own account. Pick pgx with RDS when the database already lives in Terraform and the team wants each setting in its own hands. Either way, sqlc is a good fit once the hand-written queries start to pile up.
Related guides
Frequently asked questions
How do I connect go-chi to PostgreSQL?
Create one connection pool at startup, for example with pgxpool.New(ctx, os.Getenv("DATABASE_URL")), keep it somewhere your handlers can reach, and pass r.Context() to every query so it is cancelled when the client goes away. chi handlers are plain http.HandlerFunc values, so the database code is the same as in any net/http app.
How do I add Postgres to a chi app with Encore?
Declare the database with sqldb.NewDatabase("bookmarks", sqldb.DatabaseConfig{Migrations: "./migrations"}), build the chi router in initService, and forward requests to it from a raw endpoint with path=/!fallback. encore run starts Postgres locally and applies the migrations, and on deploy Encore provisions Amazon RDS or Cloud SQL in your own cloud account.
How do I read URL parameters in a chi handler that queries Postgres?
Register the route with a placeholder such as /bookmarks/{id} and read it with chi.URLParam(r, "id"), then parse it before passing it to the query as a $1 parameter. Never build the SQL string from the parameter.
Where should chi apps run database migrations?
Run them once per deploy, before the new version takes traffic, rather than from every replica at startup where two instances can race. With golang-migrate that step usually lives in CI, after the new image is built and before it takes traffic.
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.
