sqlc Documentation

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:

You write SQL queries
You run sqlc to generate Go code that presents type-safe interfaces to those queries
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

sqlc is distributed as a single binary with zero dependencies.

macOS

brew install sqlc

Ubuntu

sudo snap install sqlc

go install

Go >= 1.17:

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

Go < 1.17:

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

Docker

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

Get pre-built binaries for v1.17.2:

See downloads.sqlc.dev for older versions.

Getting started with MySQL

This tutorial assumes that the latest version of sqlc is installed 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

This tutorial assumes that the latest version of sqlc is installed 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: "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

This tutorial assumes that the latest version of sqlc is installed 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

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

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

In PostgreSQL, ANY 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
}

Counting rows

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

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

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

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

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

Single parameter

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

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

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

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

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

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

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() (nullable 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');

The following code is generated:

type UpdateAuthorParams struct {
	Name sql.NullString
	Bio  sql.NullString
	ID   int64
}

Modifying the database schema

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

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:

goose

-- +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

-- +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

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

Warning: golang-migrate interprets 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

-- migrate:up
CREATE TABLE foo (bar INT NOT NULL);

-- migrate:down
DROP TABLE foo;

package db

type Foo struct {
	Bar int32
}

Configuring generated structs

Naming scheme

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

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

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

Uploading projects

This feature requires signing up for sqlc Cloud, 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 project, uploaded projects are tested against development releases of sqlc to verify correctness.

Add configuration

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

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

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

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

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"
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

Each mapping in the sql collection has the following keys:

engine:
- Either postgresql or mysql.
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 for the supported keys.
gen:
- A mapping to configure built-in code generators. See 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

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

The gen mapping supports the following keys:

go

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_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.
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

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

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 or mysql_type.go. 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"

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

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

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

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

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

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"
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_db_file_name: "db.go"
    output_models_file_name: "models.go"
    output_querier_file_name: "querier.go"

packages

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

overrides

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 or mysql_type.go. 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

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

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

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

Arrays

PostgreSQL arrays 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

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

PostgreSQL enums 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

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

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

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

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`

The generated method will return the error from 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`

The generated method will return the sql.Result returned by ExecContext.

-- name: DeleteAllAuthors :execresult
DELETE FROM authors;

func (q *Queries) DeleteAllAuthors(ctx context.Context) (sql.Result, error) {
	return q.db.ExecContext(ctx, deleteAllAuthors)
}

`:execrows`

The generated method will return the number of affected rows from the result returned by ExecContext.

-- name: DeleteAllAuthors :execrows
DELETE FROM authors;

func (q *Queries) DeleteAllAuthors(ctx context.Context) (int64, error) {
	_, err := q.db.ExecContext(ctx, deleteAllAuthors)
	// ...
}

`:execlastid`

The generated method will return the number generated by the database from the result returned by 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`

The generated method will return a slice of records via 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`

The generated method will return a single record via 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`

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`

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`

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

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

Environment variables

SQLCDEBUG

The SQLCDEBUG variable controls debugging variables within the runtime. It is a comma-separated list of name=val pairs settings.

dumpast

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

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

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.

000043897 	 .         	1 	task sqlc (id 1, parent 0) created
000144923 	 .   101026 	1 	region generate started (duration: 47.619781ms)
001048975 	 .   904052 	1 	region package started (duration: 14.588456ms)
001054616 	 .     5641 	1 	name=authors dir=/Users/kyle/projects/sqlc/examples/python language=python
001071257 	 .    16641 	1 	region parse started (duration: 7.966549ms)
009043960 	 .  7972703 	1 	region codegen started (duration: 6.587086ms)
009171704 	 .   127744 	1 	new goroutine 35: text/template/parse.lex·dwrap·1
010361654 	 .  1189950 	1 	new goroutine 36: text/template/parse.lex·dwrap·1
015641815 	 .  5280161 	1 	region package started (duration: 10.904938ms)
015644943 	 .     3128 	1 	name=booktest dir=/Users/kyle/projects/sqlc/examples/python language=python
015647431 	 .     2488 	1 	region parse started (duration: 4.207749ms)
019860308 	 .  4212877 	1 	region codegen started (duration: 6.681624ms)
020028488 	 .   168180 	1 	new goroutine 37: text/template/parse.lex·dwrap·1
021020310 	 .   991822 	1 	new goroutine 8: text/template/parse.lex·dwrap·1
026551163 	 .  5530853 	1 	region package started (duration: 9.217294ms)
026554368 	 .     3205 	1 	name=jets dir=/Users/kyle/projects/sqlc/examples/python language=python
026556804 	 .     2436 	1 	region parse started (duration: 3.491005ms)
030051911 	 .  3495107 	1 	region codegen started (duration: 5.711931ms)
030213937 	 .   162026 	1 	new goroutine 20: text/template/parse.lex·dwrap·1
031099938 	 .   886001 	1 	new goroutine 38: text/template/parse.lex·dwrap·1
035772637 	 .  4672699 	1 	region package started (duration: 10.267039ms)
035775688 	 .     3051 	1 	name=ondeck dir=/Users/kyle/projects/sqlc/examples/python language=python
035778150 	 .     2462 	1 	region parse started (duration: 4.094518ms)
039877181 	 .  4099031 	1 	region codegen started (duration: 6.156341ms)
040010771 	 .   133590 	1 	new goroutine 39: text/template/parse.lex·dwrap·1
040894567 	 .   883796 	1 	new goroutine 40: text/template/parse.lex·dwrap·1
046042779 	 .  5148212 	1 	region writefiles started (duration: 1.718259ms)
047767781 	 .  1725002 	1 	task end

Changelog

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

1.17.2 

Released 2023-02-22

Bug Fixes

Fix build on Windows (#2102)

1.17.1 

Released 2023-02-22

Bug Fixes

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

Add float support to MySQL (#2097)

Build

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

1.17.0 

Released 2023-02-13

Bug Fixes

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

Change ENTRYPOINT to CMD (#1943)
Update samples for HOW-TO GUIDES (#1953)

Features

Add the diff command (#1963)

Build

(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

Generate packages in parallel (#2026)

Cmd/sqlc

Bump version to v1.17.0

Codegen

Remove built-in Kotlin support (#1935)
Remove built-in Python support (#1936)

Internal/codegen

Cache pattern matching compilations (#2028)

Mysql

Add datatype tests (#1948)
Fix blob tests (#1949)

Plugins

Upgrade to wasmtime 3.0.1 (#2009)

Sqlite

Supported between expr (#1958) (#1967)

Tools

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

Wasm

Upgrade to wasmtime 5.0.0 (#2065)

1.16.0 

Released 2022-11-09

Bug Fixes

(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

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

Add HAVING support to MySQL (#1806)

Miscellaneous Tasks

Upgrade wasmtime version (#1827)
Bump wasmtime version to v1.0.0 (#1869)

Build

(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

Bump to v1.16.0

Codgen

Include serialized codegen options (#1890)

Compiler

Move Kotlin parameter logic into codegen (#1910)

Examples

Port Python examples to WASM plugin (#1903)

Pg-gen

Make sqlc-pg-gen the complete source of truth for pg_catalog.go (#1809)
Implement information_schema shema (#1815)

Python

Port all Python tests to sqlc-gen-python (#1907)
Upgrade to sqlc-gen-python v1.0.0 (#1932)

1.15.0 

Released 2022-08-07

Bug Fixes

(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

Add process-based plugin docs (#1669)
Add links to downloads.sqlc.dev (#1681)
Update transactions how to example (#1775)

Features

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

Add tests for quoting columns (#1733)
Remove catalog tests (#1762)

Testing

Add tests for fixing slice imports (#1736)
Add test cases for returning (#1737)

Build

Upgrade to Go 1.19 (#1780)
Upgrade to go-wasmtime 0.39.0 (#1781)

Plugins

(wasm) Change default cache location (#1709)
(wasm) Change the SHA-256 config key (#1710)

1.14.0 

Released 2022-06-09

Bug Fixes

(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

(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

Fix extra newline in comments for copyfrom (#1438)
Generate marshal/unmarshal with vtprotobuf (#1467)

Refactor

(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 

Released 2022-03-31

Bug Fixes

(compiler) Fix left join nullability with table aliases (#1491)
(postgresql) Remove extra newline with db argument (#1417)
(sqlite) Fix DROP TABLE (#1443)

Features

(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

Fix extra newline in comments for copyfrom (#1438)
Generate marshal/unmarshal with vtprotobuf (#1467)

Refactor

(codegen) Port Kotlin codegen package to use plugin types (#1416)
(codegen) Port Go to plugin types (#1460)
(cmd) Simplify codegen selection logic (#1466)

Config

Add basic fuzzing for config / overrides (#1500)

1.12.0 

Released 2022-02-05

Bug

ALTER TABLE SET SCHEMA (#1409)

Bug Fixes

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

Bump version to 1.11.0 (#1308)

Features

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

Add tests for :exec{result,rows} (#1344)
Delete template-based codegen (#1345)

Build

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 

Released 2021-11-24

Bug Fixes

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

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

Instrument compiler via runtime/trace (#1258)
Add MySQL support for BETWEEN arguments (#1265)

Refactor

Move from io/ioutil to io and os package (#1164)

Styling

Apply gofmt to sample code (#1261)

Build

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

Bump version to v1.11.0

1.10.0 

Released 2021-09-07

Documentation

Fix invalid language support table (#1161)
Add a getting started guide for MySQL (#1163)

Build

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

Upgrade Go to 1.17 (#1130)

Cmd/sqlc

Bump version to v1.10.0 (#1165)

Codegen/golang

Consolidate import logic (#1139)
Add pgx support for range types (#1146)
Use pgtype for hstore when using pgx (#1156)

Codgen/golang

Use p[gq]type for network address types (#1142)

Endtoend

Run go test in CI (#1134)

Engine/mysql

Add support for LIKE (#1162)

Golang

Output NullUUID when necessary (#1137)

1.9.0 

Released 2021-08-13

Documentation

Update documentation (a bit) for v1.9.0 (#1117)

Build

Bump golang from 1.16.6 to 1.16.7 (#1107)

Cmd/sqlc

Bump version to v1.9.0 (#1121)

Compiler

Add tests for COALESCE behavior (#1112)
Handle subqueries in SELECT statements (#1113)

1.8.0 

Released 2021-05-03

Documentation

Add language support Matrix (#920)

Features

Add case style config option (#905)

Python

Eliminate runtime package and use sqlalchemy (#939)

Build

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

Add the –experimental flag (#929)
Fix sqlc init (#959)

Cmd/sqlc

Bump version to v1.7.1-devel (#913)
Bump version to v1.8.0

Codegen

Generate valid enum names for symbols (#972)

Postgresql

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

Resolve return values from functions (#964)

Workflows

Only run tests once (#924)

1.7.0 

Released 2021-02-28

Bug Fixes

Struct tag formatting (#833)

Documentation

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

Adding support for pgx/v4
Adding support for pgx/v4

README

Add Go Report Card badge (#891)

Build

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

Improve alter column type (#818)

Ci

Uprade to Go 1.15 (#887)

Cmd

Allow config file location to be specified (#863)

Cmd/sqlc

Bump to version v1.6.1-devel (#807)
Bump version to v1.7.0 (#912)

Codegen/golang

Make sure to import net package (#858)

Compiler

Support UNION query

Dolphin

Generate bools for tinyint(1)
Support joins in update statements (#883)
Add support for union query

Endtoend

Add tests for INTERSECT and EXCEPT

Go.mod

Update to go 1.15 and run ‘go mod tidy’ (#808)

Mysql

Compile tinyint(1) to bool (#873)

Sql/ast

Add enum values for SetOperation

1.6.0 

Released 2020-11-23

Dolphin

Implement Rename (#651)
Skip processing view drops (#653)

README

Update language / database support (#698)

Astutils

Fix Params rewrite call (#674)

Build

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

Improve variadic argument support (#804)

Cmd/sqlc

Bump to version v1.6.0 (#806)

Codegen

Fix errant database/sql imports (#789)

Compiler

Use engine-specific reserved keywords (#677)

Dolphi

Add list of builtin functions (#795)

Dolphin

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

Add MySQL json test (#692)
Add MySQL update set multiple test (#696)

Examples

Use generated enum constants in db_test (#678)
Port ondeck to MySQL (#680)
Add MySQL authors example (#682)

Internal/cmd

Print correct config file on parse failure (#749)

Kotlin

Remove runtime dependency (#774)

Metadata

Support multiple comment prefixes (#683)

Postgresql

Support string concat operator (#701)

Sql/catalog

Add support for variadic functions (#798)

1.5.0 

Released 2020-08-05

Documentation

Build sqlc using Go 1.14 (#549)

Cmd

Add debugging support (#573)

Cmd/sqlc

Bump version to v1.4.1-devel (#548)
Bump version to v1.5.0

Compiler

Support calling functions with defaults (#635)
Skip func args without a paramRef (#636)
Return a single column from coalesce (#639)

Config

Add emit_empty_slices to version one (#552)

Contrib

Add generated code for contrib

Dinosql

Remove deprecated package (#554)

Dolphin

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

Add MySQL test for SELECT * JOIN (#565)
Add MySQL test for inflection (#567)

Engine

Create engine package (#556)

Equinox

Use the new equinox-io/setup action (#586)

Examples

Run tests for MySQL booktest (#627)

Golang

Add support for the money type (#561)
Generate correct types for int2 and int8 (#579)

Internal

Rm catalog, pg, postgres packages (#555)

Mod

Downgrade TiDB package to fix build (#603)

Mysql

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

Generate all functions in pg_catalog (#550)
Remove pg_catalog schema from tests (#638)
Move contrib code to a package

Sql/catalog

Fix comparison of pg_catalog types (#637)

Tools

Generate functions for all of contrib

Workflow

Migrate to equinox-io/setup-release-tool (#614)

1.4.0 

Released 2020-06-17

Dockerfile

Add version build argument (#487)

MySQL

Prevent Panic when WHERE clause contains parenthesis. (#531)

README

Document emit_exact_table_names (#486)

All

Remove the exp build tag (#507)

Catalog

Support functions with table parameters (#541)

Cmd

Bump to version 1.3.1-devel (#485)

Cmd/sqlc

Bump version to v1.4.0 (#547)

Codegen

Add the new codegen packages (#513)
Add the :execresult query annotation (#542)

Compiler

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

Standardize JSON formatting (#490)
Add per-test configuration files (#521)
Read expected stderr failures from disk (#527)

Internal/dinosql

Check parameter style before ref (#488)
Remove unneeded column suffix (#492)
Support named function arguments (#494)

Internal/postgresql

Fix NamedArgExpr rewrite (#491)

Multierr

Move dinosql.ParserErr to a new package (#496)

Named

Port parameter style validation to SQL (#504)

Parser

Support columns from subselect statements (#489)

Rewrite

Move parameter rewrite to package (#499)

Sqlite

Use convert functions instead of the listener (#519)

Sqlpath

Move ReadSQLFiles into a separate package (#495)

Validation

Move query validation to separate package (#498)

1.3.0 

Released 2020-05-12

Makefile

Update target (#449)

README

Add Myles as a sponsor (#469)

Testing

Make sure all Go examples build (#480)

Cmd

Bump version to v1.3.0 (#484)

Cmd/sqlc

Bump version to v1.2.1-devel (#442)

Dinosql

Inline addFile (#446)
Add PostgreSQL support for TRUNCATE (#448)

Gen

Emit json.RawMessage for JSON columns (#461)

Go.mod

Use latest lib/pq (#471)

Parser

Use same function to load SQL files (#483)

Postgresql

Fix panic walking CreateTableAsStmt (#475)

1.2.0 

Released 2020-04-07

Documentation

Publish to Docker Hub (#422)

README

Docker installation docs (#424)

Cmd/sqlc

Bump version to v1.1.1-devel (#407)
Bump version to v1.2.0 (#441)

Gen

Add special case for “campus” (#435)
Properly quote reserved keywords on expansion (#436)

Migrations

Move migration parsing to new package (#427)

Parser

Generate correct types for SELECT EXISTS (#411)

1.1.0 

Released 2020-03-17

README

Add installation instructions (#350)
Add section on running tests (#357)
Fix typo (#371)

Ast

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

Apply changes to the ValuesList slice (#372)

Cmd

Return v1.0.0 (#348)
Return next bug fix version (#349)

Cmd/sqlc

Bump version to v1.1.0 (#406)

Compiler

Wire up the experimental parsers

Config

Remove “emit_single_file” option (#367)

Dolphin

Add experimental parser for MySQL

Gen

Add option to emit single file for Go (#366)
Add support for the ltree extension (#385)

Go.mod

Add packages for MySQL and SQLite parsers

Internal/dinosql

Support Postgres macaddr type in Go (#358)

Internal/endtoend

Remove %w (#354)

Kotlin

Add Query class to support timeout and cancellation (#368)

Postgresql

Add experimental parser for MySQL

Sql

Add generic SQL AST

Sql/ast

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

Port tests over from catalog pkg (#402)

Sql/errors

Add a new errors package (#390)

Sqlite

Add experimental parser for SQLite

1.0.0 

Released 2020-02-18

Documentation

Add documentation for query commands (#270)
Add named parameter documentation (#332)

README

Add sponsors section (#333)

Cmd

Remove parse subcommand (#322)

Config

Parse V2 config format
Add support for YAML (#336)

Examples

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

Allow CTE to be used with UPDATE (#268)
Remove the PackageMap from settings (#295)

Internal/config

Create new config package (#313)

Internal/dinosql

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

Move more tests to the record/replay framework
Add update test for named params (#329)

Internal/mysql

Fix flaky test (#242)
Port tests to endtoend package (#315)

Internal/parser

Resolve nested CTEs (#324)
Error if last query is missing (#325)
Support joins with aliases (#326)
Remove print statement (#327)

Internal/sqlc

Add support for composite types (#311)

Kotlin

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

Attach range vars to insert params
Attach range vars to insert params (#342)
Remove dead code (#343)

0.1.0 

Released 2020-01-07

Documentation

Replace remaining references to DinoSQL with sqlc (#149)

README

Fix download links (#66)
Add LIMIT 1 to query that should return one (#99)

Catalog

Support “ALTER TABLE … DROP CONSTRAINT …” (#34)
Differentiate functions with different argument types (#51)

Ci

Enable tests on pull requests

Cmd

Include filenames in error messages (#69)
Do not output any changes on error (#72)

Dinosql/internal

Add lower and upper functions (#215)
Ignore alter sequence commands (#219)

Gen

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

Parse unnamed function arguments (#166)

Internal/dinosql

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

Add Makefile (#156)

Ondeck

Move all tests to GitHub CI (#58)

ParseQuery

Return either a query or an error (#178)

Parser

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

Store functions in the catalog (#41)
Add location to errors (#73)

Developing sqlc

Building

For local development, install sqlc under an alias. We suggest sqlc-dev.

go build -o ~/go/bin/sqlc-dev ./cmd/sqlc

Running Tests

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.

docker-compose up -d

The tests use the following environment variables to connect to the database

For PostgreSQL

Variable     Default Value
-------------------------
PG_HOST      127.0.0.1
PG_PORT      5432
PG_USER      postgres
PG_PASSWORD  mysecretpassword
PG_DATABASE  dinotest

For MySQL

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

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

To use plugins, you must be using Version 2 of the configuration file. The top-level plugins array defines the available plugins.

WASM plugins

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
- A WASM plugin (written in Rust) that outputs a friendly message
wasm_plugin_sqlc_gen_greeter
- An example project showing how to use a WASM plugin

Process plugins

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
- A process-based plugin that serializes the CodeGenRequest to JSON
process_plugin_sqlc_gen_json
- An example project showing how to use a process-based plugin

Privacy and data collection

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

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

We distribute sqlc using popular package managers such as Homebrew and Snapcraft. 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

We provide a few hosted services in addition to the sqlc command line tool.

sqlc.dev

Hosted on GitHub Pages
Analytics with Plausible

docs.sqlc.dev

Hosted on Read the Docs
Analytics with Plausible

play.sqlc.dev

Hosted on Heroku
Playground data stored in Google Cloud Storage
- Automatically deleted after 30 days

app.sqlc.dev / api.sqlc.dev

Hosted on Heroku
Error tracking and tracing with Sentry

sqlc Documentation

Installing sqlc

macOS

Ubuntu

go install

Go >= 1.17:

Go < 1.17:

Docker

Downloads

Getting started with MySQL

Getting started with PostgreSQL

Getting started with SQLite

Retrieving rows

Selecting columns

Passing a slice as a parameter to a query

Counting rows

Inserting rows

Returning columns from inserted rows

Using CopyFrom

Updating rows

Single parameter

Multiple parameters

Deleting rows

Preparing queries

Using transactions

Naming parameters

Nullable parameters

Modifying the database schema

Handling SQL migrations

goose

sql-migrate

tern

golang-migrate

dbmate

Configuring generated structs

Naming scheme

JSON tags

More control

Uploading projects

Add configuration

Dry run

Upload

CLI

Configuration

Version 2

sql

codegen

gen

go

Renaming fields

Type overriding

kotlin

python

json

plugins

global overrides

Version 1

packages

overrides

Per-Column Type Overrides

Package Level Overrides

rename

Datatypes

Arrays

Dates and Time

Enums

Null

UUIDs

JSON

Query annotations

:exec

:execresult

:execrows

:execlastid

:many

:one

:batchexec

:batchmany

:batchone

Database and language support

`:exec`

`:execresult`

`:execrows`

`:execlastid`

`:many`

`:one`

`:batchexec`

`:batchmany`

`:batchone`