soroushj/sqlc

Generate type safe Go from SQL

sqlc: A SQL Compiler

And lo, the Great One looked down upon the people and proclaimed:

"SQL is actually pretty great"

sqlc generates fully type-safe idiomatic Go code from SQL. Here's how it works:

1. You write SQL queries
2. You run sqlc to generate Go code that presents type-safe interfaces to those queries
3. You write application code that calls the methods sqlc generated

Seriously, it's that easy. You don't have to write any boilerplate SQL querying code ever again.

Preventing Errors

But sqlc doesn't just make you more productive by generating boilerplate for you. sqlc also prevents entire classes of common errors in SQL code. Have you ever:

• Mixed up the order of the arguments when invoking the query so they didn't match up with the SQL text
• Updated the name of a column in one query but not another
• Mistyped the name of a column in a query
• Changed the number of arguments in a query but forgot to pass the additional values
• Changed the type of a column but forgot to change the type in your code?

All of these errors are impossible with sqlc. Wait, what? How?

sqlc parses all of your queries and the DDL (e.g. CREATE TABLE) statements during the code generation processes so that it knows the names and types of every column in your tables and every expression in your queries. If any of them do not match, sqlc will fail to compile your queries, preventing entire classes of runtime problems at compile time.

Likewise, the methods that sqlc generates for you have a strict arity and correct Go type definitions that match your columns. So if you change a query's arguments or a column's type but don't update your code, it will fail to compile.

Getting Started

Okay, enough hype, let's see it in action.

First you pass the following SQL to sqlc generate:

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

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

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

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

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

And then in your application code you'd write:

// list all authors
authors, err := db.ListAuthors(ctx)
if err != nil {
    return err
}
fmt.Println(authors)

// create an author
insertedAuthor, err := db.CreateAuthor(ctx, db.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
}
fmt.Println(insertedAuthor)

// get the author we just inserted
fetchedAuthor, err := db.GetAuthor(ctx, insertedAuthor.ID)
if err != nil {
        return err
}
// prints true
fmt.Println(reflect.DeepEqual(insertedAuthor, fetchedAuthor))

To make that possible, sqlc generates readable, idiomatic Go code that you otherwise would have had to write yourself. Take a look:

package db

import (
	"context"
	"database/sql"
)

type Author struct {
	ID   int64
	Name string
	Bio  sql.NullString
}

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 deleteAuthor = `-- 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
}

const getAuthor = `-- name: GetAuthor :one
SELECT id, name, bio 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)
	var i Author
	err := row.Scan(&i.ID, &i.Name, &i.Bio)
	return i, err
}

const listAuthors = `-- name: ListAuthors :many
SELECT id, name, bio FROM authors
ORDER BY name
`

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.Name, &i.Bio); 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
}

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,
	}
}

Examples

Your favorite PostgreSQL / Go features are supported:

• SQL
  • Query annotations
  • Transactions
  • Prepared queries
  • Named parameters
  • SELECT
  • NULL
  • COUNT
  • INSERT
  • UPDATE
  • DELETE
  • RETURNING
  • ANY
• PostgreSQL Types
  • Arrays
  • Enums
  • Timestamps
  • UUIDs
• DDL
  • CREATE TABLE
  • ALTER TABLE
• Go
  • JSON struct tags
  • Migration tools

A full, end-to-end example can be found in the sample ondeck package.

Usage

Usage:
  sqlc [command]

Available Commands:
  compile     Statically check SQL for syntax and type errors
  generate    Generate Go code from SQL
  help        Help about any command
  init        Create an empty sqlc.yaml settings file
  version     Print the sqlc version number

Flags:
  -h, --help   help for sqlc

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

Settings

The sqlc tool is configured via a sqlc.yaml file. This file must be in the directory where the sqlc command is run.

version: "1"
packages:
  - name: "db"
    path: "internal/db"
    queries: "./sql/query/"
    schema: "./sql/schema/"
    engine: "postgresql"
    emit_json_tags: true
    emit_prepared_queries: true
    emit_interface: false
    emit_exact_table_names: false
    emit_empty_slices: false

Each package document 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
• schema:
  • Directory of SQL migrations or path to single SQL file
• engine:
  • Either postgresql or mysql. Defaults to postgresql. MySQL support is experimental
• emit_json_tags:
  • If true, add JSON 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.

Type Overrides

The default mapping of PostgreSQL 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 type to override. Find the full list of supported types in gen.go.
• go_type:
  • A fully qualified name to a Go type to use in the generated code.
• nullable:
  • If true, use this type when a column is nullable. Defaults to false.

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 buy you may be even more specify 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: [...]

Renaming Struct 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 dictionary 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"

Installation

macOS

brew install kyleconroy/sqlc/sqlc

Ubuntu

sudo snap install sqlc

go get

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

Downloads

Binaries for a given release can be downloaded from the stable channel on Equinox or the latest GitHub release.

Tip Releases

Each commit is deployed to the devel channel on Equinox:

• Linux
• macOS

Other Databases and Languages

sqlc currently only supports PostgreSQL / Go. MySQL and Kotlin support have been merged, but both are marked as experimental. SQLite and TypeScript support are planned.

Language	PostgreSQL	MySQL
Go	✅ - Stable	🐛 - Beta
TypeScript	⏲️ - Planned	⏲️ - Planned
Kotlin	⚠️ - Experimental

If you'd like to add another database or language, we'd welcome a contribution.

Sponsors

sqlc development is funded by our generous sponsors.

• Companies
  • Meter
  • ngrok
  • Weave
• Individuals
  • Alex Besogonov
  • Myles McDonnell

If you use sqlc at your company, please consider becoming a sponsor today.

Sponsors receive priority support via the sqlc Slack organization.

Development

Building

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

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

Running Tests

To run the tests, include the exp tag. Without this tag, a few tests will fail.

go test --tags=exp ./...

To run the tests in the examples folder, a running PostgreSQL instance is required. The tests use the following environment variables to connect to the database:

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

go test --tags=examples,exp ./...

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.sh script.

make regen

Note that this uses the sqlc-dev binary, not sqlc so make sure you have an up to date sqlc-dev binary.

Acknowledgements

sqlc was inspired by PugSQL and HugSQL.