Examples

Blog API

The examples repository contains a small blog application with users, posts, and an HTTP API. It uses goose for migrations and PlainSQL for the database code.

blog/
  migrations/       # Database schema
  queries/          # SQL and annotations
  plainsql.yaml
  gen/db/           # Generated code, split by SQL input
  cmd/blog/         # HTTP server
  cmd/devdb/        # Development database command
  internal/server/  # HTTP handlers accepting db.ReadWrite
  transactions_test.go

Start Docker and make sure the plainsql binary built from this repository is on your PATH. Then run these commands from examples/blog:

make db-up
make migrate
make generate
make check
make test
make run

With the server running, open a second terminal and create a user and a post:

curl -X POST localhost:8080/users \
  -d '{"email":"[email protected]","display_name":"Helly"}'

# Use the user ID returned above. On a fresh database it is 1.
curl -X POST localhost:8080/users/1/posts \
  -d '{"title":"Hello, world"}'

curl localhost:8080/users/1/posts

When you are done, stop the server and run make db-down to stop the database.

An annotation that changes the Go API

-- plainsql: query CreatePost returns one
-- plainsql: param $3 body text?
INSERT INTO posts AS p (user_id, title, body)
VALUES ($1, $2, $3)
RETURNING p.*;

PlainSQL takes the parameter names and types from the INSERT column list. The param annotation makes body nullable, so a caller can pass nil to write SQL NULL. RETURNING p.* selects a complete post, so the result is the shared Post model. No record annotation is needed.

Queries and transactions

The HTTP handlers call single queries through db.New(pool). The transaction test uses db.NewStore(pool) and ReadWriteTxValue to create a user and a post in one transaction. Queries and transactions explains how the callback works and what happens when it fails.

For a smaller example, see the quickstart or the walkthrough.