Changelog

sqlc is a command line tool that generates type-safe code from SQL. Today we released sqlc v1.25.0, a smaller release that removes our reliance on cgo.

What’s new

C-ya, cgo

Over the last month we’ve updated a few different modules to remove our reliance on cgo. Previously we needed cgo for three separate functions:

• Parsing PostgreSQL queries with pganalyze/pg_query_go
• Connecting to SQLite databases with mattn/go-sqlite3
• Executing WASM / WASI plugins with bytecodealliance/wasmtime-go

With the help of the community, we found cgofree alternatives for each module:

• Parsing PostgreSQL queries now uses wasilibs/go-pgquery
• Connecting to SQLite databases now uses modernc.org/sqlite
• Executing WASM / WASI plugins now uses tetratelabs/wazero

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 these updated dependencies, please open an issue.

Add tags to push and verify

You can now 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 schema changes, allowing you to verify that a new schema works with a specific set of previous queries.

$ sqlc verify --against v1.0.0

Codegen for any parsable SQL statement

We removed the gate that prevented sqlc from generating code for “unsupported” query types. In theory this means you can generate code for any parsable SQL statement, and in particular sqlc will now give you codegen output for DDL statements like CREATE TABLE .... In practice there are still many parsable SQL statements that sqlc doesn’t know how to analyze, and sqlc may panic when it encounters them. So use caution and please report any issues you find.

And more…

There were more than 20 substantive features and bug fixes included in this release, including several contributions from first-time contributors. The full list of changes is in the sqlc changelog.

We’re so excited to announce the preview release of our TypeScript plugin, sqlc-gen-typescript. TypeScript now joins Go, Python and Kotlin as supported languages. Let’s dive in.

Quick tour

The getting started guide has a complete sqlc-gen-typescript walkthrough, but we wanted to give you a quick peek at using the generated code.

We’ll start with the SQL. We have one table, authors, and a few queries.

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

After configuring sqlc to pull in the TypeScript plugin and running sqlc generate, you can use your newly-generated code like so:

import { Pool } from "pg";

// sqlc generated ./db/query_sql.ts which includes these exported functions
import {
  createAuthor,
  deleteAuthor,
  getAuthor,
  listAuthors,
} from "./db/query_sql";

async function main() {
  const client = new Pool({ 
    connectionString: process.env["DATABASE_URL"]
  });
  await client.connect();

  // list all authors
  const authors = await listAuthors(client);
  console.log(authors);

  // create an author
  const author = await createAuthor(client, {
    name: "Anders Hejlsberg",
    bio: "Original author of Turbo Pascal and co-creator of TypeScript",
  });
  if (author === null) {
    throw new Error("author not created");
  }
  console.log(author);

  // get the author we just created
  const anders = await getAuthor(client, { id: author.id });
  if (anders === null) {
    throw new Error("anders not found");
  }
  console.log(anders);

  // delete the author
  await deleteAuthor(client, { id: anders.id });
}

Design considerations

We’ve learned a ton from building out our Go, Python and Kotlin support over the last four years. Our TypeScript codegen output incorporates much of that learning.

We generate individual functions, not methods on a class. This structure allows for easier composability.

We also take advantage of TypeScript’s more advanced type-system. Each function accepts and returns interfaces, meaning that we don’t have to have a single model definition for our methods. This choice makes it easy to use existing classes or types as long as they have the correct field definitions.

Limitations

We’re launching with support for PostgreSQL via pg or postgres and MySQL via mysql2.

A reminder that this release is the start of our TypeScript support. We opted to release it early to gather feedback before the 1.0.0 release. Expect breaking changes, incomplete feature support, and bugs. So many bugs.

How it’s built

Unlike our previous plugins which are all written in Go, sqlc-gen-typescript is written in TypeScript. The reason is simple: ensure that TypeScript developers are comfortable jumping into the code, understanding how it works, and contributing back. We also wanted to take advantage of the TypeScript compiler itself, which offers APIs for generating code from an abstract syntax tree (AST).

Because sqlc prefers to interact with WASM plugins, we needed a way to run JavaScript in a WASM context. Thankfully, this July Shopify and the Bytecode Alliance announced Javy, a JavaScript-to-WebAssembly toolchain.

To take advantage of Javy we have to do a few things. First we transpile and bundle our TypeScript into a single JavaScript file using esbuild. We then take that file and run it through Javy, which outputs a .wasm file. Not too shabby!

One downside to this approach is that the WASM blob is big. As of this writing, the 0.1.0 release of sqlc-gen-typescript clocks in at 69MB. Ouch! We’re confident that we can dramatically reduce the size by splitting out the parts of the TypeScript compiler that we use from the rest of the TypeScript package, but that optimization will have to wait for a future release.

Feedback wanted

This preview release is by no means complete. We’re looking to gather feedback from the community to shape a future 1.0 release. Please create an issue on GitHub, post a message on Discord, or send us an email at hello@sqlc.dev.

You can now sign up for an account using GitHub. GitHub is our second supported identity provider after Google.

We updated sqlc verify to emit a warning if any queries do a full table scan. Full table scans, which PostgreSQL calls a sequence scan, can be disastrous when performed against a large table, as the query will touch every row in the table.

Let’s say you have an authors table and a ListAuthors query that orders authors by name.

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

Running sqlc verify will warn that the ListAuthors query will do a sequence scan.

=== Warning
=== WARN: authors query.sql ListAuthors (sequence-scan-in-query-plan)
    Running this query may result in a sequence scan against the authors table.
    To suppress this warning, add the following annotation to your query:
        -- @sqlc-verify-disable sequence-scan-in-query-plan

    -- name: ListAuthors :many
    SELECT id, name, bio FROM authors
    ORDER BY name

If the table you’re querying is small, you can silence the warning by adding an annotation to your query.

-- name: ListAuthors :many
-- @sqlc-verify-disable sequence-scan-in-query-plan
SELECT * FROM authors
ORDER BY name;

sqlc is a command line tool that generates type-safe code from SQL. Today we released sqlc v1.24.0 which includes a new feature to verify the correctness of database schema changes.

Catch unsafe database migrations with sqlc verify

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;

To configure sqlc to push, set the project key in your cloud configuration to the value of your project ID, obtained via the dashboard.

version: '2'
cloud:
  project: '<PROJECT_ID>'
sql:
- schema: schema.sql
  queries: query.sql
  engine: postgresql
  gen:
    go:
      out: db

sqlc expects to find a valid auth token in the value of the SQLC_AUTH_TOKEN environment variable. You can create an auth token via the dashboard as well.

export SQLC_AUTH_TOKEN=sqlc_xxxxxxxx

Once those have been configured, run sqlc push:

$ sqlc push

So far, so good. Now let’s add a new database migration. Here we’re adding a new created_at column to the users table.

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.

sqlc verify was designed to catch these types of problems. It ensures migrations are safe to deploy by sending your current schema and queries to sqlc cloud. There, we run your existing queries against your new schema changes to find any issues.

Here sqlc verify alerts you to the fact that adding the new column isn’t safe.

$ 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.

Pushing schema and queries

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.

MySQL support in 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.

Plugin interface refactor

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.

And more…

There were more than 20 substantive features and bug fixes included in this release, including several contributions from first-time contributors. The full list of changes is in the sqlc changelog.

sqlc is a command line tool that generates type-safe code from SQL. Today we are publishing sqlc-gen-go, a Go codegen plugin extracted from sqlc’s source.

A brief history of sqlc codegen plugins

At sqlc’s inception it was a Go-specific tool. It was (and still is) written in Go, and Go developers used sqlc to generate Go code to talk to their databases. The Go community looked kindly upon sqlc, and decided that it was good.

Over time various users thought to themselves “Hey, sqlc is pretty good at generating Go code. I wonder if it could also generate code in other languages?” And thus was born sqlc’s Kotlin and Python support. The packages implementing that support sat alongside the Go codegen package, embedded in the sqlc source.

This setup was fine, but required contributors to write Go modifying the sqlc source code in order to make changes to the way sqlc generated Kotlin and Python. And it deterred potential contributors of new code generation packages who naturally wanted to write code in their native language rather than Go.

To fix those issues, in 2022 we developed sqlc’s external codegen plugin interface, defined by protobuf messages passed via stdin and stdout. This allowed plugin implementers to write a plugin as a standalone binary or WASM module with whatever tools they preferred, as long as the end result could deserialize and serialize protobuf.

Eventually the Kotlin and Python code generation packages embedded in sqlc were ported to WASM and extracted entirely from the sqlc source into their own plugin repositories (Kotlin, Python). But until today, Go codegen was still too deeply intertwined with sqlc to be extracted easily.

Go codegen as a sqlc plugin

In the past week we have finished the last remaining cleanup items blocking the publishing of sqlc’s internal Go codegen package as an externally-usable plugin. We’ve created a read-only sqlc-gen-go repository that we’ll keep up to date with changes from sqlc’s internal Go codegen package. The extraction process doesn’t require code changes other than rewriting some import paths.

All of the configuration options you use today with sqlc to generate Go will work with this plugin using the same code path as sqlc’s embedded Go codegen package. So we hope you’ll find that it’s a drop-in replacement for our built-in Go code generation, but if not please file an issue.

In time we’ll likely remove the Go codegen package from the sqlc source, and at that point the sqlc-gen-go source will become the canonical implementation. But we intend to support sqlc-gen-go as a first-class plugin starting today, and we encourage you to fork and modify it to meet your needs.

Migrating

Migrating is by no means required. But in case you need or want to, we’ve worked hard to make switching to sqlc-gen-go as seamless as possible. Let’s say you’re generating Go code today using a sqlc.yaml configuration that looks something like this:

version: 2
sql:
- schema: "query.sql"
  queries: "query.sql"
  engine: "postgresql"
  gen:
    go:
      package: "db"
      out: "db"
      emit_json_tags: true
      emit_pointers_for_null_types: true
      query_parameter_limit: 5
      overrides:
      - column: "authors.id"
        go_type: "your/package.SomeType"
      rename:
        foo: "bar"

To use the sqlc-gen-go WASM plugin for Go codegen, your config will instead look something like this:

version: 2
plugins:
- name: golang
  wasm:
    url: "https://downloads.sqlc.dev/plugin/sqlc-gen-go_1.0.1.wasm"
    sha256: "7aa8cd8c269822afa455ac4e7a56a0a12117512811a41f3457859770b315fc27"
sql:
- schema: "query.sql"
  queries: "query.sql"
  engine: "postgresql"
  codegen:
  - plugin: golang
    out: "db"
    options:
      package: "db"
      emit_json_tags: true
      emit_pointers_for_null_types: true
      query_parameter_limit: 5
      overrides:
      - column: "authors.id"
        go_type: "your/package.SomeType"
      rename:
        foo: "bar"

The differences are:

• An additional plugins list with an entry for the Go codegen WASM plugin. If you’ve built the plugin from source you’ll want to use a file:// URL. The sha256 field is required, but will be optional in the upcoming sqlc v1.24.0 release.
• Within the sql block, rather than gen with go nested beneath you’ll have a codegen list with an entry referencing the plugin name from the top-level plugins list. All options from the current go configuration block move as-is into the options block within the codegen entry. The only special case is out, which moves up a level into the codegen entry configuration itself.

Global overrides and renames

If you have global overrides or renames configured, you’ll need to move those to the new top-level options field. Replace the go field name with the name you gave your plugin in the plugins list. We’ve used golang in this example.

If your existing configuration looks like this:

version: "2"
overrides:
  go:
    rename:
      id: "Identifier"
    overrides:
    - db_type: "timestamptz"
      nullable: true
      engine: "postgresql"
      go_type:
        import: "gopkg.in/guregu/null.v4"
        package: "null"
        type: "Time"
...

Then your updated configuration would look something like this:

version: "2"
plugins:
- name: golang
  wasm:
    url: "https://downloads.sqlc.dev/plugin/sqlc-gen-go_1.0.1.wasm"
    sha256: "7aa8cd8c269822afa455ac4e7a56a0a12117512811a41f3457859770b315fc27"
options:
  golang:
    rename:
      id: "Identifier"
    overrides:
    - db_type: "timestamptz"
      nullable: true
      engine: "postgresql"
      go_type:
        import: "gopkg.in/guregu/null.v4"
        package: "null"
        type: "Time"
...

What’s next?

We’re releasing sqlc-gen-go as a separate codegen plugin so that it’s easier to make sqlc do what you want. We encourage you to fork and modify the sqlc-gen-go plugin to get the behavior you want in your own projects.

If you have an interest in developing a new plugin from scratch using Go, you can build on top of sqlc’s plugin sdk for Go. All of our external codegen plugins, including Go, rely on this sdk. And we’ll have an alpha TypeScript plugin based on this sdk coming soon.

sqlc is a command line tool that generates type-safe code from SQL. Today we released sqlc v1.23.0, so let’s take a look at what’s new.

Database-backed query analysis

At a high level, sqlc does three things: parse SQL statements, analyze those statements, and generate source code for applications to use those statements. The second step, analysis of statements, requires sqlc to understand or make assumptions about the internal workings of the database engines it supports.

Since its inception sqlc has relied on its own built-in static catalog information from various database engines in order to perform query analysis. It augments that with information gathered from your schema to determine things like the columns a query will return and the types of query parameters. But often this information isn’t sufficient for complicated queries, and sqlc will error when it shouldn’t or will generate source code that assumes a lowest-common-denominator untyped interface for parameters and return values.

We can do better, but attempting to recreate the entirety of a database’s analysis engine inside of sqlc is at best a difficult moving target and at worst a fool’s errand. So starting with version 1.23.0 sqlc can gather additional metadata information from a running database to enhance its query analysis. This resolves a very large number of issues in the sqlc backlog, so if you have a thorny query that sqlc previously couldn’t understand it’s fairly likely the new analysis engine will handle it.

Here’s a sqlc playground example of a query that confused sqlc previously. Running sqlc generate for Go used to create a func that returned interface{} instead of time.Time for the timestamp values. No more!

-- name: ActivityInfo :one
SELECT
    COUNT(*) as NumOfActivities,
    MIN(event_time) as MinDate, 
    MAX(event_time) as MaxDate 
FROM activities;

The new database-backed analysis engine supports PostgreSQL today, with MySQL and SQLite support planned.

You will need to add a database section to your sqlc configuration file to opt in. The easiest way to do that is with sqlc Cloud’s managed databases, but you can also point sqlc at any accessible database as long as you have a valid connection string and the database’s schema is up-to-date.

We’re very excited to have you try this out with some complicated queries and let us know what works and what doesn’t.

Play around in the playground

We’ve updated the sqlc playground to support the new database-backed analyzer using sqlc Cloud’s managed databases, all without creating an auth token. Here’s a playground configuration you can use as a starting point (you don’t need to modify the "<PROJECT_ID>", it’s a magic string that we replace):

version: "2"
cloud:
  project: "<PROJECT_ID>" # <- don't change this!
sql:
- schema: "schema.sql"
  queries: "query.sql"
  engine: "postgresql"
  database:
    managed: true
  gen:
    go:
      out: db

The "<PROJECT_ID>" value only works on the playground. To use managed databases with sqlc, you’ll need a project ID and an auth token from the sqlc Cloud dashboard.

New createdb command

Building on the ephemeral database infrastructure we created to support managed databases in sqlc generate and sqlc vet, in v1.23.0 we added a simple command called createdb.

When you have a cloud project configured, you can use the sqlc createdb command to spin up a fresh ephemeral database with your schema and print its connection string to standard output. This is useful for integrating with other tools that need short-lived access to a database with your schema, like sqlx and pgtyped.

In the simplest case, you can use psql to poke around and see that it works:

psql $(sqlc createdb)

Ephemeral databases only live for two minutes. Read more in the managed databases documentation.

Support for pgvector

If you’re using pgvector, say goodbye to custom overrides! As of v1.23.0 sqlc now generates code using pgvector-go as long as you’re using pgx/v5 as your database driver. We’ve also made the pgvector extension available to managed databases.

And more…

There were more than 30 substantive features and bug fixes included in this release, including several contributions from first-time contributors. The full list of changes is in the sqlc changelog.

sqlc is a command line tool that generates type-safe code from SQL. Today we released sqlc v1.22.0, so let’s take a look at what’s new.

Managed databases

A few months ago we introduced sqlc vet to run your queries through a set of lint rules. These rules can prepare queries and access EXPLAIN output. Naturally these rules need access to a running database, and until today you had to provide your own.

sqlc can now create and maintain hosted databases for linting queries or running other tests in your development and CI environments. PostgreSQL support launches in beta today with MySQL support planned for the future. Sign up here for early access, or send us an email at hello@sqlc.dev.

We are already using these managed databases in sqlc’s own CI pipeline. For instance, we have examples in the sqlc-dev/sqlc repo with tests that exercise the code sqlc generates. We’ve updated these tests to use our managed databases. Creating a database for each test ensures that every test starts from a clean slate.

We also updated the sqlc.yaml configurations for all of those examples to enable managed databases for sqlc vet. So we can now run all of our CI jobs without depending on a PostgreSQL sidecar running alongside our job runners. Take a look at this pull request for details.

And finally, we already test sqlc using a large suite of end-to-end integration tests. With managed databases we’ve added a new ddl test to ensure that all end-to-end tests themselves have a valid schema. Each pull request now seamlessly creates (and drops) about 1000 databases.

You can read more about how to configure this latest sqlc feature in the managed databases documentation.

Smaller bug fixes and improvements

sqlc now supports DO statements using the :exec query annotation. We fixed a regression in the last release that resulted in incorrect Go imports in some instances. Users of the SQLite engine can now insert more than one set of values. And many more. See the changelog for a complete list.

GopherCon 2023

Last but not least, we’ll be at GopherCon 2023 this coming Wednesday and Thursday wearing bright blue “sqlc” shirts. We’d love to hear how you’re using sqlc, so come find us and say “hi.”