Build a Postgres API in Go
You will build an HTTP API in Go that stores items in Postgres, test it against a real database, and run it as a Docker image.
Where the docs are
- On putnami.dev: Persistence, Migrations, Testing, Configuration and Package & Docker.
- On your machine: every
go.putnami.devmodule ships its API reference asAI.md, with itsdoc/folder, in the Go module cache Putnami uses:~/.putnami/cache/go/mod/go.putnami.dev/<module>@<version>/AI.md, such asdatabase@v0.4.0/AI.md.PUTNAMI_HOMEmoves that cache to$PUTNAMI_HOME/cache/go/mod, andPUTNAMI_GO_CACHE_DIRto$PUTNAMI_GO_CACHE_DIR/mod.
Steps
1) Create the workspace and the API project
putnami init sets up the workspace in the current directory:
mkdir shop && cd shop
putnami init --extension go --project apiThe go-server template, the Go default, creates the project in api/: main.go composes the application and serves GET /, and main_test.go tests it. putnami serve api runs it.
2) Add the database modules
putnami deps add go.putnami.dev/database go.putnami.dev/migration --projects apigo.mod keeps a module that nothing imports yet as an indirect requirement, and the next putnami build drops it: write the code of the next steps before you build.
3) Write the migration
Create api/migrations/001_create_items.up.sql:
CREATE TABLE items (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL
);A .down.sql file of the same name makes the migration reversible.
4) Connect the service and add the routes
Replace api/main.go. The framework reads no DATABASE_URL variable: ServerConfig maps it, and the database plugin takes it as its DSN. A database binding for the default datasource, which a deploy target injects and the test below sets, wins over it. With AutoApply, the service applies its migrations at startup, before it listens.
package main
import (
"embed"
"os"
"strconv"
"go.putnami.dev/app"
pconfig "go.putnami.dev/config"
"go.putnami.dev/database"
"go.putnami.dev/http"
"go.putnami.dev/logger"
"go.putnami.dev/migration"
"go.putnami.dev/platform"
)
type ServerConfig struct {
Port int `json:"port" default:"3000" env:"PORT"`
DatabaseURL string `json:"databaseUrl" env:"DATABASE_URL"`
}
var RuntimeConfig = pconfig.Config[ServerConfig]("server")
//go:embed migrations/*.sql
var migrationsFS embed.FS
// itemsPlugin contributes the migrations of the items table.
type itemsPlugin struct{}
func (itemsPlugin) Name() string { return "items" }
func (itemsPlugin) MigrationSources() []migration.Source {
return []migration.Source{
database.NewSQLSource("items", database.Datasource{Name: "default", Schema: "public"}, migrationsFS),
}
}
type Item struct {
ID int64 `json:"id"`
Name string `json:"name"`
}
func main() {
cfg, err := pconfig.Load(RuntimeConfig)
if err != nil {
logger.Default().Error("config failed", err)
os.Exit(1)
}
a, _ := newApp(cfg)
if err := a.ListenAndServe(); err != nil {
logger.Default().Error("application failed", err)
os.Exit(1)
}
}
func newApp(cfg ServerConfig) (*app.Application, *http.ServerPlugin) {
server := http.NewServerPlugin(http.ServerConfig{Port: cfg.Port})
server.Use(http.Recovery())
server.Use(http.RequestID())
server.Use(http.Logging(http.LoggerOptions{
Exclude: []string{"/_/health", "/livez", "/healthz", "/readyz"}, // keep probes out of the access log
}))
server.POST("/items", http.Inject(func(pool *database.Pool, ctx *http.Context) *http.Response {
var item Item
if err := ctx.Body(&item); err != nil || item.Name == "" {
return http.JSONStatus(400, map[string]string{"message": "name is required"})
}
err := pool.QueryRow(ctx.Context(), "INSERT INTO items (name) VALUES ($1) RETURNING id", item.Name).Scan(&item.ID)
if err != nil {
return http.ErrorResponse(err)
}
return http.JSONStatus(201, item)
}))
server.GET("/items/{id}", http.Inject(func(pool *database.Pool, ctx *http.Context) *http.Response {
id, err := strconv.ParseInt(ctx.Param("id"), 10, 64)
if err != nil {
return http.NotFound()
}
item := Item{ID: id}
err = pool.QueryRow(ctx.Context(), "SELECT name FROM items WHERE id = $1", id).Scan(&item.Name)
if database.IsNoRows(err) {
return http.NotFound()
}
if err != nil {
return http.ErrorResponse(err)
}
return http.JSON(item)
}))
server.DELETE("/items/{id}", http.Inject(func(pool *database.Pool, ctx *http.Context) *http.Response {
id, err := strconv.ParseInt(ctx.Param("id"), 10, 64)
if err != nil {
return http.NotFound()
}
tag, err := pool.Exec(ctx.Context(), "DELETE FROM items WHERE id = $1", id)
if err != nil {
return http.ErrorResponse(err)
}
if tag.RowsAffected() == 0 {
return http.NotFound()
}
return http.NoContent()
}))
a := app.New("server")
a.Use(server)
a.Use(platform.NewPlugin(platform.Config{}))
a.Use(http.NewHealthPlugin())
a.Use(database.NewPlugin(database.PluginConfig{
Datasource: "default",
Pool: database.PoolConfig{DSN: cfg.DatabaseURL},
Migration: &database.MigrationConfig{AutoApply: true},
}))
a.Use(itemsPlugin{})
return a, server
}Never open the pool or query in main itself: the build runs main to describe the service, with no database.
5) Run it locally
putnami serve api starts no database: give it one in DATABASE_URL, such as DATABASE_URL='postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable' putnami serve api, or under server.databaseUrl in api/conf/.env.local.yaml.
6) Build
putnami build --projects apiThe build writes api/infra/requirements.json, which says the service needs a Postgres database. Commit it: putnami test reads it to give the tests a database.
7) Test against Postgres
putnami test starts a throwaway Postgres, in Docker or from a local install, and testprovider creates an isolated database for the suite. Replace api/main_test.go:
package main
import (
"bytes"
"context"
"encoding/json"
"fmt"
"net/http"
"net/http/httptest"
"os"
"testing"
"go.putnami.dev/database"
"go.putnami.dev/database/testprovider"
)
var handler http.Handler
func TestMain(m *testing.M) {
os.Exit(run(m))
}
func run(m *testing.M) int {
ctx := context.Background()
res, err := testprovider.Provision(ctx, testprovider.Options{})
if err != nil {
fmt.Fprintln(os.Stderr, err)
return 1
}
defer func() { _ = res.Cleanup() }()
binding, err := json.Marshal(res.Binding)
if err != nil {
fmt.Fprintln(os.Stderr, err)
return 1
}
_ = os.Setenv(database.EnvBinding, string(binding)) // the plugin connects to the test database
_ = os.Setenv("PORT", "0") // the server listens on a free port
a, server := newApp(ServerConfig{})
a.Run(func(context.Context) error { return nil }) // Start returns once the plugins started
if err := a.Start(ctx); err != nil { // applies the migrations
fmt.Fprintln(os.Stderr, err)
return 1
}
defer func() { _ = a.Stop(ctx) }()
handler = server.Handler()
return m.Run()
}
func TestCreateReadDelete(t *testing.T) {
rec := httptest.NewRecorder()
handler.ServeHTTP(rec, httptest.NewRequest("POST", "/items", bytes.NewBufferString(`{"name":"apple"}`)))
if rec.Code != 201 {
t.Fatalf("POST /items = %d %s", rec.Code, rec.Body)
}
var created Item
if err := json.Unmarshal(rec.Body.Bytes(), &created); err != nil {
t.Fatal(err)
}
path := fmt.Sprintf("/items/%d", created.ID)
rec = httptest.NewRecorder()
handler.ServeHTTP(rec, httptest.NewRequest("GET", path, nil))
if rec.Code != 200 {
t.Fatalf("GET %s = %d %s", path, rec.Code, rec.Body)
}
rec = httptest.NewRecorder()
handler.ServeHTTP(rec, httptest.NewRequest("DELETE", path, nil))
if rec.Code != 204 {
t.Fatalf("DELETE %s = %d", path, rec.Code)
}
rec = httptest.NewRecorder()
handler.ServeHTTP(rec, httptest.NewRequest("GET", path, nil))
if rec.Code != 404 {
t.Fatalf("GET %s after DELETE = %d", path, rec.Code)
}
}putnami test --projects apiWhen no Postgres answers, testprovider.Provision returns the error and the suite fails: start Docker, or install Postgres.
8) Package the image
putnami package builds only the channels you turn on, and fails for a named project with none. Turn the image channel on in api/putnami.json, beside what the template wrote:
{
"options": {
"package": {
"docker": true,
"port": 8080
}
}
}putnami package --projects api --docker-load --docker-tag localThe image is tagged api:c-<content hash>, and --docker-tag local adds api:local; --docker-load loads it into Docker. Add --platform linux/arm64 on Apple silicon. The image sets PORT to the package port, 8080 when none is set.
9) Run the image
Run Postgres and the service on one Docker network, and pass the connection in DATABASE_URL. Start the service once Postgres accepts connections: it connects and migrates before it listens, and stops when the database does not answer.
docker network create shop
docker run -d --name db --network shop -e POSTGRES_PASSWORD=postgres postgres:17
until docker exec db pg_isready -h 127.0.0.1 -U postgres; do sleep 1; done
docker run --rm --network shop -p 8080:8080 \
-e DATABASE_URL='postgres://postgres:postgres@db:5432/postgres?sslmode=disable' \
api:localThe service applies its migration at startup. In another shell:
curl -s -X POST localhost:8080/items -d '{"name":"apple"}'
curl -s localhost:8080/items/1Result
You now have a Go API that stores its data in Postgres, applies its migrations, passes its tests against a real database and runs as a Docker image.