Walkthrough: SQL to Go

This page builds up a set of queries for a small blog, one at a time. All of them go in queries/blog.sql. For each query, you see the SQL you write and the Go that plainsql generate produces. The highlighted lines in the SQL are the annotations. The highlighted parts of the Go show what each annotation changed. The annotations reference has the full syntax.

To try the queries yourself, set up a project as in the quickstart and point it at the demo database:

export DATABASE_URL='postgres://demo:demopassword1@ep-mute-bar-ae5vh57t-pooler.c-2.us-east-2.aws.neon.tech/blog'

Schema

CREATE TABLE users (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE posts (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    title text NOT NULL,
    body text,
    created_at timestamptz NOT NULL DEFAULT now()
);

In the sample data, Helly is user 1 and has posts 1 and 2. Post 1 has a NULL body. Mark is user 2 and has no posts.

1. Start with a query

The first query looks up a user ID by email. The annotation names the method GetUserID and says it returns one row. Every query needs both a name and a return kind. one means the query must return exactly one row.

-- plainsql: query GetUserID returns one
SELECT id
FROM users
WHERE email = $1;

func (q *Queries) GetUserID(
    ctx context.Context,
    email string,
) (int64, error)

PlainSQL names the parameter email because $1 is compared with the email column. The result is int64 because id is a bigint.

Read a complete row

To get the whole user instead, select every column:

-- plainsql: query GetUser returns one
SELECT * FROM users WHERE id = $1;

type User struct {
    CreatedAt   time.Time
    DisplayName *string
    Email       string
    ID          int64
}

func (q *Queries) GetUser(
    ctx context.Context,
    id int64,
) (User, error)

A complete row from one table returns the shared User model, with no extra annotation. Every query that selects a complete user returns this same type. This also works with u.* and with complete RETURNING results.

2. Name the parameter

Suppose your code calls this value loginEmail. Add a param annotation with login_email, and PlainSQL converts it to camelCase:

-- plainsql: query GetUserID returns one
-- plainsql: param $1 login_email
SELECT id
FROM users
WHERE email = $1;

func (q *Queries) GetUserID(
    ctx context.Context,
    loginEmail string,
) (int64, error)

3. List the posts of one user

many returns a slice. If the user has no posts, the slice is empty. This query selects only two columns, so PlainSQL generates a struct just for it:

-- plainsql: query ListUserPosts returns many
SELECT id, title
FROM posts
WHERE user_id = $1
ORDER BY id;

type ListUserPostsRow struct {
    ID    int64
    Title string
}

func (q *Queries) ListUserPosts(
    ctx context.Context,
    userID int64,
) ([]ListUserPostsRow, error)

4. Write or clear the text of a post

This update takes two parameters, so PlainSQL generates a Params struct. The ? in text? makes Body a pointer, so the caller can pass nil to write NULL. affected returns the number of rows the update changed.

-- plainsql: query UpdatePostBody returns affected
-- plainsql: param $1 post_id
-- plainsql: param $2 body text?
UPDATE posts
SET body = $2
WHERE id = $1;

type UpdatePostBodyParams struct {
    PostID int64
    Body   *string
}

func (q *Queries) UpdatePostBody(
    ctx context.Context,
    params UpdatePostBodyParams,
) (int64, error)

5. Read the text of a post

A post body can have text, be NULL, or belong to a post that does not exist. The result is a pointer because body is nullable. A missing post is reported through the error. The column annotation is optional here, because the schema already says body is nullable. It is still useful. If a migration makes body NOT NULL, generation fails instead of silently changing the Go type.

-- plainsql: query GetPostBody returns one
-- plainsql: column body text?
SELECT body
FROM posts
WHERE id = $1;

func (q *Queries) GetPostBody(
    ctx context.Context,
    id int64,
) (*string, error)

Call	Post	Value	Error
`GetPostBody(ctx, 1)`	Body is NULL	nil	nil
`GetPostBody(ctx, 2)`	Body has text	Pointer to the text	nil
`GetPostBody(ctx, 7)`	No post	nil	Matches `pgx.ErrNoRows`

6. Load a post and its author

This query returns a post title together with its author. The record annotation groups the columns from u into one field named Author. Because u.* is a complete row, that field is the shared User model.

-- plainsql: query GetPostWithAuthor returns one
-- plainsql: param $1 post_id
-- plainsql: record u as author
SELECT p.title, u.*
FROM posts AS p
JOIN users AS u ON u.id = p.user_id
WHERE p.id = $1;

type GetPostWithAuthorRow struct {
    Title  string
    Author User
}

func (q *Queries) GetPostWithAuthor(
    ctx context.Context,
    postID int64,
) (GetPostWithAuthorRow, error)

7. Select only the name and ID of the author

This version selects only the author’s ID and display name. Author becomes an anonymous struct with just those two fields. There is no Email field because the query does not select it.

-- plainsql: query ListPostAuthors returns many
-- plainsql: record u as author
SELECT p.title, u.id, u.display_name
FROM posts AS p
JOIN users AS u ON u.id = p.user_id
ORDER BY p.id;

type ListPostAuthorsRow struct {
    Title  string
    Author struct {
        ID          int64
        DisplayName *string
    }
}

func (q *Queries) ListPostAuthors(
    ctx context.Context,
) ([]ListPostAuthorsRow, error)

8. Join users to their posts

A LEFT JOIN keeps every user, even Mark, who has no posts. For his row there is no post, so the Post field is a pointer and is nil. When a post is present, its fields keep their normal types.

-- plainsql: query ListUsersWithPosts returns many
-- plainsql: record u as user
-- plainsql: record p as post
SELECT u.*, p.*
FROM users AS u
LEFT JOIN posts AS p ON p.user_id = u.id
ORDER BY u.id, p.id;

type Post struct {
    Body      *string
    CreatedAt time.Time
    ID        int64
    Title     string
    UserID    int64
}

type ListUsersWithPostsRow struct {
    User User
    Post *Post
}

func (q *Queries) ListUsersWithPosts(
    ctx context.Context,
) ([]ListUsersWithPostsRow, error)

User	Post	Body
Helly	1: Hello, world	nil
Helly	2: Rewriting my blog in Go	“This time it will be simple.”
Mark	nil	nil
Irving	3: The handbook did not cover this hallway	“Seven left turns. Same painting. I have filed a report with the painting.”
Dylan	4: Waffle party acceptance speech	“I would like to thank the numbers for being scary and the waffles for being waffles.”
Burt	5: Please enjoy each painting equally	“The angry painting has noticed the difference.”
Milchick	6: Your quarterly melon assessment	“Your department has earned six melon cubes. Please appoint a cube representative.”