sqlc: type-safe SQL in Go — patterns, pitfalls and fixes

The case for type-safe SQL

After weeks of building APIs in Go, you've likely felt the friction: either you're writing raw SQL strings and hoping the column names don't drift, or you're fighting an ORM's abstraction layers. sqlc: type-safe SQL in Go — patterns, pitfalls and fixes offers a different path. Instead of choosing between unsafe SQL and heavyweight ORMs, sqlc generates type-safe Go code directly from your SQL queries, giving you compile-time verification without the magic.

On day 27 of production API work, this matters. You're shipping features fast, refactoring schemas, and you need confidence that your queries still work. sqlc delivers exactly that: queries live in .sql files, generated structs and methods emerge automatically, and compile-time checking of SQL prevents entire categories of runtime bugs.

Queries live in .sql files

With sqlc, your SQL is first-class. You write real SQL—not string builders, not query objects, not escaped strings—and commit it to version control. Each .sql file becomes the source of truth for one or more related queries.

Create a queries.sql file in your project:

-- queries.sql
-- name: GetUserByID :one
SELECT id, email, created_at FROM users WHERE id = $1;

-- name: ListUsersByDomain :many
SELECT id, email, created_at FROM users WHERE email LIKE $1 ORDER BY created_at DESC;

-- name: CreateUser :exec
INSERT INTO users (email, created_at) VALUES ($1, $2);

Each query is annotated with a comment directive: the function name it will generate, and the result mode (:one for single row, :many for multiple rows, :exec for write operations, :execlast for last insert ID). This metadata tells sqlc exactly what Go signature to generate.

The real win: your SQL is readable, portable, and testable independently. You can run these queries in your database client, profile them, add indexes based on explain plans—all without touching Go. When a new team member joins, they can understand the queries immediately.

Generated structs and methods

Run sqlc generate and watch it produce type-safe Go code. For the queries above, sqlc generates:

// queries.sql.go (auto-generated)

type User struct {
	ID        int32
	Email     string
	CreatedAt time.Time
}

func (q *Queries) GetUserByID(ctx context.Context, id int32) (User, error) {
	// implementation
}

func (q *Queries) ListUsersByDomain(ctx context.Context, domain string) ([]User, error) {
	// implementation
}

func (q *Queries) CreateUser(ctx context.Context, email string, createdAt time.Time) error {
	// implementation
}

Notice the types: int32, time.Time`, string. sqlc reads your schema and infers Go types from SQL column types. This mapping is configurable—you can override type mappings in sqlc.yaml if your project prefers custom types for IDs or times.

The generated Queries struct is instantiated with a database connection pool, and all methods accept a context. No hidden connection management, no magical transaction handling. Your code controls the lifecycle.

db := pgx.Connect(ctx, dbURL)
q := New(db)
user, err := q.GetUserByID(ctx, 123)
if err != nil {
	// handle error
}
fmt.Println(user.Email)

The method signatures are typed: you pass Go values, you get Go values back. No interface{}, no reflection. The compiler checks every call.

Compile-time checking of SQL

Here's where sqlc truly shines in a production codebase. When you modify your schema or rename a column, sqlc's validation catches the breakage before you deploy. Run sqlc generate, and if a query references a non-existent column, sqlc fails:

-- ERROR in queries.sql:5: column "username" does not exist
SELECT id, username FROM users WHERE id = $1;

You fix the column name, regenerate, and deploy with confidence. This is not a runtime assertion—the Go compiler itself verifies that the generated code matches your API contract.

Additionally, sqlc validates parameter counts. If you write WHERE id = $1 AND status = $2 but only pass one argument, sqlc catches it. The generated function signature enforces the exact parameters your SQL needs.

This compile-time safety propagates through your entire codebase. A refactoring tool can rename a Go function, and if the corresponding SQL changes, sqlc generate alerts you immediately.

No ORM magic

sqlc deliberately avoids ORM abstractions. It does not try to hide SQL behind a DSL, it does not manage associations implicitly, it does not generate N+1-prone queries under the hood. Instead, it trusts you to write efficient SQL and generates the minimal code to execute it safely.

If your query needs a JOIN, you write the JOIN:

-- name: GetUserWithProfile :one
SELECT u.id, u.email, p.bio, p.avatar_url FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
WHERE u.id = $1;

sqlc generates a struct that holds all the columns:

type GetUserWithProfileRow struct {
	ID        int32
	Email     string
	Bio       sql.NullString
	AvatarUrl sql.NullString
}

You handle the NULL-ability explicitly. No hidden lazy loading, no surprise queries triggered by accessing a field. What you see in your SQL is what executes.

This transparency means your data access layer is predictable, testable, and debuggable. Profile a slow endpoint? Look at the .sql file, examine the execution plan, add an index. The path is direct.

Common mistakes

Forgetting to commit generated code. Some teams treat generated code as a temporary build artifact and don't version-control it. This breaks CI/CD and makes code review harder. Commit generated files; they're part of your reproducible build.

Mixing NULL and non-NULL columns carelessly. sqlc uses sql.NullString, sql.NullInt32, etc. for nullable columns. If you forget to use these in an optional column, sqlc will error. Read the error message carefully—it's telling you the schema reality.

Regenerating without versioning the schema. Your schema migration history must be clear. If two developers generate code from different schema states, conflicts arise. Use a migration tool (like Flyway or migrate) and ensure all developers are on the same version before running sqlc generate.

Overcomplicating queries to avoid multiple round-trips. sqlc makes it easy to write complex queries with many JOINs. Resist the urge. If you can split a query into two simpler, cacheable queries, do it. A little extra latency beats a query so complex that only you can understand it.

Ignoring the sqlc.yaml configuration. By default, sqlc looks for a schema and queries in specific directories. If your project layout differs, or if you use custom types, update sqlc.yaml. A misconfigured generation step wastes time.

Key takeaways

• sqlc reads .sql files and generates type-safe Go functions; your SQL is the source of truth and remains readable and portable.
• Generated structs and methods are typed end-to-end; no interface{}, no reflection, full compiler validation.
• Compile-time checking catches schema mismatches, missing columns, and parameter count errors before deployment.
• sqlc avoids ORM magic; you write SQL, you control queries, and you own the performance profile.
• Commit generated code, keep your schema version consistent across the team, and use sqlc.yaml to match your project layout.
• sqlc scales from simple CRUD to complex reporting queries without adding abstraction layers.