Updating Rows: Documented syntax doesn't produce expected result for MySQL and Sqlite

[ookjosh]

Version

1.27.0

What happened?

High Level

The documentation for updating rows appears to be incomplete, resulting in an error for Sqlite and a silent-but-incorrect generation for MySQL. It appears that the $1 placeholders used in the documentation are not valid for these two backends (but is valid for PostgreSQL,) and that ? should be used for the Sqlite and MySQL backends.

Suggested resolution

Update the documentation to reflect differences in SQL dialects. As someone unfamiliar with PostgreSQL it wasn't clear if the use of $1 was specific to sqlc or not, so having a note and examples using the other backends would be great.

The silent failure to generate expected MySQL outputs (see code block at bottom) is an issue that isn't as clear to fix. The user will quickly see that the generated functions don't behave as expected, but there's no output or indication as to what went wrong. One place I could see this potentially occurring is a codebase that uses PostgreSQL and then adds the MySQL backend using the same queries - it's possible that they are "compatible" enough to silently pass through. Perhaps sqlc could emit a warning if it detects the $# syntax within a query targeting MySQL?

If possible it might also be helpful to have nicer error messages that catch this case for the Sqlite version too, indicating that the syntax might be using the wrong dialect; I'm not sure how viable such a warning would be to implement.

What I did

sqcl 1.27.0, generating go code

When following the documentation for updating rows, I ran into two issues. When generating for sqlite, the $1 syntax demonstrated in the documentation's UPDATE authors SET bio = $1; results in the error below. When generating for mysql, the syntax does not result in an error but does not generate the expected go code.

Sqlite Reproduction

1. Using the attached configuration, comment out the mysql portion of the yaml
2. Run sqlc generate
3. Note the error output attached below
4. Edit the query.sql to only include the UpdateExampleGood query
5. Run sqlc generate
6. Note no error output
7. Note that sqlite/query.sql.go contains the expected UpdateExampleParams struct

MySQL Reproduction

1. Using the attached configuration, comment out the sqlite portion of the yaml
2. Revert the query.sql to the attached version if it was edited
3. Run sqlc generate
4. Note no error output
5. Note that mysql/query.sql.go contains two Query functions
   • UpdateExample does not have an associated Params struct
   • UpdateExampleGood does have the expected Params struct

Example MySQL output with expected and unexpected generated functions

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.27.0
// source: query.sql

package example_issue

import (
    "context"
)

const updateExample = `-- name: UpdateExample :exec
UPDATE Example SET name = $2 WHERE id = $1
`

/ / NOTE: No Params struct for the `$` syntax, but the function is generated anyway
func (q *Queries) UpdateExample(ctx context.Context) error {
    _, err := q.db.ExecContext(ctx, updateExample)
    return err
}

const updateExampleGood = `-- name: UpdateExampleGood :exec
UPDATE Example SET name = ? WHERE id = ?
`

type UpdateExampleGoodParams struct {
    Name string
    ID   int32
}

// NOTE: Params struct is generated with the `?` syntax
func (q *Queries) UpdateExampleGood(ctx context.Context, arg UpdateExampleGoodParams) error {
    _, err := q.db.ExecContext(ctx, updateExampleGood, arg.Name, arg.ID)
    return err
}

Relevant log output

** Running `sqlc generate` with sqlite backend:

line 2:26 no viable alternative at input 'UPDATE Example SET name = $'
# package example_issue
query.sql:1:1: no viable alternative at input 'UPDATE Example SET name = $'

** No log outputs for mysql backend

Database schema

CREATE TABLE IF NOT EXISTS Example (
    id INTEGER NOT NULL,
    name TEXT NOT NULL
);

SQL queries

-- This query fails to generate for sqlite, and doesn't generate Params for mysql
-- name: UpdateExample :exec
UPDATE Example SET name = $2 WHERE id = $1;

-- This version produces the expected output for both
-- name: UpdateExampleGood :exec
UPDATE Example SET name = ? WHERE id = ?;

Configuration

version: "2"
sql:
  - engine: "mysql"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "example_issue"
        out: "mysql"  
  - engine: "sqlite"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "example_issue"
        out: "sqlite"

  # NOTE: Included for completeness; to use you need to remove the `UpdateExampleGood` query
  # since it doesn't have compatible syntax but does work with the documented $1, $2 syntax 
  # - engine: "postgresql"
  #   queries: "query.sql"
  #   schema: "schema.sql"
  #   gen:
  #     go:
  #       package: "example_issue"
  #       out: "postgresql"

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

MySQL, SQLite

What type of code are you generating?

Go