René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Updating Rows: Documented syntax doesn't produce expected result for MySQL and Sqlite","articleBody":"### Version\n\n1.27.0\n\n### What happened?\n\n# High Level\r\nThe [documentation](https://github.com/sqlc-dev/sqlc/blob/main/docs/howto/update.md) 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.\r\n\r\n# Suggested resolution\r\nUpdate 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.\r\n\r\nThe 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? \r\n\r\nIf 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.\r\n\r\n# What I did\r\n\r\nsqcl 1.27.0, generating go code\r\n\r\nWhen following the [documentation](https://github.com/sqlc-dev/sqlc/blob/main/docs/howto/update.md) 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.\r\n\r\n## Sqlite Reproduction\r\n\r\n1. Using the attached configuration, comment out the mysql portion of the yaml\r\n2. Run `sqlc generate`\r\n3. Note the error output attached below\r\n4. Edit the `query.sql` to only include the `UpdateExampleGood` query\r\n5. Run `sqlc generate`\r\n6. Note no error output\r\n7. Note that `sqlite/query.sql.go` contains the expected UpdateExampleParams struct\r\n\r\n## MySQL Reproduction\r\n\r\n1. Using the attached configuration, comment out the sqlite portion of the yaml\r\n2. Revert the `query.sql` to the attached version if it was edited\r\n3. Run `sqlc generate`\r\n4. Note no error output\r\n5. Note that `mysql/query.sql.go` contains two Query functions\r\n    - `UpdateExample` does not have an associated Params struct\r\n    - `UpdateExampleGood` does have the expected Params struct\r\n\r\n\r\n## Example MySQL output with expected and unexpected generated functions\r\n\r\n```go\r\n// Code generated by sqlc. DO NOT EDIT.\r\n// versions:\r\n//   sqlc v1.27.0\r\n// source: query.sql\r\n\r\npackage example_issue\r\n\r\nimport (\r\n\t\"context\"\r\n)\r\n\r\nconst updateExample = `-- name: UpdateExample :exec\r\nUPDATE Example SET name = $2 WHERE id = $1\r\n`\r\n\r\n/ / NOTE: No Params struct for the `$` syntax, but the function is generated anyway\r\nfunc (q *Queries) UpdateExample(ctx context.Context) error {\r\n\t_, err := q.db.ExecContext(ctx, updateExample)\r\n\treturn err\r\n}\r\n\r\nconst updateExampleGood = `-- name: UpdateExampleGood :exec\r\nUPDATE Example SET name = ? WHERE id = ?\r\n`\r\n\r\ntype UpdateExampleGoodParams struct {\r\n\tName string\r\n\tID   int32\r\n}\r\n\r\n// NOTE: Params struct is generated with the `?` syntax\r\nfunc (q *Queries) UpdateExampleGood(ctx context.Context, arg UpdateExampleGoodParams) error {\r\n\t_, err := q.db.ExecContext(ctx, updateExampleGood, arg.Name, arg.ID)\r\n\treturn err\r\n}\r\n\r\n\r\n```\n\n### Relevant log output\n\n```shell\n** Running `sqlc generate` with sqlite backend:\r\n\r\nline 2:26 no viable alternative at input 'UPDATE Example SET name = $'\r\n# package example_issue\r\nquery.sql:1:1: no viable alternative at input 'UPDATE Example SET name = $'\r\n\r\n** No log outputs for mysql backend\n```\n\n\n### Database schema\n\n```sql\nCREATE TABLE IF NOT EXISTS Example (\r\n    id INTEGER NOT NULL,\r\n    name TEXT NOT NULL\r\n);\n```\n\n\n### SQL queries\n\n```sql\n-- This query fails to generate for sqlite, and doesn't generate Params for mysql\r\n-- name: UpdateExample :exec\r\nUPDATE Example SET name = $2 WHERE id = $1;\r\n\r\n-- This version produces the expected output for both\r\n-- name: UpdateExampleGood :exec\r\nUPDATE Example SET name = ? WHERE id = ?;\n```\n\n\n### Configuration\n\n```yaml\nversion: \"2\"\r\nsql:\r\n  - engine: \"mysql\"\r\n    queries: \"query.sql\"\r\n    schema: \"schema.sql\"\r\n    gen:\r\n      go:\r\n        package: \"example_issue\"\r\n        out: \"mysql\"  \r\n  - engine: \"sqlite\"\r\n    queries: \"query.sql\"\r\n    schema: \"schema.sql\"\r\n    gen:\r\n      go:\r\n        package: \"example_issue\"\r\n        out: \"sqlite\"\r\n  \r\n  \r\n  # NOTE: Included for completeness; to use you need to remove the `UpdateExampleGood` query\r\n  # since it doesn't have compatible syntax but does work with the documented $1, $2 syntax \r\n  # - engine: \"postgresql\"\r\n  #   queries: \"query.sql\"\r\n  #   schema: \"schema.sql\"\r\n  #   gen:\r\n  #     go:\r\n  #       package: \"example_issue\"\r\n  #       out: \"postgresql\"\n```\n\n\n### Playground URL\n\n_No response_\n\n### What operating system are you using?\n\nLinux\n\n### What database engines are you using?\n\nMySQL, SQLite\n\n### What type of code are you generating?\n\nGo","author":{"url":"https://github.com/ookjosh","@type":"Person","name":"ookjosh"},"datePublished":"2024-11-09T03:03:02.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/3697/sqlc/issues/3697"}