The stringly-typed database trap
You write a query to fetch a user by ID. You copy the column names from your migration file. You run the app locally and it works. You deploy to staging, forget to run a schema migration that renamed email to contact_email, and suddenly your production logs fill with sql: Scan error on column index 2, name "contact_email": converting driver.Value type string to a int. Or worse, the scan silently maps the wrong column to the wrong struct field, and your data becomes corrupted without a single panic.
Manual database/sql scanning in Go is unforgiving. You pass column names as strings. You guess types. You write boilerplate to handle sql.NullString and sql.NullInt64 every time a column might be nullable. The compiler cannot help you because it does not know your database schema. It only knows that you passed a string and a pointer to a struct.
How sqlc flips the script
sqlc treats your SQL as the source of truth. You write the queries first, in plain SQL files. You run a generator, and it produces Go code that matches your exact schema. The generated functions accept Go types, return Go structs, and handle nullability automatically. If your schema changes, the next generation run fails or updates the code. You catch mismatches before the binary even compiles.
Think of it like a type-safe bridge. On one side you have PostgreSQL, MySQL, or SQLite. On the other side you have Go. sqlc inspects both sides, builds the exact connectors, and hands you a package you import like any other library. You never write rows.Scan again. You never guess whether a column is int or int64. The generator does the mapping, and the Go compiler enforces it.
The minimal setup
Here is the configuration file that tells sqlc where to look and what to produce.
# version 2 is the current config format
version: "2"
# sql block defines one or more query sets
sql:
# target database dialect
- engine: "postgresql"
# directory containing your .sql files
queries: "queries/"
# directory containing your schema/migration files
schema: "schema/"
# output configuration for Go
gen:
go:
# package name for the generated code
package: "db"
# output directory
out: "db"
# adds json tags to generated structs
emit_json_tags: true
# disables prepared statement wrappers
emit_prepared_queries: false
Next, you write the queries. sqlc uses special comments to mark each query and declare its return behavior.
-- name: GetUserByID :one
-- returns exactly one row or an error
SELECT id, name, email, created_at
FROM users
WHERE id = $1;
-- name: CreateUser :one
-- inserts a row and returns the generated id
INSERT INTO users (name, email, created_at)
VALUES ($1, $2, $3)
RETURNING id;
Run the generator from your terminal. It reads the config, parses the schema, analyzes the SQL, and writes Go files into the db directory.
sqlc generate
The generated package exports functions that match your query names. You call them like normal Go functions.
package main
import (
"context"
"database/sql"
"log"
"time"
"myapp/db"
)
func main() {
// open connection to postgres
conn, err := sql.Open("pgx", "postgres://user:pass@localhost:5432/mydb")
if err != nil {
log.Fatal(err)
}
defer conn.Close()
// wrap connection in the generated query struct
q := db.New(conn)
ctx := context.Background()
// execute type-safe query
user, err := q.GetUserByID(ctx, 1)
if err != nil {
log.Fatal(err)
}
log.Printf("User: %s (%s)", user.Name, user.Email)
// insert with automatic null handling
newUser, err := q.CreateUser(ctx, db.CreateUserParams{
Name: "Alice",
Email: "alice@example.com",
CreatedAt: sql.NullTime{Time: time.Now(), Valid: true},
})
if err != nil {
log.Fatal(err)
}
log.Printf("Created user ID: %d", newUser.ID)
}
sqlc generates the db package. You import it. You call the functions. The compiler guarantees you pass the right types.
What happens under the hood
When you run sqlc generate, the tool performs three distinct steps. First, it parses your schema files to build an internal representation of every table, column, type, and constraint. Second, it parses your SQL files, extracts the queries marked with -- name:, and validates them against the schema. Third, it emits Go code.
The :one, :many, and :exec directives control the return signature. :one generates a function that returns a single struct and an error. :many returns a slice of structs and an error. :exec returns only an error, used for inserts, updates, and deletes that do not need to return rows.
Parameter placeholders like $1 and $2 become function arguments. sqlc infers the Go type from the schema column type. A PostgreSQL INT becomes int32. A VARCHAR becomes string. A TIMESTAMPTZ becomes time.Time. If a column is nullable, sqlc uses the database/sql null types automatically. You do not need to annotate your SQL with type hints. The generator reads the schema and decides.
The generated code follows standard Go conventions. Functions take context.Context as the first parameter. Error handling uses the familiar if err != nil pattern. Struct fields are exported and match your column names. The receiver name for methods follows the one-or-two-letter convention. You get idiomatic Go without writing a single line of boilerplate.
Trust the generator. Do not edit the output directory by hand. Your changes will vanish on the next run.
Real-world integration
In a production service, you rarely call the generated functions directly from HTTP handlers. You wrap them in a service layer that handles business logic, retries, and logging. The generated code becomes a data access layer that you compose with other dependencies.
package service
import (
"context"
"database/sql"
"fmt"
"log"
"time"
"myapp/db"
)
// UserService handles user lifecycle operations
type UserService struct {
q *db.Queries
}
// NewUserService creates a service with the generated query set
func NewUserService(q *db.Queries) *UserService {
return &UserService{q: q}
}
// GetUser fetches a user and wraps errors with context
func (s *UserService) GetUser(ctx context.Context, id int32) (*db.User, error) {
user, err := s.q.GetUserByID(ctx, id)
if err != nil {
// wrap database errors for upstream handlers
return nil, fmt.Errorf("fetch user %d: %w", id, err)
}
return &user, nil
}
// CreateUser validates input before touching the database
func (s *UserService) CreateUser(ctx context.Context, name, email string) (int32, error) {
if name == "" || email == "" {
return 0, fmt.Errorf("name and email are required")
}
result, err := s.q.CreateUser(ctx, db.CreateUserParams{
Name: name,
Email: email,
CreatedAt: sql.NullTime{Time: time.Now(), Valid: true},
})
if err != nil {
return 0, fmt.Errorf("create user %s: %w", email, err)
}
return result.ID, nil
}
The service layer keeps your HTTP handlers thin. You pass context.Context through every call. You wrap errors with fmt.Errorf and %w so callers can unwrap them later. You never leak database connections or skip cancellation checks. The generated code handles the scanning. Your code handles the business rules.
Context is plumbing. Run it through every long-lived call site.
When things go sideways
sqlc catches most mistakes at generation time, not runtime. If you reference a table that does not exist, the generator stops and prints a clear message. If you select a column that is not in your schema, you get a compilation-style error from the tool. If you change a column type in your migration but forget to update your query, the next sqlc generate fails.
Common pitfalls still exist. The first is schema drift. If you run migrations in production but forget to regenerate your Go code, your binary will try to call functions that expect the old types. The compiler will reject the build with cannot use x (type int32) as type int64 in argument or a similar type mismatch. Always run sqlc generate in your CI pipeline before building.
The second pitfall is overusing :many for large result sets. Fetching ten thousand rows into memory at once will spike your RSS and trigger GC pressure. Use pagination with LIMIT and OFFSET or keyset pagination. The generated code will still return a slice, but you control the size.
The third pitfall is ignoring nullability. If your schema says a column is nullable but your Go code assumes a plain string, sqlc will generate a sql.NullString field. Accessing .String without checking .Valid will panic or give you an empty value. Check the Valid field before unwrapping. The database does not lie about nulls. Your code should respect them.
The worst database bug is the one that silently returns wrong data. Catch it at generation time.
Choosing your database layer
Use sqlc when you want explicit SQL, compile-time type safety, and zero runtime reflection overhead. Use sqlx when you need lightweight struct mapping with named parameters but want to keep writing Go-driven queries. Pick an ORM like GORM or Ent when your team prefers fluent builders, automatic migrations, and is willing to trade query transparency for developer velocity. Stick to raw database/sql when you are writing a tiny script, a migration tool, or a library that must remain dependency-free.