



sqlc 1.18.0 documentation

[![Logo](_static/logo.png)](#)

 v1.18.0

 

Overview

* [Installing sqlc](index.html#document-overview/install)

Tutorials

* [Getting started with MySQL](index.html#document-tutorials/getting-started-mysql)

* [Getting started with PostgreSQL](index.html#document-tutorials/getting-started-postgresql)

* [Getting started with SQLite](index.html#document-tutorials/getting-started-sqlite)

How-to Guides

* [Retrieving rows](index.html#document-howto/select)

* [Counting rows](index.html#document-howto/query_count)

* [Inserting rows](index.html#document-howto/insert)

* [Updating rows](index.html#document-howto/update)

* [Deleting rows](index.html#document-howto/delete)

* [Preparing queries](index.html#document-howto/prepared_query)

* [Using transactions](index.html#document-howto/transactions)

* [Naming parameters](index.html#document-howto/named_parameters)

* [Modifying the database schema](index.html#document-howto/ddl)

* [Configuring generated structs](index.html#document-howto/structs)

* [Uploading projects](index.html#document-howto/upload)

Reference

* [CLI](index.html#document-reference/cli)

* [Configuration](index.html#document-reference/config)

* [Datatypes](index.html#document-reference/datatypes)

* [Query annotations](index.html#document-reference/query-annotations)

* [Database and language support](index.html#document-reference/language-support)

* [Environment variables](index.html#document-reference/environment-variables)

* [Changelog](index.html#document-reference/changelog)

Conceptual Guides

* [Using Go and pgx](index.html#document-guides/using-go-and-pgx)

* [Developing sqlc](index.html#document-guides/development)

* [Authoring plugins](index.html#document-guides/plugins)

* [Privacy and data collection](index.html#document-guides/privacy)

[sqlc](#)

* »

* sqlc 1.18.0 documentation

* [Edit on GitHub](https://github.com/kyleconroy/sqlc/blob/v1.18.0/docs/index)

---

sqlc Documentation[](#sqlc-documentation "Permalink to this headline")

=======================================================================

> 

> 

> And lo, the Great One looked down upon the people and proclaimed:“SQL is actually pretty great”

> 

> 

> 

> 

> 

sqlc generates **fully type-safe idiomatic Go code** from SQL. Here’s how it

works:

1. You write SQL queries

2. You run sqlc to generate Go code that presents type-safe interfaces to those

queries

3. You write application code that calls the methods sqlc generated

Seriously, it’s that easy. You don’t have to write any boilerplate SQL querying

code ever again.

Installing sqlc[](#installing-sqlc "Permalink to this headline")

-----------------------------------------------------------------

sqlc is distributed as a single binary with zero dependencies.

### macOS[](#macos "Permalink to this headline")

```

brew install sqlc

```

### Ubuntu[](#ubuntu "Permalink to this headline")

```

sudo snap install sqlc

```

### go install[](#go-install "Permalink to this headline")

#### Go >= 1.17:[](#go-1-17 "Permalink to this headline")

```

go install github.com/kyleconroy/sqlc/cmd/sqlc@latest

```

#### Go < 1.17:[](#id1 "Permalink to this headline")

```

go get github.com/kyleconroy/sqlc/cmd/sqlc

```

### Docker[](#docker "Permalink to this headline")

```

docker pull kjconroy/sqlc

```

Run `sqlc` using `docker run`:

```

docker run --rm -v $(pwd):/src -w /src kjconroy/sqlc generate

```

Run `sqlc` using `docker run` in the Command Prompt on Windows (`cmd`):

```

docker run --rm -v "%cd%:/src" -w /src kjconroy/sqlc generate

```

### Downloads[](#downloads "Permalink to this headline")

Get pre-built binaries for *v1.18.0*:

* [Linux](https://github.com/kyleconroy/sqlc/releases/download/v1.18.0/sqlc_1.18.0_linux_amd64.tar.gz)

* [macOS](https://github.com/kyleconroy/sqlc/releases/download/v1.18.0/sqlc_1.18.0_darwin_amd64.zip)

* [Windows (MySQL only)](https://github.com/kyleconroy/sqlc/releases/download/v1.18.0/sqlc_1.18.0_windows_amd64.zip)

See [downloads.sqlc.dev](https://downloads.sqlc.dev/) for older versions.

Getting started with MySQL[](#getting-started-with-mysql "Permalink to this headline")

---------------------------------------------------------------------------------------

This tutorial assumes that the latest version of sqlc is

[installed](../overview/install.html) and ready to use.

Create a new directory called `sqlc-tutorial` and open it up.

Initialize a new Go module named `tutorial.sql.dev/app`

```

go mod init tutorial.sqlc.dev/app

```

sqlc looks for either a `sqlc.yaml` or `sqlc.json` file in the current

directory. In our new directory, create a file named `sqlc.yaml` with the

following contents:

```

version: 1

packages:

  - path: "tutorial"

    name: "tutorial"

    engine: "mysql"

    schema: "schema.sql"

    queries: "query.sql"

```

sqlc needs to know your database schema and queries. In the same directory,

create a file named `schema.sql` with the following contents:

```

CREATE TABLE authors (

  id   BIGINT  NOT NULL AUTO\_INCREMENT PRIMARY KEY,

  name text    NOT NULL,

  bio  text

);

```

Next, create a `query.sql` file with the following four queries:

```

-- name: GetAuthor :one

SELECT \* FROM authors

WHERE id = ? LIMIT 1;

-- name: ListAuthors :many

SELECT \* FROM authors

ORDER BY name;

-- name: CreateAuthor :execresult

INSERT INTO authors (

  name, bio

) VALUES (

  ?, ?

);

-- name: DeleteAuthor :exec

DELETE FROM authors

WHERE id = ?;

```

You are now ready to generate code. Run the `generate` command. You shouldn’t see any errors or output.

```

sqlc generate

```

You should now have a `tutorial` package containing three files.

```

├── go.mod

├── query.sql

├── schema.sql

├── sqlc.yaml

└── tutorial

    ├── db.go

    ├── models.go

    └── query.sql.go

```

You can use your newly generated queries in `app.go`.

```

package main

import (

	"context"

	"database/sql"

	"log"

	"reflect"

	"tutorial.sqlc.dev/app/tutorial"

	\_ "github.com/go-sql-driver/mysql"

)

func run() error {

	ctx := context.Background()

	db, err := sql.Open("mysql", "user:password@/dbname")

	if err != nil {

		return err

	}

	queries := tutorial.New(db)

	// list all authors

	authors, err := queries.ListAuthors(ctx)

	if err != nil {

		return err

	}

	log.Println(authors)

	// create an author

	result, err := queries.CreateAuthor(ctx, tutorial.CreateAuthorParams{

		Name: "Brian Kernighan",

		Bio:  sql.NullString{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},

	})

	if err != nil {

		return err

	}

	insertedAuthorID, err := result.LastInsertId()

	if err != nil {

		return err

	}

	log.Println(insertedAuthorID)

	// get the author we just inserted

	fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthorID)

	if err != nil {

		return err

	}

	// prints true

	log.Println(reflect.DeepEqual(insertedAuthorID, fetchedAuthor.ID))

	return nil

}

func main() {

	if err := run(); err != nil {

		log.Fatal(err)

	}

}

```

Before the code will compile, you’ll need to add the Go MySQL driver.

```

go get github.com/go-sql-driver/mysql

go build ./...

```

To make that possible, sqlc generates readable, **idiomatic** Go code that you

otherwise would have had to write yourself. Take a look in `tutorial/query.sql.go`.

Getting started with PostgreSQL[](#getting-started-with-postgresql "Permalink to this headline")

-------------------------------------------------------------------------------------------------

This tutorial assumes that the latest version of sqlc is

[installed](../overview/install.html) and ready to use.

Create a new directory called `sqlc-tutorial` and open it up.

Initialize a new Go module named `tutorial.sqlc.dev/app`

```

go mod init tutorial.sqlc.dev/app

```

sqlc looks for either a `sqlc.yaml` or `sqlc.json` file in the current

directory. In our new directory, create a file named `sqlc.yaml` with the

following contents:

```

version: "2"

sql:

  - engine: "postgresql"

    queries: "query.sql"

    schema: "schema.sql"

    gen:

      go:

        package: "tutorial"

        out: "tutorial"

```

sqlc needs to know your database schema and queries in order to generate code.

In the same directory, create a file named `schema.sql` with the following

content:

```

CREATE TABLE authors (

  id   BIGSERIAL PRIMARY KEY,

  name text      NOT NULL,

  bio  text

);

```

Next, create a `query.sql` file with the following four queries:

```

-- 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;

```

If you **do not** want your SQL `UPDATE` queries to return the updated record

to the user, add this to `query.sql`:

```

-- name: UpdateAuthor :exec

UPDATE authors

  set name = $2,

  bio = $3

WHERE id = $1;

```

Otherwise, to return the updated record to the user, add this to `query.sql`:

```

-- name: UpdateAuthor :one

UPDATE authors

  set name = $2,

  bio = $3

WHERE id = $1

RETURNING *;

```

You are now ready to generate code. You shouldn’t see any errors or output.

```

sqlc generate

```

You should now have a `tutorial` package containing three files.

```

├── go.mod

├── query.sql

├── schema.sql

├── sqlc.yaml

└── tutorial

    ├── db.go

    ├── models.go

    └── query.sql.go

```

You can use your newly generated queries in `app.go`.

```

package main

import (

	"context"

	"database/sql"

	"log"

	"reflect"

	"tutorial.sqlc.dev/app/tutorial"

	\_ "github.com/lib/pq"

)

func run() error {

	ctx := context.Background()

	db, err := sql.Open("postgres", "user=pqgotest dbname=pqgotest sslmode=verify-full")

	if err != nil {

		return err

	}

	queries := tutorial.New(db)

	// list all authors

	authors, err := queries.ListAuthors(ctx)

	if err != nil {

		return err

	}

	log.Println(authors)

	// create an author

	insertedAuthor, err := queries.CreateAuthor(ctx, tutorial.CreateAuthorParams{

		Name: "Brian Kernighan",

		Bio:  sql.NullString{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},

	})

	if err != nil {

		return err

	}

	log.Println(insertedAuthor)

	// get the author we just inserted

	fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthor.ID)

	if err != nil {

		return err

	}

	// prints true

	log.Println(reflect.DeepEqual(insertedAuthor, fetchedAuthor))

	return nil

}

func main() {

	if err := run(); err != nil {

		log.Fatal(err)

	}

}

```

Before the code will compile, you’ll need to add the Go PostgreSQL driver.

```

go get github.com/lib/pq

go build ./...

```

sqlc generates readable, **idiomatic** Go code that you otherwise would have

had to write yourself. Take a look in the `tutorial` package to see what code

sqlc generated.

Getting started with SQLite[](#getting-started-with-sqlite "Permalink to this headline")

-----------------------------------------------------------------------------------------

This tutorial assumes that the latest version of sqlc is

[installed](../overview/install.html) and ready to use.

Create a new directory called `sqlc-tutorial` and open it up.

Initialize a new Go module named `tutorial.sql.dev/app`

```

go mod init tutorial.sqlc.dev/app

```

sqlc looks for either a `sqlc.yaml` or `sqlc.json` file in the current

directory. In our new directory, create a file named `sqlc.yaml` with the

following contents:

```

version: 2

sql:

  - engine: "sqlite"

    schema: "schema.sql"

    queries: "query.sql"

    gen:

      go:

        package: "tutorial"

        out: "tutorial"

```

sqlc needs to know your database schema and queries. In the same directory,

create a file named `schema.sql` with the following contents:

```

CREATE TABLE authors (

  id   INTEGER PRIMARY KEY,

  name text    NOT NULL,

  bio  text

);

```

Next, create a `query.sql` file with the following four queries:

```

-- name: GetAuthor :one

SELECT \* FROM authors

WHERE id = ? LIMIT 1;

-- name: ListAuthors :many

SELECT \* FROM authors

ORDER BY name;

-- name: CreateAuthor :one

INSERT INTO authors (

  name, bio

) VALUES (

  ?, ?

)

RETURNING \*;

-- name: DeleteAuthor :exec

DELETE FROM authors

WHERE id = ?;

```

For SQL UPDATE if you do not want to return the updated record to the user, add this to the `query.sql` file:

```

-- name: UpdateAuthor :exec

UPDATE authors

set name = ?,

bio = ?

WHERE id = ?;

```

Otherwise, to return the updated record back to the user, add this to the `query.sql` file:

```

-- name: UpdateAuthor :one

UPDATE authors

set name = ?,

bio = ?

WHERE id = ?

RETURNING \*;

```

You are now ready to generate code. Run the `generate` command. You shouldn’t see any errors or output.

```

sqlc generate

```

You should now have a `tutorial` package containing three files.

```

├── go.mod

├── query.sql

├── schema.sql

├── sqlc.yaml

└── tutorial

    ├── db.go

    ├── models.go

    └── query.sql.go

```

You can use your newly generated queries in `app.go`.

```

package main

import (

	"context"

	"database/sql"

	"log"

	"reflect"

	"tutorial.sqlc.dev/app/tutorial"

	\_ "embed"

	\_ "github.com/mattn/go-sqlite3"

)

//go:embed schema.sql

var ddl string

func run() error {

	ctx := context.Background()

	db, err := sql.Open("sqlite3", ":memory:")

	if err != nil {

		return err

	}

	// create tables

	if \_, err := db.ExecContext(ctx, ddl); err != nil {

		return err

	}

	queries := tutorial.New(db)

	// list all authors

	authors, err := queries.ListAuthors(ctx)

	if err != nil {

		return err

	}

	log.Println(authors)

	// create an author

	insertedAuthor, err := queries.CreateAuthor(ctx, tutorial.CreateAuthorParams{

		Name: "Brian Kernighan",

		Bio:  sql.NullString{String: "Co-author of The C Programming Language and The Go Programming Language", Valid: true},

	})

	if err != nil {

		return err

	}

	log.Println(insertedAuthor)

	// get the author we just inserted

	fetchedAuthor, err := queries.GetAuthor(ctx, insertedAuthor.ID)

	if err != nil {

		return err

	}

	// prints true

	log.Println(reflect.DeepEqual(insertedAuthor, fetchedAuthor))

	return nil

}

func main() {

	if err := run(); err != nil {

		log.Fatal(err)

	}

}

```

Before the code will compile, you’ll need to add the Go SQLite driver.

```

go mod tidy

go build ./...

```

To make that possible, sqlc generates readable, **idiomatic** Go code that you

otherwise would have had to write yourself. Take a look in `tutorial/query.sql.go`.

Retrieving rows[](#retrieving-rows "Permalink to this headline")

-----------------------------------------------------------------

To generate a database access method, annotate a query with a specific comment.

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  bio        text   NOT NULL,

  birth_year int    NOT NULL

);

-- name: GetAuthor :one

SELECT * FROM authors

WHERE id = $1;

-- name: ListAuthors :many

SELECT * FROM authors

ORDER BY id;

```

A few new pieces of code are generated beyond the `Author` struct. An interface

for the underlying database is generated. The `\*sql.DB` and `\*sql.Tx` types

satisfy this interface.

The database access methods are added to a `Queries` struct, which is created

using the `New` method.

Note that the `\*` in our query has been replaced with explicit column names.

This change ensures that the query will never return unexpected data.

Our query was annotated with `:one`, meaning that it should only return a

single row. We scan the data from that one into a `Author` struct.

Since the get query has a single parameter, the `GetAuthor` method takes a single

`int` as an argument.

Since the list query has no parameters, the `ListAuthors` method accepts no

arguments.

```

package db

import (

	"context"

	"database/sql"

)

type Author struct {

	ID        int

	Bio       string

	BirthYear int

}

type DBTX interface {

	QueryContext(context.Context, string, ...interface{}) (\*sql.Rows, error)

	QueryRowContext(context.Context, string, ...interface{}) \*sql.Row

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const getAuthor = `-- name: GetAuthor :one

SELECT id, bio, birth\_year FROM authors

WHERE id = $1

`

func (q \*Queries) GetAuthor(ctx context.Context, id int) (Author, error) {

	row := q.db.QueryRowContext(ctx, getAuthor, id)

	var i Author

	err := row.Scan(&i.ID, &i.Bio, &i.BirthYear)

	return i, err

}

const listAuthors = `-- name: ListAuthors :many

SELECT id, bio, birth\_year FROM authors

ORDER BY id

`

func (q \*Queries) ListAuthors(ctx context.Context) ([]Author, error) {

	rows, err := q.db.QueryContext(ctx, listAuthors)

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	var items []Author

	for rows.Next() {

		var i Author

		if err := rows.Scan(&i.ID, &i.Bio, &i.BirthYear); err != nil {

			return nil, err

		}

		items = append(items, i)

	}

	if err := rows.Close(); err != nil {

		return nil, err

	}

	if err := rows.Err(); err != nil {

		return nil, err

	}

	return items, nil

}

```

### Selecting columns[](#selecting-columns "Permalink to this headline")

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  bio        text   NOT NULL,

  birth_year int    NOT NULL

);

-- name: GetBioForAuthor :one

SELECT bio FROM authors

WHERE id = $1;

-- name: GetInfoForAuthor :one

SELECT bio, birth_year FROM authors

WHERE id = $1;

```

When selecting a single column, only that value that returned. The `GetBioForAuthor`

method takes a single `int` as an argument and returns a `string` and an

`error`.

When selecting multiple columns, a row record (method-specific struct) is

returned. In this case, `GetInfoForAuthor` returns a struct with two fields:

`Bio` and `BirthYear`.

```

package db

import (

	"context"

	"database/sql"

)

type DBTX interface {

	QueryRowContext(context.Context, string, ...interface{}) \*sql.Row

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const getBioForAuthor = `-- name: GetBioForAuthor :one

SELECT bio FROM authors

WHERE id = $1

`

func (q \*Queries) GetBioForAuthor(ctx context.Context, id int) (string, error) {

	row := q.db.QueryRowContext(ctx, getBioForAuthor, id)

	var i string

	err := row.Scan(&i)

	return i, err

}

const getInfoForAuthor = `-- name: GetInfoForAuthor :one

SELECT bio, birth\_year FROM authors

WHERE id = $1

`

type GetInfoForAuthorRow struct {

	Bio       string

	BirthYear int

}

func (q \*Queries) GetInfoForAuthor(ctx context.Context, id int) (GetInfoForAuthorRow, error) {

	row := q.db.QueryRowContext(ctx, getInfoForAuthor, id)

	var i GetInfoForAuthorRow

	err := row.Scan(&i.Bio, &i.BirthYear)

	return i, err

}

```

### Passing a slice as a parameter to a query[](#passing-a-slice-as-a-parameter-to-a-query "Permalink to this headline")

#### PostgreSQL[](#postgresql "Permalink to this headline")

In PostgreSQL,

[ANY](https://www.postgresql.org/docs/current/functions-comparisons.html#id-1.5.8.28.16)

allows you to check if a value exists in an array expression. Queries using ANY

with a single parameter will generate method signatures with slices as

arguments. Use the postgres data types, eg: int, varchar, etc.

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  bio        text   NOT NULL,

  birth_year int    NOT NULL

);

-- name: ListAuthorsByIDs :many

SELECT * FROM authors

WHERE id = ANY($1::int[]);

```

The above SQL will generate the following code:

```

package db

import (

	"context"

	"database/sql"

	"github.com/lib/pq"

)

type Author struct {

	ID        int

	Bio       string

	BirthYear int

}

type DBTX interface {

	QueryContext(context.Context, string, ...interface{}) (\*sql.Rows, error)

	QueryRowContext(context.Context, string, ...interface{}) \*sql.Row

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const listAuthors = `-- name: ListAuthorsByIDs :many

SELECT id, bio, birth\_year FROM authors

WHERE id = ANY($1::int[])

`

func (q \*Queries) ListAuthorsByIDs(ctx context.Context, ids []int) ([]Author, error) {

	rows, err := q.db.QueryContext(ctx, listAuthors, pq.Array(ids))

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	var items []Author

	for rows.Next() {

		var i Author

		if err := rows.Scan(&i.ID, &i.Bio, &i.BirthYear); err != nil {

			return nil, err

		}

		items = append(items, i)

	}

	if err := rows.Close(); err != nil {

		return nil, err

	}

	if err := rows.Err(); err != nil {

		return nil, err

	}

	return items, nil

}

```

#### MySQL[](#mysql "Permalink to this headline")

MySQL differs from PostgreSQL in that placeholders must be generated based on

the number of elements in the slice you pass in. Though trivial it is still

something of a nuisance. The passed in slice must not be nil or empty or an

error will be returned (ie not a panic). The placeholder insertion location is

marked by the meta-function `sqlc.slice()` (which is similar to `sqlc.arg()`

that you see documented under [Naming parameters](named_parameters)).

To rephrase, the `sqlc.slice('param')` behaves identically to `sqlc.arg()` it

terms of how it maps the explicit argument to the function signature, eg:

* `sqlc.slice('ids')` maps to `ids []GoType` in the function signature

* `sqlc.slice(cust\_ids)` maps to `custIds []GoType` in the function signature

(like `sqlc.arg()`, the parameter does not have to be quoted)

This feature is not compatible with `emit\_prepared\_queries` statement found in the

[Configuration file](index.html#document-reference/config).

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  bio        text   NOT NULL,

  birth\_year int    NOT NULL

);

-- name: ListAuthorsByIDs :many

SELECT \* FROM authors

WHERE id IN (sqlc.slice('ids'));

```

The above SQL will generate the following code:

```

package db

import (

	"context"

	"database/sql"

	"fmt"

	"strings"

)

type Author struct {

	ID        int

	Bio       string

	BirthYear int

}

type DBTX interface {

	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)

	PrepareContext(context.Context, string) (\*sql.Stmt, error)

	QueryContext(context.Context, string, ...interface{}) (\*sql.Rows, error)

	QueryRowContext(context.Context, string, ...interface{}) \*sql.Row

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

func (q \*Queries) WithTx(tx \*sql.Tx) \*Queries {

	return &Queries{

		db: tx,

	}

}

const listAuthorsByIDs = `-- name: ListAuthorsByIDs :many

SELECT id, bio, birth\_year FROM authors

WHERE id IN (/\*SLICE:ids\*/?)

`

func (q \*Queries) ListAuthorsByIDs(ctx context.Context, ids []int64) ([]Author, error) {

	sql := listAuthorsByIDs

	var queryParams []interface{}

	if len(ids) == 0 {

		return nil, fmt.Errorf("slice ids must have at least one element")

	}

	for \_, v := range ids {

		queryParams = append(queryParams, v)

	}

	sql = strings.Replace(sql, "/\*SLICE:ids\*/?", strings.Repeat(",?", len(ids))[1:], 1)

	rows, err := q.db.QueryContext(ctx, sql, queryParams...)

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	var items []Author

	for rows.Next() {

		var i Author

		if err := rows.Scan(&i.ID, &i.Bio, &i.BirthYear); err != nil {

			return nil, err

		}

		items = append(items, i)

	}

	if err := rows.Close(); err != nil {

		return nil, err

	}

	if err := rows.Err(); err != nil {

		return nil, err

	}

	return items, nil

}

```

Counting rows[](#counting-rows "Permalink to this headline")

-------------------------------------------------------------

```

CREATE TABLE authors (

  id       SERIAL PRIMARY KEY,

  hometown text   NOT NULL

);

-- name: CountAuthors :one

SELECT count(\*) FROM authors;

-- name: CountAuthorsByTown :many

SELECT hometown, count(\*) FROM authors

GROUP BY 1

ORDER BY 1;

```

```

package db

import (

	"context"

	"database/sql"

)

type DBTX interface {

	QueryContext(context.Context, string, ...interface{}) (\*sql.Rows, error)

	QueryRowContext(context.Context, string, ...interface{}) \*sql.Row

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const countAuthors = `-- name: CountAuthors :one

SELECT count(\*) FROM authors

`

func (q \*Queries) CountAuthors(ctx context.Context) (int, error) {

	row := q.db.QueryRowContext(ctx, countAuthors)

	var i int

	err := row.Scan(&i)

	return i, err

}

const countAuthorsByTown = `-- name: CountAuthorsByTown :many

SELECT hometown, count(\*) FROM authors

GROUP BY 1

ORDER BY 1

`

type CountAuthorsByTownRow struct {

	Hometown string

	Count    int

}

func (q \*Queries) CountAuthorsByTown(ctx context.Context) ([]CountAuthorsByTownRow, error) {

	rows, err := q.db.QueryContext(ctx, countAuthorsByTown)

	if err != nil {

		return nil, err

	}

	defer rows.Close()

	items := []CountAuthorsByTownRow{}

	for rows.Next() {

		var i CountAuthorsByTownRow

		if err := rows.Scan(&i.Hometown, &i.Count); err != nil {

			return nil, err

		}

		items = append(items, i)

	}

	if err := rows.Close(); err != nil {

		return nil, err

	}

	if err := rows.Err(); err != nil {

		return nil, err

	}

	return items, nil

}

```

Inserting rows[](#inserting-rows "Permalink to this headline")

---------------------------------------------------------------

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  bio        text   NOT NULL

);

-- name: CreateAuthor :exec

INSERT INTO authors (bio) VALUES ($1);

```

```

package db

import (

	"context"

	"database/sql"

)

type DBTX interface {

	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const createAuthor = `-- name: CreateAuthor :exec

INSERT INTO authors (bio) VALUES ($1)

`

func (q \*Queries) CreateAuthor(ctx context.Context, bio string) error {

	\_, err := q.db.ExecContext(ctx, createAuthor, bio)

	return err

}

```

### Returning columns from inserted rows[](#returning-columns-from-inserted-rows "Permalink to this headline")

sqlc has full support for the `RETURNING` statement.

```

-- Example queries for sqlc

CREATE TABLE authors (

  id   BIGSERIAL PRIMARY KEY,

  name text      NOT NULL,

  bio  text

);

-- name: CreateAuthor :one

INSERT INTO authors (

  name, bio

) VALUES (

  $1, $2

)

RETURNING *;

-- name: CreateAuthorAndReturnId :one

INSERT INTO authors (

  name, bio

) VALUES (

  $1, $2

)

RETURNING id;

```

```

package db

import (

	"context"

	"database/sql"

)

const createAuthor = `-- name: CreateAuthor :one

INSERT INTO authors (

 name, bio

) VALUES (

 $1, $2

)

RETURNING id, name, bio

`

type CreateAuthorParams struct {

	Name string

	Bio  sql.NullString

}

func (q \*Queries) CreateAuthor(ctx context.Context, arg CreateAuthorParams) (Author, error) {

	row := q.db.QueryRowContext(ctx, createAuthor, arg.Name, arg.Bio)

	var i Author

	err := row.Scan(&i.ID, &i.Name, &i.Bio)

	return i, err

}

const createAuthorAndReturnId = `-- name: CreateAuthorAndReturnId :one

INSERT INTO authors (

 name, bio

) VALUES (

 $1, $2

)

RETURNING id

`

type CreateAuthorAndReturnIdParams struct {

	Name string

	Bio  sql.NullString

}

func (q \*Queries) CreateAuthorAndReturnId(ctx context.Context, arg CreateAuthorAndReturnIdParams) (int64, error) {

	row := q.db.QueryRowContext(ctx, createAuthorAndReturnId, arg.Name, arg.Bio)

	var id int64

	err := row.Scan(&id)

	return id, err

}

```

### Using CopyFrom[](#using-copyfrom "Permalink to this headline")

PostgreSQL supports the Copy Protocol that can insert rows a lot faster than sequential inserts. You can use this easily with sqlc:

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  name       text   NOT NULL,

  bio        text   NOT NULL

);

-- name: CreateAuthors :copyfrom

INSERT INTO authors (name, bio) VALUES ($1, $2);

```

```

type CreateAuthorsParams struct {

	Name string

	Bio  string

}

func (q \*Queries) CreateAuthors(ctx context.Context, arg []CreateAuthorsParams) (int64, error) {

	return q.db.CopyFrom(ctx, []string{"authors"}, []string{"name", "bio"}, &iteratorForCreateAuthors{rows: arg})

}

```

Updating rows[](#updating-rows "Permalink to this headline")

-------------------------------------------------------------

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  bio        text   NOT NULL

);

```

### Single parameter[](#single-parameter "Permalink to this headline")

If your query has a single parameter, your Go method will also have a single

parameter.

```

-- name: UpdateAuthorBios :exec

UPDATE authors SET bio = $1;

```

```

package db

import (

	"context"

	"database/sql"

)

type DBTX interface {

	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const updateAuthorBios = `-- name: UpdateAuthorBios :exec

UPDATE authors SET bio = $1

`

func (q \*Queries) UpdateAuthorBios(ctx context.Context, bio string) error {

	\_, err := q.db.ExecContext(ctx, updateAuthorBios, bio)

	return err

}

```

### Multiple parameters[](#multiple-parameters "Permalink to this headline")

If your query has more than one parameter, your Go method will accept a

`Params` struct.

```

-- name: UpdateAuthor :exec

UPDATE authors SET bio = $2

WHERE id = $1;

```

```

package db

import (

	"context"

	"database/sql"

)

type DBTX interface {

	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const updateAuthor = `-- name: UpdateAuthor :exec

UPDATE authors SET bio = $2

WHERE id = $1

`

type UpdateAuthorParams struct {

	ID  int32

	Bio string

}

func (q \*Queries) UpdateAuthor(ctx context.Context, arg UpdateAuthorParams) error {

	\_, err := q.db.ExecContext(ctx, updateAuthor, arg.ID, arg.Bio)

	return err

}

```

Deleting rows[](#deleting-rows "Permalink to this headline")

-------------------------------------------------------------

```

CREATE TABLE authors (

  id         SERIAL PRIMARY KEY,

  bio        text   NOT NULL

);

-- name: DeleteAuthor :exec

DELETE FROM authors WHERE id = $1;

```

```

package db

import (

	"context"

	"database/sql"

)

type DBTX interface {

	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

const deleteAuthor = `-- name: DeleteAuthor :exec

DELETE FROM authors WHERE id = $1

`

func (q \*Queries) DeleteAuthor(ctx context.Context, id int) error {

	\_, err := q.db.ExecContext(ctx, deleteAuthor, id)

	return err

}

```

Preparing queries[](#preparing-queries "Permalink to this headline")

---------------------------------------------------------------------

```

CREATE TABLE records (

  id SERIAL PRIMARY KEY

);

-- name: GetRecord :one

SELECT * FROM records

WHERE id = $1;

```

sqlc has an option to use prepared queries. These prepared queries also work

with transactions.

```

package db

import (

	"context"

	"database/sql"

	"fmt"

)

type Record struct {

	ID int32

}

type DBTX interface {

	PrepareContext(context.Context, string) (\*sql.Stmt, error)

	QueryRowContext(context.Context, string, ...interface{}) \*sql.Row

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

func Prepare(ctx context.Context, db DBTX) (\*Queries, error) {

	q := Queries{db: db}

	var err error

	if q.getRecordStmt, err = db.PrepareContext(ctx, getRecord); err != nil {

		return nil, fmt.Errorf("error preparing query GetRecord: %w", err)

	}

	return &q, nil

}

func (q \*Queries) queryRow(ctx context.Context, stmt \*sql.Stmt, query string, args ...interface{}) \*sql.Row {

	switch {

	case stmt != nil && q.tx != nil:

		return q.tx.StmtContext(ctx, stmt).QueryRowContext(ctx, args...)

	case stmt != nil:

		return stmt.QueryRowContext(ctx, args...)

	default:

		return q.db.QueryRowContext(ctx, query, args...)

	}

}

type Queries struct {

	db            DBTX

	tx            \*sql.Tx

	getRecordStmt \*sql.Stmt

}

func (q \*Queries) WithTx(tx \*sql.Tx) \*Queries {

	return &Queries{

		db:            tx,

		tx:            tx,

		getRecordStmt: q.getRecordStmt,

	}

}

const getRecord = `-- name: GetRecord :one

SELECT id FROM records

WHERE id = $1

`

func (q \*Queries) GetRecord(ctx context.Context, id int32) (int32, error) {

	row := q.queryRow(ctx, q.getRecordStmt, getRecord, id)

	err := row.Scan(&id)

	return id, err

}

```

Using transactions[](#using-transactions "Permalink to this headline")

-----------------------------------------------------------------------

In the code generated by sqlc, the `WithTx` method allows a `Queries` instance to be associated with a transaction.

For example, with the following SQL structure:

`schema.sql`:

```

CREATE TABLE records (

  id SERIAL PRIMARY KEY,

  counter INT NOT NULL

);

```

`query.sql`

```

-- name: GetRecord :one

SELECT * FROM records

WHERE id = $1;

-- name: UpdateRecord :exec

UPDATE records SET counter = $2

WHERE id = $1;

```

And the generated code from sqlc in `db.go`:

```

package tutorial

import (

	"context"

	"database/sql"

)

type DBTX interface {

	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)

	PrepareContext(context.Context, string) (\*sql.Stmt, error)

	QueryContext(context.Context, string, ...interface{}) (\*sql.Rows, error)

	QueryRowContext(context.Context, string, ...interface{}) \*sql.Row

}

func New(db DBTX) \*Queries {

	return &Queries{db: db}

}

type Queries struct {

	db DBTX

}

func (q \*Queries) WithTx(tx \*sql.Tx) \*Queries {

	return &Queries{

		db: tx,

	}

}

```

You’d use it like this:

```

func bumpCounter(ctx context.Context, db \*sql.DB, queries \*tutorial.Queries, id int32) error {

	tx, err := db.Begin()

	if err != nil {

		return err

	}

	defer tx.Rollback()

	qtx := queries.WithTx(tx)

	r, err := qtx.GetRecord(ctx, id)

	if err != nil {

		return err

	}

	if err := qtx.UpdateRecord(ctx, tutorial.UpdateRecordParams{

		ID:      r.ID,

		Counter: r.Counter + 1,

	}); err != nil {

		return err

	}

	return tx.Commit()

}

```

Naming parameters[](#naming-parameters "Permalink to this headline")

---------------------------------------------------------------------

sqlc tried to generate good names for positional parameters, but sometimes it

lacks enough context. The following SQL generates parameters with less than

ideal names:

```

-- name: UpsertAuthorName :one

UPDATE author

SET

  name = CASE WHEN $1::bool

    THEN $2::text

    ELSE name

    END

RETURNING *;

```

```

type UpdateAuthorNameParams struct {

	Column1   bool   `json:""`

	Column2\_2 string `json:"\_2"`

}

```

In these cases, named parameters give you the control over field names on the

Params struct.

```

-- name: UpsertAuthorName :one

UPDATE author

SET

  name = CASE WHEN sqlc.arg(set\_name)::bool

    THEN sqlc.arg(name)::text

    ELSE name

    END

RETURNING \*;

```

```

type UpdateAuthorNameParams struct {

	SetName bool   `json:"set\_name"`

	Name    string `json:"name"`

}

```

If the `sqlc.arg()` syntax is too verbose for your taste, you can use the `@`

operator as a shortcut.

```

-- name: UpsertAuthorName :one

UPDATE author

SET

  name = CASE WHEN @set\_name::bool

    THEN @name::text

    ELSE name

    END

RETURNING \*;

```

### Nullable parameters[](#nullable-parameters "Permalink to this headline")

sqlc infers the nullability of any specified parameters, and often does exactly

what you want. If you want finer control over the nullability of your

parameters, you may use `sqlc.narg()` (**n**ullable arg) to override the default

behavior. Using `sqlc.narg` tells sqlc to ignore whatever nullability it has

inferred and generate a nullable parameter instead. There is no nullable

equivalent of the `@` syntax.

Here is an example that uses a single query to allow updating an author’s

name, bio or both.

```

-- name: UpdateAuthor :one

UPDATE author

SET

 name = coalesce(sqlc.narg('name'), name),

 bio = coalesce(sqlc.narg('bio'), bio)

WHERE id = sqlc.arg('id')

RETURNING \*;

```

The following code is generated:

```

type UpdateAuthorParams struct {

	Name sql.NullString

	Bio  sql.NullString

	ID   int64

}

```

Modifying the database schema[](#modifying-the-database-schema "Permalink to this headline")

---------------------------------------------------------------------------------------------

sqlc parses `CREATE TABLE` and `ALTER TABLE` statements in order to generate

the necessary code.

```

CREATE TABLE authors (

  id          SERIAL PRIMARY KEY,

  birth\_year  int    NOT NULL

);

ALTER TABLE authors ADD COLUMN bio text NOT NULL;

ALTER TABLE authors DROP COLUMN birth\_year;

ALTER TABLE authors RENAME TO writers;

```

```

package db

type Writer struct {

	ID  int

	Bio string

}

```

### Handling SQL migrations[](#handling-sql-migrations "Permalink to this headline")

sqlc does not perform database migrations for you. However, sqlc is able to

differentiate between up and down migrations. sqlc ignores down migrations when

parsing SQL files.

sqlc supports parsing migrations from the following tools:

* [dbmate](https://github.com/amacneil/dbmate)

* [golang-migrate](https://github.com/golang-migrate/migrate)

* [goose](https://github.com/pressly/goose)

* [sql-migrate](https://github.com/rubenv/sql-migrate)

* [tern](https://github.com/jackc/tern)

#### goose[](#goose "Permalink to this headline")

```

-- +goose Up

CREATE TABLE post (

    id    int NOT NULL,

    title text,

    body  text,

    PRIMARY KEY(id)

);

-- +goose Down

DROP TABLE post;

```

```

package db

type Post struct {

	ID    int

	Title sql.NullString

	Body  sql.NullString

}

```

#### sql-migrate[](#sql-migrate "Permalink to this headline")

```

-- +migrate Up

-- SQL in section 'Up' is executed when this migration is applied

CREATE TABLE people (id int);

-- +migrate Down

-- SQL section 'Down' is executed when this migration is rolled back

DROP TABLE people;

```

```

package db

type People struct {

	ID int32

}

```

#### tern[](#tern "Permalink to this headline")

```

CREATE TABLE comment (id int NOT NULL, text text NOT NULL);

---- create above / drop below ----

DROP TABLE comment;

```

```

package db

type Comment struct {

	ID   int32

	Text string

}

```

#### golang-migrate[](#golang-migrate "Permalink to this headline")

**Warning:**

[golang-migrate interprets](https://github.com/golang-migrate/migrate/blob/master/MIGRATIONS.md#migration-filename-format)

migration filenames numerically. However, sqlc parses migration files in

lexicographic order. If you choose to have sqlc enumerate your migration files,

make sure their numeric ordering matches their lexicographic ordering to avoid

unexpected behavior. This can be done by prepending enough zeroes to the

migration filenames.

This doesn’t work as intended.

```

1\_initial.up.sql

...

9\_foo.up.sql

# this migration file will be parsed BEFORE 9\_foo

10\_bar.up.sql

```

This worked as intended.

```

001\_initial.up.sql

...

009\_foo.up.sql

010\_bar.up.sql

```

In `20060102.up.sql`:

```

CREATE TABLE post (

    id    int NOT NULL,

    title text,

    body  text,

    PRIMARY KEY(id)

);

```

In `20060102.down.sql`:

```

DROP TABLE post;

```

```

package db

type Post struct {

	ID    int

	Title sql.NullString

	Body  sql.NullString

}

```

#### dbmate[](#dbmate "Permalink to this headline")

```

-- migrate:up

CREATE TABLE foo (bar INT NOT NULL);

-- migrate:down

DROP TABLE foo;

```

```

package db

type Foo struct {

	Bar int32

}

```

Configuring generated structs[](#configuring-generated-structs "Permalink to this headline")

---------------------------------------------------------------------------------------------

### Naming scheme[](#naming-scheme "Permalink to this headline")

Structs generated from tables will attempt to use the singular form of a table

name if the table name is pluralized.

```

CREATE TABLE authors (

  id   SERIAL PRIMARY KEY,

  name text   NOT NULL

);

```

```

package db

// Struct names use the singular form of table names

type Author struct {

	ID   int

	Name string

}

```

### JSON tags[](#json-tags "Permalink to this headline")

```

CREATE TABLE authors (

  id         SERIAL    PRIMARY KEY,

  created\_at timestamp NOT NULL

);

```

sqlc can generate structs with JSON tags. The JSON name for a field matches

the column name in the database.

```

package db

import (

	"time"

)

type Author struct {

	ID        int       `json:"id"`

	CreatedAt time.Time `json:"created\_at"`

}

```

### More control[](#more-control "Permalink to this headline")

See the Type Overrides section of the Configuration File docs for fine-grained control over struct field types and tags.

Uploading projects[](#uploading-projects "Permalink to this headline")

-----------------------------------------------------------------------

*This feature requires signing up for [sqlc Cloud](https://app.sqlc.dev), which is currently in beta.*

Uploading your project ensures that future releases of sqlc do not break your

existing code. Similar to Rust’s [crater](https://github.com/rust-lang/crater)

project, uploaded projects are tested against development releases of sqlc to

verify correctness.

### Add configuration[](#add-configuration "Permalink to this headline")

After creating a project, add the project ID to your sqlc configuration file.

```

version: "1"

project:

  id: "<PROJECT-ID>"

packages: []

```

```

{

  "version": "1",

  "project": {

    "id": "<PROJECT-ID>"

  },

  "packages": [

  ]

}

```

You’ll also need to create an API token and make it available via the

`SQLC\_AUTH\_TOKEN` environment variable.

```

export SQLC\_AUTH\_TOKEN=sqlc_xxxxxxxx

```

### Dry run[](#dry-run "Permalink to this headline")

You can see what’s included when uploading your project by using using the `--dry-run` flag:

```

sqlc upload --dry-run

```

The output will be the exact HTTP request sent by `sqlc`.

### Upload[](#upload "Permalink to this headline")

Once you’re ready to upload, remove the `--dry-run` flag.

```

sqlc upload

```

By uploading your project, you’re making sqlc more stable and reliable. Thanks!

CLI[](#cli "Permalink to this headline")

-----------------------------------------

```

Usage:

  sqlc [command]

Available Commands:

  compile     Statically check SQL for syntax and type errors

  completion  Generate the autocompletion script for the specified shell

  generate    Generate Go code from SQL

  help        Help about any command

  init        Create an empty sqlc.yaml settings file

  upload      Upload the schema, queries, and configuration for this project

  version     Print the sqlc version number

Flags:

  -x, --experimental   enable experimental features (default: false)

  -f, --file string    specify an alternate config file (default: sqlc.yaml)

  -h, --help           help for sqlc

Use "sqlc [command] --help" for more information about a command.

```

Configuration[](#configuration "Permalink to this headline")

-------------------------------------------------------------

The `sqlc` tool is configured via a `sqlc.yaml` or `sqlc.json` file. This

file must be in the directory where the `sqlc` command is run.

### Version 2[](#version-2 "Permalink to this headline")

```

version: "2"

sql:

- schema: "postgresql/schema.sql"

  queries: "postgresql/query.sql"

  engine: "postgresql"

  gen:

    go: 

      package: "authors"

      out: "postgresql"

- schema: "mysql/schema.sql"

  queries: "mysql/query.sql"

  engine: "mysql"

  gen:

    go:

      package: "authors"

      out: "mysql"

```

#### sql[](#sql "Permalink to this headline")

Each mapping in the `sql` collection has the following keys:

* `engine`:

	+ One of `postgresql`, `mysql` or `sqlite`.

* `schema`:

	+ Directory of SQL migrations or path to single SQL file; or a list of paths.

* `queries`:

	+ Directory of SQL queries or path to single SQL file; or a list of paths.

* `codegen`:

	+ A colleciton of mappings to configure code generators. See [codegen](#codegen) for the supported keys.

* `gen`:

	+ A mapping to configure built-in code generators. See [gen](#gen) for the supported keys.

* `strict\_function\_checks`

	+ If true, return an error if a called SQL function does not exist. Defaults to `false`.

	+

#### codegen[](#codegen "Permalink to this headline")

The `codegen` mapping supports the following keys:

* `out`:

	+ Output directory for generated code.

* `plugin`:

	+ The name of the plugin. Must be defined in the `plugins` collection.

* `options`:

	+ A mapping of plugin-specific options.

```

version: '2'

plugins:

- name: py

  wasm:

    url: https://github.com/tabbed/sqlc-gen-python/releases/download/v0.16.0-alpha/sqlc-gen-python.wasm

    sha256: 428476c7408fd4c032da4ec74e8a7344f4fa75e0f98a5a3302f238283b9b95f2

sql:

- schema: "schema.sql"

  queries: "query.sql"

  engine: postgresql

  codegen:

  - out: src/authors

    plugin: py

    options:

      package: authors

      emit\_sync\_querier: true

      emit\_async\_querier: true

      query\_parameter\_limit: 5

```

#### gen[](#gen "Permalink to this headline")

The `gen` mapping supports the following keys:

##### go[](#go "Permalink to this headline")

* `package`:

	+ The package name to use for the generated code. Defaults to `out` basename.

* `out`:

	+ Output directory for generated code.

* `sql\_package`:

	+ Either `pgx/v4`, `pgx/v5` or `database/sql`. Defaults to `database/sql`.

* `emit\_db\_tags`:

	+ If true, add DB tags to generated structs. Defaults to `false`.

* `emit\_prepared\_queries`:

	+ If true, include support for prepared queries. Defaults to `false`.

* `emit\_interface`:

	+ If true, output a `Querier` interface in the generated package. Defaults to `false`.

* `emit\_exact\_table\_names`:

	+ If true, struct names will mirror table names. Otherwise, sqlc attempts to singularize plural table names. Defaults to `false`.

* `emit\_empty\_slices`:

	+ If true, slices returned by `:many` queries will be empty instead of `nil`. Defaults to `false`.

* `emit\_exported\_queries`:

	+ If true, autogenerated SQL statement can be exported to be accessed by another package.

* `emit\_json\_tags`:

	+ If true, add JSON tags to generated structs. Defaults to `false`.

* `emit\_result\_struct\_pointers`:

	+ If true, query results are returned as pointers to structs. Queries returning multiple results are returned as slices of pointers. Defaults to `false`.

* `emit\_params\_struct\_pointers`:

	+ If true, parameters are passed as pointers to structs. Defaults to `false`.

* `emit\_methods\_with\_db\_argument`:

	+ If true, generated methods will accept a DBTX argument instead of storing a DBTX on the `\*Queries` struct. Defaults to `false`.

* `emit\_enum\_valid\_method`:

	+ If true, generate a Valid method on enum types,

	indicating whether a string is a valid enum value.

* `emit\_all\_enum\_values`:

	+ If true, emit a function per enum type

	that returns all valid enum values.

* `json\_tags\_case\_style`:

	+ `camel` for camelCase, `pascal` for PascalCase, `snake` for snake\_case or `none` to use the column name in the DB. Defaults to `none`.

* `output\_batch\_file\_name`:

	+ Customize the name of the batch file. Defaults to `batch.go`.

* `output\_db\_file\_name`:

	+ Customize the name of the db file. Defaults to `db.go`.

* `output\_models\_file\_name`:

	+ Customize the name of the models file. Defaults to `models.go`.

* `output\_querier\_file\_name`:

	+ Customize the name of the querier file. Defaults to `querier.go`.

* `output\_files\_suffix`:

	+ If specified the suffix will be added to the name of the generated files.

* `query\_parameter\_limit`:

	+ Positional arguments that will be generated in Go functions (>= `1` or `-1`). To always emit a parameter struct, you would need to set it to `-1`. `0` is invalid. Defaults to `1`.

	`rename`:

	+ Customize the name of generated struct fields. Explained in detail on the `Renaming fields` section.

* `overrides`:

	+ It is a collection of definitions that dictates which types are used to map a database types. Explained in detail on the `Type overriding` section.

###### Renaming fields[](#renaming-fields "Permalink to this headline")

Struct field names are generated from column names using a simple algorithm:

split the column name on underscores and capitalize the first letter of each

part.

```

account     -> Account

spotify\_url -> SpotifyUrl

app\_id      -> AppID

```

If you’re not happy with a field’s generated name, use the `rename` mapping

to pick a new name. The keys are column names and the values are the struct

field name to use.

```

version: "2"

sql:

- schema: "postgresql/schema.sql"

  queries: "postgresql/query.sql"

  engine: "postgresql"

  gen:

    go: 

      package: "authors"

      out: "postgresql"

      rename:

        spotify\_url: "SpotifyURL"

```

###### Type overriding[](#type-overriding "Permalink to this headline")

The default mapping of PostgreSQL/MySQL types to Go types only uses packages outside

the standard library when it must.

For example, the `uuid` PostgreSQL type is mapped to `github.com/google/uuid`.

If a different Go package for UUIDs is required, specify the package in the

`overrides` array. In this case, I’m going to use the `github.com/gofrs/uuid`

instead.

```

version: "2"

sql:

- schema: "postgresql/schema.sql"

  queries: "postgresql/query.sql"

  engine: "postgresql"

  gen:

    go: 

      package: "authors"

      out: "postgresql"

      overrides:

        - db\_type: "uuid"

          go\_type: "github.com/gofrs/uuid.UUID"

```

Each mapping of the `overrides` collection has the following keys:

* `db\_type`:

	+ The PostgreSQL or MySQL type to override. Find the full list of supported types in [postgresql\_type.go](https://github.com/kyleconroy/sqlc/blob/main/internal/codegen/golang/postgresql_type.go#L12) or [mysql\_type.go](https://github.com/kyleconroy/sqlc/blob/main/internal/codegen/golang/mysql_type.go#L12). Note that for Postgres you must use the pg\_catalog prefixed names where available. Can’t be used if the `column` key is defined.

* `column`

	+ In case the type overriding should be done on specific a column of a table instead of a type. `column` should be of the form `table.column` but you can be even more specific by specifying `schema.table.column` or `catalog.schema.table.column`. Can’t be used if the `db\_type` key is defined.

* `go\_type`:

	+ A fully qualified name to a Go type to use in the generated code.

* `go\_struct\_tag`:

	+ A reflect-style struct tag to use in the generated code, e.g. `a:"b" x:"y,z"`.

	If you want general json/db tags for all fields, use `emit\_db\_tags` and/or `emit\_json\_tags` instead.

* `nullable`:

	+ If true, use this type when a column is nullable. Defaults to `false`.

For more complicated import paths, the `go\_type` can also be an object.

```

version: "2"

sql:

- schema: "postgresql/schema.sql"

  queries: "postgresql/query.sql"

  engine: "postgresql"

  gen:

    go: 

      package: "authors"

      out: "postgresql"

      overrides:

        - db\_type: "uuid"

          go\_type:

            import: "a/b/v2"

            package: "b"

            type: "MyType"

            pointer: true

```

When generating code, entries using the `column` key will always have preference over

entries using the `db\_type` key in order to generate the struct.

##### kotlin[](#kotlin "Permalink to this headline")

* `package`:

	+ The package name to use for the generated code.

* `out`:

	+ Output directory for generated code.

* `emit\_exact\_table\_names`:

	+ If true, use the exact table name for generated models. Otherwise, guess a singular form. Defaults to `false`.

##### python[](#python "Permalink to this headline")

* `package`:

	+ The package name to use for the generated code.

* `out`:

	+ Output directory for generated code.

* `emit\_exact\_table\_names`:

	+ If true, use the exact table name for generated models. Otherwise, guess a singular form. Defaults to `false`.

* `emit\_sync\_querier`:

	+ If true, generate a class with synchronous methods. Defaults to `false`.

* `emit\_async\_querier`:

	+ If true, generate a class with asynchronous methods. Defaults to `false`.

* `emit\_pydantic\_models`:

	+ If true, generate classes that inherit from `pydantic.BaseModel`. Otherwise, define classes using the `dataclass` decorator. Defaults to `false`.

##### json[](#json "Permalink to this headline")

* `out`:

	+ Output directory for the generated JSON.

* `filename`:

	+ Filename for the generated JSON document. Defaults to `codegen\_request.json`.

* `indent`:

	+ Indent string to use in the JSON document. Defaults to .

#### plugins[](#plugins "Permalink to this headline")

Each mapping in the `plugins` collection has the following keys:

* `name`:

	+ The name of this plugin. Required

* `process`: A mapping with a single `cmd` key

	+ `cmd`:

	

	

		- The executable to call when using this plugin

* `wasm`: A mapping with a two keys `url` and `sha256`

	+ `url`:

	

	

		- The URL to fetch the WASM file. Supports the `https://` or `file://` schemes.

	+ `sha256`

	

	

		- The SHA256 checksum for the downloaded file.

```

version: 2

plugins:

- name: "py"

  wasm: 

    url: "https://github.com/tabbed/sqlc-gen-python/releases/download/v0.16.0-alpha/sqlc-gen-python.wasm"

    sha256: "428476c7408fd4c032da4ec74e8a7344f4fa75e0f98a5a3302f238283b9b95f2"

- name: "js"

  process: 

    cmd: "sqlc-gen-json"

```

#### global overrides[](#global-overrides "Permalink to this headline")

Sometimes, the same configuration must be done across various specfications of code generation.

Then a global definition for type overriding and field renaming can be done using the `overrides` mapping the following manner:

```

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"

sql:

- schema: "postgresql/schema.sql"

  queries: "postgresql/query.sql"

  engine: "postgresql"

  gen:

    go: 

      package: "authors"

      out: "postgresql"

- schema: "mysql/schema.sql"

  queries: "mysql/query.sql"

  engine: "mysql"

  gen:

    go:

      package: "authors"

      out: "mysql

```

With the previous configuration, whenever a struct field is generated from a table column that is called `id`, it will generated as `Identifier`.

Also, whenever there is a nullable `timestamp with time zone` column in a Postgres table, it will be generated as `null.Time`.

Note that, the mapping for global type overrides has a field called `engine` that is absent in the regular type overrides. This field is only used when there are multiple definitions using multiple engines. Otherwise, the value of the `engine` key will be defaulted to the engine that is currently being used.

Currently, type overrides and field renaming, both global and regular, are only fully supported in Go.

### Version 1[](#version-1 "Permalink to this headline")

```

version: "1"

packages:

  - name: "db"

    path: "internal/db"

    queries: "./sql/query/"

    schema: "./sql/schema/"

    engine: "postgresql"

    emit\_prepared\_queries: true

    emit\_interface: false

    emit\_exact\_table\_names: false

    emit\_empty\_slices: false

    emit\_exported\_queries: false

    emit\_json\_tags: true

    emit\_result\_struct\_pointers: false

    emit\_params\_struct\_pointers: false

    emit\_methods\_with\_db\_argument: false

    emit\_pointers\_for\_null\_types: false

    emit\_enum\_valid\_method: false

    emit\_all\_enum\_values: false

    json\_tags\_case\_style: "camel"

    output\_batch\_file\_name: "batch.go"

    output\_db\_file\_name: "db.go"

    output\_models\_file\_name: "models.go"

    output\_querier\_file\_name: "querier.go"

```

#### packages[](#packages "Permalink to this headline")

Each mapping in the `packages` collection has the following keys:

* `name`:

	+ The package name to use for the generated code. Defaults to `path` basename.

* `path`:

	+ Output directory for generated code.

* `queries`:

	+ Directory of SQL queries or path to single SQL file; or a list of paths.

* `schema`:

	+ Directory of SQL migrations or path to single SQL file; or a list of paths.

* `engine`:

	+ Either `postgresql` or `mysql`. Defaults to `postgresql`.

* `sql\_package`:

	+ Either `pgx/v4`, `pgx/v5` or `database/sql`. Defaults to `database/sql`.

* `emit\_db\_tags`:

	+ If true, add DB tags to generated structs. Defaults to `false`.

* `emit\_prepared\_queries`:

	+ If true, include support for prepared queries. Defaults to `false`.

* `emit\_interface`:

	+ If true, output a `Querier` interface in the generated package. Defaults to `false`.

* `emit\_exact\_table\_names`:

	+ If true, struct names will mirror table names. Otherwise, sqlc attempts to singularize plural table names. Defaults to `false`.

* `emit\_empty\_slices`:

	+ If true, slices returned by `:many` queries will be empty instead of `nil`. Defaults to `false`.

* `emit\_exported\_queries`:

	+ If true, autogenerated SQL statement can be exported to be accessed by another package.

* `emit\_json\_tags`:

	+ If true, add JSON tags to generated structs. Defaults to `false`.

* `emit\_result\_struct\_pointers`:

	+ If true, query results are returned as pointers to structs. Queries returning multiple results are returned as slices of pointers. Defaults to `false`.

* `emit\_params\_struct\_pointers`:

	+ If true, parameters are passed as pointers to structs. Defaults to `false`.

* `emit\_methods\_with\_db\_argument`:

	+ If true, generated methods will accept a DBTX argument instead of storing a DBTX on the `\*Queries` struct. Defaults to `false`.

* `emit\_pointers\_for\_null\_types`:

	+ If true and `sql\_package` is set to `pgx/v4`, generated types for nullable columns are emitted as pointers (ie. `\*string`) instead of `database/sql` null types (ie. `NullString`). Defaults to `false`.

* `emit\_enum\_valid\_method`:

	+ If true, generate a Valid method on enum types,

	indicating whether a string is a valid enum value.

* `emit\_all\_enum\_values`:

	+ If true, emit a function per enum type

	that returns all valid enum values.

* `json\_tags\_case\_style`:

	+ `camel` for camelCase, `pascal` for PascalCase, `snake` for snake\_case or `none` to use the column name in the DB. Defaults to `none`.

* `output\_batch\_file\_name`:

	+ Customize the name of the batch file. Defaults to `batch.go`.

* `output\_db\_file\_name`:

	+ Customize the name of the db file. Defaults to `db.go`.

* `output\_models\_file\_name`:

	+ Customize the name of the models file. Defaults to `models.go`.

* `output\_querier\_file\_name`:

	+ Customize the name of the querier file. Defaults to `querier.go`.

* `output\_files\_suffix`:

	+ If specified the suffix will be added to the name of the generated files.

* `query\_parameter\_limit`:

	+ Positional arguments that will be generated in Go functions (`>= 0`). To always emit a parameter struct, you would need to set it to `0`. Defaults to `1`.

#### overrides[](#overrides "Permalink to this headline")

The default mapping of PostgreSQL/MySQL types to Go types only uses packages outside

the standard library when it must.

For example, the `uuid` PostgreSQL type is mapped to `github.com/google/uuid`.

If a different Go package for UUIDs is required, specify the package in the

`overrides` array. In this case, I’m going to use the `github.com/gofrs/uuid`

instead.

```

version: "1"

packages: [...]

overrides:

  - go\_type: "github.com/gofrs/uuid.UUID"

    db\_type: "uuid"

```

Each override document has the following keys:

* `db\_type`:

	+ The PostgreSQL or MySQL type to override. Find the full list of supported types in [postgresql\_type.go](https://github.com/kyleconroy/sqlc/blob/main/internal/codegen/golang/postgresql_type.go#L12) or [mysql\_type.go](https://github.com/kyleconroy/sqlc/blob/main/internal/codegen/golang/mysql_type.go#L12). Note that for Postgres you must use the pg\_catalog prefixed names where available.

* `go\_type`:

	+ A fully qualified name to a Go type to use in the generated code.

* `go\_struct\_tag`:

	+ A reflect-style struct tag to use in the generated code, e.g. `a:"b" x:"y,z"`.

	If you want general json/db tags for all fields, use `emit\_db\_tags` and/or `emit\_json\_tags` instead.

* `nullable`:

	+ If true, use this type when a column is nullable. Defaults to `false`.

For more complicated import paths, the `go\_type` can also be an object.

```

version: "1"

packages: [...]

overrides:

  - db\_type: "uuid"

    go\_type:

      import: "a/b/v2"

      package: "b"

      type: "MyType"

```

##### Per-Column Type Overrides[](#per-column-type-overrides "Permalink to this headline")

Sometimes you would like to override the Go type used in model or query generation for

a specific field of a table and not on a type basis as described in the previous section.

This may be configured by specifying the `column` property in the override definition. `column`

should be of the form `table.column` but you can be even more specific by specifying `schema.table.column`

or `catalog.schema.table.column`.

```

version: "1"

packages: [...]

overrides:

  - column: "authors.id"

    go\_type: "github.com/segmentio/ksuid.KSUID"

```

##### Package Level Overrides[](#package-level-overrides "Permalink to this headline")

Overrides can be configured globally, as demonstrated in the previous sections, or they can be configured on a per-package which

scopes the override behavior to just a single package:

```

version: "1"

packages:

  - overrides: [...]

```

#### rename[](#rename "Permalink to this headline")

Struct field names are generated from column names using a simple algorithm:

split the column name on underscores and capitalize the first letter of each

part.

```

account     -> Account

spotify\_url -> SpotifyUrl

app\_id      -> AppID

```

If you’re not happy with a field’s generated name, use the `rename` mapping

to pick a new name. The keys are column names and the values are the struct

field name to use.

```

version: "1"

packages: [...]

rename:

  spotify\_url: "SpotifyURL"

```

Datatypes[](#datatypes "Permalink to this headline")

-----------------------------------------------------

### Arrays[](#arrays "Permalink to this headline")

PostgreSQL [arrays](https://www.postgresql.org/docs/current/arrays.html) are

materialized as Go slices. Currently, the `pgx/v5` sql package only supports multidimensional arrays.

```

CREATE TABLE places (

  name text   not null,

  tags text[]

);

```

```

package db

type Place struct {

	Name string

	Tags []string

}

```

### Dates and Time[](#dates-and-time "Permalink to this headline")

All PostgreSQL time and date types are returned as `time.Time` structs. For

null time or date values, the `NullTime` type from `database/sql` is used.

The `pgx/v5` sql package uses the appropriate pgx types.

```

CREATE TABLE authors (

  id         SERIAL    PRIMARY KEY,

  created\_at timestamp NOT NULL DEFAULT NOW(),

  updated\_at timestamp

);

```

```

package db

import (

	"database/sql"

	"time"

)

type Author struct {

	ID        int

	CreatedAt time.Time

	UpdatedAt sql.NullTime

}

```

### Enums[](#enums "Permalink to this headline")

PostgreSQL [enums](https://www.postgresql.org/docs/current/arrays.html) are

mapped to an aliased string type.

```

CREATE TYPE status AS ENUM (

  'open',

  'closed'

);

CREATE TABLE stores (

  name   text    PRIMARY KEY,

  status status  NOT NULL

);

```

```

package db

type Status string

const (

	StatusOpen   Status = "open"

	StatusClosed Status = "closed"

)

type Store struct {

	Name   string

	Status Status

}

```

### Null[](#null "Permalink to this headline")

For structs, null values are represented using the appropriate type from the

`database/sql` or `pgx` package.

```

CREATE TABLE authors (

  id   SERIAL PRIMARY KEY,

  name text   NOT NULL,

  bio  text

);

```

```

package db

import (

	"database/sql"

)

type Author struct {

	ID   int

	Name string

	Bio  sql.NullString

}

```

### UUIDs[](#uuids "Permalink to this headline")

The Go standard library does not come with a `uuid` package. For UUID support,

sqlc uses the excellent `github.com/google/uuid` package.

```

CREATE TABLE records (

  id   uuid PRIMARY KEY

);

```

```

package db

import (

	"github.com/google/uuid"

)

type Author struct {

	ID uuid.UUID

}

```

For MySQL, there is no native `uuid` data type. When using `UUID\_TO\_BIN` to store a `UUID()`, the underlying field type is `BINARY(16)` which by default sqlc would interpret this to `sql.NullString`. To have sqlc automatically convert these fields to a `uuid.UUID` type, use an overide on the column storing the `uuid`.

```

{

  "overrides": [

    {

      "column": "\*.uuid",

      "go\_type": "github.com/google/uuid.UUID"

    }

  ]

}

```

### JSON[](#json "Permalink to this headline")

By default, sqlc will generate the `[]byte`, `pgtype.JSON` or `json.RawMessage` for JSON column type.

But if you use the `pgx/v5` sql package then you can specify a some struct instead of default type.

The `pgx` implementation will marshall/unmarshall the struct automatically.

```

package dto

type BookData struct {

	Genres    []string `json:"genres"`

	Title     string   `json:"title"`

	Published bool     `json:"published"`

}

```

```

CREATE TABLE books (

  data jsonb

);

```

```

{

  "overrides": [

    {

      "column": "books.data",

      "go\_type": {

        "import":"example/db",

        "package": "dto",

        "type":"BookData"

      }

    }

  ]

}

```

```

package db

import (

	"example.com/db/dto"

)

type Book struct {

    Data \*dto.BookData

}

```

Query annotations[](#query-annotations "Permalink to this headline")

---------------------------------------------------------------------

sqlc requires each query to have a small comment indicating the name and

command. The format of this comment is as follows:

```

-- name: <name> <command>

```

### `:exec`[](#exec "Permalink to this headline")

The generated method will return the error from

[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

```

-- name: DeleteAuthor :exec

DELETE FROM authors

WHERE id = $1;

```

```

func (q \*Queries) DeleteAuthor(ctx context.Context, id int64) error {

	\_, err := q.db.ExecContext(ctx, deleteAuthor, id)

	return err

}

```

### `:execresult`[](#execresult "Permalink to this headline")

The generated method will return the [sql.Result](https://golang.org/pkg/database/sql/#Result) returned by

[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

```

-- name: DeleteAllAuthors :execresult

DELETE FROM authors;

```

```

func (q \*Queries) DeleteAllAuthors(ctx context.Context) (sql.Result, error) {

	return q.db.ExecContext(ctx, deleteAllAuthors)

}

```

### `:execrows`[](#execrows "Permalink to this headline")

The generated method will return the number of affected rows from the

[result](https://golang.org/pkg/database/sql/#Result) returned by

[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

```

-- name: DeleteAllAuthors :execrows

DELETE FROM authors;

```

```

func (q \*Queries) DeleteAllAuthors(ctx context.Context) (int64, error) {

	\_, err := q.db.ExecContext(ctx, deleteAllAuthors)

	// ...

}

```

### `:execlastid`[](#execlastid "Permalink to this headline")

The generated method will return the number generated by the database from the

[result](https://golang.org/pkg/database/sql/#Result) returned by

[ExecContext](https://golang.org/pkg/database/sql/#DB.ExecContext).

```

-- name: InsertAuthor :execlastid

INSERT INTO authors (name) VALUES (?);

```

```

func (q \*Queries) InsertAuthor(ctx context.Context, name string) (int64, error) {

	\_, err := q.db.ExecContext(ctx, insertAuthor, name)

	// ...

}

```

### `:many`[](#many "Permalink to this headline")

The generated method will return a slice of records via

[QueryContext](https://golang.org/pkg/database/sql/#DB.QueryContext).

```

-- name: ListAuthors :many

SELECT \* FROM authors

ORDER BY name;

```

```

func (q \*Queries) ListAuthors(ctx context.Context) ([]Author, error) {

	rows, err := q.db.QueryContext(ctx, listAuthors)

	// ...

}

```

### `:one`[](#one "Permalink to this headline")

The generated method will return a single record via

[QueryRowContext](https://golang.org/pkg/database/sql/#DB.QueryRowContext).

```

-- name: GetAuthor :one

SELECT * FROM authors

WHERE id = $1 LIMIT 1;

```

```

func (q \*Queries) GetAuthor(ctx context.Context, id int64) (Author, error) {

	row := q.db.QueryRowContext(ctx, getAuthor, id)

	// ...

}

```

### `:batchexec`[](#batchexec "Permalink to this headline")

**NOTE: This command only works with PostgreSQL using the `pgx/v4` and `pgx/v5` drivers and outputting Go code.**

The generated method will return a batch object. The batch object will have

the following methods:

* `Exec`, that takes a `func(int, error)` parameter,

* `Close`, to close the batch operation early.

```

-- name: DeleteBook :batchexec

DELETE FROM books

WHERE book_id = $1;

```

```

type DeleteBookBatchResults struct {

	br  pgx.BatchResults

	ind int

}

func (q \*Queries) DeleteBook(ctx context.Context, bookID []int32) \*DeleteBookBatchResults {

	//...

}

func (b \*DeleteBookBatchResults) Exec(f func(int, error)) {

	//...

}

func (b \*DeleteBookBatchResults) Close() error {

	//...

}

```

### `:batchmany`[](#batchmany "Permalink to this headline")

**NOTE: This command only works with PostgreSQL using the `pgx/v4` and `pgx/v5` drivers and outputting Go code.**

The generated method will return a batch object. The batch object will have

the following methods:

* `Query`, that takes a `func(int, []T, error)` parameter, where `T` is your query’s return type

* `Close`, to close the batch operation early.

```

-- name: BooksByTitleYear :batchmany

SELECT * FROM books

WHERE title = $1 AND year = $2;

```

```

type BooksByTitleYearBatchResults struct {

	br  pgx.BatchResults

	ind int

}

type BooksByTitleYearParams struct {

	Title string `json:"title"`

	Year  int32  `json:"year"`

}

func (q \*Queries) BooksByTitleYear(ctx context.Context, arg []BooksByTitleYearParams) \*BooksByTitleYearBatchResults {

	//...

}

func (b \*BooksByTitleYearBatchResults) Query(f func(int, []Book, error)) {

	//...

}

func (b \*BooksByTitleYearBatchResults) Close() error {

	//...

}

```

### `:batchone`[](#batchone "Permalink to this headline")

**NOTE: This command only works with PostgreSQL using the `pgx/v4` and `pgx/v5` drivers and outputting Go code.**

The generated method will return a batch object. The batch object will have

the following methods:

* `QueryRow`, that takes a `func(int, T, error)` parameter, where `T` is your query’s return type

* `Close`, to close the batch operation early.

```

-- name: CreateBook :batchone

INSERT INTO books (

    author_id,

    isbn

) VALUES (

    $1,

    $2

)

RETURNING book_id, author_id, isbn

```

```

type CreateBookBatchResults struct {

	br  pgx.BatchResults

	ind int

}

type CreateBookParams struct {

	AuthorID int32  `json:"author\_id"`

	Isbn     string `json:"isbn"`

}

func (q \*Queries) CreateBook(ctx context.Context, arg []CreateBookParams) \*CreateBookBatchResults {

	//...

}

func (b \*CreateBookBatchResults) QueryRow(f func(int, Book, error)) {

	//...

}

func (b \*CreateBookBatchResults) Close() error {

	//...

}

```

Database and language support[](#database-and-language-support "Permalink to this headline")

---------------------------------------------------------------------------------------------

| Language | Plugin | MySQL | PostgreSQL | SQLite |

| --- | --- | --- | --- | --- |

| Go | (built-in) | Stable | Stable | Beta |

| Kotlin | sqlc-gen-kotlin | Beta | Beta | Not implemented |

| Python | sqlc-gen-python | Beta | Beta | Not implemented |

### Future Language Support[](#future-language-support "Permalink to this headline")

* [C#](https://github.com/kyleconroy/sqlc/issues/373)

* [TypeScript](https://github.com/kyleconroy/sqlc/issues/296)

Environment variables[](#environment-variables "Permalink to this headline")

-----------------------------------------------------------------------------

### SQLCCACHE[](#sqlccache "Permalink to this headline")

The `SQLCCACHE` environment variable dictates where `sqlc` will store cached

WASM-based plugins and modules. By default `sqlc` follows the [XDG Base

Directory

Specification](https://specifications.freedesktop.org/basedir-spec/basedir-spec-latest.html).

### SQLCDEBUG[](#sqlcdebug "Permalink to this headline")

The `SQLCDEBUG` variable controls debugging variables within the runtime. It is

a comma-separated list of name=val pairs settings.

#### dumpast[](#dumpast "Permalink to this headline")

The `dumpast` command shows the SQL AST that was generated by the parser. Note

that this is the generic SQL AST, not the engine-specific SQL AST.

```

SQLCDEBUG=dumpast=1

```

```

([]interface {}) (len=1 cap=1) {

 (\*catalog.Catalog)(0xc0004f48c0)({

  Comment: (string) "",

  DefaultSchema: (string) (len=6) "public",

  Name: (string) "",

  Schemas: ([]\*catalog.Schema) (len=3 cap=4) {

   (\*catalog.Schema)(0xc0004f4930)({

    Name: (string) (len=6) "public",

    Tables: ([]\*catalog.Table) (len=1 cap=1) {

     (\*catalog.Table)(0xc00052ff20)({

      Rel: (\*ast.TableName)(0xc00052fda0)({

       Catalog: (string) "",

       Schema: (string) "",

       Name: (string) (len=7) "authors"

      }),

```

#### dumpcatalog[](#dumpcatalog "Permalink to this headline")

The `dumpcatalog` command outputs the entire catalog. If you’re using MySQL or

PostgreSQL, this can be a bit overwhelming. Expect this output to change in

future versions.

```

SQLCDEBUG=dumpcatalog=1

```

```

([]interface {}) (len=1 cap=1) {

 (\*catalog.Catalog)(0xc00050d1f0)({

  Comment: (string) "",

  DefaultSchema: (string) (len=6) "public",

  Name: (string) "",

  Schemas: ([]\*catalog.Schema) (len=3 cap=4) {

   (\*catalog.Schema)(0xc00050d260)({

    Name: (string) (len=6) "public",

    Tables: ([]\*catalog.Table) (len=1 cap=1) {

     (\*catalog.Table)(0xc0000c0840)({

      Rel: (\*ast.TableName)(0xc0000c06c0)({

       Catalog: (string) "",

       Schema: (string) "",

       Name: (string) (len=7) "authors"

      }),

```

#### trace[](#trace "Permalink to this headline")

The `trace` command is helpful for tracking down performance issues.

`SQLCDEBUG=trace=1`

By default, the trace output is written to `trace.out` in the current working

directory. You can configure a different path if needed.

`SQLCDEBUG=trace=name.out`

View the execution trace using the Go `trace` tool.

```

go tool trace trace.out

```

There’s a ton of different views for the trace output, but here’s an example

log showing the execution time for each package.

```

0.000043897 	 .         	1 	task sqlc (id 1, parent 0) created

0.000144923 	 .   101026 	1 	region generate started (duration: 47.619781ms)

0.001048975 	 .   904052 	1 	region package started (duration: 14.588456ms)

0.001054616 	 .     5641 	1 	name=authors dir=/Users/kyle/projects/sqlc/examples/python language=python

0.001071257 	 .    16641 	1 	region parse started (duration: 7.966549ms)

0.009043960 	 .  7972703 	1 	region codegen started (duration: 6.587086ms)

0.009171704 	 .   127744 	1 	new goroutine 35: text/template/parse.lex·dwrap·1

0.010361654 	 .  1189950 	1 	new goroutine 36: text/template/parse.lex·dwrap·1

0.015641815 	 .  5280161 	1 	region package started (duration: 10.904938ms)

0.015644943 	 .     3128 	1 	name=booktest dir=/Users/kyle/projects/sqlc/examples/python language=python

0.015647431 	 .     2488 	1 	region parse started (duration: 4.207749ms)

0.019860308 	 .  4212877 	1 	region codegen started (duration: 6.681624ms)

0.020028488 	 .   168180 	1 	new goroutine 37: text/template/parse.lex·dwrap·1

0.021020310 	 .   991822 	1 	new goroutine 8: text/template/parse.lex·dwrap·1

0.026551163 	 .  5530853 	1 	region package started (duration: 9.217294ms)

0.026554368 	 .     3205 	1 	name=jets dir=/Users/kyle/projects/sqlc/examples/python language=python

0.026556804 	 .     2436 	1 	region parse started (duration: 3.491005ms)

0.030051911 	 .  3495107 	1 	region codegen started (duration: 5.711931ms)

0.030213937 	 .   162026 	1 	new goroutine 20: text/template/parse.lex·dwrap·1

0.031099938 	 .   886001 	1 	new goroutine 38: text/template/parse.lex·dwrap·1

0.035772637 	 .  4672699 	1 	region package started (duration: 10.267039ms)

0.035775688 	 .     3051 	1 	name=ondeck dir=/Users/kyle/projects/sqlc/examples/python language=python

0.035778150 	 .     2462 	1 	region parse started (duration: 4.094518ms)

0.039877181 	 .  4099031 	1 	region codegen started (duration: 6.156341ms)

0.040010771 	 .   133590 	1 	new goroutine 39: text/template/parse.lex·dwrap·1

0.040894567 	 .   883796 	1 	new goroutine 40: text/template/parse.lex·dwrap·1

0.046042779 	 .  5148212 	1 	region writefiles started (duration: 1.718259ms)

0.047767781 	 .  1725002 	1 	task end

```

#### processplugins[](#processplugins "Permalink to this headline")

Setting this value to `0` disables process-based plugins. If a process-based

plugin is declared in the configuration file, running any `sqlc` command will

return an error.

`SQLCDEBUG=processplugins=0`

### SQLCTMPDIR[](#sqlctmpdir "Permalink to this headline")

If specified, use the given directory as the base for temporary folders. Only

applies when using WASM-based codegen plugins. When not specified, this

defaults to passing an empty string to

[`os.MkdirTemp`](https://pkg.go.dev/os#MkdirTemp).

Changelog[](#changelog "Permalink to this headline")

-----------------------------------------------------

All notable changes to this project will be documented in this file.

### [1.18.0](https://github.com/kyleconroy/sqlc/releases/tag/1.18.0)[](#id1 "Permalink to this headline")

Released 2023-04-27

#### Release notes[](#release-notes "Permalink to this headline")

##### Remote code generation[](#remote-code-generation "Permalink to this headline")

*Developed by [@andrewmbenton](https://github.com/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](https://docs.google.com/forms/d/e/1FAIpQLScDWrGtTgZWKt3mdlF5R2XCX6tL1pMkB4yuZx5yq684tTNN1Q/viewform?usp=sf_link).

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.

##### sqlc.embed[](#sqlc-embed "Permalink to this headline")

*Developed by [@nickjackson](https://github.com/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

}

```

##### sqlc.slice[](#sqlc-slice "Permalink to this headline")

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

##### Batch operation improvements[](#batch-operation-improvements "Permalink to this headline")

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.

##### Configurable query parameter limits for Go[](#configurable-query-parameter-limits-for-go "Permalink to this headline")

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

genreated method will use a argument struct.

#### Changes[](#changes "Permalink to this headline")

##### Bug Fixes[](#bug-fixes "Permalink to this headline")

* Prevent variable redeclaration in single param conflict for pgx (#2058)

* Retrieve Larg/Rarg join query after inner join (#2051)

* Rename argument when conflicted to imported package (#2048)

* Pgx closed batch return pointer if need #1959 (#1960)

* Correct singularization of “waves” (#2194)

* Honor Package level renames in v2 yaml config (#2001)

* (mysql) Prevent UPDATE … JOIN panic #1590 (#2154)

* Mysql delete join panic (#2197)

* Missing import with pointer overrides, solves #2168 #2125 (#2217)

##### Documentation[](#documentation "Permalink to this headline")

* (config.md) Add `sqlite` as engine option (#2164)

* Add first pass at pgx documentation (#2174)

* Add missed configuration option (#2188)

* `specifies parameter ":one" without containing a RETURNING clause` (#2173)

##### Features[](#features "Permalink to this headline")

* Add `sqlc.embed` to allow model re-use (#1615)

* (Go) Add query\_parameter\_limit conf to codegen (#1558)

* Add remote execution for codegen (#2214)

##### Testing[](#testing "Permalink to this headline")

* Skip tests if required plugins are missing (#2104)

* Add tests for reanme fix in v2 (#2196)

* Regenerate batch output for filename tests

* Remove remote test (#2232)

* Regenerate test output

##### Bin/sqlc[](#bin-sqlc "Permalink to this headline")

* Add SQLCTMPDIR environment variable (#2189)

##### Build[](#build "Permalink to this headline")

* (deps) Bump github.com/antlr/antlr4/runtime/Go/antlr (#2109)

* (deps) Bump github.com/jackc/pgx/v4 from 4.18.0 to 4.18.1 (#2119)

* (deps) Bump golang from 1.20.1 to 1.20.2 (#2135)

* (deps) Bump google.golang.org/protobuf from 1.28.1 to 1.29.0 (#2137)

* (deps) Bump google.golang.org/protobuf from 1.29.0 to 1.29.1 (#2143)

* (deps) Bump golang from 1.20.2 to 1.20.3 (#2192)

* (deps) Bump actions/setup-go from 3 to 4 (#2150)

* (deps) Bump google.golang.org/protobuf from 1.29.1 to 1.30.0 (#2151)

* (deps) Bump github.com/spf13/cobra from 1.6.1 to 1.7.0 (#2193)

* (deps) Bump github.com/lib/pq from 1.10.7 to 1.10.8 (#2211)

* (deps) Bump github.com/lib/pq from 1.10.8 to 1.10.9 (#2229)

* (deps) Bump github.com/go-sql-driver/mysql from 1.7.0 to 1.7.1 (#2228)

##### Cmd/sqlc[](#cmd-sqlc "Permalink to this headline")

* Remove –experimental flag (#2170)

* Add option to disable process-based plugins (#2180)

* Bump version to v1.18.0

##### Codegen[](#codegen "Permalink to this headline")

* Correctly generate CopyFrom columns for single-column copyfroms (#2185)

##### Config[](#config "Permalink to this headline")

* Add top-level cloud configuration (#2204)

##### Engine/postgres[](#engine-postgres "Permalink to this headline")

* Upgrade to pg\_query\_go/v4 (#2114)

##### Ext/wasm[](#ext-wasm "Permalink to this headline")

* Check exit code on returned error (#2223)

##### Parser[](#parser "Permalink to this headline")

* Generate correct types for `SELECT NOT EXISTS` (#1972)

##### Sqlite[](#sqlite "Permalink to this headline")

* Add support for CREATE TABLE … STRICT (#2175)

##### Wasm[](#wasm "Permalink to this headline")

* Upgrade to wasmtime v8.0.0 (#2222)

### [1.17.2](https://github.com/kyleconroy/sqlc/releases/tag/1.17.2)[](#id2 "Permalink to this headline")

Released 2023-02-22

#### Bug Fixes[](#id3 "Permalink to this headline")

* Fix build on Windows (#2102)

### [1.17.1](https://github.com/kyleconroy/sqlc/releases/tag/1.17.1)[](#id4 "Permalink to this headline")

Released 2023-02-22

#### Bug Fixes[](#id5 "Permalink to this headline")

* Prefer to use []T over pgype.Array[T] (#2090)

* Revert changes to Dockerfile (#2091)

* Do not throw error when IF NOT EXISTS is used on ADD COLUMN (#2092)

#### MySQL[](#mysql "Permalink to this headline")

* Add `float` support to MySQL (#2097)

#### Build[](#id6 "Permalink to this headline")

* (deps) Bump golang from 1.20.0 to 1.20.1 (#2082)

### [1.17.0](https://github.com/kyleconroy/sqlc/releases/tag/1.17.0)[](#id7 "Permalink to this headline")

Released 2023-02-13

#### Bug Fixes[](#id8 "Permalink to this headline")

* Initialize generated code outside function (#1850)

* (engine/mysql) Take into account column’s charset to distinguish text/blob, (var)char/(var)binary (#776) (#1895)

* The enum Value method returns correct type (#1996)

* Documentation for Inserting Rows (#2034)

* Add import statements even if only pointer types exist (#2046)

* Search from Rexpr if not found from Lexpr (#2056)

#### Documentation[](#id9 "Permalink to this headline")

* Change ENTRYPOINT to CMD (#1943)

* Update samples for HOW-TO GUIDES (#1953)

#### Features[](#id10 "Permalink to this headline")

* Add the diff command (#1963)

#### Build[](#id11 "Permalink to this headline")

* (deps) Bump github.com/mattn/go-sqlite3 from 1.14.15 to 1.14.16 (#1913)

* (deps) Bump github.com/spf13/cobra from 1.6.0 to 1.6.1 (#1909)

* Fix devcontainer (#1942)

* Run sqlc-pg-gen via GitHub Actions (#1944)

* Move large arrays out of functions (#1947)

* Fix conflicts from pointer configuration (#1950)

* (deps) Bump github.com/go-sql-driver/mysql from 1.6.0 to 1.7.0 (#1988)

* (deps) Bump github.com/jackc/pgtype from 1.12.0 to 1.13.0 (#1978)

* (deps) Bump golang from 1.19.3 to 1.19.4 (#1992)

* (deps) Bump certifi from 2020.12.5 to 2022.12.7 in /docs (#1993)

* (deps) Bump golang from 1.19.4 to 1.19.5 (#2016)

* (deps) Bump golang from 1.19.5 to 1.20.0 (#2045)

* (deps) Bump github.com/jackc/pgtype from 1.13.0 to 1.14.0 (#2062)

* (deps) Bump github.com/jackc/pgx/v4 from 4.17.2 to 4.18.0 (#2063)

#### Cmd[](#cmd "Permalink to this headline")

* Generate packages in parallel (#2026)

#### Cmd/sqlc[](#id12 "Permalink to this headline")

* Bump version to v1.17.0

#### Codegen[](#id13 "Permalink to this headline")

* Remove built-in Kotlin support (#1935)

* Remove built-in Python support (#1936)

#### Internal/codegen[](#internal-codegen "Permalink to this headline")

* Cache pattern matching compilations (#2028)

#### Mysql[](#id14 "Permalink to this headline")

* Add datatype tests (#1948)

* Fix blob tests (#1949)

#### Plugins[](#plugins "Permalink to this headline")

* Upgrade to wasmtime 3.0.1 (#2009)

#### Sqlite[](#id15 "Permalink to this headline")

* Supported between expr (#1958) (#1967)

#### Tools[](#tools "Permalink to this headline")

* Regenerate scripts skips dirs that contains diff exec command (#1987)

#### Wasm[](#id16 "Permalink to this headline")

* Upgrade to wasmtime 5.0.0 (#2065)

### [1.16.0](https://github.com/kyleconroy/sqlc/releases/tag/1.16.0)[](#id17 "Permalink to this headline")

Released 2022-11-09

#### Bug Fixes[](#id18 "Permalink to this headline")

* (validate) Sqlc.arg & sqlc.narg are not “missing” (#1814)

* Emit correct comment for nullable enums (#1819)

* 🐛 Correctly switch `coalesce()` result `.NotNull` value (#1664)

* Prevent batch infinite loop with arg length (#1794)

* Support version 2 in error message (#1839)

* Handle empty column list in postgresql (#1843)

* Batch imports filter queries, update cmds having ret type (#1842)

* Named params contribute to batch parameter count (#1841)

#### Documentation[](#id19 "Permalink to this headline")

* Add a getting started guide for SQLite (#1798)

* Various readability improvements (#1854)

* Add documentation for codegen plugins (#1904)

* Update migration guides with links (#1933)

#### Features[](#id20 "Permalink to this headline")

* Add HAVING support to MySQL (#1806)

#### Miscellaneous Tasks[](#miscellaneous-tasks "Permalink to this headline")

* Upgrade wasmtime version (#1827)

* Bump wasmtime version to v1.0.0 (#1869)

#### Build[](#id21 "Permalink to this headline")

* (deps) Bump github.com/jackc/pgconn from 1.12.1 to 1.13.0 (#1785)

* (deps) Bump github.com/mattn/go-sqlite3 from 1.14.13 to 1.14.15 (#1799)

* (deps) Bump github.com/jackc/pgx/v4 from 4.16.1 to 4.17.0 (#1786)

* (deps) Bump github.com/jackc/pgx/v4 from 4.17.0 to 4.17.1 (#1825)

* (deps) Bump github.com/bytecodealliance/wasmtime-go (#1826)

* (deps) Bump github.com/jackc/pgx/v4 from 4.17.1 to 4.17.2 (#1831)

* (deps) Bump golang from 1.19.0 to 1.19.1 (#1834)

* (deps) Bump github.com/google/go-cmp from 0.5.8 to 0.5.9 (#1838)

* (deps) Bump github.com/lib/pq from 1.10.6 to 1.10.7 (#1835)

* (deps) Bump github.com/bytecodealliance/wasmtime-go (#1857)

* (deps) Bump github.com/spf13/cobra from 1.5.0 to 1.6.0 (#1893)

* (deps) Bump golang from 1.19.1 to 1.19.3 (#1920)

#### Cmd/sqlc[](#id22 "Permalink to this headline")

* Bump to v1.16.0

#### Codgen[](#codgen "Permalink to this headline")

* Include serialized codegen options (#1890)

#### Compiler[](#compiler "Permalink to this headline")

* Move Kotlin parameter logic into codegen (#1910)

#### Examples[](#examples "Permalink to this headline")

* Port Python examples to WASM plugin (#1903)

#### Pg-gen[](#pg-gen "Permalink to this headline")

* Make sqlc-pg-gen the complete source of truth for pg\_catalog.go (#1809)

* Implement information\_schema shema (#1815)

#### Python[](#python "Permalink to this headline")

* Port all Python tests to sqlc-gen-python (#1907)

* Upgrade to sqlc-gen-python v1.0.0 (#1932)

### [1.15.0](https://github.com/kyleconroy/sqlc/releases/tag/1.15.0)[](#id23 "Permalink to this headline")

Released 2022-08-07

#### Bug Fixes[](#id24 "Permalink to this headline")

* (mysql) Typo (#1700)

* (postgresql) Add quotes for CamelCase columns (#1729)

* Cannot parse SQLite upsert statement (#1732)

* (sqlite) Regenerate test output for builtins (#1735)

* (wasm) Version modules by wasmtime version (#1734)

* Missing imports (#1637)

* Missing slice import for querier (#1773)

#### Documentation[](#id25 "Permalink to this headline")

* Add process-based plugin docs (#1669)

* Add links to downloads.sqlc.dev (#1681)

* Update transactions how to example (#1775)

#### Features[](#id26 "Permalink to this headline")

* More SQL Syntax Support for SQLite (#1687)

* (sqlite) Promote SQLite support to beta (#1699)

* Codegen plugins, powered by WASM (#1684)

* Set user-agent for plugin downloads (#1707)

* Null enums types (#1485)

* (sqlite) Support stdlib functions (#1712)

* (sqlite) Add support for returning (#1741)

#### Miscellaneous Tasks[](#id27 "Permalink to this headline")

* Add tests for quoting columns (#1733)

* Remove catalog tests (#1762)

#### Testing[](#id28 "Permalink to this headline")

* Add tests for fixing slice imports (#1736)

* Add test cases for returning (#1737)

#### Build[](#id29 "Permalink to this headline")

* Upgrade to Go 1.19 (#1780)

* Upgrade to go-wasmtime 0.39.0 (#1781)

#### Plugins[](#id30 "Permalink to this headline")

* (wasm) Change default cache location (#1709)

* (wasm) Change the SHA-256 config key (#1710)

### [1.14.0](https://github.com/kyleconroy/sqlc/releases/tag/1.14.0)[](#id31 "Permalink to this headline")

Released 2022-06-09

#### Bug Fixes[](#id32 "Permalink to this headline")

* (postgresql) Remove extra newline with db argument (#1417)

* (sqlite) Fix DROP TABLE (#1443)

* (compiler) Fix left join nullability with table aliases (#1491)

* Regenerate testdata for CREATE TABLE AS (#1516)

* (bundler) Only close multipart writer once (#1528)

* (endtoend) Regenerate testdata for exex\_lastid

* (pgx) Copyfrom imports (#1626)

* Validate sqlc function arguments (#1633)

* Fixed typo `sql.narg` in doc (#1668)

#### Features[](#id33 "Permalink to this headline")

* (golang) Add Enum.Valid and AllEnumValues (#1613)

* (sqlite) Start expanding support (#1410)

* (pgx) Add support for batch operations (#1437)

* (sqlite) Add support for delete statements (#1447)

* (codegen) Insert comments in interfaces (#1458)

* (sdk) Add the plugin SDK package (#1463)

* Upload projects (#1436)

* Add sqlc version to generated Kotlin code (#1512)

* Add sqlc version to generated Go code (#1513)

* Pass sqlc version in codegen request (#1514)

* (postgresql) Add materialized view support (#1509)

* (python) Graduate Python support to beta (#1520)

* Run sqlc with docker on windows cmd (#1557)

* Add JSON “codegen” output (#1565)

* Add sqlc.narg() for nullable named params (#1536)

* Process-based codegen plugins (#1578)

#### Miscellaneous Tasks[](#id34 "Permalink to this headline")

* Fix extra newline in comments for copyfrom (#1438)

* Generate marshal/unmarshal with vtprotobuf (#1467)

#### Refactor[](#refactor "Permalink to this headline")

* (codegen) Port Kotlin codegen package to use plugin types (#1416)

* (codegen) Port Go to plugin types (#1460)

* (cmd) Simplify codegen selection logic (#1466)

* (sql/catalog) Improve Readability (#1595)

* Add basic fuzzing for config / overrides (#1500)

### [1.13.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.13.0)[](#id35 "Permalink to this headline")

Released 2022-03-31

#### Bug Fixes[](#id36 "Permalink to this headline")

* (compiler) Fix left join nullability with table aliases (#1491)

* (postgresql) Remove extra newline with db argument (#1417)

* (sqlite) Fix DROP TABLE (#1443)

#### Features[](#id37 "Permalink to this headline")

* (cli) Upload projects (#1436)

* (codegen) Add sqlc version to generated Go code (#1513)

* (codegen) Add sqlc version to generated Kotlin code (#1512)

* (codegen) Insert comments in interfaces (#1458)

* (codegen) Pass sqlc version in codegen request (#1514)

* (pgx) Add support for batch operations (#1437)

* (postgresql) Add materialized view support (#1509)

* (python) Graduate Python support to beta (#1520)

* (sdk) Add the plugin SDK package (#1463)

* (sqlite) Add support for delete statements (#1447)

* (sqlite) Start expanding support (#1410)

#### Miscellaneous Tasks[](#id38 "Permalink to this headline")

* Fix extra newline in comments for copyfrom (#1438)

* Generate marshal/unmarshal with vtprotobuf (#1467)

#### Refactor[](#id39 "Permalink to this headline")

* (codegen) Port Kotlin codegen package to use plugin types (#1416)

* (codegen) Port Go to plugin types (#1460)

* (cmd) Simplify codegen selection logic (#1466)

#### Config[](#id40 "Permalink to this headline")

* Add basic fuzzing for config / overrides (#1500)

### [1.12.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.12.0)[](#id41 "Permalink to this headline")

Released 2022-02-05

#### Bug[](#bug "Permalink to this headline")

* ALTER TABLE SET SCHEMA (#1409)

#### Bug Fixes[](#id42 "Permalink to this headline")

* Update ANTLR v4 go.mod entry (#1336)

* Check delete statements for CTEs (#1329)

* Fix validation of GROUP BY on field aliases (#1348)

* Fix imports when non-copyfrom queries needed imports that copyfrom queries didn’t (#1386)

* Remove extra comment newline (#1395)

* Enable strict function checking (#1405)

#### Documentation[](#id43 "Permalink to this headline")

* Bump version to 1.11.0 (#1308)

#### Features[](#id44 "Permalink to this headline")

* Inheritance (#1339)

* Generate query code using ASTs instead of templates (#1338)

* Add support for CREATE TABLE a ( LIKE b ) (#1355)

* Add support for sql.NullInt16 (#1376)

#### Miscellaneous Tasks[](#id45 "Permalink to this headline")

* Add tests for :exec{result,rows} (#1344)

* Delete template-based codegen (#1345)

#### Build[](#id46 "Permalink to this headline")

* Bump github.com/jackc/pgx/v4 from 4.14.0 to 4.14.1 (#1316)

* Bump golang from 1.17.3 to 1.17.4 (#1331)

* Bump golang from 1.17.4 to 1.17.5 (#1337)

* Bump github.com/spf13/cobra from 1.2.1 to 1.3.0 (#1343)

* Remove devel Docker build

* Bump golang from 1.17.5 to 1.17.6 (#1369)

* Bump github.com/google/go-cmp from 0.5.6 to 0.5.7 (#1382)

* Format all Go code (#1387)

### [1.11.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.11.0)[](#id47 "Permalink to this headline")

Released 2021-11-24

#### Bug Fixes[](#id48 "Permalink to this headline")

* Update incorrect signatures (#1180)

* Correct aggregate func sig (#1182)

* Jsonb\_build\_object (#1211)

* Case-insensitive identifiers (#1216)

* Incorrect handling of meta (#1228)

* Detect invalid INSERT expression (#1231)

* Respect alias name for coalesce (#1232)

* Mark nullable when casting NULL (#1233)

* Support nullable fields in joins for MySQL engine (#1249)

* Fix between expression handling of table references (#1268)

* Support nullable fields in joins on same table (#1270)

* Fix missing binds in ORDER BY (#1273)

* Set RV for TargetList items on updates (#1252)

* Fix MySQL parser for query without trailing semicolon (#1282)

* Validate table alias references (#1283)

* Add support for MySQL ON DUPLICATE KEY UPDATE (#1286)

* Support references to columns in joined tables in UPDATE statements (#1289)

* Add validation for GROUP BY clause column references (#1285)

* Prevent variable redeclaration in single param conflict (#1298)

* Use common params struct field for same named params (#1296)

#### Documentation[](#id49 "Permalink to this headline")

* Replace deprecated go get with go install (#1181)

* Fix package name referenced in tutorial (#1202)

* Add environment variables (#1264)

* Add go.17+ install instructions (#1280)

* Warn about golang-migrate file order (#1302)

#### Features[](#id50 "Permalink to this headline")

* Instrument compiler via runtime/trace (#1258)

* Add MySQL support for BETWEEN arguments (#1265)

#### Refactor[](#id51 "Permalink to this headline")

* Move from io/ioutil to io and os package (#1164)

#### Styling[](#styling "Permalink to this headline")

* Apply gofmt to sample code (#1261)

#### Build[](#id52 "Permalink to this headline")

* Bump golang from 1.17.0 to 1.17.1 (#1173)

* Bump eskatos/gradle-command-action from 1 to 2 (#1220)

* Bump golang from 1.17.1 to 1.17.2 (#1227)

* Bump github.com/pganalyze/pg\_query\_go/v2 (#1234)

* Bump actions/checkout from 2.3.4 to 2.3.5 (#1238)

* Bump babel from 2.9.0 to 2.9.1 in /docs (#1245)

* Bump golang from 1.17.2 to 1.17.3 (#1272)

* Bump actions/checkout from 2.3.5 to 2.4.0 (#1267)

* Bump github.com/lib/pq from 1.10.3 to 1.10.4 (#1278)

* Bump github.com/jackc/pgx/v4 from 4.13.0 to 4.14.0 (#1303)

#### Cmd/sqlc[](#id53 "Permalink to this headline")

* Bump version to v1.11.0

### [1.10.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.10.0)[](#id54 "Permalink to this headline")

Released 2021-09-07

#### Documentation[](#id55 "Permalink to this headline")

* Fix invalid language support table (#1161)

* Add a getting started guide for MySQL (#1163)

#### Build[](#id56 "Permalink to this headline")

* Bump golang from 1.16.7 to 1.17.0 (#1129)

* Bump github.com/lib/pq from 1.10.2 to 1.10.3 (#1160)

#### Ci[](#ci "Permalink to this headline")

* Upgrade Go to 1.17 (#1130)

#### Cmd/sqlc[](#id57 "Permalink to this headline")

* Bump version to v1.10.0 (#1165)

#### Codegen/golang[](#codegen-golang "Permalink to this headline")

* Consolidate import logic (#1139)

* Add pgx support for range types (#1146)

* Use pgtype for hstore when using pgx (#1156)

#### Codgen/golang[](#codgen-golang "Permalink to this headline")

* Use p[gq]type for network address types (#1142)

#### Endtoend[](#endtoend "Permalink to this headline")

* Run `go test` in CI (#1134)

#### Engine/mysql[](#engine-mysql "Permalink to this headline")

* Add support for LIKE (#1162)

#### Golang[](#golang "Permalink to this headline")

* Output NullUUID when necessary (#1137)

### [1.9.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.9.0)[](#id58 "Permalink to this headline")

Released 2021-08-13

#### Documentation[](#id59 "Permalink to this headline")

* Update documentation (a bit) for v1.9.0 (#1117)

#### Build[](#id60 "Permalink to this headline")

* Bump golang from 1.16.6 to 1.16.7 (#1107)

#### Cmd/sqlc[](#id61 "Permalink to this headline")

* Bump version to v1.9.0 (#1121)

#### Compiler[](#id62 "Permalink to this headline")

* Add tests for COALESCE behavior (#1112)

* Handle subqueries in SELECT statements (#1113)

### [1.8.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.8.0)[](#id63 "Permalink to this headline")

Released 2021-05-03

#### Documentation[](#id64 "Permalink to this headline")

* Add language support Matrix (#920)

#### Features[](#id65 "Permalink to this headline")

* Add case style config option (#905)

#### Python[](#id66 "Permalink to this headline")

* Eliminate runtime package and use sqlalchemy (#939)

#### Build[](#id67 "Permalink to this headline")

* Bump github.com/google/go-cmp from 0.5.4 to 0.5.5 (#926)

* Bump github.com/lib/pq from 1.9.0 to 1.10.0 (#931)

* Bump golang from 1.16.0 to 1.16.1 (#935)

* Bump golang from 1.16.1 to 1.16.2 (#942)

* Bump github.com/jackc/pgx/v4 from 4.10.1 to 4.11.0 (#956)

* Bump github.com/go-sql-driver/mysql from 1.5.0 to 1.6.0 (#961)

* Bump github.com/pganalyze/pg\_query\_go/v2 (#965)

* Bump urllib3 from 1.26.3 to 1.26.4 in /docs (#968)

* Bump golang from 1.16.2 to 1.16.3 (#963)

* Bump github.com/lib/pq from 1.10.0 to 1.10.1 (#980)

#### Cmd[](#id68 "Permalink to this headline")

* Add the –experimental flag (#929)

* Fix sqlc init (#959)

#### Cmd/sqlc[](#id69 "Permalink to this headline")

* Bump version to v1.7.1-devel (#913)

* Bump version to v1.8.0

#### Codegen[](#id70 "Permalink to this headline")

* Generate valid enum names for symbols (#972)

#### Postgresql[](#postgresql "Permalink to this headline")

* Support generated columns

* Add test for PRIMARY KEY INCLUDE

* Add tests for CREATE TABLE PARTITION OF

* CREATE TRIGGER EXECUTE FUNCTION

* Add support for renaming types (#971)

#### Sql/ast[](#sql-ast "Permalink to this headline")

* Resolve return values from functions (#964)

#### Workflows[](#workflows "Permalink to this headline")

* Only run tests once (#924)

### [1.7.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.7.0)[](#id71 "Permalink to this headline")

Released 2021-02-28

#### Bug Fixes[](#id72 "Permalink to this headline")

* Struct tag formatting (#833)

#### Documentation[](#id73 "Permalink to this headline")

* Include all the existing Markdown files (#877)

* Split docs into four sections (#882)

* Reorganize and consolidate documentation

* Add link to Windows download (#888)

* Shorten the README (#889)

#### Features[](#id74 "Permalink to this headline")

* Adding support for pgx/v4

* Adding support for pgx/v4

#### README[](#readme "Permalink to this headline")

* Add Go Report Card badge (#891)

#### Build[](#id75 "Permalink to this headline")

* Bump github.com/google/go-cmp from 0.5.3 to 0.5.4 (#813)

* Bump github.com/lib/pq from 1.8.0 to 1.9.0 (#820)

* Bump golang from 1.15.5 to 1.15.6 (#822)

* Bump github.com/jackc/pgx/v4 from 4.9.2 to 4.10.0 (#823)

* Bump github.com/jackc/pgx/v4 from 4.10.0 to 4.10.1 (#839)

* Bump golang from 1.15.6 to 1.15.7 (#855)

* Bump golang from 1.15.7 to 1.15.8 (#881)

* Bump github.com/spf13/cobra from 1.1.1 to 1.1.2 (#892)

* Bump golang from 1.15.8 to 1.16.0 (#897)

* Bump github.com/lfittl/pg\_query\_go from 1.0.1 to 1.0.2 (#901)

* Bump github.com/spf13/cobra from 1.1.2 to 1.1.3 (#893)

#### Catalog[](#catalog "Permalink to this headline")

* Improve alter column type (#818)

#### Ci[](#id76 "Permalink to this headline")

* Uprade to Go 1.15 (#887)

#### Cmd[](#id77 "Permalink to this headline")

* Allow config file location to be specified (#863)

#### Cmd/sqlc[](#id78 "Permalink to this headline")

* Bump to version v1.6.1-devel (#807)

* Bump version to v1.7.0 (#912)

#### Codegen/golang[](#id79 "Permalink to this headline")

* Make sure to import net package (#858)

#### Compiler[](#id80 "Permalink to this headline")

* Support UNION query

#### Dolphin[](#dolphin "Permalink to this headline")

* Generate bools for tinyint(1)

* Support joins in update statements (#883)

* Add support for union query

#### Endtoend[](#id81 "Permalink to this headline")

* Add tests for INTERSECT and EXCEPT

#### Go.mod[](#go-mod "Permalink to this headline")

* Update to go 1.15 and run ‘go mod tidy’ (#808)

#### Mysql[](#id82 "Permalink to this headline")

* Compile tinyint(1) to bool (#873)

#### Sql/ast[](#id83 "Permalink to this headline")

* Add enum values for SetOperation

### [1.6.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.6.0)[](#id84 "Permalink to this headline")

Released 2020-11-23

#### Dolphin[](#id85 "Permalink to this headline")

* Implement Rename (#651)

* Skip processing view drops (#653)

#### README[](#id86 "Permalink to this headline")

* Update language / database support (#698)

#### Astutils[](#astutils "Permalink to this headline")

* Fix Params rewrite call (#674)

#### Build[](#id87 "Permalink to this headline")

* Bump golang from 1.14 to 1.15.3 (#765)

* Bump docker/build-push-action from v1 to v2.1.0 (#764)

* Bump github.com/google/go-cmp from 0.4.0 to 0.5.2 (#766)

* Bump github.com/spf13/cobra from 1.0.0 to 1.1.1 (#767)

* Bump github.com/jackc/pgx/v4 from 4.6.0 to 4.9.2 (#768)

* Bump github.com/lfittl/pg\_query\_go from 1.0.0 to 1.0.1 (#773)

* Bump github.com/google/go-cmp from 0.5.2 to 0.5.3 (#783)

* Bump golang from 1.15.3 to 1.15.5 (#782)

* Bump github.com/lib/pq from 1.4.0 to 1.8.0 (#769)

#### Catalog[](#id88 "Permalink to this headline")

* Improve variadic argument support (#804)

#### Cmd/sqlc[](#id89 "Permalink to this headline")

* Bump to version v1.6.0 (#806)

#### Codegen[](#id90 "Permalink to this headline")

* Fix errant database/sql imports (#789)

#### Compiler[](#id91 "Permalink to this headline")

* Use engine-specific reserved keywords (#677)

#### Dolphi[](#dolphi "Permalink to this headline")

* Add list of builtin functions (#795)

#### Dolphin[](#id92 "Permalink to this headline")

* Update to the latest MySQL parser (#665)

* Add ENUM() support (#676)

* Add test for table aliasing (#684)

* Add MySQL ddl\_create\_table test (#685)

* Implete TRUNCATE table (#697)

* Represent tinyint as int32 (#797)

* Add support for coalesce (#802)

* Add function signatures (#796)

#### Endtoend[](#id93 "Permalink to this headline")

* Add MySQL json test (#692)

* Add MySQL update set multiple test (#696)

#### Examples[](#id94 "Permalink to this headline")

* Use generated enum constants in db\_test (#678)

* Port ondeck to MySQL (#680)

* Add MySQL authors example (#682)

#### Internal/cmd[](#internal-cmd "Permalink to this headline")

* Print correct config file on parse failure (#749)

#### Kotlin[](#kotlin "Permalink to this headline")

* Remove runtime dependency (#774)

#### Metadata[](#metadata "Permalink to this headline")

* Support multiple comment prefixes (#683)

#### Postgresql[](#id95 "Permalink to this headline")

* Support string concat operator (#701)

#### Sql/catalog[](#sql-catalog "Permalink to this headline")

* Add support for variadic functions (#798)

### [1.5.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.5.0)[](#id96 "Permalink to this headline")

Released 2020-08-05

#### Documentation[](#id97 "Permalink to this headline")

* Build sqlc using Go 1.14 (#549)

#### Cmd[](#id98 "Permalink to this headline")

* Add debugging support (#573)

#### Cmd/sqlc[](#id99 "Permalink to this headline")

* Bump version to v1.4.1-devel (#548)

* Bump version to v1.5.0

#### Compiler[](#id100 "Permalink to this headline")

* Support calling functions with defaults (#635)

* Skip func args without a paramRef (#636)

* Return a single column from coalesce (#639)

#### Config[](#id101 "Permalink to this headline")

* Add emit\_empty\_slices to version one (#552)

#### Contrib[](#contrib "Permalink to this headline")

* Add generated code for contrib

#### Dinosql[](#dinosql "Permalink to this headline")

* Remove deprecated package (#554)

#### Dolphin[](#id102 "Permalink to this headline")

* Add support for column aliasing (#566)

* Implement star expansion for subqueries (#619)

* Implement exapansion with reserved words (#620)

* Implement parameter refs (#621)

* Implement limit and offest (#622)

* Implement inserts (#623)

* Implement delete (#624)

* Implement simple update statements (#625)

* Implement INSERT … SELECT (#626)

* Use test driver instead of TiDB driver (#629)

* Implement named parameters via sqlc.arg() (#632)

#### Endtoend[](#id103 "Permalink to this headline")

* Add MySQL test for SELECT \* JOIN (#565)

* Add MySQL test for inflection (#567)

#### Engine[](#engine "Permalink to this headline")

* Create engine package (#556)

#### Equinox[](#equinox "Permalink to this headline")

* Use the new equinox-io/setup action (#586)

#### Examples[](#id104 "Permalink to this headline")

* Run tests for MySQL booktest (#627)

#### Golang[](#id105 "Permalink to this headline")

* Add support for the money type (#561)

* Generate correct types for int2 and int8 (#579)

#### Internal[](#internal "Permalink to this headline")

* Rm catalog, pg, postgres packages (#555)

#### Mod[](#mod "Permalink to this headline")

* Downgrade TiDB package to fix build (#603)

#### Mysql[](#id106 "Permalink to this headline")

* Upgrade to the latest vitess commit (#562)

* Support to infer type of a duplicated arg (#615)

* Allow some builtin functions to be nullable (#616)

#### Postgresql[](#id107 "Permalink to this headline")

* Generate all functions in pg\_catalog (#550)

* Remove pg\_catalog schema from tests (#638)

* Move contrib code to a package

#### Sql/catalog[](#id108 "Permalink to this headline")

* Fix comparison of pg\_catalog types (#637)

#### Tools[](#id109 "Permalink to this headline")

* Generate functions for all of contrib

#### Workflow[](#workflow "Permalink to this headline")

* Migrate to equinox-io/setup-release-tool (#614)

### [1.4.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.4.0)[](#id110 "Permalink to this headline")

Released 2020-06-17

#### Dockerfile[](#dockerfile "Permalink to this headline")

* Add version build argument (#487)

#### MySQL[](#id111 "Permalink to this headline")

* Prevent Panic when WHERE clause contains parenthesis. (#531)

#### README[](#id112 "Permalink to this headline")

* Document emit\_exact\_table\_names (#486)

#### All[](#all "Permalink to this headline")

* Remove the exp build tag (#507)

#### Catalog[](#id113 "Permalink to this headline")

* Support functions with table parameters (#541)

#### Cmd[](#id114 "Permalink to this headline")

* Bump to version 1.3.1-devel (#485)

#### Cmd/sqlc[](#id115 "Permalink to this headline")

* Bump version to v1.4.0 (#547)

#### Codegen[](#id116 "Permalink to this headline")

* Add the new codegen packages (#513)

* Add the :execresult query annotation (#542)

#### Compiler[](#id117 "Permalink to this headline")

* Validate function calls (#505)

* Port bottom of parseQuery (#510)

* Don’t mutate table name (#517)

* Enable experimental parser by default (#518)

* Apply rename rules to enum constants (#523)

* Temp fix for typecast function parameters (#530)

#### Endtoend[](#id118 "Permalink to this headline")

* Standardize JSON formatting (#490)

* Add per-test configuration files (#521)

* Read expected stderr failures from disk (#527)

#### Internal/dinosql[](#internal-dinosql "Permalink to this headline")

* Check parameter style before ref (#488)

* Remove unneeded column suffix (#492)

* Support named function arguments (#494)

#### Internal/postgresql[](#internal-postgresql "Permalink to this headline")

* Fix NamedArgExpr rewrite (#491)

#### Multierr[](#multierr "Permalink to this headline")

* Move dinosql.ParserErr to a new package (#496)

#### Named[](#named "Permalink to this headline")

* Port parameter style validation to SQL (#504)

#### Parser[](#id119 "Permalink to this headline")

* Support columns from subselect statements (#489)

#### Rewrite[](#rewrite "Permalink to this headline")

* Move parameter rewrite to package (#499)

#### Sqlite[](#id120 "Permalink to this headline")

* Use convert functions instead of the listener (#519)

#### Sqlpath[](#sqlpath "Permalink to this headline")

* Move ReadSQLFiles into a separate package (#495)

#### Validation[](#validation "Permalink to this headline")

* Move query validation to separate package (#498)

### [1.3.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.3.0)[](#id121 "Permalink to this headline")

Released 2020-05-12

#### Makefile[](#makefile "Permalink to this headline")

* Update target (#449)

#### README[](#id122 "Permalink to this headline")

* Add Myles as a sponsor (#469)

#### Testing[](#id123 "Permalink to this headline")

* Make sure all Go examples build (#480)

#### Cmd[](#id124 "Permalink to this headline")

* Bump version to v1.3.0 (#484)

#### Cmd/sqlc[](#id125 "Permalink to this headline")

* Bump version to v1.2.1-devel (#442)

#### Dinosql[](#id126 "Permalink to this headline")

* Inline addFile (#446)

* Add PostgreSQL support for TRUNCATE (#448)

#### Gen[](#gen "Permalink to this headline")

* Emit json.RawMessage for JSON columns (#461)

#### Go.mod[](#id127 "Permalink to this headline")

* Use latest lib/pq (#471)

#### Parser[](#id128 "Permalink to this headline")

* Use same function to load SQL files (#483)

#### Postgresql[](#id129 "Permalink to this headline")

* Fix panic walking CreateTableAsStmt (#475)

### [1.2.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.2.0)[](#id130 "Permalink to this headline")

Released 2020-04-07

#### Documentation[](#id131 "Permalink to this headline")

* Publish to Docker Hub (#422)

#### README[](#id132 "Permalink to this headline")

* Docker installation docs (#424)

#### Cmd/sqlc[](#id133 "Permalink to this headline")

* Bump version to v1.1.1-devel (#407)

* Bump version to v1.2.0 (#441)

#### Gen[](#id134 "Permalink to this headline")

* Add special case for “campus” (#435)

* Properly quote reserved keywords on expansion (#436)

#### Migrations[](#migrations "Permalink to this headline")

* Move migration parsing to new package (#427)

#### Parser[](#id135 "Permalink to this headline")

* Generate correct types for SELECT EXISTS (#411)

### [1.1.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.1.0)[](#id136 "Permalink to this headline")

Released 2020-03-17

#### README[](#id137 "Permalink to this headline")

* Add installation instructions (#350)

* Add section on running tests (#357)

* Fix typo (#371)

#### Ast[](#ast "Permalink to this headline")

* Add AST for ALTER TABLE ADD / DROP COLUMN (#376)

* Add support for CREATE TYPE as ENUM (#388)

* Add support for CREATE / DROP SCHEMA (#389)

#### Astutils[](#id138 "Permalink to this headline")

* Apply changes to the ValuesList slice (#372)

#### Cmd[](#id139 "Permalink to this headline")

* Return v1.0.0 (#348)

* Return next bug fix version (#349)

#### Cmd/sqlc[](#id140 "Permalink to this headline")

* Bump version to v1.1.0 (#406)

#### Compiler[](#id141 "Permalink to this headline")

* Wire up the experimental parsers

#### Config[](#id142 "Permalink to this headline")

* Remove “emit\_single\_file” option (#367)

#### Dolphin[](#id143 "Permalink to this headline")

* Add experimental parser for MySQL

#### Gen[](#id144 "Permalink to this headline")

* Add option to emit single file for Go (#366)

* Add support for the ltree extension (#385)

#### Go.mod[](#id145 "Permalink to this headline")

* Add packages for MySQL and SQLite parsers

#### Internal/dinosql[](#id146 "Permalink to this headline")

* Support Postgres macaddr type in Go (#358)

#### Internal/endtoend[](#internal-endtoend "Permalink to this headline")

* Remove %w (#354)

#### Kotlin[](#id147 "Permalink to this headline")

* Add Query class to support timeout and cancellation (#368)

#### Postgresql[](#id148 "Permalink to this headline")

* Add experimental parser for MySQL

#### Sql[](#sql "Permalink to this headline")

* Add generic SQL AST

#### Sql/ast[](#id149 "Permalink to this headline")

* Port support for COMMENT ON (#391)

* Implement DROP TYPE (#397)

* Implement ALTER TABLE RENAME (#398)

* Implement ALTER TABLE RENAME column (#399)

* Implement ALTER TABLE SET SCHEMA (#400)

#### Sql/catalog[](#id150 "Permalink to this headline")

* Port tests over from catalog pkg (#402)

#### Sql/errors[](#sql-errors "Permalink to this headline")

* Add a new errors package (#390)

#### Sqlite[](#id151 "Permalink to this headline")

* Add experimental parser for SQLite

### [1.0.0](https://github.com/kyleconroy/sqlc/releases/tag/v1.0.0)[](#id152 "Permalink to this headline")

Released 2020-02-18

#### Documentation[](#id153 "Permalink to this headline")

* Add documentation for query commands (#270)

* Add named parameter documentation (#332)

#### README[](#id154 "Permalink to this headline")

* Add sponsors section (#333)

#### Cmd[](#id155 "Permalink to this headline")

* Remove parse subcommand (#322)

#### Config[](#id156 "Permalink to this headline")

* Parse V2 config format

* Add support for YAML (#336)

#### Examples[](#id157 "Permalink to this headline")

* Add the jets and booktest examples (#237)

* Move sqlc.json into examples folder (#238)

* Add the authors example (#241)

* Add build tag to authors tests (#319)

#### Internal[](#id158 "Permalink to this headline")

* Allow CTE to be used with UPDATE (#268)

* Remove the PackageMap from settings (#295)

#### Internal/config[](#internal-config "Permalink to this headline")

* Create new config package (#313)

#### Internal/dinosql[](#id159 "Permalink to this headline")

* Emit Querier interface (#240)

* Strip leading “go-“ or trailing “-go” from import (#262)

* Overrides can now be basic types (#271)

* Import needed types for Querier (#285)

* Handle schema-scoped enums (#310)

* Ignore golang-migrate rollbacks (#320)

#### Internal/endtoend[](#id160 "Permalink to this headline")

* Move more tests to the record/replay framework

* Add update test for named params (#329)

#### Internal/mysql[](#internal-mysql "Permalink to this headline")

* Fix flaky test (#242)

* Port tests to endtoend package (#315)

#### Internal/parser[](#internal-parser "Permalink to this headline")

* Resolve nested CTEs (#324)

* Error if last query is missing (#325)

* Support joins with aliases (#326)

* Remove print statement (#327)

#### Internal/sqlc[](#internal-sqlc "Permalink to this headline")

* Add support for composite types (#311)

#### Kotlin[](#id161 "Permalink to this headline")

* Support primitives

* Arrays, enums, and dates

* Generate examples

* README for examples

* Factor out db setup extension

* Fix enums, use List instead of Array

* Port Go tests for examples

* Rewrite numbered params to positional params

* Always use use, fix indents

* Unbox query params

#### Parser[](#id162 "Permalink to this headline")

* Attach range vars to insert params

* Attach range vars to insert params (#342)

* Remove dead code (#343)

### [0.1.0](https://github.com/kyleconroy/sqlc/releases/tag/v0.1.0)[](#id163 "Permalink to this headline")

Released 2020-01-07

#### Documentation[](#id164 "Permalink to this headline")

* Replace remaining references to DinoSQL with sqlc (#149)

#### README[](#id165 "Permalink to this headline")

* Fix download links (#66)

* Add LIMIT 1 to query that should return one (#99)

#### Catalog[](#id166 "Permalink to this headline")

* Support “ALTER TABLE … DROP CONSTRAINT …” (#34)

* Differentiate functions with different argument types (#51)

#### Ci[](#id167 "Permalink to this headline")

* Enable tests on pull requests

#### Cmd[](#id168 "Permalink to this headline")

* Include filenames in error messages (#69)

* Do not output any changes on error (#72)

#### Dinosql/internal[](#dinosql-internal "Permalink to this headline")

* Add lower and upper functions (#215)

* Ignore alter sequence commands (#219)

#### Gen[](#id169 "Permalink to this headline")

* Add DO NOT EDIT comments to generated code (#50)

* Include all schemas when generating models (#90)

* Prefix structs with schema name (#91)

* Generate single import for uuid package (#98)

* Use same import logic for all Go files

* Pick correct struct to return for queries (#107)

* Create consistent JSON tags (#110)

* Add Close method to Queries struct (#127)

* Ignore empty override settings (#128)

* Turn SQL comments into Go comments (#136)

#### Internal/catalog[](#internal-catalog "Permalink to this headline")

* Parse unnamed function arguments (#166)

#### Internal/dinosql[](#id170 "Permalink to this headline")

* Prepare() with no GoQueries still valid (#95)

* Fix multiline comment rendering (#142)

* Dereference alias nodes on walk (#158)

* Ignore sql-migrate rollbacks (#160)

* Sort imported packages (#165)

* Add support for timestamptz (#169)

* Error on missing queries (#180)

* Use more database/sql null types (#182)

* Support the pg\_temp schema (#183)

* Override columns with array type (#184)

* Implement robust expansion

* Implement robust expansion (#186)

* Add COMMENT ON support (#191)

* Add DATE support

* Add DATE support (#196)

* Filter out invalid characters (#198)

* Quote reserved keywords (#205)

* Return parser errors first (#207)

* Implement advisory locks (#212)

* Error on duplicate query names (#221)

* Fix incorrect enum names (#223)

* Add support for numeric types

* Add support for numeric types (#228)

#### Internal/dinosql/testdata/ondeck[](#internal-dinosql-testdata-ondeck "Permalink to this headline")

* Add Makefile (#156)

#### Ondeck[](#ondeck "Permalink to this headline")

* Move all tests to GitHub CI (#58)

#### ParseQuery[](#parsequery "Permalink to this headline")

* Return either a query or an error (#178)

#### Parser[](#id171 "Permalink to this headline")

* Use schema when resolving catalog refs (#82)

* Support function calls in expressions (#104)

* Correctly handle single files (#119)

* Return error if missing RETURNING (#131)

* Add support for mathmatical operators (#132)

* Add support for simple case expressions (#134)

* Error on mismatched INSERT input (#135)

* Set IsArray on joined columns (#139)

#### Pg[](#pg "Permalink to this headline")

* Store functions in the catalog (#41)

* Add location to errors (#73)

Using Go and pgx[](#using-go-and-pgx "Permalink to this headline")

-------------------------------------------------------------------

Note

Experimental support for `pgx/v5` was added in v1.17.2. Full support will be

included in v1.18.0. Until then, you’ll need to pass the `--experimental`

flag to `sqlc generate`.

pgx is a pure Go driver and toolkit for PostgreSQL. It’s become the default

PostgreSQL package for many Gophers since lib/pq was put into maitience mode.

### Getting started[](#getting-started "Permalink to this headline")

To start generating code that uses pgx, set the `sql\_package` field in

your `sqlc.yaml` configuration file. Valid options are `pgx/v4` or

`pgx/v5`

```

version: "2"

sql:

  - engine: "postgresql"

    queries: "query.sql"

    schema: "query.sql"

    gen:

      go:

        package: "db"

        sql\_package: "pgx/v5"

        out: "db"

```

If you don’t have an existing sqlc project on hand, create a directory with the

configuration file above and the following `query.sql` file.

```

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;

```

Generating the code will now give you pgx-compatible database access methods.

```

sqlc generate --experimental

```

### Generated code walkthrough[](#generated-code-walkthrough "Permalink to this headline")

The generated code is very similar to the code generated when using

`lib/pq`. However, instead of using `database/sql`, the code uses

pgx types directly.

```

package main

import (

    "context"

    "fmt"

    "os"

    "github.com/jackc/pgx/v5"

    "example.com/sqlc-tutorial/db"

)

func main() {

    // urlExample := "postgres://username:password@localhost:5432/database\_name"

    conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE\_URL"))

    if err != nil {

            fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)

            os.Exit(1)

    }

    defer conn.Close(context.Background())

    q := db.New(conn)

    author, err := q.GetAuthor(context.Background(), 1)

    if err != nil {

            fmt.Fprintf(os.Stderr, "GetAuthor failed: %v\n", err)

            os.Exit(1)

    }

    fmt.Println(author.Name)

}

```

Developing sqlc[](#developing-sqlc "Permalink to this headline")

-----------------------------------------------------------------

### Building[](#building "Permalink to this headline")

For local development, install `sqlc` under an alias. We suggest `sqlc-dev`.

```

go build -o ~/go/bin/sqlc-dev ./cmd/sqlc

```

### Running Tests[](#running-tests "Permalink to this headline")

```

go test ./...

```

To run the tests in the examples folder, use the `examples` tag.

```

go test --tags=examples ./...

```

These tests require locally-running database instances. Run these databases

using [Docker Compose](https://docs.docker.com/compose/).

```

docker-compose up -d

```

The tests use the following environment variables to connect to the

database

#### For PostgreSQL[](#for-postgresql "Permalink to this headline")

```

Variable     Default Value

-------------------------

PG\_HOST      127.0.0.1

PG\_PORT      5432

PG\_USER      postgres

PG\_PASSWORD  mysecretpassword

PG\_DATABASE  dinotest

```

#### For MySQL[](#for-mysql "Permalink to this headline")

```

Variable     Default Value

-------------------------

MYSQL\_HOST      127.0.0.1

MYSQL\_PORT      3306

MYSQL\_USER      root

MYSQL\_ROOT\_PASSWORD  mysecretpassword

MYSQL\_DATABASE  dinotest

```

### Regenerate expected test output[](#regenerate-expected-test-output "Permalink to this headline")

If you need to update a large number of expected test output in the

`internal/endtoend/testdata` directory, run the `regenerate` script.

```

go build -o ~/go/bin/sqlc-dev ./cmd/sqlc

go run scripts/regenerate/main.go

```

Note that this uses the `sqlc-dev` binary, not `sqlc` so make sure you have an

up to date `sqlc-dev` binary.

Authoring plugins[](#authoring-plugins "Permalink to this headline")

---------------------------------------------------------------------

To use plugins, you must be using [Version 2](../reference/config.html) of

the configuration file. The top-level `plugins` array defines the available

plugins.

### WASM plugins[](#wasm-plugins "Permalink to this headline")

> 

> WASM plugins are fully sandboxed. Plugins do not have access to the network,

> filesystem, or environment variables.

> 

> 

> 

In the `codegen` section, the `out` field dictates what directory will contain

the new files. The `plugin` key must reference a plugin defined in the

top-level `plugins` map. The `options` are serialized to a string and passed on

to the plugin itself.

```

{

  "version": "2",

  "plugins": [

    {

      "name": "greeter",

      "wasm": {

        "url": "https://github.com/kyleconroy/sqlc-gen-greeter/releases/download/v0.1.0/sqlc-gen-greeter.wasm",

        "sha256": "afc486dac2068d741d7a4110146559d12a013fd0286f42a2fc7dcd802424ad07"

      }

    }

  ],

  "sql": [

    {

      "schema": "schema.sql",

      "queries": "query.sql",

      "engine": "postgresql",

      "codegen": [

        {

          "out": "gen",

          "plugin": "greeter"

        }

      ]

    }

  ]

}

```

For a complete working example see the following files:

* [sqlc-gen-greeter](https://github.com/kyleconroy/sqlc-gen-greeter)

	+ A WASM plugin (written in Rust) that outputs a friendly message

* [wasm\_plugin\_sqlc\_gen\_greeter](https://github.com/kyleconroy/sqlc/tree/main/internal/endtoend/testdata/wasm_plugin_sqlc_gen_greeter)

	+ An example project showing how to use a WASM plugin

### Process plugins[](#process-plugins "Permalink to this headline")

> 

> Process-based plugins offer minimal security. Only use plugins that you

> trust. Better yet, only use plugins that you’ve written yourself.

> 

> 

> 

In the `codegen` section, the `out` field dictates what directory will contain

the new files. The `plugin` key must reference a plugin defined in the

top-level `plugins` map. The `options` are serialized to a string and passed on

to the plugin itself.

```

{

  "version": "2",

  "plugins": [

    {

      "name": "jsonb",

      "process": {

        "cmd": "sqlc-gen-json"

      }

    }

  ],

   "sql": [

    {

      "schema": "schema.sql",

      "queries": "query.sql",

      "engine": "postgresql",

      "codegen": [

        {

          "out": "gen",

          "plugin": "jsonb",

          "options": {

            "indent": " ",

            "filename": "codegen.json"

          }

        }

      ]

    }

  ]

}

```

For a complete working example see the following files:

* [sqlc-gen-json](https://github.com/kyleconroy/sqlc/tree/main/cmd/sqlc-gen-json)

	+ A process-based plugin that serializes the CodeGenRequest to JSON

* [process\_plugin\_sqlc\_gen\_json](https://github.com/kyleconroy/sqlc/tree/main/internal/endtoend/testdata/process_plugin_sqlc_gen_json)

	+ An example project showing how to use a process-based plugin

Privacy and data collection[](#privacy-and-data-collection "Permalink to this headline")

-----------------------------------------------------------------------------------------

These days, it feels like every piece of software is tracking you. From your

browser, to your phone, to your terminal, programs collect as much data about

you as possible and send it off to the cloud for analysis.

We believe the best way to keep data safe is to never collect it in the first

place.

### Our Privacy Pledge[](#our-privacy-pledge "Permalink to this headline")

The `sqlc` command line tool does not collect any information. It

does not send crash reports to a third-party. It does not gather anonymous

aggregate user behaviour analytics.

No analytics.

No finger-printing.

No tracking.

Not now and not in the future.

#### Distribution Channels[](#distribution-channels "Permalink to this headline")

We distribute sqlc using popular package managers such as

[Homebrew](https://brew.sh/) and [Snapcraft](https://snapcraft.io/). These

package managers and their associated command-line tools do collect usage

metrics.

We use these services to make it easy to for users to install sqlc. There will

always be an option to download sqlc from a stable URL.

### Hosted Services[](#hosted-services "Permalink to this headline")

We provide a few hosted services in addition to the sqlc command line tool.

#### sqlc.dev[](#sqlc-dev "Permalink to this headline")

* Hosted on [GitHub Pages](https://pages.github.com/)

* Analytics with [Plausible](https://plausible.io/privacy-focused-web-analytics)

#### docs.sqlc.dev[](#docs-sqlc-dev "Permalink to this headline")

* Hosted on [Read the Docs](https://readthedocs.org/)

* Analytics with [Plausible](https://plausible.io/privacy-focused-web-analytics)

#### play.sqlc.dev[](#play-sqlc-dev "Permalink to this headline")

* Hosted on [Heroku](https://heroku.com)

* Playground data stored in [Google Cloud Storage](https://cloud.google.com/storage)

	+ Automatically deleted after 30 days

#### app.sqlc.dev / api.sqlc.dev[](#app-sqlc-dev-api-sqlc-dev "Permalink to this headline")

* Hosted on [Heroku](https://heroku.com)

* Error tracking and tracing with [Sentry](https://sentry.io)

