sqlc — Type-Safe Go Code Generation from SQL

sqlc is a tool that automatically generates type-safe Go code from SQL queries. You get both the expressiveness of SQL and Go's type safety simultaneously.

---

Introduction to sqlc

sqlc solves both ORM's "hiding SQL" problem and raw SQL's "no types" problem.

SQL query files (.sql)
       ↓  sqlc generate
Type-safe Go code (models.go, query.go)
       ↓
Use directly (compile-time type checking)

Installation

# Install with Go tool
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

# macOS (Homebrew)
brew install sqlc

---

Project Structure

myapp/
├── sqlc.yaml          # sqlc configuration file
├── db/
│   ├── migrations/    # migration files
│   │   └── 001_init.sql
│   ├── queries/       # SQL query files
│   │   └── users.sql
│   └── sqlc/          # generated code (do not edit)
│       ├── db.go
│       ├── models.go
│       └── users.sql.go
└── main.go

sqlc.yaml Configuration File

version: "2"
sql:
  - engine: "postgresql"
    queries: "./db/queries"
    schema: "./db/migrations"
    gen:
      go:
        package: "sqlcdb"
        out: "./db/sqlc"
        emit_json_tags: true
        emit_prepared_queries: false
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true

---

Schema and Query Writing

Migration Files (Schema Definition)

-- db/migrations/001_init.sql
CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT      NOT NULL,
    email      TEXT      UNIQUE NOT NULL,
    age        INT,
    bio        TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
    id         BIGSERIAL PRIMARY KEY,
    user_id    BIGINT    NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title      TEXT      NOT NULL,
    content    TEXT,
    published  BOOLEAN   NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Query Files

Queries specify their name and return type with special comments.

-- db/queries/users.sql

-- name: GetUser :one
SELECT * FROM users
WHERE id = $1 LIMIT 1;

-- name: GetUserByEmail :one
SELECT * FROM users
WHERE email = $1 LIMIT 1;

-- name: ListUsers :many
SELECT * FROM users
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;

-- name: CountUsers :one
SELECT COUNT(*) FROM users;

-- name: CreateUser :one
INSERT INTO users (name, email, age, bio)
VALUES ($1, $2, $3, $4)
RETURNING *;

-- name: UpdateUser :one
UPDATE users
SET name = $2,
    age  = $3,
    bio  = $4,
    updated_at = NOW()
WHERE id = $1
RETURNING *;

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

-- name: SearchUsers :many
SELECT * FROM users
WHERE name ILIKE $1 OR email ILIKE $1
ORDER BY name;

Query Return Type Specifiers

Specifier	Meaning
:one	*Model single row
:many	[]Model multiple rows
:exec	error no rows
:execresult	sql.Result, error
:execrows	int64, error

---

Code Generation

sqlc generate

Generated files:

models.go — auto-generated table structs

// Code generated by sqlc. DO NOT EDIT.
package sqlcdb

import (
    "time"
)

type User struct {
    ID        int64          `json:"id"`
    Name      string         `json:"name"`
    Email     string         `json:"email"`
    Age       sql.NullInt32  `json:"age"`
    Bio       sql.NullString `json:"bio"`
    CreatedAt time.Time      `json:"created_at"`
    UpdatedAt time.Time      `json:"updated_at"`
}

type Post struct {
    ID        int64          `json:"id"`
    UserID    int64          `json:"user_id"`
    Title     string         `json:"title"`
    Content   sql.NullString `json:"content"`
    Published bool           `json:"published"`
    CreatedAt time.Time      `json:"created_at"`
}

users.sql.go — auto-generated query functions

// Code generated by sqlc. DO NOT EDIT.
package sqlcdb

const createUser = `-- name: CreateUser :one
INSERT INTO users (name, email, age, bio)
VALUES ($1, $2, $3, $4)
RETURNING id, name, email, age, bio, created_at, updated_at
`

type CreateUserParams struct {
    Name  string         `json:"name"`
    Email string         `json:"email"`
    Age   sql.NullInt32  `json:"age"`
    Bio   sql.NullString `json:"bio"`
}

func (q *Queries) CreateUser(ctx context.Context, arg CreateUserParams) (User, error) {
    row := q.db.QueryRowContext(ctx, createUser, arg.Name, arg.Email, arg.Age, arg.Bio)
    var i User
    err := row.Scan(
        &i.ID, &i.Name, &i.Email, &i.Age, &i.Bio, &i.CreatedAt, &i.UpdatedAt,
    )
    return i, err
}

---

Practical Usage Example

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
    "myapp/db/sqlc"
)

func main() {
    db, err := sql.Open("postgres",
        "host=localhost user=myuser password=mypass dbname=mydb sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    queries := sqlcdb.New(db)
    ctx := context.Background()

    // Create user
    user, err := queries.CreateUser(ctx, sqlcdb.CreateUserParams{
        Name:  "Kim Golang",
        Email: "golang@example.com",
        Age:   sql.NullInt32{Int32: 30, Valid: true},
        Bio:   sql.NullString{String: "Go developer", Valid: true},
    })
    if err != nil {
        log.Fatal("create user failed:", err)
    }
    fmt.Printf("Created: ID=%d, Name=%s\n", user.ID, user.Name)

    // Query user
    found, err := queries.GetUser(ctx, user.ID)
    if err == sql.ErrNoRows {
        fmt.Println("User not found")
    } else if err != nil {
        log.Fatal(err)
    } else {
        fmt.Printf("Found: %s <%s>\n", found.Name, found.Email)
    }

    // List users (pagination)
    users, err := queries.ListUsers(ctx, sqlcdb.ListUsersParams{
        Limit:  10,
        Offset: 0,
    })
    if err != nil {
        log.Fatal(err)
    }
    for _, u := range users {
        fmt.Printf("  - [%d] %s\n", u.ID, u.Name)
    }

    // Update
    updated, err := queries.UpdateUser(ctx, sqlcdb.UpdateUserParams{
        ID:   user.ID,
        Name: "Kim Golang (updated)",
        Age:  sql.NullInt32{Int32: 31, Valid: true},
        Bio:  sql.NullString{String: "Senior Go developer", Valid: true},
    })
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Updated: %s\n", updated.Name)

    // Delete
    if err := queries.DeleteUser(ctx, user.ID); err != nil {
        log.Fatal(err)
    }
    fmt.Println("Deleted")
}

---

Using with Transactions

sqlc can accept both *sql.DB and *sql.Tx.

func transferOwnership(ctx context.Context, db *sql.DB, fromUserID, postID, toUserID int64) error {
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Use sqlc in transaction
    qtx := sqlcdb.New(tx)

    // Verify post ownership
    post, err := qtx.GetPost(ctx, postID)
    if err != nil {
        return fmt.Errorf("query post failed: %w", err)
    }
    if post.UserID != fromUserID {
        return fmt.Errorf("not owner")
    }

    // Transfer ownership
    if err := qtx.UpdatePostOwner(ctx, sqlcdb.UpdatePostOwnerParams{
        ID:     postID,
        UserID: toUserID,
    }); err != nil {
        return fmt.Errorf("transfer ownership failed: %w", err)
    }

    return tx.Commit()
}

---

sqlc vs GORM vs database/sql Comparison

Criteria	database/sql	GORM	sqlc
SQL Control	Full	Limited	Full
Type Safety	Manual	ORM-based	Compile-time
Code Amount	Large	Small	Medium
Complex Queries	Easy	Difficult	Easy
Performance	Best	Low	Best
Learning Curve	Medium	Low	Low
Migration	Separate tool	AutoMigrate	Separate tool

Recommended Scenarios:

• database/sql: Complex custom queries, maximum performance required
• GORM: Fast development, simple CRUD-focused apps
• sqlc: Know SQL well and want type safety (recommended)

---

Key Summary

• sqlc auto-generates SQL → Go code for type safety
• Don't edit generated code; modify SQL files then regenerate
• Transactions work identically with the sqlc.New(tx) pattern
• The workflow of "schema change → modify SQL → sqlc generate → compiler catches missing updates" is very powerful.