Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Sign up
Appearance settings

v1.20.0 #2550

v1.20.0 #2550
Jul 31, 2023 · 1 comment
Discussion options

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

New Contributors

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

This discussion was created from the release v1.20.0.
You must be logged in to vote

Replies: 1 comment

Comment options

Could you publish a new version on snap
You must be logged in to vote
0 replies
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet

AltStyle によって変換されたページ (->オリジナル) /