Annotations

An annotation is a SQL comment that starts with -- plainsql:. Put annotations directly above the statement they describe, one per line. Every statement must end with a semicolon.

Annotation	Form	Purpose
query	query <Name> returns <kind>	Name the generated method and set its return kind. Required.
param	param $N <name> [<type>[?]]	Name a placeholder and set its type or nullability.
column	column <path> [<type>[?]]	Set the type or nullability of one result column.
record	record <source> [as <name>]	Group the columns from one table or alias into one value.

The examples on this page use pgx/v5 and the default Go type mappings.

query

query <Name> returns one | many | none | affected

Every statement needs exactly one query annotation, and it must come before any other annotation. The name becomes the Go method name, so use UpperCamelCase and keep it unique within the project.

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

id, err := queries.GetUserID(ctx, "[email protected]")

Return kinds

Return kind	Go result	Rows
one	(T, error)	Exactly one row.
many	([]T, error)	Zero or more rows. An empty result gives an empty slice.
none	error	No result rows. A statement that changes no rows is not an error.
affected	(int64, error)	No result rows. Returns the number of changed rows.

With one, zero rows return pgx.ErrNoRows and more than one row returns pgx.ErrTooManyRows. PlainSQL wraps these errors with the query name, so check them with errors.Is:

id, err := queries.GetUserID(ctx, "[email protected]")
if errors.Is(err, pgx.ErrNoRows) {
    // No user has this email.
}

Use one or many for INSERT ... RETURNING.

Result type

A shared model is a Go struct that matches one table, such as User for users. PlainSQL generates it once in models.go. Every query that selects a complete row of that table returns it:

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

The method returns (User, error), or ([]User, error) with returns many.

This also works for u.*, for a column list that names every column, and for the RETURNING results of INSERT, UPDATE, or DELETE. The result must include every column of the table with its original name and type. Column order does not matter.

A query that selects one column returns that column’s Go type directly, for example int64 for id. This is true even when the table has only one column: SELECT id returns int64, and SELECT * returns the model.

Any other result with two or more columns gets its own struct named <Name>Row. This happens when you select some of a table’s columns, rename a column, or select a computed value. In joins, CTEs, and subqueries, use record to group columns into a model. To return structs as pointers, set result_structs: pointers.

param

param $N <name> [<type>[?]]

PlainSQL names parameters from the SQL when it can. In WHERE email = $1, the parameter is email. Names also come from insert columns, SET assignments in updates, LIMIT, and OFFSET.

Use param when PlainSQL cannot find a name, or when you want a different one.

Name a parameter

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

id, err := queries.GetUserID(ctx, loginEmail)

Write names in lower_snake_case. PlainSQL converts them to camelCase in Go:

• login_email becomes loginEmail.
• user_id becomes userID.
• type becomes type_ because type is a Go keyword.

Accept NULL

By default, a parameter cannot be NULL, even when the column allows it. In this example, posts.body is a nullable text column. To let callers set it to NULL, declare $2 as text?. The ? means the value can be NULL:

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

type UpdatePostBodyParams struct {
    ID   int64
    Body *string
}

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

count, err := queries.UpdatePostBody(ctx, db.UpdatePostBodyParams{
    ID:   1,
    Body: nil, // Writes SQL NULL.
})

Without the annotation, Body is a string and callers cannot pass nil. To mark a parameter nullable, you must give the database type followed by ?. You cannot write ? alone.

Arrays

-- plainsql: query ListPostTitlesByIDs returns many
-- plainsql: param $1 post_ids
SELECT title
FROM posts
WHERE id = ANY($1)
ORDER BY id;

titles, err := queries.ListPostTitlesByIDs(ctx, []int64{1, 2})

Because posts.id is bigint, Postgres infers $1 in ANY($1) as bigint[]. PlainSQL maps that to []int64.

Type

You can also give a parameter a type. PlainSQL checks it against the type Postgres infers. Here, posts.id is bigint, so Postgres infers $1 as bigint:

-- plainsql: query GetPostBody returns one
-- plainsql: param $1 id integer
SELECT body
FROM posts
WHERE id = $1;

PlainSQL rejects this annotation because integer is not bigint. To fix it, write bigint or leave the type out.

To accept an integer parameter instead, cast $1 in the SQL:

-- plainsql: query GetPostBody returns one
-- plainsql: param $1 id integer
SELECT body
FROM posts
WHERE id = $1::integer;

Now Postgres infers $1 as integer, and the annotation matches. The Go parameter becomes id int32 instead of id int64.

column

column <path> [<type>[?]]

Postgres always tells PlainSQL the type of a result column, but it does not always say whether the value can be NULL. Use column to fill in that information, or to make sure a column keeps the type you expect.

Set nullability

Postgres reports that upper(title) returns text, but PlainSQL cannot tell whether it can be NULL. This annotation declares that label is never NULL:

-- plainsql: query ListLoudTitles returns many
-- plainsql: column label text
SELECT upper(title) AS label
FROM posts
ORDER BY id;

titles, err := queries.ListLoudTitles(ctx) // []string, error

If the value can be NULL, write text? instead. The result then becomes []*string.

Always include the type here. A column annotation with only a path does not tell PlainSQL whether the value can be NULL.

Check the schema

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

body, err := queries.GetPostBody(ctx, 1) // *string, error

Here, posts.body is a nullable text column, so the annotation matches the schema and changes nothing. If a later migration makes the column NOT NULL, generation fails because the schema no longer matches text?. Without the annotation, the Go return type would silently change from *string to string.

Paths

The path is the name of the result column. If the column has an alias, use the alias: AS label matches column label. For a column inside a record, put the record name first: column author.label text.

record

record <source> [as <name>]

In a join, the result has columns from more than one table. record groups the columns from one table or alias into one Go field. as <name> sets the name of that field.

Reuse a shared model

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

type GetPostWithAuthorRow struct {
    Post   Post
    Author User
}

p.* and u.* select complete rows, so the fields use the shared Post and User models.

as author names the field Author. The type stays User. To rename the model itself, see the configuration.

Select some columns

-- 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
    }
}

This query selects only two columns from u, so Author is an anonymous struct with those two fields instead of the shared User model.

Return one record directly

-- plainsql: query GetPostAuthor returns one
-- plainsql: record u
SELECT u.*
FROM posts AS p
JOIN users AS u ON u.id = p.user_id
WHERE p.id = $1;

user, err := queries.GetPostAuthor(ctx, 1) // User, error

When the record is the whole result, the method returns it directly. Here, that is User.

Outer joins

In an outer join, a row may have no match on one side. The record for that side becomes a pointer, such as Post *Post, and is nil when there is no match. The fields inside a present post keep their normal types. The walkthrough shows this with users who have no posts.