vet
- Linting queries
Added in v1.19.0
sqlc vet
runs queries through a set of lint rules.
Rules are defined in the sqlc
configuration file. They
consist of a name, message, and a Common Expression Language
(CEL) expression. Expressions are evaluated
using cel-go. If an expression evaluates to
true
, sqlc vet
will report an error using the given message.
Defining lint rules
Each lint rule’s CEL expression has access to information from your sqlc configuration and queries via variables defined in the following proto messages.
message Config
{
string version = 1;
string engine = 2 ;
repeated string schema = 3;
repeated string queries = 4;
}
message Query
{
// SQL body
string sql = 1;
// Name of the query
string name = 2;
// One of "many", "one", "exec", etc.
string cmd = 3;
// Query parameters, if any
repeated Parameter params = 4;
}
message Parameter
{
int32 number = 1;
}
In addition to this basic information, when you have a PostgreSQL or MySQL
database connection configured
each CEL expression has access to the output from running EXPLAIN ...
on your query
via the postgresql.explain
and mysql.explain
variables.
This output is quite complex and depends on the structure of your query but sqlc attempts
to parse and provide as much information as it can. See
Rules using EXPLAIN ...
output for more information.
Here are a few example rules just using the basic configuration and query information available to the CEL expression environment. While these examples are simplistic, they give you a flavor of the types of rules you can write.
version: 2
sql:
- schema: "query.sql"
queries: "query.sql"
engine: "postgresql"
gen:
go:
package: "authors"
out: "db"
rules:
- no-pg
- no-delete
- only-one-param
- no-exec
rules:
- name: no-pg
message: "invalid engine: postgresql"
rule: |
config.engine == "postgresql"
- name: no-delete
message: "don't use delete statements"
rule: |
query.sql.contains("DELETE")
- name: only-one-param
message: "too many parameters"
rule: |
query.params.size() > 1
- name: no-exec
message: "don't use exec"
rule: |
query.cmd == "exec"
Rules using EXPLAIN ...
output
Added in v1.20.0
The CEL 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.
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:
- debug
rules:
- name: debug
rule: "!has(postgresql.explain)" # A dummy rule to trigger explain
Please note that databases configured with a uri
must have an up-to-date
schema for vet
to work correctly, and sqlc
does not apply schema migrations
to your database. Use your migration tool of choice to create the necessary
tables and objects before running sqlc vet
with rules that depend on
EXPLAIN ...
output.
Alternatively, configure managed databases to have
sqlc
create hosted ephemeral databases with the correct schema automatically.
Built-in rules
sqlc/db-prepare
When a database connection is configured, you can
run the built-in sqlc/db-prepare
rule. This rule will attempt to prepare
each of your queries against the connected database and report any failures.
version: 2
sql:
- schema: "schema.sql"
queries: "query.sql"
engine: "postgresql"
gen:
go:
package: "authors"
out: "db"
database:
uri: "postgresql://postgres:password@localhost:5432/postgres"
rules:
- sqlc/db-prepare
Please note that databases configured with a uri
must have an up-to-date
schema for vet
to work correctly, and sqlc
does not apply schema migrations
to your database. Use your migration tool of choice to create the necessary
tables and objects before running sqlc vet
with the sqlc/db-prepare
rule.
Alternatively, configure managed databases to have
sqlc
create hosted ephemeral databases with the correct schema automatically.
version: 2
cloud:
project: "<PROJECT_ID>"
sql:
- schema: "schema.sql"
queries: "query.sql"
engine: "postgresql"
gen:
go:
package: "authors"
out: "db"
database:
managed: true
rules:
- sqlc/db-prepare
To see this in action, check out the authors example.
Running lint rules
When you add the name of a defined rule to the rules list
for a sql package,
sqlc vet
will evaluate that rule against every query in the package.
In the example below, two rules are defined but only one is enabled.
version: 2
sql:
- schema: "query.sql"
queries: "query.sql"
engine: "postgresql"
gen:
go:
package: "authors"
out: "db"
rules:
- no-delete
rules:
- name: no-pg
message: "invalid engine: postgresql"
rule: |
config.engine == "postgresql"
- name: no-delete
message: "don't use delete statements"
rule: |
query.sql.contains("DELETE")
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;