Go being type-safe is probably one of the biggest benefits that attracted you to the language. However, if you ask any Go developer, they will tell you about some pain they experienced writing to SQL databases.

I recently discovered sqlc, and it’s been a delight to use.

What is it?

sqlc generates type-safe code from SQL.

• You write queries in SQL.

• You run sqlc to generate code with type-safe interfaces to those queries.

• You write application code that calls the generated code.

Here’s an example. For an input of:

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

You get type-safe Go code such as:

// Code generated by sqlc. DO NOT EDIT.
// source: query.sql

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

Powerful stuff!

What are the downsides?

• Some folks really do not like code generation. I sort of get this, but this seems a great use case.

• Some folks reported limitations such as “in some cases, it doesn’t support count(*) as X but it works with count(id)… Also, scoped query is not supported (e.g., adding a WHERE on a query), which can be blocking on some projects.”

Personally, I think it is worth these tradeoffs.

My advice: if you are looking for an ORM for Go, using GORM, or writing raw SQL queries, use this instead.