← Back to go ORM Libraries

sqlc

sqlc is "Generate type-safe Go from SQL" - a Go-specific SQL query compiler that automatically generates type-safe Go code from raw SQL files and performs compile-time SQL syntax checking and type validation. Without going through ORM abstraction layers, it maximizes SQL's expressiveness while providing Go's type safety and IDE support. It automatically detects changes in database schemas and SQL queries, regenerating corresponding Go code to maintain consistency between SQL and Go code at all times, significantly reducing runtime errors.

GoSQLType-safeCode GenerationQuery CompilerCLI

GitHub Overview

sqlc-dev/sqlc

Generate type-safe code from SQL

Stars15,717
Watchers85
Forks911
Created:June 21, 2019
Language:Go
License:MIT License

Topics

code-generatorgokotlinmysqlormpostgresqlpythonsqlsqlite

Star History

sqlc-dev/sqlc Star History
Data as of: 8/13/2025, 01:43 AM

Library

sqlc

Overview

sqlc is "Generate type-safe Go from SQL" - a Go-specific SQL query compiler that automatically generates type-safe Go code from raw SQL files and performs compile-time SQL syntax checking and type validation. Without going through ORM abstraction layers, it maximizes SQL's expressiveness while providing Go's type safety and IDE support. It automatically detects changes in database schemas and SQL queries, regenerating corresponding Go code to maintain consistency between SQL and Go code at all times, significantly reducing runtime errors.

Details

sqlc 2025 edition provides complete support for PostgreSQL, MySQL, SQLite, and SQL Server, achieving precise code generation that understands each database's dialects and proprietary features. Modern Go code generation leveraging Generics, support for advanced data types like JSON, UUID, and arrays, and automatic optimization of prepared statements achieve enterprise-level performance. Development experience improvements through CI pipeline integration, Docker support, and VS Code extensions maximize team development productivity. The SQL-first approach facilitates collaboration with DBAs and utilization of existing SQL.

Key Features

  • Type-Safe Code Generation: Automatic generation of completely type-safe Go code from SQL queries
  • SQL-First: Development approach that doesn't compromise SQL's expressiveness
  • Multi-DB Support: Support for PostgreSQL, MySQL, SQLite, SQL Server
  • Compile-Time Validation: Execute SQL syntax and type checking at build time
  • High Performance: Direct SQL execution without ORM overhead
  • Developer Experience: VS Code integration, CI support, Docker image provision

Pros and Cons

Pros

  • Advanced query description capabilities with full utilization of SQL's expressiveness
  • Significant reduction of runtime errors through compile-time type checking
  • High performance without ORM overhead
  • Easy collaboration with existing SQL queries and DBAs
  • High readability and maintainability of generated Go code
  • Unified handling of multiple databases possible

Cons

  • Go language exclusive with no multi-language support
  • Code generation step required, complicating build process
  • Excessive overhead for simple CRUD operations in early development
  • Not suitable for dynamic query construction
  • No ORM-like relationship features provided
  • Learning barrier for developers accustomed to Active Record pattern

Reference Pages

Code Examples

Setup

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

# Initialize project
mkdir sqlc-example
cd sqlc-example
go mod init sqlc-example

# Create sqlc configuration file
sqlc init

# sqlc.yaml
version: "2"
project:
  id: "sqlc-example"
sql:
  - engine: "postgresql"
    queries: "sql/queries/"
    schema: "sql/schema/"
    gen:
      go:
        package: "db"
        out: "internal/db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_prepared_queries: true
        emit_interface: true
        emit_exact_table_names: true
        emit_empty_slices: true
        overrides:
          - db_type: "pg_catalog.varchar"
            go_type: "string"
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"

# Add dependencies
go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/pgxpool
go get github.com/google/uuid

Schema and Query Definition

-- sql/schema/001_init.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER NOT NULL CHECK (age >= 0),
    active BOOLEAN NOT NULL DEFAULT true,
    profile JSONB,
    tags TEXT[],
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    published BOOLEAN NOT NULL DEFAULT false,
    published_at TIMESTAMP,
    view_count INTEGER NOT NULL DEFAULT 0,
    tags TEXT[],
    metadata JSONB,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(active);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);
CREATE INDEX idx_posts_published_at ON posts(published_at);

-- Trigger function (automatic update time)
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_update_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER posts_update_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

-- sql/queries/users.sql
-- name: CreateUser :one
INSERT INTO users (name, email, age, profile, tags)
VALUES ($1, $2, $3, $4, $5)
RETURNING *;

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

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

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

-- name: ListActiveUsers :many
SELECT * FROM users
WHERE active = true
ORDER BY name;

-- name: SearchUsersByName :many
SELECT * FROM users
WHERE name ILIKE '%' || $1 || '%'
ORDER BY name
LIMIT $2;

-- name: UpdateUser :one
UPDATE users
SET name = $2, email = $3, age = $4, profile = $5, tags = $6
WHERE id = $1
RETURNING *;

-- name: UpdateUserActive :exec
UPDATE users
SET active = $2
WHERE id = $1;

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

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

-- name: CountActiveUsers :one
SELECT COUNT(*) FROM users
WHERE active = true;

-- name: GetUsersByAgeRange :many
SELECT * FROM users
WHERE age BETWEEN $1 AND $2
ORDER BY age, name;

-- name: BatchUpdateUserAges :exec
UPDATE users
SET age = age + $1
WHERE id = ANY($2::bigint[]);

-- sql/queries/posts.sql
-- name: CreatePost :one
INSERT INTO posts (title, content, user_id, tags, metadata)
VALUES ($1, $2, $3, $4, $5)
RETURNING *;

-- name: GetPost :one
SELECT * FROM posts
WHERE id = $1;

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

-- name: ListPostsByUser :many
SELECT * FROM posts
WHERE user_id = $1
ORDER BY created_at DESC;

-- name: ListPublishedPosts :many
SELECT * FROM posts
WHERE published = true
ORDER BY published_at DESC
LIMIT $1 OFFSET $2;

-- name: GetPostsWithUsers :many
SELECT p.*, u.name as author_name, u.email as author_email
FROM posts p
JOIN users u ON p.user_id = u.id
ORDER BY p.created_at DESC
LIMIT $1 OFFSET $2;

-- name: SearchPosts :many
SELECT * FROM posts
WHERE title ILIKE '%' || $1 || '%'
   OR content ILIKE '%' || $1 || '%'
ORDER BY created_at DESC
LIMIT $2;

-- name: UpdatePost :one
UPDATE posts
SET title = $2, content = $3, tags = $4, metadata = $5
WHERE id = $1
RETURNING *;

-- name: PublishPost :one
UPDATE posts
SET published = true, published_at = CURRENT_TIMESTAMP
WHERE id = $1
RETURNING *;

-- name: UnpublishPost :one
UPDATE posts
SET published = false, published_at = NULL
WHERE id = $1
RETURNING *;

-- name: IncrementViewCount :exec
UPDATE posts
SET view_count = view_count + 1
WHERE id = $1;

-- name: DeletePost :exec
DELETE FROM posts
WHERE id = $1;

-- name: DeletePostsByUser :exec
DELETE FROM posts
WHERE user_id = $1;

-- name: GetPopularPosts :many
SELECT * FROM posts
WHERE published = true
ORDER BY view_count DESC
LIMIT $1;

-- name: GetPostStatistics :one
SELECT
    COUNT(*) as total_posts,
    COUNT(*) FILTER (WHERE published = true) as published_posts,
    AVG(view_count) as avg_view_count,
    MAX(view_count) as max_view_count
FROM posts;

-- name: GetUserPostCounts :many
SELECT
    u.id,
    u.name,
    COUNT(p.id) as post_count,
    COUNT(p.id) FILTER (WHERE p.published = true) as published_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name
ORDER BY post_count DESC;

Code Generation and Basic Usage

// internal/db/models.go (generated file)
package db

import (
    "database/sql/driver"
    "encoding/json"
    "time"
    "github.com/google/uuid"
    "github.com/lib/pq"
)

type User struct {
    ID        int64           `json:"id"`
    Name      string          `json:"name"`
    Email     string          `json:"email"`
    Age       int32           `json:"age"`
    Active    bool            `json:"active"`
    Profile   json.RawMessage `json:"profile"`
    Tags      pq.StringArray  `json:"tags"`
    CreatedAt time.Time       `json:"created_at"`
    UpdatedAt time.Time       `json:"updated_at"`
}

type Post struct {
    ID          int64           `json:"id"`
    Title       string          `json:"title"`
    Content     string          `json:"content"`
    UserID      int64           `json:"user_id"`
    Published   bool            `json:"published"`
    PublishedAt *time.Time      `json:"published_at"`
    ViewCount   int32           `json:"view_count"`
    Tags        pq.StringArray  `json:"tags"`
    Metadata    json.RawMessage `json:"metadata"`
    CreatedAt   time.Time       `json:"created_at"`
    UpdatedAt   time.Time       `json:"updated_at"`
}

# Execute code generation
sqlc generate

// main.go
package main

import (
    "context"
    "encoding/json"
    "fmt"
    "log"
    "os"
    
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/lib/pq"
    
    "sqlc-example/internal/db"
)

// Database connection
func connectDB() (*pgxpool.Pool, error) {
    databaseURL := os.Getenv("DATABASE_URL")
    if databaseURL == "" {
        databaseURL = "postgresql://postgres:password@localhost:5432/sqlc_example?sslmode=disable"
    }
    
    config, err := pgxpool.ParseConfig(databaseURL)
    if err != nil {
        return nil, fmt.Errorf("failed to parse config: %w", err)
    }
    
    pool, err := pgxpool.NewWithConfig(context.Background(), config)
    if err != nil {
        return nil, fmt.Errorf("failed to create connection pool: %w", err)
    }
    
    return pool, nil
}

// User service
type UserService struct {
    queries *db.Queries
}

func NewUserService(pool *pgxpool.Pool) *UserService {
    return &UserService{
        queries: db.New(pool),
    }
}

func (s *UserService) CreateUser(ctx context.Context, name, email string, age int32, profile map[string]interface{}, tags []string) (*db.User, error) {
    // Convert profile to JSON
    profileJSON, err := json.Marshal(profile)
    if err != nil {
        return nil, fmt.Errorf("failed to marshal profile: %w", err)
    }
    
    user, err := s.queries.CreateUser(ctx, db.CreateUserParams{
        Name:    name,
        Email:   email,
        Age:     age,
        Profile: profileJSON,
        Tags:    pq.StringArray(tags),
    })
    if err != nil {
        return nil, fmt.Errorf("failed to create user: %w", err)
    }
    
    return &user, nil
}

func (s *UserService) GetUser(ctx context.Context, id int64) (*db.User, error) {
    user, err := s.queries.GetUser(ctx, id)
    if err != nil {
        if err == pgx.ErrNoRows {
            return nil, fmt.Errorf("user not found: %d", id)
        }
        return nil, fmt.Errorf("failed to get user: %w", err)
    }
    
    return &user, nil
}

func (s *UserService) UpdateUser(ctx context.Context, id int64, name, email string, age int32, profile map[string]interface{}, tags []string) (*db.User, error) {
    profileJSON, err := json.Marshal(profile)
    if err != nil {
        return nil, fmt.Errorf("failed to marshal profile: %w", err)
    }
    
    user, err := s.queries.UpdateUser(ctx, db.UpdateUserParams{
        ID:      id,
        Name:    name,
        Email:   email,
        Age:     age,
        Profile: profileJSON,
        Tags:    pq.StringArray(tags),
    })
    if err != nil {
        return nil, fmt.Errorf("failed to update user: %w", err)
    }
    
    return &user, nil
}

func (s *UserService) ListUsers(ctx context.Context, limit, offset int32) ([]db.User, error) {
    users, err := s.queries.ListUsers(ctx, db.ListUsersParams{
        Limit:  limit,
        Offset: offset,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to list users: %w", err)
    }
    
    return users, nil
}

func (s *UserService) SearchUsers(ctx context.Context, query string, limit int32) ([]db.User, error) {
    users, err := s.queries.SearchUsersByName(ctx, db.SearchUsersByNameParams{
        Name:  query,
        Limit: limit,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to search users: %w", err)
    }
    
    return users, nil
}

func (s *UserService) GetUsersByAgeRange(ctx context.Context, minAge, maxAge int32) ([]db.User, error) {
    users, err := s.queries.GetUsersByAgeRange(ctx, db.GetUsersByAgeRangeParams{
        Age:   minAge,
        Age_2: maxAge,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to get users by age range: %w", err)
    }
    
    return users, nil
}

func (s *UserService) DeleteUser(ctx context.Context, id int64) error {
    err := s.queries.DeleteUser(ctx, id)
    if err != nil {
        return fmt.Errorf("failed to delete user: %w", err)
    }
    
    return nil
}

// Usage example
func demonstrateBasicOperations() {
    ctx := context.Background()
    
    // Database connection
    pool, err := connectDB()
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    defer pool.Close()
    
    userService := NewUserService(pool)
    
    // User creation
    profile := map[string]interface{}{
        "bio":      "Software Developer",
        "location": "Tokyo, Japan",
        "website":  "https://example.com",
    }
    tags := []string{"developer", "go", "postgres"}
    
    user, err := userService.CreateUser(ctx, "Alice Johnson", "[email protected]", 28, profile, tags)
    if err != nil {
        log.Printf("Failed to create user: %v", err)
        return
    }
    
    fmt.Printf("Created user: %+v\n", user)
    
    // Get user
    retrievedUser, err := userService.GetUser(ctx, user.ID)
    if err != nil {
        log.Printf("Failed to get user: %v", err)
        return
    }
    
    fmt.Printf("Retrieved user: %+v\n", retrievedUser)
    
    // Update user
    updatedProfile := map[string]interface{}{
        "bio":      "Senior Software Developer",
        "location": "Tokyo, Japan",
        "website":  "https://alice-dev.com",
        "skills":   []string{"Go", "PostgreSQL", "Docker"},
    }
    updatedTags := []string{"senior-developer", "go", "postgres", "docker"}
    
    updatedUser, err := userService.UpdateUser(ctx, user.ID, "Alice Johnson Smith", "[email protected]", 29, updatedProfile, updatedTags)
    if err != nil {
        log.Printf("Failed to update user: %v", err)
        return
    }
    
    fmt.Printf("Updated user: %+v\n", updatedUser)
    
    // Search users
    searchResults, err := userService.SearchUsers(ctx, "Alice", 10)
    if err != nil {
        log.Printf("Failed to search users: %v", err)
        return
    }
    
    fmt.Printf("Search results: %+v\n", searchResults)
    
    // Filter by age range
    ageRangeUsers, err := userService.GetUsersByAgeRange(ctx, 25, 35)
    if err != nil {
        log.Printf("Failed to get users by age range: %v", err)
        return
    }
    
    fmt.Printf("Users in age range 25-35: %+v\n", ageRangeUsers)
}

Post Functionality and JOIN Queries

// Post service
type PostService struct {
    queries *db.Queries
}

func NewPostService(pool *pgxpool.Pool) *PostService {
    return &PostService{
        queries: db.New(pool),
    }
}

func (s *PostService) CreatePost(ctx context.Context, title, content string, userID int64, tags []string, metadata map[string]interface{}) (*db.Post, error) {
    metadataJSON, err := json.Marshal(metadata)
    if err != nil {
        return nil, fmt.Errorf("failed to marshal metadata: %w", err)
    }
    
    post, err := s.queries.CreatePost(ctx, db.CreatePostParams{
        Title:    title,
        Content:  content,
        UserID:   userID,
        Tags:     pq.StringArray(tags),
        Metadata: metadataJSON,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to create post: %w", err)
    }
    
    return &post, nil
}

func (s *PostService) GetPost(ctx context.Context, id int64) (*db.Post, error) {
    post, err := s.queries.GetPost(ctx, id)
    if err != nil {
        if err == pgx.ErrNoRows {
            return nil, fmt.Errorf("post not found: %d", id)
        }
        return nil, fmt.Errorf("failed to get post: %w", err)
    }
    
    return &post, nil
}

func (s *PostService) ListPostsByUser(ctx context.Context, userID int64) ([]db.Post, error) {
    posts, err := s.queries.ListPostsByUser(ctx, userID)
    if err != nil {
        return nil, fmt.Errorf("failed to list posts by user: %w", err)
    }
    
    return posts, nil
}

func (s *PostService) GetPostsWithUsers(ctx context.Context, limit, offset int32) ([]db.GetPostsWithUsersRow, error) {
    posts, err := s.queries.GetPostsWithUsers(ctx, db.GetPostsWithUsersParams{
        Limit:  limit,
        Offset: offset,
    })
    if err != nil {
        return nil, fmt.Errorf("failed to get posts with users: %w", err)
    }
    
    return posts, nil
}

func (s *PostService) PublishPost(ctx context.Context, id int64) (*db.Post, error) {
    post, err := s.queries.PublishPost(ctx, id)
    if err != nil {
        return nil, fmt.Errorf("failed to publish post: %w", err)
    }
    
    return &post, nil
}

func (s *PostService) IncrementViewCount(ctx context.Context, id int64) error {
    err := s.queries.IncrementViewCount(ctx, id)
    if err != nil {
        return fmt.Errorf("failed to increment view count: %w", err)
    }
    
    return nil
}

func (s *PostService) GetPopularPosts(ctx context.Context, limit int32) ([]db.Post, error) {
    posts, err := s.queries.GetPopularPosts(ctx, limit)
    if err != nil {
        return nil, fmt.Errorf("failed to get popular posts: %w", err)
    }
    
    return posts, nil
}

func (s *PostService) GetPostStatistics(ctx context.Context) (*db.GetPostStatisticsRow, error) {
    stats, err := s.queries.GetPostStatistics(ctx)
    if err != nil {
        return nil, fmt.Errorf("failed to get post statistics: %w", err)
    }
    
    return &stats, nil
}

func (s *PostService) GetUserPostCounts(ctx context.Context) ([]db.GetUserPostCountsRow, error) {
    counts, err := s.queries.GetUserPostCounts(ctx)
    if err != nil {
        return nil, fmt.Errorf("failed to get user post counts: %w", err)
    }
    
    return counts, nil
}

// Usage example
func demonstratePostOperations() {
    ctx := context.Background()
    
    pool, err := connectDB()
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    defer pool.Close()
    
    userService := NewUserService(pool)
    postService := NewPostService(pool)
    
    // Create user
    user, err := userService.CreateUser(ctx, "John Doe", "[email protected]", 30, map[string]interface{}{}, []string{"author"})
    if err != nil {
        log.Printf("Failed to create user: %v", err)
        return
    }
    
    // Create post
    metadata := map[string]interface{}{
        "category": "technology",
        "readTime": 5,
        "featured": true,
    }
    tags := []string{"go", "programming", "tutorial"}
    
    post, err := postService.CreatePost(ctx, "Getting Started with Go", "This is a comprehensive guide to Go programming...", user.ID, tags, metadata)
    if err != nil {
        log.Printf("Failed to create post: %v", err)
        return
    }
    
    fmt.Printf("Created post: %+v\n", post)
    
    // Publish post
    publishedPost, err := postService.PublishPost(ctx, post.ID)
    if err != nil {
        log.Printf("Failed to publish post: %v", err)
        return
    }
    
    fmt.Printf("Published post: %+v\n", publishedPost)
    
    // Increment view count
    err = postService.IncrementViewCount(ctx, post.ID)
    if err != nil {
        log.Printf("Failed to increment view count: %v", err)
        return
    }
    
    // Get posts with user information joined
    postsWithUsers, err := postService.GetPostsWithUsers(ctx, 10, 0)
    if err != nil {
        log.Printf("Failed to get posts with users: %v", err)
        return
    }
    
    fmt.Printf("Posts with users: %+v\n", postsWithUsers)
    
    // Get statistics
    stats, err := postService.GetPostStatistics(ctx)
    if err != nil {
        log.Printf("Failed to get statistics: %v", err)
        return
    }
    
    fmt.Printf("Post statistics: %+v\n", stats)
    
    // User post counts
    userPostCounts, err := postService.GetUserPostCounts(ctx)
    if err != nil {
        log.Printf("Failed to get user post counts: %v", err)
        return
    }
    
    fmt.Printf("User post counts: %+v\n", userPostCounts)
}

Transactions and Advanced Features

// Transaction service
type TransactionService struct {
    pool *pgxpool.Pool
}

func NewTransactionService(pool *pgxpool.Pool) *TransactionService {
    return &TransactionService{pool: pool}
}

// Create user with initial posts simultaneously
func (s *TransactionService) CreateUserWithPosts(ctx context.Context, userName, userEmail string, userAge int32, initialPosts []struct {
    Title   string
    Content string
    Tags    []string
}) (*db.User, []db.Post, error) {
    tx, err := s.pool.Begin(ctx)
    if err != nil {
        return nil, nil, fmt.Errorf("failed to begin transaction: %w", err)
    }
    defer tx.Rollback(ctx)
    
    queries := db.New(tx)
    
    // Create user
    user, err := queries.CreateUser(ctx, db.CreateUserParams{
        Name:    userName,
        Email:   userEmail,
        Age:     userAge,
        Profile: json.RawMessage("{}"),
        Tags:    pq.StringArray{},
    })
    if err != nil {
        return nil, nil, fmt.Errorf("failed to create user: %w", err)
    }
    
    // Create initial posts
    var posts []db.Post
    for _, postData := range initialPosts {
        post, err := queries.CreatePost(ctx, db.CreatePostParams{
            Title:    postData.Title,
            Content:  postData.Content,
            UserID:   user.ID,
            Tags:     pq.StringArray(postData.Tags),
            Metadata: json.RawMessage("{}"),
        })
        if err != nil {
            return nil, nil, fmt.Errorf("failed to create post: %w", err)
        }
        posts = append(posts, post)
    }
    
    if err = tx.Commit(ctx); err != nil {
        return nil, nil, fmt.Errorf("failed to commit transaction: %w", err)
    }
    
    return &user, posts, nil
}

// Transfer posts between users
func (s *TransactionService) TransferPosts(ctx context.Context, fromUserID, toUserID int64) error {
    tx, err := s.pool.Begin(ctx)
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }
    defer tx.Rollback(ctx)
    
    queries := db.New(tx)
    
    // Check existence of both users
    _, err = queries.GetUser(ctx, fromUserID)
    if err != nil {
        return fmt.Errorf("source user not found: %w", err)
    }
    
    _, err = queries.GetUser(ctx, toUserID)
    if err != nil {
        return fmt.Errorf("target user not found: %w", err)
    }
    
    // Transfer posts (custom query)
    _, err = tx.Exec(ctx, "UPDATE posts SET user_id = $1 WHERE user_id = $2", toUserID, fromUserID)
    if err != nil {
        return fmt.Errorf("failed to transfer posts: %w", err)
    }
    
    if err = tx.Commit(ctx); err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }
    
    return nil
}

// Batch processing: Bulk update multiple users' ages
func (s *TransactionService) BatchUpdateAges(ctx context.Context, ageIncrement int32, userIDs []int64) error {
    tx, err := s.pool.Begin(ctx)
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }
    defer tx.Rollback(ctx)
    
    queries := db.New(tx)
    
    err = queries.BatchUpdateUserAges(ctx, db.BatchUpdateUserAgesParams{
        Age:    ageIncrement,
        Column: userIDs,
    })
    if err != nil {
        return fmt.Errorf("failed to batch update ages: %w", err)
    }
    
    if err = tx.Commit(ctx); err != nil {
        return fmt.Errorf("failed to commit transaction: %w", err)
    }
    
    return nil
}

// Usage example
func demonstrateTransactions() {
    ctx := context.Background()
    
    pool, err := connectDB()
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    defer pool.Close()
    
    txService := NewTransactionService(pool)
    
    // Create user with initial posts simultaneously
    initialPosts := []struct {
        Title   string
        Content string
        Tags    []string
    }{
        {"Welcome Post", "Welcome to my blog!", []string{"welcome", "introduction"}},
        {"About Me", "Let me introduce myself...", []string{"about", "personal"}},
        {"My First Tutorial", "This is my first technical post.", []string{"tutorial", "programming"}},
    }
    
    user, posts, err := txService.CreateUserWithPosts(ctx, "Content Creator", "[email protected]", 25, initialPosts)
    if err != nil {
        log.Printf("Failed to create user with posts: %v", err)
        return
    }
    
    fmt.Printf("Created user: %+v\n", user)
    fmt.Printf("Created %d posts\n", len(posts))
    
    // Create another user
    userService := NewUserService(pool)
    targetUser, err := userService.CreateUser(ctx, "Another User", "[email protected]", 30, map[string]interface{}{}, []string{})
    if err != nil {
        log.Printf("Failed to create target user: %v", err)
        return
    }
    
    // Transfer posts
    err = txService.TransferPosts(ctx, user.ID, targetUser.ID)
    if err != nil {
        log.Printf("Failed to transfer posts: %v", err)
        return
    }
    
    fmt.Printf("Transferred posts from user %d to user %d\n", user.ID, targetUser.ID)
    
    // Batch age update
    userIDs := []int64{user.ID, targetUser.ID}
    err = txService.BatchUpdateAges(ctx, 1, userIDs)
    if err != nil {
        log.Printf("Failed to batch update ages: %v", err)
        return
    }
    
    fmt.Printf("Updated ages for users: %v\n", userIDs)
}

Error Handling and Practical Patterns

// Custom error type
type AppError struct {
    Code    string
    Message string
    Err     error
}

func (e *AppError) Error() string {
    if e.Err != nil {
        return fmt.Sprintf("%s: %s (%v)", e.Code, e.Message, e.Err)
    }
    return fmt.Sprintf("%s: %s", e.Code, e.Message)
}

func (e *AppError) Unwrap() error {
    return e.Err
}

// Error helper functions
func NewAppError(code, message string, err error) *AppError {
    return &AppError{
        Code:    code,
        Message: message,
        Err:     err,
    }
}

func IsNotFoundError(err error) bool {
    return err == pgx.ErrNoRows
}

// Safe service implementation
type SafeUserService struct {
    queries *db.Queries
}

func NewSafeUserService(pool *pgxpool.Pool) *SafeUserService {
    return &SafeUserService{
        queries: db.New(pool),
    }
}

func (s *SafeUserService) CreateUserSafely(ctx context.Context, name, email string, age int32) (*db.User, error) {
    // Validation
    if name == "" {
        return nil, NewAppError("VALIDATION_ERROR", "Name cannot be empty", nil)
    }
    if email == "" {
        return nil, NewAppError("VALIDATION_ERROR", "Email cannot be empty", nil)
    }
    if age < 0 || age > 150 {
        return nil, NewAppError("VALIDATION_ERROR", "Age must be between 0 and 150", nil)
    }
    
    // Duplicate check
    _, err := s.queries.GetUserByEmail(ctx, email)
    if err == nil {
        return nil, NewAppError("DUPLICATE_EMAIL", "Email already exists", nil)
    }
    if !IsNotFoundError(err) {
        return nil, NewAppError("DATABASE_ERROR", "Failed to check email uniqueness", err)
    }
    
    // Create user
    user, err := s.queries.CreateUser(ctx, db.CreateUserParams{
        Name:    name,
        Email:   email,
        Age:     age,
        Profile: json.RawMessage("{}"),
        Tags:    pq.StringArray{},
    })
    if err != nil {
        return nil, NewAppError("CREATE_ERROR", "Failed to create user", err)
    }
    
    return &user, nil
}

func (s *SafeUserService) GetUserSafely(ctx context.Context, id int64) (*db.User, error) {
    user, err := s.queries.GetUser(ctx, id)
    if err != nil {
        if IsNotFoundError(err) {
            return nil, NewAppError("NOT_FOUND", fmt.Sprintf("User not found: %d", id), err)
        }
        return nil, NewAppError("DATABASE_ERROR", "Failed to get user", err)
    }
    
    return &user, nil
}

func (s *SafeUserService) UpdateUserSafely(ctx context.Context, id int64, name, email string, age int32) (*db.User, error) {
    // Existence check
    _, err := s.GetUserSafely(ctx, id)
    if err != nil {
        return nil, err
    }
    
    // Validation
    if name == "" {
        return nil, NewAppError("VALIDATION_ERROR", "Name cannot be empty", nil)
    }
    if email == "" {
        return nil, NewAppError("VALIDATION_ERROR", "Email cannot be empty", nil)
    }
    
    // Email duplicate check (excluding self)
    existingUser, err := s.queries.GetUserByEmail(ctx, email)
    if err == nil && existingUser.ID != id {
        return nil, NewAppError("DUPLICATE_EMAIL", "Email already exists", nil)
    }
    if err != nil && !IsNotFoundError(err) {
        return nil, NewAppError("DATABASE_ERROR", "Failed to check email uniqueness", err)
    }
    
    // Execute update
    user, err := s.queries.UpdateUser(ctx, db.UpdateUserParams{
        ID:      id,
        Name:    name,
        Email:   email,
        Age:     age,
        Profile: json.RawMessage("{}"),
        Tags:    pq.StringArray{},
    })
    if err != nil {
        return nil, NewAppError("UPDATE_ERROR", "Failed to update user", err)
    }
    
    return &user, nil
}

// Connection pool health check
func (s *SafeUserService) HealthCheck(ctx context.Context) error {
    _, err := s.queries.CountUsers(ctx)
    if err != nil {
        return NewAppError("HEALTH_CHECK_ERROR", "Database connection failed", err)
    }
    
    return nil
}

// Usage example and error handling
func demonstrateErrorHandling() {
    ctx := context.Background()
    
    pool, err := connectDB()
    if err != nil {
        log.Fatalf("Failed to connect to database: %v", err)
    }
    defer pool.Close()
    
    safeService := NewSafeUserService(pool)
    
    // Health check
    if err := safeService.HealthCheck(ctx); err != nil {
        log.Printf("Health check failed: %v", err)
        return
    }
    fmt.Println("✓ Database health check passed")
    
    // Normal user creation
    user, err := safeService.CreateUserSafely(ctx, "Jane Doe", "[email protected]", 28)
    if err != nil {
        var appErr *AppError
        if errors.As(err, &appErr) {
            log.Printf("Application error [%s]: %s", appErr.Code, appErr.Message)
        } else {
            log.Printf("Unexpected error: %v", err)
        }
        return
    }
    fmt.Printf("✓ Successfully created user: %+v\n", user)
    
    // Validation error test
    _, err = safeService.CreateUserSafely(ctx, "", "invalid-email", -5)
    if err != nil {
        var appErr *AppError
        if errors.As(err, &appErr) {
            fmt.Printf("✓ Correctly caught validation error [%s]: %s\n", appErr.Code, appErr.Message)
        }
    }
    
    // Duplicate email error test
    _, err = safeService.CreateUserSafely(ctx, "Another Jane", "[email protected]", 30)
    if err != nil {
        var appErr *AppError
        if errors.As(err, &appErr) {
            fmt.Printf("✓ Correctly caught duplicate email error [%s]: %s\n", appErr.Code, appErr.Message)
        }
    }
    
    // Non-existent user retrieval test
    _, err = safeService.GetUserSafely(ctx, 999999)
    if err != nil {
        var appErr *AppError
        if errors.As(err, &appErr) {
            fmt.Printf("✓ Correctly caught not found error [%s]: %s\n", appErr.Code, appErr.Message)
        }
    }
    
    // Normal update
    updatedUser, err := safeService.UpdateUserSafely(ctx, user.ID, "Jane Smith", "[email protected]", 29)
    if err != nil {
        log.Printf("Update failed: %v", err)
        return
    }
    fmt.Printf("✓ Successfully updated user: %+v\n", updatedUser)
}

// Main function
func main() {
    fmt.Println("=== sqlc Demo ===\n")
    
    demonstrateBasicOperations()
    fmt.Println("\n" + strings.Repeat("=", 50) + "\n")
    
    demonstratePostOperations()
    fmt.Println("\n" + strings.Repeat("=", 50) + "\n")
    
    demonstrateTransactions()
    fmt.Println("\n" + strings.Repeat("=", 50) + "\n")
    
    demonstrateErrorHandling()
}