sqlc: Type-Safe SQL Query Compilation for Go Developers

In modern backend development, Go is praised for its performance and simplicity. However, when it comes to database interactions, developers often face a trade-off: use an ORM for convenience but sacrifice control and performance, or write raw SQL and manually map results, risking runtime errors and boilerplate. sqlc offers a third path: it compiles your SQL queries into type-safe Go code, giving you the best of both worlds. With sqlc, you write standard SQL, run a command, and get generated functions that return strongly-typed structs. This eliminates entire classes of bugs (e.g., column name typos, type mismatches) and reduces boilerplate. sqlc supports PostgreSQL, MySQL, and SQLite, and integrates seamlessly into Go projects via go generate. In this tutorial, you'll learn how to set up sqlc, write queries, generate code, and use it in production. We'll cover CRUD operations, advanced queries, and best practices to avoid common pitfalls. By the end, you'll be able to ship database code faster and with more confidence.

What is sqlc and Why Use It?

sqlc is a code generation tool that takes SQL queries and produces type-safe Go code. Unlike ORMs that abstract SQL, sqlc keeps you writing raw SQL but eliminates the manual mapping step. This means you get compile-time checks for column names, types, and nullability. sqlc supports PostgreSQL, MySQL, and SQLite. It's particularly useful for projects that require high performance, as there is no runtime reflection or query building overhead. Additionally, sqlc integrates with go generate, making it easy to regenerate code whenever your schema or queries change. The generated code is idiomatic Go, using standard database/sql interfaces, so it works with any database driver.

version: "2"
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "query.sql"
    gen:
      go:
        package: "db"
        out: "db"
        sql_package: "pgx/v5"

Setting Up sqlc: Schema and Configuration

To use sqlc, you need a database schema file (DDL) and a queries file (DML). The configuration file sqlc.yaml tells sqlc where to find these files and how to generate Go code. Below is an example for PostgreSQL. The schema file defines tables, and the queries file contains your SQL statements with special annotations. sqlc uses these annotations to determine the method name and return type. For example, -- name: GetUser :one indicates a query that returns a single row. The generated code will include a method GetUser that returns a struct and an error.

CREATE TABLE users (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

Writing Queries with sqlc Annotations

sqlc queries are standard SQL with a comment annotation that specifies the method name and result type. The annotation format is -- name: <MethodName> <:one|:many|:exec>. :one returns a single row (or error if no rows), :many returns a slice, and :exec returns a result (for INSERT/UPDATE/DELETE without returning rows). You can also use :execrows to get the number of affected rows. Parameters are automatically inferred from the query; sqlc uses $1, $2, etc. for PostgreSQL, and ? for MySQL. The generated function will accept these parameters as arguments.

-- name: GetUser :one
SELECT id, name, email FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT id, name, email FROM users ORDER BY name;

-- name: CreateUser :exec
INSERT INTO users (name, email) VALUES ($1, $2);

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

Generating and Using the Go Code

Run sqlc generate in the directory containing sqlc.yaml. This produces a Go package with generated functions. Each function accepts a context.Context and a sql.DB (or pgxpool.Pool if using pgx) and returns the appropriate types. The generated code uses database/sql interfaces, so you can mock it for testing. Below is an example of using the generated code in a Go application. Note that sqlc also generates a Queries struct that holds a *sql.DB, allowing you to call methods on it.

package main

import (
    "context"
    "database/sql"
    "log"
    "yourproject/db"
)

func main() {
    conn, err := sql.Open("postgres", "postgres://user:pass@localhost/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close()

    queries := db.New(conn)

    // Create a user
    err = queries.CreateUser(context.Background(), "Alice", "alice@example.com")
    if err != nil {
        log.Fatal(err)
    }

    // Get user by ID
    user, err := queries.GetUser(context.Background(), 1)
    if err != nil {
        log.Fatal(err)
    }
    log.Printf("User: %+v", user)

    // List all users
    users, err := queries.ListUsers(context.Background())
    if err != nil {
        log.Fatal(err)
    }
    for _, u := range users {
        log.Printf("User: %+v", u)
    }
}

Advanced Queries: Joins, Aggregates, and Subqueries

sqlc supports complex SQL including joins, aggregates, subqueries, and CTEs. The generated struct will contain fields for all selected columns. For joins, you can either select specific columns or use sqlc.embed to embed a whole table's columns. sqlc.embed is a special annotation that tells sqlc to include all columns from a table, which is useful for JOINs. For example, -- name: GetUserWithOrders :many with a JOIN can embed both user and order fields. sqlc also handles nullable columns by generating pointer types (e.g., *string) for columns that are nullable in the schema.

-- name: GetUserWithOrders :many
SELECT sqlc.embed(users), sqlc.embed(orders)
FROM users
JOIN orders ON users.id = orders.user_id
WHERE users.id = $1;

-- name: CountOrdersByUser :many
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;

Handling Transactions and Batch Operations

sqlc generated methods work with transactions by accepting a sql.Tx instead of sql.DB. You can create a Queries instance with a transaction using db.New(tx). This allows you to perform multiple operations atomically. For batch operations, you can use :exec for each statement within a transaction. sqlc does not generate batch insert methods, but you can use PostgreSQL's COPY or write a loop. Below is an example of using a transaction.

tx, err := conn.BeginTx(ctx, nil)
if err != nil {
    log.Fatal(err)
}
defer tx.Rollback()

q := db.New(tx)

// Create user and order in a transaction
userID, err := q.CreateUser(ctx, "Bob", "bob@example.com")
if err != nil {
    log.Fatal(err)
}
err = q.CreateOrder(ctx, userID, "item1")
if err != nil {
    log.Fatal(err)
}

tx.Commit()

Testing with sqlc Generated Code

Testing database code is crucial. sqlc generates interfaces that can be mocked. You can use the Querier interface (generated by sqlc) to create mock implementations. Alternatively, use a test database with real queries. For unit tests, you can use database/sql mock drivers like go-sqlmock to simulate database responses. For integration tests, use a temporary database (e.g., via Docker) and run migrations before tests. sqlc's generated code is straightforward to test because it's just functions that take a context and a DB handle.

func TestGetUser(t *testing.T) {
    // Using go-sqlmock
    db, mock, err := sqlmock.New()
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()

    queries := db.New(db)
    rows := sqlmock.NewRows([]string{"id", "name", "email"}).
        AddRow(1, "Alice", "alice@example.com")
    mock.ExpectQuery("SELECT id, name, email FROM users WHERE id = \$1").
        WithArgs(1).
        WillReturnRows(rows)

    user, err := queries.GetUser(context.Background(), 1)
    if err != nil {
        t.Errorf("unexpected error: %v", err)
    }
    if user.Name != "Alice" {
        t.Errorf("expected Alice, got %s", user.Name)
    }
}