Type annotations for query parameters

[kyleconroy]

I propose adding type annotations for query parameters. Users will no longer need to cast parameters to the desired type. This proposal builds on Andrew's query annotation work for vet.

Unified syntax

We added the @sqlc-vet-disable annotation to disable vet rules on a per-query basis. We can extend this syntax to support other per-query configuration options.

The current syntax for query name and command are different, so we'll standardize on the @ prefix. This will be the new, preferred syntax for name and command.

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- becomes

-- @name GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

The existing syntax will continue to work, but it will be an error to use both annotations on a single query.

-- INVALID!
-- name: GetAuthor :one
-- @name GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

Query command

Today, queries must have a name and a command. With the new syntax, the command option will default to exec.

-- These two annotations are the same

-- @name DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

-- @name DeleteAuthor
DELETE FROM authors
WHERE id = $1;

Validation

sqlc will delegate command validation to codegen plugins, allowing plugins to implement new commands without having to merge anything into sqlc.

If you want to still validate those, you can simulate the current behavior by using this vet rule.

rules:
  - name: validate-command
    rule: |
      !(query.cmd in ["exec", "one", "many", "execrows", "execlastid", "execresult", "batchone", "batchmany", "batchexec"])

Type annotations for query parameters

The @param annotation supports passing type information without having to use a cast.

-- @param name type

Casts are required today when sqlc infers an incorrect type, but these casts are passed down to the engine itself, possibly hurting performance. For example, this cast is required to get sqlc working correctly, but isn't needed at runtime.

-- @name ListAuthorsByIDs :many
SELECT * FROM authors
WHERE id = ANY(@ids::bigint[]);

Here's what it looks like with the new syntax. The type annotation is engine-specific and is the same that you'd pass to CAST or CREATE TABLE.

-- @name ListAuthorsByIDs :many
-- @param ids BIGINT[]
SELECT * FROM authors
WHERE id = ANY(@ids);

If a parameter has a type annotation, that will be used instead of inferring the type from the query.

NULL values

sqlc will infer the nullability of parameters by default. You can force a parameter to be nullable using the ? operator, or not null using the ! operator.

-- @name CreateAuthor :one
-- @param name! TEXT -- force NOT NULL
-- @param bio? TEXT -- force NULL
INSERT INTO authors (
  name, bio
) VALUES (
  @name, @bio
)
RETURNING *;

Positional parameters

If your parameters do not have a given name, you can refer to them by number to add a type annotation and nullability.

For PostgreSQL:

-- @name CreateAuthor :one
-- @param 1? TEXT
-- @param 2? TEXT
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING *;

And for MySQL or SQLite:

-- @name CreateAuthor :one
-- @param 1? TEXT
-- @param 2? TEXT
INSERT INTO authors (
  name, bio
) VALUES (
  ?1, ?2
);

sqlc.arg / sqlc.narg

Using the proposed annotation syntax allows you to replace sqlc.arg() and sqlc.narg().

For example this query with sqlc.arg() and sqlc.narg()

-- name: UpdateAuthor :one
UPDATE author
SET
 name = coalesce(sqlc.narg('name')::text, name),
 bio = coalesce(sqlc.narg('bio')::text, bio)
WHERE id = sqlc.arg('id')::bigint
RETURNING *;

is equivalent to this one without

-- name: UpdateAuthor :one
-- @param name? TEXT
-- @param bio? TEXT
-- @param id! BIGINT
UPDATE author
SET
 name = coalesce(@name, name),
 bio = coalesce(@bio, bio)
WHERE id = @id
RETURNING *;

sqlc.arg and sqlc.narg will continue to work, but will likely be deprecated in favor of the @foo syntax. You can use sqlc.arg() with the new @param annotation syntax (to avoid explicit casts), but not sqlc.narg(). This constraint is intended to eliminate confusion about precedence of nullability directives.

So for example this will work

-- name: UpdateAuthor :one
-- @param id! BIGINT
UPDATE author
SET
 name = coalesce(sqlc.narg('name')::text, name),
 bio = coalesce(sqlc.narg('bio')::text, bio)
WHERE id = sqlc.arg('id')
RETURNING *;

but this won't

-- name: UpdateAuthor :one
-- @param name TEXT
-- @param bio TEXT
UPDATE author
SET
 name = coalesce(sqlc.narg('name'), name),
 bio = coalesce(sqlc.narg('bio'), bio)
WHERE id = sqlc.arg('id')::bigint
RETURNING *;

To make this work, switch sqlc.narg to sqlc.arg and add a ? to the param annotation.

-- name: UpdateAuthor :one
-- @param name? TEXT
-- @param bio? TEXT
UPDATE author
SET
 name = coalesce(sqlc.arg('name'), name),
 bio = coalesce(sqlc.arg('bio'), bio)
WHERE id = sqlc.arg('id')::bigint
RETURNING *;

Why comments?

We're using comments instead of sqlc.* functions to avoid engine-specific parsing issues. For example, we've run into issues with the MySQL parser not support functions in certain parameter locations.

Full example

This is the normal example from the playground using the new syntax.

-- @name GetAuthor :one
-- @param id! BIGINT
SELECT * FROM authors
WHERE id = @id LIMIT 1;

-- @name ListAuthors :many
SELECT * FROM authors
ORDER BY name;

-- @name CreateAuthor :one
-- @param name! TEXT
-- @param bio TEXT
INSERT INTO authors (
  name, bio
) VALUES (
  @name, @bio
)
RETURNING *;

-- @name GetAuthor
-- @param id! BIGINT
DELETE FROM authors
WHERE id = @id;

Future

The plan is to use similar annotations to support type annotations for output columns and values, Go type overrides for parameters and outputs, and JSON unmarshal / marshal hints.

[andrewmbenton]

In the named parameter example, I would expect the syntax to be

-- @name CreateAuthor :one
-- @param @name TEXT NOT NULL
-- @param @bio TEXT

so that it's consistent with the $n and ?n treatment. Alternatively we could change the syntax in the numbered parameter cases to remove the $ and ? and just leave the numbers.

[kyleconroy]

I don't like the repetition of the @ character, but agree that the ? and $ syntax feels unnecessary. We shouldn't allow named parameters to start with a number anyways.

[Emyrk]

Love this. Been type casting params for awhile now, but this is a much cleaner solution imo. :+1:

Does this mean we no longer would need sqlc.narg?

[kyleconroy]

@Emyrk I just update the proposal with a slightly different syntax, which allows for specifying type information without change the nullability of a parameter.

[andrewmbenton]

Does this mean we no longer would need sqlc.narg?

Yes, we just added a bit more clarity about that to the proposal.

[aliml92]

@kyleconroy @andrewmbenton Would it be okay to use generated model structs in place of query params? By having a syntax like this:

-- @name CreateProduct :one
-- @modelParam Product 

or any syntax that hints using model struct as params. I was able to achieve that by applyning new config option called params_struct_overrides in my forked repo. https://github.com/aliml92/sqlc/commit/d38d58a784b40e1c871e9083cf5ae3277a33ad60. Here are the sample code:

1. sqlc.yaml:

version: "2"
sql:
  - engine: "postgresql"
    queries: "./db/queries"
    schema: "./db/migrations"
    gen:
      go:
        package: "store"
        sql_package: "pgx/v5"
        out: "./internal/store"
        
        params_struct_overrides:
        - method_name: CreateProduct
          model_name: Product  

2. models.go

type Product struct {
	ID         int64              `json:"id"`
	StoreID    int32              `json:"store_id"`
	CategoryID int32              `json:"category_id"`
	Name       string             `json:"name"`
	Brand      *string            `json:"brand"`
	Slug       string             `json:"slug"`
	ImageLinks []byte             `json:"image_links"`
	Specs      []byte             `json:"specs"`
	CreatedAt  pgtype.Timestamptz `json:"created_at"`
	UpdatedAt  pgtype.Timestamptz `json:"updated_at"`
}

3. productcatalog.sql.go

 const createProduct = `-- name: CreateProduct :one
INSERT INTO products 
    (store_id, category_id, name, brand, slug, image_links, specs)
VALUES
    ($1, $2, $3, $4, $5, $6, $7)
RETURNING id
`

func (q *Queries) CreateProduct(ctx context.Context, p Product) (int64, error) {
	row := q.db.QueryRow(ctx, createProduct,
		p.StoreID,
		p.CategoryID,
		p.Name,
		p.Brand,
		p.Slug,
		p.ImageLinks,
		p.Specs,
	)
	var id int64
	err := row.Scan(&id)
	return id, err
}

However, enabling such override by type annotations would be much preferable though.

[Emyrk]

However, enabling such override by type annotations would be much preferable though.

I also have wanted this feature, but there are potentially better ways to solve this (eg sqlc.embed as an example).

My fear of overrides is you lose some of the power of sqlc, which is making sure the models always match the query. If custom models are supported, is there anyway to add some "linting" or something that would warn the user when a new column is added and their custom model does not have it?

The query has all selected columns, so that would be possible. Just food for thought that custom structs might want some additional support to keep them "in line" with the sql.

[Emyrk]

:+1: again for this. Would be really helpful alongside type overrides for some edge case stuff. Currently trying to get tuples to work as parameters