Bot releases are hidden (Show)
This release is mainly a bug fix release. It also includes an important security fix for users using output plugins.
int16
for MySQL SMALLINT
and YEAR
(#3106)int8
for MySQL TINYINT (#3298)Published by kyleconroy 10 months ago
You can add tags when pushing schema and queries to sqlc Cloud. Tags operate like git tags, meaning you can overwrite previously-pushed tag values. We suggest tagging pushes to associate them with something relevant from your environment, e.g. a git tag or branch name.
$ sqlc push --tag v1.0.0
Once you've created a tag, you can refer to it when verifying changes, allowing you
to compare the existing schema against a known set of previous queries.
$ sqlc verify --against v1.0.0
cgo
Over the last month, we've switched out a few different modules to remove our reliance on cgo. Previously, we needed cgo for three separate functions:
With the help of the community, we found cgo-free alternatives for each module:
For the first time, Windows users can enjoy full PostgreSQL support without using WSL. It's a Christmas miracle!
If you run into any issues with the updated dependencies, please open an issue.
name
annotation (#3072)"strings"
for sqlc.slice()
with pgx (#3073)push
, add Go plugin link (#3023)emit_sql_as_comment
option to Go code plugin (#2735)Full Changelog: https://github.com/sqlc-dev/sqlc/compare/v1.24.0...v1.25.0
Published by kyleconroy 11 months ago
Schema updates and poorly-written queries often bring down production databases. That’s bad.
Out of the box, sqlc generate
catches some of these issues. Running sqlc vet
with the sqlc/db-prepare
rule catches more subtle problems. But there is a large class of issues that sqlc can’t prevent by looking at current schema and queries alone.
For instance, when a schema change is proposed, existing queries and code running in production might fail when the schema change is applied. Enter sqlc verify
, which analyzes existing queries against new schema changes and errors if there are any issues.
Let's look at an example. Assume you have these two tables in production.
CREATE TABLE users (
id UUID PRIMARY KEY
);
CREATE TABLE user_actions (
id UUID PRIMARY KEY,
user_id UUID NOT NULL,
action TEXT,
created_at TIMESTAMP
);
Your application contains the following query to join user actions against the users table.
-- name: GetUserActions :many
SELECT * FROM users u
JOIN user_actions ua ON u.id = ua.user_id
ORDER BY created_at;
So far, so good. Then assume you propose this schema change:
ALTER TABLE users ADD COLUMN created_at TIMESTAMP;
Running sqlc generate
fails with this change, returning a column reference "created_at" is ambiguous
error. You update your query to fix the issue.
-- name: GetUserActions :many
SELECT * FROM users u
JOIN user_actions ua ON u.id = ua.user_id
ORDER BY u.created_at;
While that change fixes the issue, there's a production outage waiting to happen. When the schema change is applied, the existing GetUserActions
query will begin to fail. The correct way to fix this is to deploy the updated query before applying the schema migration.
It ensures migrations are safe to deploy by sending your current schema and queries to sqlc cloud. There, we run the queries for your latest push against your new schema changes. This check catches backwards incompatible schema changes for existing queries.
Here sqlc verify
alerts you to the fact that ORDER BY "created_at" is ambiguous.
$ sqlc verify
FAIL: app query.sql
=== Failed
=== FAIL: app query.sql GetUserActions
ERROR: column reference "created_at" is ambiguous (SQLSTATE 42702)
By the way, this scenario isn't made up! It happened to us a few weeks ago. We've been happily testing early versions of verify
for the last two weeks and haven't had any issues since.
This type of verification is only the start. If your application is deployed on-prem by your customers, verify
could tell you if it's safe for your customers to rollback to an older version of your app, even after schema migrations have been run.
upload
command to push
We've renamed the upload
sub-command to push
. We changed the data sent along in a push request. Upload used to include the configuration file, migrations, queries, and all generated code. Push drops the generated code in favor of including the plugin.GenerateRequest, which is the protocol buffer message we pass to codegen plugins.
We also add annotations to each push. By default, we include these environment variables if they are present:
GITHUB_REPOSITORY
GITHUB_REF
GITHUB_REF_NAME
GITHUB_REF_TYPE
GITHUB_SHA
Like upload, push
should be run when you tag a release of your application. We run it on every push to main, as we continuously deploy those commits.
createdb
The createdb
command, added in the last release, now supports MySQL. If you have a cloud project configured, you can use sqlc createdb
to spin up a new ephemeral database with your schema and print its connection string to standard output. This is useful for integrating with other tools. Read more in the managed databases documentation.
This release includes a refactored plugin interface to better support future functionality. Plugins now support different methods via a gRPC service interface, allowing plugins to support different functionality in a backwards-compatible way.
By using gRPC interfaces, we can even (theoretically) support remote plugins, but that's something for another day.
Full Changelog: https://github.com/sqlc-dev/sqlc/compare/v1.23.0...v1.24.0
Published by kyleconroy 12 months ago
With a database connection configured, sqlc generate
will gather metadata from that database to support its query analysis. Turning this on resolves a large number of issues in the backlog related to type inference and more complex queries. The easiest way to try it out is with managed databases.
The database-backed analyzer currently supports PostgreSQL, with MySQL and SQLite support planned in the future.
createdb
commandWhen you have a cloud project configured, you can use the new sqlc createdb
command to spin up a new ephemeral database with your schema and print its connection string to standard output. This is useful for integrating with other tools. Read more in the managed databases documentation.
If you're using pgvector, say goodbye to custom overrides! sqlc now generates code using pgvector-go as long as you're using pgx
. The pgvector extension is also available in managed databases.
With the new emit_build_tags
configuration parameter you can set build tags for sqlc to add at the top of generated source files.
database
config in all cases by @andrewmbenton in https://github.com/sqlc-dev/sqlc/pull/2856
CommentSyntax
on windows by @andrewmbenton in https://github.com/sqlc-dev/sqlc/pull/2867
Full Changelog: https://github.com/sqlc-dev/sqlc/compare/v1.22.0...v1.23.0
Published by kyleconroy about 1 year ago
sqlc vet
If you're using sqlc vet to write rules that require access to a running database, sqlc
can now start and manage that database for you. PostgreSQL support is available today, with MySQL on the way.
When you turn on managed databases, sqlc
will use your schema to create a template database that it can copy to make future runs of sqlc vet
very performant.
This feature relies on configuration obtained via sqlc Cloud. If you're interested in early access you can sign up here or send us an email at [email protected].
Read more in the managed databases documentation.
Full Changelog: https://github.com/sqlc-dev/sqlc/compare/v1.21.0...v1.22.0
Published by kyleconroy about 1 year ago
sqlc
previously didn't know how to parse a CALL
statement when using the MySQL engine,
which meant it was impossible to use sqlc with stored procedures in MySQL databases.
Additionally, sqlc
now supports IS [NOT] NULL
in queries. And LIMIT
and OFFSET
clauses
now work with UNION
.
GitHub user @orisano continues to bring bugfixes and
improvements to sqlc
's SQLite engine. See the "Changes" section below for the
full list.
If you're authoring a sqlc plugin, you can now configure
sqlc to pass your plugin the values of specific environment variables.
For example, if your plugin
needs the PATH
environment variable, add PATH
to the env
list in the
plugins
collection.
version: '2'
sql:
- schema: schema.sql
queries: query.sql
engine: postgresql
codegen:
- out: gen
plugin: test
plugins:
- name: test
env:
- PATH
wasm:
url: https://github.com/sqlc-dev/sqlc-gen-test/releases/download/v0.1.0/sqlc-gen-test.wasm
sha256: 138220eae508d4b65a5a8cea555edd155eb2290daf576b7a8b96949acfeb3790
A variable named SQLC_VERSION
is always included in the plugin's
environment, set to the version of the sqlc
executable invoking it.
CAST
tests and rearranged postgres tests for same by @andrewmbenton in https://github.com/sqlc-dev/sqlc/pull/2551
--experimental
flag by @andrewmbenton in https://github.com/sqlc-dev/sqlc/pull/2567
Walk
by @Juneezee in https://github.com/sqlc-dev/sqlc/pull/2660
UNION ALL
by @orisano in https://github.com/sqlc-dev/sqlc/pull/2613
Full Changelog: https://github.com/sqlc-dev/sqlc/compare/v1.20.0...v1.21.0
Published by kyleconroy about 1 year ago
EXPLAIN ...
for queries to the CEL program environment by @andrewmbenton in https://github.com/sqlc-dev/sqlc/pull/2489
prepareable()
func and a var name collision by @andrewmbenton in https://github.com/sqlc-dev/sqlc/pull/2509
buf format -w
by @andrewmbenton in https://github.com/sqlc-dev/sqlc/pull/2536
Full Changelog: https://github.com/sqlc-dev/sqlc/compare/v1.19.1...v1.20.0
Published by kyleconroy over 1 year ago
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.19.0...v1.19.1
Published by kyleconroy over 1 year ago
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
, an error is reported using the given message.
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"
vet
also marks the first time that sqlc
can connect to a live, running database server. We'll expand this functionality over time, but for now it powers the sqlc/db-prepare
built-in rule.
When a database is configured, the sqlc/db-prepare
rule will attempt to prepare each of your queries against the connected database and report any failures.
version: 2
sql:
- schema: "query.sql"
queries: "query.sql"
engine: "postgresql"
gen:
go:
package: "authors"
out: "db"
database:
uri: "postgresql://postgres:password@localhost:5432/postgres"
rules:
- sqlc/db-prepare
To see this in action, check out the authors example.
Please note that sqlc
does not manage or migrate your database. Use your migration tool of choice to create the necessary database tables and objects before running sqlc vet
.
Added a new configuration parameter omit_unused_structs
which, when set to true, filters out table and enum structs that aren't used in queries for a given package.
With the addition of sqlc diff
and sqlc vet
, we encourage users to run sqlc in your CI/CD pipelines. See our suggested CI/CD setup for more information.
The sqlc-gen-kotlin and sqlc-gen-python plugins have been updated use the upcoming WASI support in Go 1.21. Building these plugins no longer requires TinyGo.
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.18.0...v1.19.0
Published by kyleconroy over 1 year ago
Developed by @andrewmbenton
At its core, sqlc is powered by SQL engines, which include parsers, formatters,
analyzers and more. While our goal is to support each engine on each operating
system, it's not always possible. For example, the PostgreSQL engine does not
work on Windows.
To bridge that gap, we're announcing remote code generation, currently in
private alpha. To join the private alpha, sign up for the waitlist.
To configure remote generation, configure a cloud
block in sqlc.json
.
{
"version": "2",
"cloud": {
"organization": "<org-id>",
"project": "<project-id>",
},
...
}
You'll also need to the SQLC_AUTH_TOKEN
environment variable.
export SQLC_AUTH_TOKEN=<token>
When the cloud configuration exists, sqlc generate
will default to remote
generation. If you'd like to generate code locally, pass the --no-remote
option.
sqlc generate --no-remote
Remote generation is off by default and requires an opt-in to use.
Developed by @nickjackson
Embedding allows you to reuse existing model structs in more queries, resulting
in less manual serilization work. First, imagine we have the following schema
with students and test scores.
CREATE TABLE students (
id bigserial PRIMARY KEY,
name text,
age integer
)
CREATE TABLE test_scores (
student_id bigint,
score integer,
grade text
)
We want to select the student record and the highest score they got on a test.
Here's how we'd usually do that:
-- name: HighScore :many
WITH high_scores AS (
SELECT student_id, max(score) as high_score
FROM test_scores
GROUP BY 1
)
SELECT students.*, high_score::integer
FROM students
JOIN high_scores ON high_scores.student_id = students.id;
When using Go, sqlc will produce a struct like this:
type HighScoreRow struct {
ID int64
Name sql.NullString
Age sql.NullInt32
HighScore int32
}
With embedding, the struct will contain a model for the table instead of a
flattened list of columns.
-- name: HighScoreEmbed :many
WITH high_scores AS (
SELECT student_id, max(score) as high_score
FROM test_scores
GROUP BY 1
)
SELECT sqlc.embed(students), high_score::integer
FROM students
JOIN high_scores ON high_scores.student_id = students.id;
type HighScoreRow struct {
Student Student
HighScore int32
}
Developed by Paul Cameron and Jille Timmermans
The MySQL Go driver does not support passing slices to the IN operator. The
sqlc.slice
function generates a dynamic query at runtime with the correct
number of parameters.
/* name: SelectStudents :many */
SELECT * FROM students
WHERE age IN (sqlc.slice("ages"))
func (q *Queries) SelectStudents(ctx context.Context, arges []int32) ([]Student, error) {
This feature is only supported in MySQL and cannot be used with prepared
queries.
When using batches with pgx, the error returned when a batch is closed is
exported by the generated package. This change allows for cleaner error
handling using errors.Is
.
errors.Is(err, generated_package.ErrBatchAlreadyClosed)
Previously, you would have had to check match on the error message itself.
err.Error() == "batch already closed"
The generated code for batch operations always lived in batch.go
. This file
name can now be configured via the output_batch_file_name
configuration
option.
By default, sqlc will limit Go functions to a single parameter. If a query
includes more than one parameter, the generated method will use an argument
struct instead of positional arguments. This behavior can now be changed via
the query_parameter_limit
configuration option. If set to 0
, every
generated method will use a argument struct.
SELECT NOT EXISTS
by @haines in https://github.com/kyleconroy/sqlc/pull/1972
sqlite
as engine option by @aaanders in https://github.com/kyleconroy/sqlc/pull/2164
sqlc.embed
to allow model re-use by @nickjackson in https://github.com/kyleconroy/sqlc/pull/1615
specifies parameter ":one" without containing a RETURNING clause
by @ihatov08 in https://github.com/kyleconroy/sqlc/pull/2173
--no-remote
flag by @andrewmbenton in https://github.com/kyleconroy/sqlc/pull/2218
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.17.2...v1.18.0
Published by kyleconroy over 1 year ago
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.17.0...v1.17.1
Published by kyleconroy over 1 year ago
This release contained a build failure for Windows and will not be released. Please see v1.17.2
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.17.0...v1.17.1
Published by kyleconroy over 1 year ago
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.16.0...v1.17.0
Published by kyleconroy almost 2 years ago
coalesce()
result .NotNull
value by @mpyw in https://github.com/kyleconroy/sqlc/pull/1664
AUTOINCREMENT
from example by @thecashewtrader in https://github.com/kyleconroy/sqlc/pull/1891
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.15.0...v1.16.0
Published by kyleconroy about 2 years ago
docs/reference/config.md
by @Dexter2389 in https://github.com/kyleconroy/sqlc/pull/1764
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.14.0...v1.15.0
Published by kyleconroy over 2 years ago
sql.narg
in doc by @akhilmhdh in https://github.com/kyleconroy/sqlc/pull/1668
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.13.0...v1.14.0
Published by kyleconroy over 2 years ago
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.12.0...v1.13.0
Published by kyleconroy over 2 years ago
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.11.0...v1.12.0
Published by kyleconroy almost 3 years ago
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.10.0...v1.11.0
Published by kyleconroy about 3 years ago
Release notes coming soon!
go test
in CI by @kyleconroy in https://github.com/kyleconroy/sqlc/pull/1134
Full Changelog: https://github.com/kyleconroy/sqlc/compare/v1.9.0...v1.10.0