René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Type annotations for query parameters","articleBody":"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\r\nwork for `vet`.\r\n\r\n## Unified syntax\r\n\r\nWe added the `@sqlc-vet-disable` annotation to disable vet rules on a per-query\r\nbasis. We can extend this syntax to support other per-query configuration\r\noptions.\r\n\r\nThe current syntax for query name and command are different, so we'll\r\nstandardize on the `@` prefix. This will be the new, preferred syntax for name\r\nand command.\r\n\r\n```sql\r\n-- name: GetAuthor :one\r\nSELECT * FROM authors\r\nWHERE id = $1 LIMIT 1;\r\n\r\n-- becomes\r\n\r\n-- @name GetAuthor :one\r\nSELECT * FROM authors\r\nWHERE id = $1 LIMIT 1;\r\n```\r\n\r\nThe existing syntax will continue to work, but it will be an error to use both\r\nannotations on a single query.\r\n\r\n```sql\r\n-- INVALID!\r\n-- name: GetAuthor :one\r\n-- @name GetAuthor :one\r\nSELECT * FROM authors\r\nWHERE id = $1 LIMIT 1;\r\n```\r\n\r\n## Query command\r\n\r\nToday, queries must have a name and a command. With the new syntax, the\r\ncommand option will default to `exec`.\r\n\r\n```sql\r\n-- These two annotations are the same\r\n\r\n-- @name DeleteAuthor :exec\r\nDELETE FROM authors\r\nWHERE id = $1;\r\n\r\n-- @name DeleteAuthor\r\nDELETE FROM authors\r\nWHERE id = $1;\r\n```\r\n\r\n### Validation\r\n\r\nsqlc will delegate command validation to codegen plugins, allowing plugins to implement new commands without having\r\nto merge anything into sqlc.\r\n\r\nIf you want to still validate those, you can simulate the current behavior by\r\nusing this vet rule.\r\n\r\n```yaml\r\nrules:\r\n  - name: validate-command\r\n    rule: |\r\n      !(query.cmd in [\"exec\", \"one\", \"many\", \"execrows\", \"execlastid\", \"execresult\", \"batchone\", \"batchmany\", \"batchexec\"])\r\n```\r\n\r\n### Type annotations for query parameters\r\n\r\nThe `@param` annotation supports passing type information without having to use\r\na cast. \r\n\r\n```sql\r\n-- @param name type\r\n```\r\n\r\nCasts are required today when sqlc infers an incorrect type, but these\r\ncasts are passed down to the engine itself, possibly hurting performance. \r\nFor example, this cast is required to get sqlc working correctly, but isn't needed at runtime.\r\n\r\n```sql\r\n-- @name ListAuthorsByIDs :many\r\nSELECT * FROM authors\r\nWHERE id = ANY(@ids::bigint[]);\r\n```\r\nHere's what it looks like with the new syntax. The type annotation is\r\nengine-specific and is the same that you'd pass to CAST or CREATE TABLE.\r\n\r\n```sql\r\n-- @name ListAuthorsByIDs :many\r\n-- @param ids BIGINT[]\r\nSELECT * FROM authors\r\nWHERE id = ANY(@ids);\r\n```\r\n\r\nIf a parameter has a type annotation, that will be used instead of inferring the\r\ntype from the query.\r\n\r\n### NULL values\r\n\r\nsqlc 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.\r\n\r\n```sql\r\n-- @name CreateAuthor :one\r\n-- @param name! TEXT -- force NOT NULL\r\n-- @param bio? TEXT -- force NULL\r\nINSERT INTO authors (\r\n  name, bio\r\n) VALUES (\r\n  @name, @bio\r\n)\r\nRETURNING *;\r\n```\r\n\r\n### Positional parameters\r\n\r\nIf your parameters do not have a given name, you can refer to them by number to add a type annotation and nullability.\r\n\r\nFor PostgreSQL:\r\n\r\n```sql\r\n-- @name CreateAuthor :one\r\n-- @param 1? TEXT\r\n-- @param 2? TEXT\r\nINSERT INTO authors (\r\n  name, bio\r\n) VALUES (\r\n  $1, $2\r\n)\r\nRETURNING *;\r\n```\r\n\r\nAnd for MySQL or SQLite:\r\n\r\n```sql\r\n-- @name CreateAuthor :one\r\n-- @param 1? TEXT\r\n-- @param 2? TEXT\r\nINSERT INTO authors (\r\n  name, bio\r\n) VALUES (\r\n  ?1, ?2\r\n);\r\n```\r\n\r\n## sqlc.arg / sqlc.narg\r\n\r\nUsing the proposed annotation syntax allows you to replace `sqlc.arg()` and `sqlc.narg()`.\r\n\r\nFor example this query with `sqlc.arg()` and `sqlc.narg()`\r\n\r\n```sql\r\n-- name: UpdateAuthor :one\r\nUPDATE author\r\nSET\r\n name = coalesce(sqlc.narg('name')::text, name),\r\n bio = coalesce(sqlc.narg('bio')::text, bio)\r\nWHERE id = sqlc.arg('id')::bigint\r\nRETURNING *;\r\n```\r\n\r\nis equivalent to this one without\r\n\r\n```sql\r\n-- name: UpdateAuthor :one\r\n-- @param name? TEXT\r\n-- @param bio? TEXT\r\n-- @param id! BIGINT\r\nUPDATE author\r\nSET\r\n name = coalesce(@name, name),\r\n bio = coalesce(@bio, bio)\r\nWHERE id = @id\r\nRETURNING *;\r\n```\r\n\r\n`sqlc.arg` and `sqlc.narg` will continue to work, but will likely be deprecated in favor of the `@foo` syntax.\r\nYou can use `sqlc.arg()` with the new `@param` annotation syntax (to avoid explicit casts), but not `sqlc.narg()`. This constraint\r\nis intended to eliminate confusion about precedence of nullability directives.\r\n\r\nSo for example this will work\r\n\r\n```sql\r\n-- name: UpdateAuthor :one\r\n-- @param id! BIGINT\r\nUPDATE author\r\nSET\r\n name = coalesce(sqlc.narg('name')::text, name),\r\n bio = coalesce(sqlc.narg('bio')::text, bio)\r\nWHERE id = sqlc.arg('id')\r\nRETURNING *;\r\n```\r\n\r\nbut this won't\r\n\r\n```sql\r\n-- name: UpdateAuthor :one\r\n-- @param name TEXT\r\n-- @param bio TEXT\r\nUPDATE author\r\nSET\r\n name = coalesce(sqlc.narg('name'), name),\r\n bio = coalesce(sqlc.narg('bio'), bio)\r\nWHERE id = sqlc.arg('id')::bigint\r\nRETURNING *;\r\n```\r\n\r\nTo make this work, switch `sqlc.narg` to `sqlc.arg` and add a `?` to the param annotation.\r\n\r\n```sql\r\n-- name: UpdateAuthor :one\r\n-- @param name? TEXT\r\n-- @param bio? TEXT\r\nUPDATE author\r\nSET\r\n name = coalesce(sqlc.arg('name'), name),\r\n bio = coalesce(sqlc.arg('bio'), bio)\r\nWHERE id = sqlc.arg('id')::bigint\r\nRETURNING *;\r\n```\r\n\r\n## Why comments?\r\n\r\nWe'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.\r\n\r\n## Full example\r\n\r\nThis is the normal example from the playground using the new syntax.\r\n\r\n```sql\r\n-- @name GetAuthor :one\r\n-- @param id! BIGINT\r\nSELECT * FROM authors\r\nWHERE id = @id LIMIT 1;\r\n\r\n-- @name ListAuthors :many\r\nSELECT * FROM authors\r\nORDER BY name;\r\n\r\n-- @name CreateAuthor :one\r\n-- @param name! TEXT\r\n-- @param bio TEXT\r\nINSERT INTO authors (\r\n  name, bio\r\n) VALUES (\r\n  @name, @bio\r\n)\r\nRETURNING *;\r\n\r\n-- @name GetAuthor\r\n-- @param id! BIGINT\r\nDELETE FROM authors\r\nWHERE id = @id;\r\n```\r\n\r\n## Future\r\nThe 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.","author":{"url":"https://github.com/kyleconroy","@type":"Person","name":"kyleconroy"},"datePublished":"2023-10-03T15:26:45.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":11},"url":"https://github.com/2800/sqlc/issues/2800"}