v1.20.0

[kyleconroy]

Release Notes

kyleconroy/sqlc is now sqlc-dev/sqlc

We've completed our migration to the sqlc-dev/sqlc repository. All existing links and installation instructions will continue to work. If you're using the go tool to install sqlc, you'll need to use the new import path to get v1.20.0 (and all future versions).

# INCORRECT: old import path
go install github.com/kyleconroy/sqlc/cmd/sqlc@v1.20.0

# CORRECT: new import path
go install github.com/sqlc-dev/sqlc/cmd/sqlc@v1.20.0

We designed the upgrade process to be as smooth as possible. If you run into any issues, please file a bug report via GitHub.

Use EXPLAIN ... output in lint rules

sqlc vet can now run EXPLAIN on your queries and include the results for use in your lint rules. For example, this rule checks that SELECT queries use an index.

version: 2
sql:
  - schema: "query.sql"
    queries: "query.sql"
    engine: "postgresql"
    database:
      uri: "postgresql://postgres:postgres@localhost:5432/postgres"
    gen:
      go:
        package: "db"
        out: "db"
    rules:
      - has-index
rules:
- name: has-index
  rule: >
    query.sql.startsWith("SELECT") &&
    !(postgresql.explain.plan.plans.all(p, has(p.index_name) || p.plans.all(p, has(p.index_name))))

The expression environment has two variables containing EXPLAIN ... output, postgresql.explain and mysql.explain. sqlc only populates the variable associated with your configured database engine, and only when you have a database connection configured.

For the postgresql engine, sqlc runs

EXPLAIN (ANALYZE false, VERBOSE, COSTS, SETTINGS, BUFFERS, FORMAT JSON) ...

where "..." is your query string, and parses the output into a PostgreSQLExplain proto message.

For the mysql engine, sqlc runs

EXPLAIN FORMAT=JSON ...

where "..." is your query string, and parses the output into a MySQLExplain proto message.

These proto message definitions are too long to include here, but you can find them in the protos directory within the sqlc source tree.

The output from EXPLAIN ... depends on the structure of your query so it's a bit difficult to offer generic examples. Refer to the PostgreSQL documentation and MySQL documentation for more information.

...
rules:
- name: postgresql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "postgresql.explain.plan.total_cost > 1.0"
- name: postgresql-no-seq-scan
  message: "Query plan results in a sequential scan"
  rule: "postgresql.explain.plan.node_type == 'Seq Scan'"
- name: mysql-query-too-costly
  message: "Query cost estimate is too high"
  rule: "has(mysql.explain.query_block.cost_info) && double(mysql.explain.query_block.cost_info.query_cost) > 2.0"
- name: mysql-must-use-primary-key
  message: "Query plan doesn't use primary key"
  rule: "has(mysql.explain.query_block.table.key) && mysql.explain.query_block.table.key != 'PRIMARY'"

When building rules that depend on EXPLAIN ... output, it may be helpful to see the actual JSON returned from the database. sqlc will print it When you set the environment variable SQLCDEBUG=dumpexplain=1. Use this environment variable together with a dummy rule to see EXPLAIN ... output for all of your queries.

Opting-out of lint rules

For any query, you can tell sqlc vet not to evaluate lint rules using the @sqlc-vet-disable query annotation.

/* name: GetAuthor :one */
/* @sqlc-vet-disable */
SELECT * FROM authors
WHERE id = ? LIMIT 1;

Bulk insert for MySQL

Developed by @Jille

MySQL now supports the :copyfrom query annotation. The generated code uses the LOAD DATA command to insert data quickly and efficiently.

Use caution with this feature. Errors and duplicate keys are treated as warnings and insertion will continue, even without an error for some cases. Use this in a transaction and use SHOW WARNINGS to check for any problems and roll back if necessary.

Check the error handling documentation for more information.

CREATE TABLE foo (a text, b integer, c DATETIME, d DATE);

-- name: InsertValues :copyfrom
INSERT INTO foo (a, b, c, d) VALUES (?, ?, ?, ?);

func (q *Queries) InsertValues(ctx context.Context, arg []InsertValuesParams) (int64, error) {
	...
}

LOAD DATA support must be enabled in the MySQL server.

CAST support for MySQL

Developed by @ryanpbrewster and @RadhiFadlillah

sqlc now understands CAST calls in MySQL queries, offering greater flexibility when generating code for complex queries.

CREATE TABLE foo (bar BOOLEAN NOT NULL);

-- name: SelectColumnCast :many
SELECT CAST(bar AS BIGINT) FROM foo;

package querytest

import (
	"context"
)

const selectColumnCast = `-- name: SelectColumnCast :many
SELECT CAST(bar AS BIGINT) FROM foo
`

func (q *Queries) SelectColumnCast(ctx context.Context) ([]int64, error) {
  ...
}

SQLite improvements

AA slew of fixes landed for our SQLite implementation, bringing it closer to parity with MySQL and PostgreSQL. We want to thank @orisano for their continued dedication to improving sqlc's SQLite support.

What's Changed

• Mention UUID type for pgx/v5 by @peterwillis in Mention UUID type for pgx/v5 #2448
• Update BUG_REPORT.yml by @andrewmbenton in Update BUG_REPORT.yml #2460
• scripts: Add new version to BUG_REPORT.yml by @kyleconroy in scripts: Add new version to BUG_REPORT.yml #2461
• build: Run all supported versions of MySQL / PostgreSQL by @kyleconroy in build: Run all supported versions of MySQL / PostgreSQL #2463
• docs: Add a description to the document for cases when a query result has no rows by @n3xem in docs: Add a description to the document for cases when a query result has no rows #2462
• feat: add basic parsing of boolean flag query metadata annotations by @andrewmbenton in feat: add basic parsing of boolean flag query metadata annotations #2464
• feat(vet): Introduce a query annotation to opt out of sqlc vet rules by @andrewmbenton in feat(vet): Introduce a query annotation to opt out of sqlc vet rules #2474
• fix(vet): report an error when a query is unpreparable, remove dead code by @andrewmbenton in fix(vet): report an error when a query is unpreparable, remove dead code #2486
• build(deps): bump pygments from 2.7.4 to 2.15.0 in /docs by @dependabot in build(deps): bump pygments from 2.7.4 to 2.15.0 in /docs #2485
• build(deps): bump github.com/jackc/pgconn from 1.14.0 to 1.14.1 by @dependabot in build(deps): bump github.com/jackc/pgconn from 1.14.0 to 1.14.1 #2483
• feat(vet): Add output from EXPLAIN ... for queries to the CEL program environment by @andrewmbenton in feat(vet): Add output from EXPLAIN ... for queries to the CEL program environment #2489
• docs: update copyright and author by @andrewmbenton in docs: update copyright and author #2490
• fix: sqlite log by @mcdoker18 in fix: sqlite log #2488
• build(deps): bump github.com/google/cel-go from 0.16.0 to 0.17.1 by @dependabot in build(deps): bump github.com/google/cel-go from 0.16.0 to 0.17.1 #2484
• docs: Check Python dependencies via dependabot by @kyleconroy in docs: Check Python dependencies via dependabot #2497
• build(deps): bump idna from 2.10 to 3.4 in /docs by @dependabot in build(deps): bump idna from 2.10 to 3.4 in /docs #2499
• build(deps): bump packaging from 20.9 to 23.1 in /docs by @dependabot in build(deps): bump packaging from 20.9 to 23.1 in /docs #2498
• build(deps): bump pygments from 2.15.0 to 2.15.1 in /docs by @dependabot in build(deps): bump pygments from 2.15.0 to 2.15.1 in /docs #2500
• fix(engine/sqlite): support for repeated table_option by @orisano in fix(engine/sqlite): support for repeated table_option #2482
• test: Add profiles to docker compose by @kyleconroy in test: Add profiles to docker compose #2503
• Add example sqlc.yaml for migration parsing by @samfweb in Add example sqlc.yaml for migration parsing #2479
• fix(engine/sqlite): added function to convert cast node by @orisano in fix(engine/sqlite): added function to convert cast node #2470
• build(deps): bump certifi from 2022.12.7 to 2023.7.22 in /docs by @kyleconroy in build(deps): bump certifi from 2022.12.7 to 2023.7.22 in /docs #2504
• fix(engine/sqlite): fixed to be able to find relation from WITH clause by @orisano in fix(engine/sqlite): fixed to be able to find relation from WITH clause #2444
• build(deps): bump sphinx from 4.4.0 to 6.1.0 in /docs by @kyleconroy in build(deps): bump sphinx from 4.4.0 to 6.1.0 in /docs #2505
• build: Add psql and mysqlsh to devenv by @kyleconroy in build: Add psql and mysqlsh to devenv #2507
• fix(sql/catalog): Support pg_dump output by @kyleconroy in fix(sql/catalog): Support pg_dump output #2508
• docs: small updates by @andrewmbenton in docs: small updates #2506
• fix(engine/sqlite): fixed IN operator precedence by @orisano in fix(engine/sqlite): fixed IN operator precedence #2428
• fix(engine/sqlite): fix table_alias rules by @orisano in fix(engine/sqlite): fix table_alias rules #2465
• fix(engine/sqlite): fix join_operator rule by @orisano in fix(engine/sqlite): fix join_operator rule #2434
• fix(vet): clean up unnecessary prepareable() func and a var name collision by @andrewmbenton in fix(vet): clean up unnecessary prepareable() func and a var name collision #2509
• Clarify option package support by @johanbrandhorst in Clarify option package support #2513
• fix(vet): Split vet messages out of codegen.proto by @kyleconroy in fix(vet): Split vet messages out of codegen.proto #2511
• build(deps): bump urllib3 from 1.26.5 to 2.0.4 in /docs by @dependabot in build(deps): bump urllib3 from 1.26.5 to 2.0.4 in /docs #2516
• build(deps): bump chardet from 4.0.0 to 5.1.0 in /docs by @dependabot in build(deps): bump chardet from 4.0.0 to 5.1.0 in /docs #2517
• build(deps): bump snowballstemmer from 2.1.0 to 2.2.0 in /docs by @dependabot in build(deps): bump snowballstemmer from 2.1.0 to 2.2.0 in /docs #2519
• build(deps): bump pytz from 2021.1 to 2023.3 in /docs by @dependabot in build(deps): bump pytz from 2021.1 to 2023.3 in /docs #2520
• build(deps): bump sphinxcontrib-htmlhelp from 2.0.0 to 2.0.1 in /docs by @dependabot in build(deps): bump sphinxcontrib-htmlhelp from 2.0.0 to 2.0.1 in /docs #2518
• fix missing unsigned param by @hiroebe in fix missing unsigned param #2522
• feat(debug): add debug flag and docs for dumping vet rule variables by @andrewmbenton in feat(debug): add debug flag and docs for dumping vet rule variables #2521
• Fix code geneation for sqlc.slice by @SoMuchForSubtlety in Fix code geneation for sqlc.slice #2431
• build(deps): bump pyparsing from 2.4.7 to 3.1.0 in /docs by @dependabot in build(deps): bump pyparsing from 2.4.7 to 3.1.0 in /docs #2530
• build(deps): bump alabaster from 0.7.12 to 0.7.13 in /docs by @dependabot in build(deps): bump alabaster from 0.7.12 to 0.7.13 in /docs #2526
• feat(sqlite): virtual tables and fts5 supported by @mcdoker18 in feat(sqlite): virtual tables and fts5 supported #2531
• fix(vet): query.cmd was always set to ":" by @thokra-nav in fix(vet): query.cmd was always set to ":" #2525
• build(docs): Ignore updates for sphinx by @kyleconroy in build(docs): Ignore updates for sphinx #2532
• build(deps): bump babel from 2.9.1 to 2.12.1 in /docs by @dependabot in build(deps): bump babel from 2.9.1 to 2.12.1 in /docs #2527
• build(deps): bump sphinxcontrib-applehelp from 1.0.2 to 1.0.4 in /docs by @dependabot in build(deps): bump sphinxcontrib-applehelp from 1.0.2 to 1.0.4 in /docs #2533
• chore!: Rename kyleconroy/sqlc to sqlc-dev/sqlc by @kyleconroy in chore!: Rename kyleconroy/sqlc to sqlc-dev/sqlc #2523
• docs: Point GitHub links to new repository location by @kyleconroy in docs: Point GitHub links to new repository location #2534
• fix(engine/sqlite): To lowercase ast.ResTarget.Name by @orisano in fix(engine/sqlite): To lowercase ast.ResTarget.Name #2433
• chore(proto): reformat protos using buf format -w by @andrewmbenton in chore(proto): reformat protos using buf format -w #2536
• build(deps): bump google.golang.org/grpc from 1.56.2 to 1.57.0 by @dependabot in build(deps): bump google.golang.org/grpc from 1.56.2 to 1.57.0 #2535
• fix(compiler): correctly validate alias in order/group by clauses for joins by @andrewmbenton in fix(compiler): correctly validate alias in order/group by clauses for joins #2537
• chore: Update FEATURE_REQUEST.yml to include SQLite engine option by @andrewmbenton in chore: Update FEATURE_REQUEST.yml to include SQLite engine option #2540
• feat(sql/catalog): Support ALTER TABLE IF EXISTS by @kyleconroy in feat(sql/catalog): Support ALTER TABLE IF EXISTS #2542
• feat(mysql): Implement cast function parser by @RadhiFadlillah in feat(mysql): Implement cast function parser #2473
• feat(mysql): :copyfrom support via LOAD DATA INFILE by @kyleconroy in feat(mysql): :copyfrom support via LOAD DATA INFILE #2545
• feat(postgresql): Add support for PostgreSQL multi-dimensional arrays by @colli173 in feat(postgresql): Add support for PostgreSQL multi-dimensional arrays #2338
• fix(codegen/golang): fix sqlc.embed to work with pq.Array wrap by @orisano in fix(codegen/golang): fix sqlc.embed to work with pq.Array wrap #2544
• refactor(compiler): remove some duplicate code by @andrewmbenton in refactor(compiler): remove some duplicate code #2546
• build(deps): bump pyparsing from 3.1.0 to 3.1.1 in /docs by @dependabot in build(deps): bump pyparsing from 3.1.0 to 3.1.1 in /docs #2547
• chore: Finish migration to sqlc-dev/sqlc by @kyleconroy in chore: Finish migration to sqlc-dev/sqlc #2548
• feat(vet): Add default query parameters for explain queries by @kyleconroy in feat(vet): Add default query parameters for explain queries #2543
• feat(cmd/sqlc): Bump version to 1.20.0 by @kyleconroy in feat(cmd/sqlc): Bump version to 1.20.0 #2549

New Contributors

• @peterwillis made their first contribution in Mention UUID type for pgx/v5 #2448
• @n3xem made their first contribution in docs: Add a description to the document for cases when a query result has no rows #2462
• @samfweb made their first contribution in Add example sqlc.yaml for migration parsing #2479
• @hiroebe made their first contribution in fix missing unsigned param #2522
• @SoMuchForSubtlety made their first contribution in Fix code geneation for sqlc.slice #2431
• @thokra-nav made their first contribution in fix(vet): query.cmd was always set to ":" #2525
• @RadhiFadlillah made their first contribution in feat(mysql): Implement cast function parser #2473
• @colli173 made their first contribution in feat(postgresql): Add support for PostgreSQL multi-dimensional arrays #2338

Full Changelog: v1.19.1...v1.20.0

---

This discussion was created from the release v1.20.0.

[fresonn]

Could you publish a new version on snap