Kyle Conroy Gray

Introducing sqlc - Compile SQL queries to type-safe Go

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

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

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:

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.