Go sqlc Database Layer in Practice: 5 Core Patterns for Building Type-Safe Data Access with SQL Code Generation

编程语言

In 2026, Go database access has finally moved past the primitive era of "string SQL + interface{} scanning." sqlc solves the type safety problem in an elegant way: you write standard SQL, and it generates type-safe Go code. No ORM abstraction leaks, no tedious hand-written sql.Scan, no runtime reflection performance overhead. sqlc knows at compile time what fields and types your queries return — any SQL error is caught during code generation. This article walks you through 5 core patterns to build a production-grade sqlc database access layer.

Core Concepts

Concept Description Key Tool
sqlc Configuration YAML config file defining generation rules sqlc.yaml
SQL Query Annotations Comments in SQL files defining query metadata -- name: QueryName :one
Code Generation sqlc compiles SQL into type-safe Go code sqlc generate
Database Migrations Versioned database schema change management golang-migrate
Custom Types Go type to SQL type mapping overrides sqlc.yaml overrides

Problem Analysis: 5 Pain Points of Go Database Access

Pain Point 1: No Type Checking for Hand-Written SQL Strings

db.Query("SELECT id, name FROM users WHERE id = $1", id) — typos in table names, column names, and parameter type mismatches only surface at runtime.

Pain Point 2: Tedious and Error-Prone Manual sql.Scan Mapping

Every query requires hand-written rows.Scan(&id, &name, &email, &createdAt). Field order must match the SELECT clause — adding or removing fields is extremely error-prone.

Pain Point 3: Severe ORM Abstraction Leaks

GORM's Preload, Joins, and Association work well for simple cases, but complex queries produce uncontrollable SQL, frequent N+1 problems, and difficult debugging.

Pain Point 4: Database Migrations Out of Sync with Code

Migration scripts and Go code are two separate systems. Schema changes often leave Go code un-updated, causing runtime errors.

Pain Point 5: Repetitive Transaction Management Code

Every transactional operation requires boilerplate db.Begin(), tx.Commit(), tx.Rollback() — missing Rollback causes connection leaks.

Core Pattern 1: sqlc Configuration and Basic Query Generation

sqlc's core workflow is extremely simple: write SQL query files → configure sqlc.yaml → run sqlc generate → get type-safe Go code. All SQL is standard SQL — no new query language to learn.

# sqlc.yaml
version: "2"
sql:
  - engine: "postgresql"
    queries: "queries/"
    schema: "migrations/"
    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: false
        emit_empty_slices: true
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
          - db_type: "timestamptz"
            go_type: "time.Time"
          - db_type: "text"
            go_type: "string"
          - column: "users.avatar_url"
            go_type:
              type: "NullString"
              import: "database/sql"
          - column: "users.role"
            go_type:
              type: "UserRole"
              import: "toolsku/internal/db/custom"

-- queries/users.sql

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

-- name: GetUserByEmail :one
SELECT id, name, email, role, avatar_url, created_at, updated_at
FROM users
WHERE email = $1;

-- name: ListUsers :many
SELECT id, name, email, role, avatar_url, created_at, updated_at
FROM users
WHERE ($1::text IS NULL OR name ILIKE '%' || $1 || '%')
  AND ($2::text IS NULL OR email ILIKE '%' || $2 || '%')
ORDER BY created_at DESC
LIMIT $3 OFFSET $4;

-- name: CountUsers :one
SELECT COUNT(*) FROM users
WHERE ($1::text IS NULL OR name ILIKE '%' || $1 || '%')
  AND ($2::text IS NULL OR email ILIKE '%' || $2 || '%');

-- name: CreateUser :one
INSERT INTO users (name, email, role, avatar_url)
VALUES ($1, $2, $3, $4)
RETURNING id, name, email, role, avatar_url, created_at, updated_at;

-- name: UpdateUser :one
UPDATE users
SET name = $2, email = $3, role = $4, avatar_url = $5, updated_at = NOW()
WHERE id = $1
RETURNING id, name, email, role, avatar_url, created_at, updated_at;

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

Key Takeaways:

  • -- name: QueryName :one/:many/:exec annotations define query name and return type
  • :one returns a single record, :many returns a slice, :exec returns no data
  • sql.NullString handles nullable fields, avoiding zero-value ambiguity
  • RETURNING clause lets INSERT/UPDATE return complete records, avoiding secondary queries

Core Pattern 2: Complex Queries and JOIN Code Generation

sqlc's support for JOIN queries is one of its most powerful features. It automatically generates structs containing all JOIN fields — no manual mapping required. For complex report queries and aggregation queries, sqlc generates equally precise types.

-- queries/orders.sql

-- name: GetOrderWithDetails :one
SELECT
    o.id, o.order_number, o.status, o.total_amount, o.created_at,
    u.id AS user_id, u.name AS user_name, u.email AS user_email,
    p.id AS product_id, p.name AS product_name, p.price AS product_price,
    oi.quantity, oi.unit_price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.id = $1;

-- name: ListOrdersWithUser :many
SELECT
    o.id, o.order_number, o.status, o.total_amount, o.created_at,
    u.id AS user_id, u.name AS user_name, u.email AS user_email
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE ($1::text IS NULL OR o.status = $1::order_status)
  AND ($2::timestamptz IS NULL OR o.created_at >= $2)
  AND ($3::timestamptz IS NULL OR o.created_at <= $3)
ORDER BY o.created_at DESC
LIMIT $4 OFFSET $5;

-- name: GetOrderStatistics :one
SELECT
    COUNT(*) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_revenue,
    COALESCE(AVG(o.total_amount), 0) AS average_order_value,
    COUNT(CASE WHEN o.status = 'completed' THEN 1 END) AS completed_orders,
    COUNT(CASE WHEN o.status = 'pending' THEN 1 END) AS pending_orders,
    COUNT(CASE WHEN o.status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders o
WHERE ($1::timestamptz IS NULL OR o.created_at >= $1)
  AND ($2::timestamptz IS NULL OR o.created_at <= $2);

-- name: SearchOrders :many
SELECT o.id, o.order_number, o.status, o.total_amount, o.created_at, u.name AS user_name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE (o.order_number ILIKE '%' || $1 || '%' OR u.name ILIKE '%' || $1 || '%')
ORDER BY o.created_at DESC
LIMIT $2 OFFSET $3;

Key Takeaways:

  • sqlc automatically generates structs for JOIN queries containing all fields, using AS aliases for names
  • Aggregation query results (COUNT, SUM, AVG) have precise types inferred by sqlc
  • $1::text IS NULL pattern implements optional filter conditions — one query covers multiple scenarios
  • pgtype.Numeric handles PostgreSQL NUMERIC type, preventing precision loss

Core Pattern 3: Transaction Management and Database Migrations

sqlc generates a Queries struct that doesn't manage connections or transactions by itself. Through sqlc's emit_interface option, a Querier interface is generated, which combined with a custom transaction manager enables elegant transaction handling.

// internal/db/tx.go - Transaction manager
package db

import (
	"context"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

// Store database storage layer (wraps connection pool and transactions)
type Store struct {
	db *pgxpool.Pool
	*Queries
}

// NewStore creates Store
func NewStore(db *pgxpool.Pool) *Store {
	return &Store{
		db:      db,
		Queries: New(db),
	}
}

// ExecTx executes operations within a transaction
func (s *Store) ExecTx(ctx context.Context, fn func(*Queries) error) error {
	tx, err := s.db.Begin(ctx)
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}

	defer func() {
		if err != nil {
			if rbErr := tx.Rollback(ctx); rbErr != nil {
				err = fmt.Errorf("tx error: %v, rollback error: %w", err, rbErr)
			}
		}
	}()

	q := New(tx)
	if err = fn(q); err != nil {
		return err
	}

	return tx.Commit(ctx)
}

// TransferTx executes a transfer transaction
func (s *Store) TransferTx(ctx context.Context, arg TransferTxParams) (TransferTxResult, error) {
	var result TransferTxResult

	err := s.ExecTx(ctx, func(q *Queries) error {
		var err error

		result.Transfer, err = q.CreateTransfer(ctx, CreateTransferParams{
			FromAccountID: arg.FromAccountID,
			ToAccountID:   arg.ToAccountID,
			Amount:        arg.Amount,
		})
		if err != nil {
			return fmt.Errorf("create transfer: %w", err)
		}

		result.FromEntry, err = q.CreateEntry(ctx, CreateEntryParams{
			AccountID: arg.FromAccountID, Amount: -arg.Amount,
		})
		if err != nil {
			return fmt.Errorf("create from entry: %w", err)
		}

		result.ToEntry, err = q.CreateEntry(ctx, CreateEntryParams{
			AccountID: arg.ToAccountID, Amount: arg.Amount,
		})
		if err != nil {
			return fmt.Errorf("create to entry: %w", err)
		}

		result.FromAccount, err = q.AddAccountBalance(ctx, AddAccountBalanceParams{
			ID: arg.FromAccountID, Amount: -arg.Amount,
		})
		if err != nil {
			return fmt.Errorf("update from account: %w", err)
		}

		result.ToAccount, err = q.AddAccountBalance(ctx, AddAccountBalanceParams{
			ID: arg.ToAccountID, Amount: arg.Amount,
		})
		if err != nil {
			return fmt.Errorf("update to account: %w", err)
		}

		return nil
	})

	return result, err
}

// CreateOrderTx creates an order transaction
func (s *Store) CreateOrderTx(ctx context.Context, orderParams CreateOrderParams, items []CreateOrderItemParams) (Order, error) {
	var order Order

	err := s.ExecTx(ctx, func(q *Queries) error {
		var err error

		order, err = q.CreateOrder(ctx, orderParams)
		if err != nil {
			return fmt.Errorf("create order: %w", err)
		}

		for _, item := range items {
			item.OrderID = order.ID
			if _, err = q.CreateOrderItem(ctx, item); err != nil {
				return fmt.Errorf("create order item: %w", err)
			}
		}

		for _, item := range items {
			if err = q.DecrementProductStock(ctx, DecrementProductStockParams{
				ID: item.ProductID, Quantity: item.Quantity,
			}); err != nil {
				return fmt.Errorf("decrement stock: %w", err)
			}
		}

		return nil
	})

	return order, err
}

-- migrations/001_create_users.up.sql
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
    avatar_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name ON users(name);

-- migrations/001_create_users.down.sql
DROP TABLE IF EXISTS users;

Key Takeaways:

  • Store wraps the connection pool and Queries, providing transaction management
  • ExecTx accepts a closure function, automatically handling Begin/Commit/Rollback
  • Inside the closure, New(tx) creates a transaction-bound Queries; all operations run in the same transaction
  • embed.FS embeds migration files, compiling into a single binary

Core Pattern 4: Custom Types and Enum Mapping

sqlc's default generated types are sometimes imprecise — for example, PostgreSQL ENUM types map to string by default. Through sqlc.yaml overrides and emit_enum configuration, you can achieve precise type mapping.

# sqlc.yaml - Custom type configuration
version: "2"
sql:
  - engine: "postgresql"
    queries: "queries/"
    schema: "migrations/"
    gen:
      go:
        package: "db"
        out: "internal/db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_enum_valid_method: true
        emit_all_enum_values: true
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
          - db_type: "timestamptz"
            go_type: "time.Time"
          - db_type: "order_status"
            go_type:
              type: "OrderStatus"
              import: "toolsku/internal/db/custom"
          - db_type: "user_role"
            go_type:
              type: "UserRole"
              import: "toolsku/internal/db/custom"
          - db_type: "numeric"
            go_type:
              type: "Decimal"
              import: "github.com/shopspring/decimal"

// internal/db/custom/types.go - Custom type definitions
package custom

import (
	"database/sql/driver"
	"fmt"
)

// UserRole user role enum
type UserRole string

const (
	UserRoleUser      UserRole = "user"
	UserRoleAdmin     UserRole = "admin"
	UserRoleModerator UserRole = "moderator"
)

func (r UserRole) Valid() bool {
	switch r {
	case UserRoleUser, UserRoleAdmin, UserRoleModerator:
		return true
	}
	return false
}

func (r UserRole) Value() (driver.Value, error) {
	if !r.Valid() {
		return nil, fmt.Errorf("invalid user role: %s", r)
	}
	return string(r), nil
}

func (r *UserRole) Scan(value interface{}) error {
	if value == nil {
		*r = UserRoleUser
		return nil
	}
	s, ok := value.(string)
	if !ok {
		return fmt.Errorf("cannot scan %T into UserRole", value)
	}
	*r = UserRole(s)
	if !r.Valid() {
		return fmt.Errorf("invalid user role: %s", s)
	}
	return nil
}

// OrderStatus order status enum
type OrderStatus string

const (
	OrderStatusPending    OrderStatus = "pending"
	OrderStatusProcessing OrderStatus = "processing"
	OrderStatusCompleted  OrderStatus = "completed"
	OrderStatusCancelled  OrderStatus = "cancelled"
)

func (s OrderStatus) Valid() bool {
	switch s {
	case OrderStatusPending, OrderStatusProcessing, OrderStatusCompleted, OrderStatusCancelled:
		return true
	}
	return false
}

func (s OrderStatus) Value() (driver.Value, error) {
	if !s.Valid() {
		return nil, fmt.Errorf("invalid order status: %s", s)
	}
	return string(s), nil
}

func (s *OrderStatus) Scan(value interface{}) error {
	if value == nil {
		*s = OrderStatusPending
		return nil
	}
	v, ok := value.(string)
	if !ok {
		return fmt.Errorf("cannot scan %T into OrderStatus", value)
	}
	*s = OrderStatus(v)
	if !s.Valid() {
		return fmt.Errorf("invalid order status: %s", v)
	}
	return nil
}

Key Takeaways:

  • emit_enum_valid_method: true generates Valid() methods for enums
  • emit_all_enum_values: true generates constants for all enum values
  • Custom types must implement driver.Valuer and sql.Scanner interfaces
  • shopspring/decimal handles NUMERIC type, preventing float64 precision loss

Core Pattern 5: Production-Grade Database Access Layer Encapsulation

Wrap sqlc-generated code into a production-grade data access layer with connection pool management, health checks, timeout control, retry logic, and other production-grade features.

// internal/db/store.go - Production-grade database storage layer
package db

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

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5/pgxpool"
)

// StoreConfig storage layer configuration
type StoreConfig struct {
	DatabaseURL       string
	MaxConns          int32
	MinConns          int32
	MaxConnLifetime   time.Duration
	MaxConnIdleTime   time.Duration
	HealthCheckPeriod time.Duration
}

func DefaultStoreConfig(url string) StoreConfig {
	return StoreConfig{
		DatabaseURL: url, MaxConns: 25, MinConns: 5,
		MaxConnLifetime: 30 * time.Minute, MaxConnIdleTime: 5 * time.Minute,
		HealthCheckPeriod: 1 * time.Minute,
	}
}

// Store production-grade database storage layer
type Store struct {
	pool *pgxpool.Pool
	*Queries
}

func NewStore(ctx context.Context, cfg StoreConfig) (*Store, error) {
	poolConfig, err := pgxpool.ParseConfig(cfg.DatabaseURL)
	if err != nil {
		return nil, fmt.Errorf("parse database url: %w", err)
	}

	poolConfig.MaxConns = cfg.MaxConns
	poolConfig.MinConns = cfg.MinConns
	poolConfig.MaxConnLifetime = cfg.MaxConnLifetime
	poolConfig.MaxConnIdleTime = cfg.MaxConnIdleTime
	poolConfig.HealthCheckPeriod = cfg.HealthCheckPeriod

	pool, err := pgxpool.NewWithConfig(ctx, poolConfig)
	if err != nil {
		return nil, fmt.Errorf("create connection pool: %w", err)
	}

	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}

	return &Store{pool: pool, Queries: New(pool)}, nil
}

func (s *Store) Close() { s.pool.Close() }

func (s *Store) Health(ctx context.Context) error {
	ctx, cancel := context.WithTimeout(ctx, 3*time.Second)
	defer cancel()
	return s.pool.Ping(ctx)
}

func (s *Store) Stats() *pgxpool.Stat { return s.pool.Stat() }

// UserService user service layer
type UserService struct {
	store *Store
}

func NewUserService(store *Store) *UserService {
	return &UserService{store: store}
}

// ListUsersInput list query input
type ListUsersInput struct {
	Search string
	Page   int32
	Size   int32
}

// ListUsersOutput list query output
type ListUsersOutput struct {
	Users    []User `json:"users"`
	Total    int64  `json:"total"`
	Page     int32  `json:"page"`
	PageSize int32  `json:"page_size"`
}

// List gets user list (with pagination and search)
func (s *UserService) List(ctx context.Context, input ListUsersInput) (*ListUsersOutput, error) {
	if input.Page < 1 { input.Page = 1 }
	if input.Size < 1 || input.Size > 100 { input.Size = 20 }

	offset := (input.Page - 1) * input.Size

	var searchName, searchEmail sql.NullString
	if input.Search != "" {
		searchName = sql.NullString{String: input.Search, Valid: true}
		searchEmail = sql.NullString{String: input.Search, Valid: true}
	}

	count, err := s.store.CountUsers(ctx, CountUsersParams{Name: searchName, Email: searchEmail})
	if err != nil {
		return nil, fmt.Errorf("count users: %w", err)
	}

	users, err := s.store.ListUsers(ctx, ListUsersParams{
		Name: searchName, Email: searchEmail, Limit: input.Size, Offset: offset,
	})
	if err != nil {
		return nil, fmt.Errorf("list users: %w", err)
	}

	return &ListUsersOutput{
		Users: users, Total: count, Page: input.Page, PageSize: input.Size,
	}, nil
}

// Create creates a user (with email uniqueness check)
func (s *UserService) Create(ctx context.Context, params CreateUserParams) (*User, error) {
	_, err := s.store.GetUserByEmail(ctx, params.Email)
	if err == nil {
		return nil, fmt.Errorf("email already exists: %s", params.Email)
	}
	if err != sql.ErrNoRows {
		return nil, fmt.Errorf("check email: %w", err)
	}

	user, err := s.store.CreateUser(ctx, params)
	if err != nil {
		return nil, fmt.Errorf("create user: %w", err)
	}
	return &user, nil
}

// Update updates a user
func (s *UserService) Update(ctx context.Context, id uuid.UUID, params UpdateUserParams) (*User, error) {
	_, err := s.store.GetUser(ctx, id)
	if err != nil {
		return nil, fmt.Errorf("user not found: %w", err)
	}
	params.ID = id
	user, err := s.store.UpdateUser(ctx, params)
	if err != nil {
		return nil, fmt.Errorf("update user: %w", err)
	}
	return &user, nil
}

// Delete deletes a user
func (s *UserService) Delete(ctx context.Context, id uuid.UUID) error {
	_, err := s.store.GetUser(ctx, id)
	if err != nil {
		return fmt.Errorf("user not found: %w", err)
	}
	return s.store.DeleteUser(ctx, id)
}

Key Takeaways:

  • pgxpool connection pool configuration (MaxConns, MinConns, timeouts) is critical for production
  • UserService encapsulates business logic; Handlers focus only on HTTP protocol
  • embed.FS embeds migration files — no extra files needed at deployment time

Common Pitfalls

Pitfall 1: Manually Modifying sqlc-Generated Code

# ❌ Wrong: Manually editing sqlc-generated code, will be overwritten on next generate
vim internal/db/users.sql.go

# ✅ Correct: Make all changes in SQL files or custom types, then regenerate
vim queries/users.sql
sqlc generate

Pitfall 2: Using Zero Values Instead of Null Types for Nullable Fields

// ❌ Wrong: Nullable field using string zero value, can't distinguish NULL from empty string
type User struct {
	AvatarUrl string `json:"avatar_url"` // NULL becomes ""
}

// ✅ Correct: Use sql.NullString or pointer type
type User struct {
	AvatarUrl sql.NullString `json:"avatar_url"` // NULL is Valid:false
}

Pitfall 3: Forgetting to Use tx-Created Queries in Transactions

// ❌ Wrong: Using global Queries in a transaction, operations not in same transaction
func (s *Store) TransferTx(ctx context.Context) error {
	tx, _ := s.db.Begin(ctx)
	defer tx.Rollback(ctx)
	s.CreateTransfer(ctx, ...)  // Not in transaction!
	return tx.Commit(ctx)
}

// ✅ Correct: Use New(tx) to create transaction-bound Queries
func (s *Store) TransferTx(ctx context.Context) error {
	tx, _ := s.db.Begin(ctx)
	defer tx.Rollback(ctx)
	q := New(tx)
	q.CreateTransfer(ctx, ...)  // In transaction
	return tx.Commit(ctx)
}

Pitfall 4: Incorrect overrides Path in sqlc.yaml

# ❌ Wrong: column path using table name without schema.table
overrides:
  - column: "avatar_url"
    go_type: "*string"

# ✅ Correct: Use full schema.table.column path
overrides:
  - column: "public.users.avatar_url"
    go_type: "*string"

Pitfall 5: Non-Standard Migration File Naming

# ❌ Wrong: Migration filename missing sequence prefix
migrations/create_users.up.sql

# ✅ Correct: Use sequence prefix to ensure execution order
migrations/001_create_users.up.sql
migrations/001_create_users.down.sql

Error Troubleshooting

Error Symptom Possible Cause Troubleshooting Method Solution
sqlc generate error SQL syntax error Check SQL in queries/ directory Fix SQL syntax
Generated code type mismatch overrides misconfigured Check sqlc.yaml overrides Use correct column/db_type path
Runtime Scan error SELECT fields don't match struct Check SQL SELECT list Ensure SELECT includes all needed fields
Connection pool exhausted MaxConns too small Check pool.Stats() Increase MaxConns or investigate slow queries
Transaction timeout Transaction execution too long Check operations in transaction Add context timeout or split transaction
Migration conflict Multiple instances migrating simultaneously Check migrate lock mechanism Use migrate advisory lock
NULL value panic Nullable field not using Null type Check field definitions Use sql.NullString or pointer
Enum validation failure Database value not in enum range Check database data Add database CHECK constraint
JOIN result missing fields SELECT doesn't include JOIN fields Check SQL SELECT Add missing JOIN fields
pgtype.Numeric precision loss Using float64 for NUMERIC Check overrides configuration Use shopspring/decimal

Advanced Optimization

1. sqlc Verification Mode

Use sqlc vet in CI to check SQL query quality, detecting performance issues like SELECT *, missing LIMIT, N+1 queries, etc.

2. Batch Operation Optimization

Use pgx's CopyFrom for bulk inserts — 10-100x faster than individual INSERTs. sqlc supports pgx batch query mode.

3. Read-Write Separation

Configure primary-replica database connection pools. Write operations go to the primary, read operations to replicas. Encapsulate read-write routing logic in Store.

4. Query Cache Layer

Add Redis caching at the UserService layer. Hot queries (like user info) check cache first, then database, reducing database pressure.

5. Slow Query Monitoring

Record all SQL execution times via pgx's ConnConfig.Tracer. Queries exceeding thresholds trigger automatic alerts. Combine with OpenTelemetry for full-chain tracing.

Comparison

Dimension sqlc GORM sqlx Ent
Type Safety ✅ Compile-time checking ⚠️ Runtime checking ❌ None ✅ Code generation
SQL Control ✅ Full control ❌ ORM generates ✅ Full control ❌ Abstraction layer
Learning Curve ✅ SQL only ⚠️ ORM concepts ✅ SQL only ⚠️ Graph API
Code Generation ✅ Auto-generated ❌ Reflection ❌ Hand-written ✅ Auto-generated
JOIN Support ✅ Native SQL ⚠️ Preload ✅ Native SQL ✅ Graph traversal
Migration Management ❌ External tool needed ✅ AutoMigrate ❌ External tool needed ✅ Built-in
Performance ✅ Prepared statements ⚠️ Reflection overhead ✅ Native ⚠️ Abstraction overhead
Debugging Difficulty ✅ SQL visible ❌ Generated SQL uncontrollable ✅ SQL visible ⚠️ Abstraction layer

Summary

sqlc solves Go database access type safety in the simplest way: you write SQL, it generates Go code. No ORM abstraction leaks, no tedious hand-written Scan, no runtime reflection performance overhead. When you need complex queries, write SQL directly; when you need type safety, sqlc generates it. SQL is the universal language of databases — sqlc lets Go developers converse with databases in the most natural way. That's sqlc's philosophy.

Online Tool Recommendations

  • JSON Formatter — Format sqlc-generated JSON tag structures for quick data model verification
  • cURL to Code — Convert cURL commands to Go HTTP code for quick API endpoint testing
  • Hash Calculator — Calculate migration file hash values to verify migration integrity

Try these browser-local tools — no sign-up required →

#sqlc#Go数据库#SQL代码生成#PostgreSQL#2026#编程语言