Right, so you’ve told sqlc about your database schema. Good. Now we get to the fun part: actually telling it what you want to do with that data. This is where we define our queries. Forget writing a single line of database/sql boilerplate; we’re going to write SQL, and sqlc will write the Go code for us. It’s like having a very meticulous, incredibly fast intern who never complains about the coffee.

We do this in .sql files, conventionally in a db/query directory. Each file is usually named after the entity it operates on, like authors.sql or books.sql. Inside, we don’t just write raw SQL. We annotate it with a special comment that tells sqlc exactly what kind of Go function we want generated. This is the magic incantation.

The Anatomy of a sqlc Query

Let’s say we want to get an author by their ID. Here’s how we’d define that query in authors.sql:

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

That comment, -- name: GetAuthor :one, is the entire spec. Let’s break it down:

  • GetAuthor is the name of the Go method that will be generated. Make it descriptive. You’ll be calling this in your code.
  • :one is the result cardinality. It tells sqlc you expect this query to return exactly one row. If it finds zero or more than one, the generated code will return an error for you to handle. This is fantastic because it bakes this critical assumption right into the method signature.

The $1 is a placeholder parameter. sqlc will see this, look at your schema to determine the type of the id column, and generate a function that takes a parameter of the corresponding Go type. In this case, it would be func (q *Queries) GetAuthor(ctx context.Context, id int64) (Author, error).

Other cardinalities are :many for, you guessed it, multiple rows, and :exec for queries that don’t return any rows at all (INSERT, UPDATE, DELETE).

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

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

Parameterizing All the Things

You’re not limited to simple WHERE clauses. You can use parameters anywhere. Want to create a new author? Use parameters for every value to avoid SQL injection vulns (which sqlc completely eliminates, by the way—one of its killer features).

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

Notice we’re using :one here because we’re RETURNING *. This is a great pattern: your Create method will return the freshly created record, complete with any database-default values like auto-generated IDs or timestamps. The generated function will be func (q *Queries) CreateAuthor(ctx context.Context, name string, bio string) (Author, error).

You can get more complex, using parameters for LIMIT and OFFSET in pagination, or even in a CASE statement. sqlc handles it.

Dealing with Complex Results and Joins

What if your query doesn’t map neatly to a single table? What if you have a join? This is where sqlc’s configuration earns its keep. Let’s get all books with their author’s name.

-- name: ListBooksWithAuthor :many
SELECT books.id, books.title, books.author_id, authors.name as author_name
FROM books
JOIN authors ON authors.id = books.author_id;

sqlc is brilliant but it’s not clairvoyant. It can’t guess what struct you want this returned into. By default, it will try to map it to the books table schema and fail because author_name isn’t there. This is where you need to tell it about a custom return type in your sqlc.yaml configuration.

# In your sqlc.yaml, under the queries section for this file:
queries:
  - schema: "db"
    paths: ["db/query"]
    # This is the new bit:
    emit_json_tags: true
    emit_result_struct_pointers: true
    overrides:
      - go_type: "db.BookWithAuthor"
        query: ListBooksWithAuthor
        column: "id"

Now you need to define that BookWithAuthor struct. You do this by creating a Go file in the same package as your generated models. sqlc will see it and use it.

// In a file like db/custom_types.go
package db

type BookWithAuthor struct {
	ID         int64   `json:"id"`
	Title      string  `json:"title"`
	AuthorID   int64   `json:"author_id"`
	AuthorName string  `json:"author_name"`
}

It’s a slight bit of manual work, but it’s a one-time cost for a complex query, and it’s still infinitely better than writing the entire database layer by hand.

Running the Magic Command

With your queries defined, the moment of truth arrives. You run:

sqlc generate

Assuming your config is correct and your SQL is valid, sqlc will silently do its work. If not, it will scream at you with very helpful error messages. This command reads your sqlc.yaml, connects to your database (or uses the local schema files you provided) to understand the types, reads your query files, and then generates a beautiful, type-safe Go package in the output directory you specified.

The generated code is refreshingly straightforward. You’ll get a models.go file with all your structs (Author, Book) and a db.go file with a Queries struct that has all your methods (GetAuthor, ListAuthors, CreateAuthor) attached to it. It uses the standard database/sql package under the hood, so it’s efficient and familiar. You can then use this Queries struct in your application, passing in a *sql.DB or *sql.Conn, and start calling your methods. It feels like cheating, and after you use it, you’ll wonder why this isn’t the default way everyone interacts with SQL databases in Go.