The typo that breaks production
You spent three hours debugging a production issue only to find a typo in a column name buried inside a raw SQL string. The database returned an error, but your Go code treated it as a success because you ignored the error check, or worse, the schema changed and your struct fields no longer matched the columns. This is the daily grind of raw SQL in Go. sqlc fixes this by treating your SQL as the source of truth and generating type-safe Go code from it.
What sqlc actually does
sqlc stands for "SQL compiler." It reads your SQL files and a configuration file, then generates Go structs and query functions. Think of it like a translator that speaks fluent SQL and fluent Go. You write the query in SQL because that's where you're comfortable. sqlc reads that query, checks it against your database schema, and writes the Go code that executes it safely. You get the flexibility of SQL with the safety of Go's type system.
The tool runs as a build step. You write SQL, run the generator, and import the resulting Go package. If your SQL references a column that doesn't exist, generation fails. If you change the schema, the generated code breaks until you update the queries. This catches schema drift before it hits production.
Setting up the generator
Every sqlc project starts with a configuration file. The tool uses sqlc.yaml to know which database dialect you're using, where your schema lives, and where your queries are. Version 2 of the config is the current standard.
Here's the configuration for a PostgreSQL project:
# sqlc.yaml
# version 2 config structure
version: '2'
sql:
- engine: "postgresql"
# schema files define the tables and types sqlc validates against
schema: "schema.sql"
# query files contain the SQL that becomes Go functions
queries: "query.sql"
gen:
go:
# package name for the generated Go code
package: "db"
# output directory for generated files
out: "db"
The schema key points to files that create your tables. sqlc parses these to understand column names and types. The queries key points to files containing the SQL you want to turn into Go functions. The gen.go section tells the tool to output Go code in a package named db inside the db directory.
Convention aside: Go projects often run code generators via go generate. Add //go:generate sqlc generate to a file in your project so teammates can run go generate ./... to build everything. This keeps the build step explicit and reproducible.
Writing queries that become code
Create a schema file that defines your tables. sqlc uses this to validate queries and generate structs.
-- schema.sql
-- CREATE TABLE defines the structure sqlc uses for type checking
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
Now write the queries. sqlc uses special comments to tell the generator how to map SQL results to Go functions. The -- name: directive sets the function name. The :one, :many, or :exec suffix tells sqlc what the function returns.
-- query.sql
-- name: GetUser :one
-- :one tells sqlc to return a single User struct
SELECT id, name, email, created_at FROM users WHERE id = $1;
-- name: ListUsers :many
-- :many tells sqlc to return a slice of User structs
SELECT id, name, email, created_at FROM users ORDER BY created_at DESC;
-- name: CreateUser :execresult
-- :execresult returns the number of rows affected and the last insert id
INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id;
The $1, $2 placeholders are standard PostgreSQL parameter syntax. sqlc maps these to function arguments. The RETURNING clause lets sqlc generate a function that returns the inserted ID.
Using the generated code
Run sqlc generate in your project directory. The tool creates a db folder with Go files. You import this package and use the generated functions.
Here's how to use the generated code in a minimal program:
package main
import (
"context"
"database/sql"
"fmt"
"log"
"example.com/myapp/db"
)
func main() {
// Open a connection using the standard library driver
conn, err := sql.Open("postgres", "postgres://user:pass@localhost/dbname?sslmode=disable")
if err != nil {
log.Fatal(err)
}
defer conn.Close()
// New creates a queryer bound to the connection
q := db.New(conn)
// GetUser is the generated function from query.sql
// It takes context and the ID argument mapped from $1
user, err := q.GetUser(context.Background(), 1)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Found user: %s (%s)\n", user.Name, user.Email)
}
The db.New(conn) call creates a queryer object. This object holds the database connection and provides all the generated functions. The GetUser function takes a context.Context as the first argument, following Go convention. sqlc generates this automatically. The second argument matches the $1 placeholder in the SQL.
Convention aside: context.Context always goes as the first parameter in Go functions that perform I/O. sqlc respects this convention. Pass r.Context() in HTTP handlers to propagate cancellation and deadlines.
How the generation works
When you run sqlc generate, the tool performs several steps. It parses the schema files to build a model of your database. It parses the query files and validates each query against the schema. If a query references a table or column that doesn't exist, generation fails with an error like column "foo" does not exist. This is a compile-time check, not a runtime check.
The generator creates a Go struct for each table referenced in your queries. The struct fields match the column names, converted to PascalCase. The id column becomes ID, created_at becomes CreatedAt. sqlc also generates Params structs for queries with multiple arguments, though simple queries often use individual arguments.
The generated code uses database/sql under the hood. It prepares statements, executes them, and scans results into structs. You don't write scanning code. sqlc handles the mapping between SQL types and Go types.
sqlc also generates an interface called Querier. This interface lists all the generated functions. This follows the Go mantra "accept interfaces, return structs." You can pass the Querier interface to your business logic, making it easy to mock the database for tests.
Generated code is boring code. That's the goal.
Realistic usage in an HTTP handler
In a real application, you'll use sqlc inside HTTP handlers or service layers. Here's a handler that fetches a user and returns JSON.
// handler.go
package main
import (
"database/sql"
"encoding/json"
"errors"
"net/http"
"example.com/myapp/db"
)
// UserHandler handles requests for user data
type UserHandler struct {
// Querier interface allows dependency injection for testing
q db.Querier
}
func (h *UserHandler) GetUser(w http.ResponseWriter, r *http.Request) {
// Extract ID from query parameter
id := r.URL.Query().Get("id")
if id == "" {
http.Error(w, "missing id", http.StatusBadRequest)
return
}
// Parse ID to int32 matching the generated function signature
var userID int32
if _, err := fmt.Sscanf(id, "%d", &userID); err != nil {
http.Error(w, "invalid id", http.StatusBadRequest)
return
}
// Call the generated function with request context
user, err := h.q.GetUser(r.Context(), userID)
if err != nil {
// sql.ErrNoRows is returned when :one query finds no matching row
if errors.Is(err, sql.ErrNoRows) {
http.Error(w, "user not found", http.StatusNotFound)
return
}
// Log and return 500 for other database errors
log.Printf("database error: %v", err)
http.Error(w, "internal server error", http.StatusInternalServerError)
return
}
// Return user as JSON
w.Header().Set("Content-Type", "application/json")
json.NewEncoder(w).Encode(user)
}
The handler uses the Querier interface. This makes the handler testable. You can pass a mock implementation of Querier in tests without touching the database. The GetUser call returns sql.ErrNoRows if no user matches the ID. You must check for this error explicitly. sqlc generates the error, but you handle the logic.
Convention aside: if err != nil { return err } is verbose by design. The Go community accepts this boilerplate because it makes the unhappy path visible. Don't hide errors. Check them and decide how to respond.
Handling transactions
sqlc doesn't manage transactions automatically. You start a transaction, pass the transaction object to db.New, and commit or rollback manually. The generated functions work with *sql.Tx just like they work with *sql.DB.
Here's how to update a user within a transaction:
func updateUserEmail(ctx context.Context, q db.Querier, tx *sql.Tx, id int32, newEmail string) error {
// Create a queryer bound to the transaction
txQ := db.New(tx)
// Update the email using the generated function
err := txQ.UpdateUser(ctx, db.UpdateUserParams{
ID: id,
Email: newEmail,
})
if err != nil {
return err
}
// Perform additional operations within the same transaction
// ...
return nil
}
func main() {
conn, _ := sql.Open("postgres", "dsn")
defer conn.Close()
// Begin a transaction
tx, err := conn.BeginTx(context.Background(), nil)
if err != nil {
log.Fatal(err)
}
defer tx.Rollback()
// Run the update logic
err = updateUserEmail(context.Background(), db.New(conn), tx, 1, "new@example.com")
if err != nil {
log.Fatal(err)
}
// Commit the transaction if all operations succeeded
err = tx.Commit()
if err != nil {
log.Fatal(err)
}
}
The db.New(tx) call creates a queryer that executes queries within the transaction. If you commit, all changes persist. If you rollback, all changes are discarded. sqlc generates the UpdateUser function with a Params struct because the query has multiple arguments. The struct fields match the placeholders in the SQL.
Transactions are explicit in Go. sqlc doesn't hide this. You control when data is committed.
Pitfalls and compiler errors
sqlc catches many errors at generation time, but some issues only appear at runtime.
If you reference a column that doesn't exist, sqlc generate fails with column "foo" does not exist. This is good. It stops you from deploying broken code. If you use an ambiguous column name in a join, the generator rejects the query with column reference "id" is ambiguous. You must qualify the column with the table name.
Runtime errors still happen. If a :one query returns no rows, the generated function returns sql.ErrNoRows. You must handle this error. If you don't, your program might crash or return a zero-value struct that looks like valid data.
sqlc doesn't prevent N+1 query problems. If you write a loop that calls a generated function for each item, you get N+1 queries. sqlc generates the code for the bad query. You're responsible for writing efficient SQL. Use joins or batch queries when appropriate.
sqlc doesn't manage migrations. You need a separate tool like golang-migrate or atlas to apply schema changes. sqlc only reads the schema to validate queries. It doesn't modify the database.
The worst goroutine bug is the one that never logs. The same applies to database errors. Log unexpected errors. Return expected errors to the caller. Don't swallow errors silently.
When to use sqlc
Use sqlc when you want type safety for SQL queries without giving up the power of raw SQL. Use an ORM like GORM when you need rapid prototyping and don't mind the abstraction layer and potential performance overhead. Use raw database/sql when you have a tiny project with one or two queries and don't want the build step. Use sqlc with pgx when you need PostgreSQL-specific features like JSONB support or array scanning.
Trust the generator. Write the SQL.