Skip to content

Type annotations for query parameters #2800

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
kyleconroy opened this issue Oct 3, 2023 · 8 comments
Open

Type annotations for query parameters #2800

kyleconroy opened this issue Oct 3, 2023 · 8 comments
Labels

Comments

@kyleconroy
Copy link
Collaborator

kyleconroy commented Oct 3, 2023

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
Copy link
Collaborator

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
Copy link
Collaborator Author

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
Copy link
Contributor

Emyrk commented Oct 6, 2023

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

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

@kyleconroy
Copy link
Collaborator Author

@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
Copy link
Collaborator

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

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

kyleconroy added a commit that referenced this issue Oct 13, 2023
MySQL and PostgreSQL now have the same behavior, generating a nullable string parameter. That behavior isn't right, as we'd expect the parameter to be a boolean. This can be fixed by using a type case or the new annotations in #2800.

Fixes #2847
andrewmbenton added a commit that referenced this issue Dec 21, 2023
@aliml92
Copy link

aliml92 commented Jan 26, 2024

@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. aliml92@d38d58a. 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  
  1. 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"`
}
  1. 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
Copy link
Contributor

Emyrk commented Jan 26, 2024

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
Copy link
Contributor

Emyrk commented Jun 5, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

4 participants