https://conroy.org/introducing-sqlc
https://github.com/kyleconroy/sqlc

Ask any Go developer and they will tell you how painful it is to write programs that talk to SQL databases. Go feels like it isn’t even half as productive compared to working with toolkits like SQLAlchemy, Diesel, Hibernate or ActiveRecord. The existing tools in the Go ecosystem force application developers to hand-write mapping functions or litter their code with unsafe empty interfaces.

Introducing sqlc
I’ve been feeling this pain for years, so six months ago I started developing a solution. Today, I’m excited to announce the result of that work. sqlc is a new tool that makes working with SQL in Go a joy.

It dramatically improves the developer experience of working with relational databases without sacrificing type-safety or runtime performance. It does not use struct tags, hand-written mapper functions, unnecessary reflection or add any new dependencies to your code. In fact, it even provides correctness and safety guarantees that no other SQL toolkit in the Go ecosystem can match.

sqlc accomplishes all of this by taking a fundamentally different approach: compiling SQL into fully type-safe, idiomatic Go code. sqlc can take this SQL:

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

– name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;
and generate the following Go code automatically.

package db

import (
“context”
“database/sql”
)

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

const getAuthor = -- name: GetAuthor :one
SELECT id, name, bio FROM authors
WHERE id = $1 LIMIT 1


type Queries struct {
db *sql.DB
}

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
}
For years, software engineers have been generating SQL queries from annotated objects in programming languages. SQL is already a structured, typed language; we should be generating correct, type-safe code in every programming language from the source of truth: SQL itself.

Problems using SQL from Go, today
Working with relational databases in any programming language is challenging. Go in particular is especially painful; even with the existing packages and tools, writing and maintaining queries in a Go application is a chore.

Low-level standard library
Go’s standard library offers the database/sql package for interacting with relational databases, but most applications outgrow the database/sql package and reach for a full-fledged ORM or a higher-level library abstraction. Why?

Using the database/sql package is straightforward. Write a query, pass in the necessary arguments, and scan the results back into fields. Programmers are responsible for explicitly specifying the mapping between a SQL field and its value in the program for both inputs and outputs.

Once an application has more than a few queries, maintaining these mappings is cumbersome and severely impacts programmer productivity. Worse, it’s trivial to make mistakes that are not caught until runtime. If you switch the order of parameters in your query, the parameter mapping must be updated. If a column is added to a table, all queries must be updated to return that value. If the type in SQL does not match the type in your code, failures will not occur until query execution time.

Higher-level libraries
The Go community has produced higher-level libraries (github.com/jmoiron/sqlx) and ORMs (github.com/jinzhu/gorm) to solve these issues. However, higher-level libraries still require manual mapping via query text and struct tags that, if incorrect, will only fail at runtime.

ORMs do away with much of the manual mapping but require you to write your queries now in a pseudo-sql DSL that basically reinvents SQL in a set of Go function calls.

Invalid SQL
With either approach, it is still trivial to make errors that the compiler can’t check. As a Go programmer, 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 both 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?

How to use sqlc in 3 steps
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 generates
Seriously, it’s that easy. You don’t have to write any boilerplate SQL querying code ever again. sqlc generates fully-type-safe idiomatic Go code from your queries. sqlc also prevents entire classes of common errors in SQL code.

During code generation, sqlc parses all of your queries and DDL statements (e.g. CREATE TABLE) 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, catching would-be runtime errors before they happen.

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.

A guided tour of sqlc
That all sounds great, but what does it look like in practice?

Download and install
To start, download the latest version of sqlc and add it to your $PATH. You’ll also want to have Go 1.13 installed on your system. Create a new directory for the example project.

$ mkdir sqlc-tour
$ cd sqlc-tour
$ go mod init github.com/kyleconroy/sqlc-tour
Configuration file
sqlc uses a configuration file at the root of your project to store settings. Create sqlc.json with the following contents:

{
“version”: “1”,
“packages”: [{
“schema”: “schema.sql”,
“queries”: “query.sql”,
“name”: “main”,
“path”: “.”
}]
}
sqlc will generate a Go package for each entry in the packages list. Each entry has four required properties:

schema
Path to a SQL file that defines database tables (can also be a directory of SQL files)
queries
Path to a SQL file with application queries (can also be a directory of SQL files)
name
The package name to use for the generated code. Defaults to path basename
path
Output directory for generated code
Write DDL
Let’s build an application for tracking authors. It’s a small application with only a single table. Define the authors table in schema.sql.

– schema.sql
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text
);
Write queries
Your application needs a few queries to create, insert, update and delete author records. Queries are annotated with a small comment that includes a Go method name and the database/sql function to use.

– query.sql

– 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;
Generate code
With just the information stored in these two SQL files, sqlc can now generate database access methods for your application. Make sure that you’re in the sqlc-tour directory and run the following command:

$ sqlc generate
This will generate three files: db.go, models.go, and query.sql.go.

db.go defines a shared interface for using a *sql.DB or *sql.Tx to execute queries, as well as the Queries struct which contains the database access methods.

db.go
// db.go
package main

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,
}
}
models.go
models.go contains the structs associated with the database tables. In this example it contains a single struct, Author.

// models.go
package main

import “database/sql”

type Author struct {
ID int64
Name string
Bio sql.NullString
}
query.sql.go
query.sql.go contains the data access methods we defined in query.sql. It’s a bit verbose, but this is code you would have had to write yourself!

// query.sql.go
package main

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 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
}
Star expansion
Were you surprised to see SELECT * / RETURNING * in query.sql? sqlc replaces * references with the correct columns when generating code. Take a second look at the createAuthor, listAuthor and getAuthor SQL queries in the example above.

Write your application code
It’s now easy to create, delete and fetch author records. Paste the following into main.go. It should build (go build) without any errors.

package main

import (
“context”
“database/sql”
“fmt”
)

func run(ctx context.Context, db *sql.DB) error {
q := &Queries{db: db}

insertedAuthor, err := q.CreateAuthor(ctx, CreateAuthorParams{
Name: “Brian Kernighan”,
Bio: sql.NullString{
String: “Co-author of The C Programming Language”,
Valid: true,
},
})
if err != nil {
return err
}

authors, err := q.ListAuthors(ctx)
if err != nil {
return err
}
fmt.Println(authors)

err = q.DeleteAuthor(ctx, insertedAuthor.ID)
if err != nil {
return err
}
return nil
}

func main() {
// TODO: Open a connection to your PostgreSQL database
run(context.Background(), nil)
}
All the above code can be found on in the sqlc-tour repository. A larger, more complicated example application can be found in the ondeck package in the sqlc repository.

Packed with power
Don’t let the previous example’s simplicity fool you. sqlc has support for complex queries and advanced usage patterns:

Transactions and prepared statements
Aggregates, case statements, and common table expressions
RETURNING values from INSERT, UPDATE, and DELETE statements
PostgreSQL types like arrays, enums, timestamps, and UUIDs
Go type overrides for individual columns or PostgreSQL values
Generated structs with JSON tags
How it works
You might be wondering how this all works. It’s not magic, but it’s close: sqlc parses queries using the same parser as your PostgreSQL database.

A first pass uses DDL statements to build an in-memory representation of your database. Next, sqlc parses each query and uses the in-memory representation to determine input parameters and output columns.

This is only possible thanks to the amazing work by Lukas Fittl on pg_query_go. If you need help diagnosing PostgreSQL performance issues, his service pganalyze may be exactly what you need.

What’s next
While it’s still early days, sqlc is ready for production. It’s used for all database access in my own projects:

https://equinox.io - Go application packaging & distribution
https://upcoming.fm - Spotify playlists for your favorite music venues
https://chaincontrol.org - SMS notifications for winter road closures in Tahoe
More importantly, it’s seen adoption in larger companies. Both ngrok and Weave use sqlc to power portions of their stack.

sqlc is a young project in active development. It currently only supports PostgreSQL and Go. However, it’s designed to support additional language backends in the future. If you’d like sqlc support for your language of choice, create an issue or send me an email at kyle@conroy.org.

Lastly, I like to thank the authors of hugsql, pugsql, and protoc, which served as inspiration for sqlc. Without these tools, sqlc would not exist.