sqlc vs Hand Written database sql Code and the Real Gap
sqlc against hand written database/sql on PostgreSQL: generated code, latency within 5 percent, 11 allocations against 31, and where hand written code wins
Hand written database/sql code is the baseline every Go data library argues with, and the argument usually goes one of two ways. Either "you do not need anything, the standard library is enough", or "the standard library is verbose and error prone, use a tool". Both are right about something. This article puts sqlc, which generates database/sql style code from SQL, next to the hand written version of the same four queries on PostgreSQL 18.6, and measures what the generation changes: speed, allocations, lines you maintain and the mistakes the compiler catches.
Tested with Go 1.25.6, sqlc v1.30.0, pgx v5.11.0 (native and stdlib), PostgreSQL 18.6 in Docker, macOS on Apple Silicon (arm64)
It builds on the sqlc tutorial and database/sql explained. The migration between the two styles is covered in sqlc with database/sql, and the wider tool comparison in GORM vs sqlc vs Ent vs sqlx.
What is being compared
Four operations on accounts and orders: an insert that returns an id, a lookup by unique email, a list of 50 orders for one account, and an account joined with its 50 orders. The hand written version uses database/sql over pgx's stdlib adapter. The sqlc version is the generated package over a pgxpool.
package lab
import (
"context"
"fmt"
"os"
"github.com/jackc/pgx/v5/pgxpool"
)
// Store is the surface every implementation provides.
type Store interface {
CreateAccount(ctx context.Context, email, name string) (int64, error)
AccountByEmail(ctx context.Context, email string) (string, error)
ListOrders(ctx context.Context, accountID int64, limit int) (int, error)
AccountWithOrders(ctx context.Context, accountID int64) (int, error)
}
const base = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"
func dsn(schema string) string { return base + "&search_path=" + schema }
// setup recreates a schema, creates the tables and seeds 200 accounts with 50 orders each.
func setup(ctx context.Context, schema string) error {
pool, err := pgxpool.New(ctx, base)
if err != nil {
return err
}
defer pool.Close()
ddl, err := os.ReadFile("schema.sql")
if err != nil {
return err
}
sql := fmt.Sprintf("drop schema if exists %[1]s cascade; create schema %[1]s; set search_path=%[1]s;", schema) + string(ddl)
if _, err := pool.Exec(ctx, sql); err != nil {
return err
}
_, err = pool.Exec(ctx, fmt.Sprintf(`
set search_path=%s;
insert into accounts (email, name) select 'seed'||g||'@x.io', 'Seed '||g from generate_series(1,200) g;
insert into orders (account_id, total_cents, status)
select a, 100*o, case when o %% 5 = 0 then 'refunded' else 'paid' end
from generate_series(1,200) a, generate_series(1,50) o;
analyze accounts; analyze orders;`, schema))
return err
}package lab
import (
"context"
"database/sql"
_ "github.com/jackc/pgx/v5/stdlib"
)
type sqlStore struct{ db *sql.DB }
func newSQL(schema string) (Store, error) {
db, err := sql.Open("pgx", dsn(schema))
return sqlStore{db}, err
}
func (s sqlStore) CreateAccount(ctx context.Context, email, name string) (int64, error) {
var id int64
err := s.db.QueryRowContext(ctx, `insert into accounts (email, name) values ($1, $2) returning id`, email, name).Scan(&id)
return id, err
}
func (s sqlStore) AccountByEmail(ctx context.Context, email string) (string, error) {
var id int64
var e, name string
var created any
err := s.db.QueryRowContext(ctx, `select id, email, name, created_at from accounts where email = $1`, email).Scan(&id, &e, &name, &created)
return name, err
}
func (s sqlStore) ListOrders(ctx context.Context, accountID int64, limit int) (int, error) {
rows, err := s.db.QueryContext(ctx, `select id, account_id, total_cents, status from orders where account_id = $1 order by id limit $2`, accountID, limit)
if err != nil {
return 0, err
}
defer rows.Close()
n := 0
for rows.Next() {
var id, acc int64
var total int
var status string
if err := rows.Scan(&id, &acc, &total, &status); err != nil {
return 0, err
}
n++
}
return n, rows.Err()
}
func (s sqlStore) AccountWithOrders(ctx context.Context, accountID int64) (int, error) {
rows, err := s.db.QueryContext(ctx, `select a.id, a.name, o.id, o.total_cents from accounts a join orders o on o.account_id = a.id where a.id = $1 order by o.id`, accountID)
if err != nil {
return 0, err
}
defer rows.Close()
n := 0
for rows.Next() {
var aid, oid int64
var name string
var total int
if err := rows.Scan(&aid, &name, &oid, &total); err != nil {
return 0, err
}
n++
}
return n, rows.Err()
}That is 67 lines for four queries, and most of it is the same shape repeated: run the query, scan into locals, check the error, loop over rows, check rows.Err(). It is not hard code. It is code where a forgotten defer rows.Close() or a scan with the columns in the wrong order compiles and fails later.
The sqlc side starts with the same SQL in a file, plus a configuration:
create table accounts (
id bigserial primary key,
email text not null unique,
name text not null,
created_at timestamptz not null default now()
);
create table orders (
id bigserial primary key,
account_id bigint not null references accounts(id),
total_cents int not null,
status text not null
);
create index orders_account_idx on orders (account_id);-- name: CreateAccount :one
insert into accounts (email, name) values ($1, $2) returning id;
-- name: AccountByEmail :one
select id, email, name, created_at from accounts where email = $1;
-- name: ListOrders :many
select id, account_id, total_cents, status from orders where account_id = $1 order by id limit $2;
-- name: AccountWithOrders :many
select a.id as account_id, a.name, o.id as order_id, o.total_cents
from accounts a join orders o on o.account_id = a.id
where a.id = $1 order by o.id;version: "2"
sql:
- engine: postgresql
schema: schema.sql
queries: query.sql
gen:
go:
package: db
out: db
sql_package: pgx/v5and ends with the adapter that implements the benchmark's interface over the generated methods:
package lab
import (
"context"
"example.test/lab/db"
"github.com/jackc/pgx/v5/pgxpool"
)
type sqlcStore struct{ q *db.Queries }
func newSQLC(schema string) (Store, error) {
pool, err := pgxpool.New(context.Background(), dsn(schema))
if err != nil {
return nil, err
}
return sqlcStore{db.New(pool)}, nil
}
func (s sqlcStore) CreateAccount(ctx context.Context, email, name string) (int64, error) {
return s.q.CreateAccount(ctx, db.CreateAccountParams{Email: email, Name: name})
}
func (s sqlcStore) AccountByEmail(ctx context.Context, email string) (string, error) {
a, err := s.q.AccountByEmail(ctx, email)
return a.Name, err
}
func (s sqlcStore) ListOrders(ctx context.Context, accountID int64, limit int) (int, error) {
os, err := s.q.ListOrders(ctx, db.ListOrdersParams{AccountID: accountID, Limit: int32(limit)})
return len(os), err
}
func (s sqlcStore) AccountWithOrders(ctx context.Context, accountID int64) (int, error) {
rows, err := s.q.AccountWithOrders(ctx, accountID)
return len(rows), err
}package lab
import (
"context"
"fmt"
"sync/atomic"
"testing"
)
var impls = []struct {
name string
make func(schema string) (Store, error)
}{
{"database/sql", newSQL},
{"sqlc", newSQLC},
}
var counter atomic.Int64
func each(b *testing.B, body func(b *testing.B, s Store)) {
ctx := context.Background()
if err := setup(ctx, "lab_cmp"); err != nil {
b.Fatal(err)
}
for _, im := range impls {
s, err := im.make("lab_cmp")
if err != nil {
b.Fatal(err)
}
b.Run(im.name, func(b *testing.B) {
b.ReportAllocs()
body(b, s)
})
}
}
func BenchmarkCreate(b *testing.B) {
each(b, func(b *testing.B, s Store) {
for i := 0; i < b.N; i++ {
if _, err := s.CreateAccount(context.Background(), fmt.Sprintf("n%d@x.io", counter.Add(1)), "N"); err != nil {
b.Fatal(err)
}
}
})
}
func BenchmarkGetByEmail(b *testing.B) {
each(b, func(b *testing.B, s Store) {
for i := 0; i < b.N; i++ {
if _, err := s.AccountByEmail(context.Background(), "[email protected]"); err != nil {
b.Fatal(err)
}
}
})
}
func BenchmarkListOrders(b *testing.B) {
each(b, func(b *testing.B, s Store) {
for i := 0; i < b.N; i++ {
if n, err := s.ListOrders(context.Background(), 7, 50); err != nil || n != 50 {
b.Fatal(n, err)
}
}
})
}
func BenchmarkAccountWithOrders(b *testing.B) {
each(b, func(b *testing.B, s Store) {
for i := 0; i < b.N; i++ {
if n, err := s.AccountWithOrders(context.Background(), 7); err != nil || n != 50 {
b.Fatal(n, err)
}
}
})
}set -e
~/go/bin/sqlc generate
go mod tidy 2>&1 | tail -3
go test -run=^$ -bench=. -benchtime=2s -count=1 -benchmem . 2>&1 | grep -E "^(Benchmark|ok|FAIL|---|[[:space:]]+bench)" | sed -E "s/-[0-9]+ / /"BenchmarkCreate/database/sql 5293 442159 ns/op 872 B/op 19 allocs/op
BenchmarkCreate/sqlc 4628 438401 ns/op 508 B/op 11 allocs/op
BenchmarkGetByEmail/database/sql 10719 210339 ns/op 1184 B/op 31 allocs/op
BenchmarkGetByEmail/sqlc 10000 221832 ns/op 692 B/op 11 allocs/op
BenchmarkListOrders/database/sql 2800 983298 ns/op 5392 B/op 416 allocs/op
BenchmarkListOrders/sqlc 2533 965208 ns/op 12156 B/op 162 allocs/op
BenchmarkAccountWithOrders/database/sql 7578 322107 ns/op 5360 B/op 416 allocs/op
BenchmarkAccountWithOrders/sqlc 7227 301402 ns/op 12291 B/op 162 allocs/op
ok example.test/lab 23.970sWhat sqlc wrote
The generated query.sql.go is 114 lines for the four queries; with db.go (the DBTX interface and New) and models.go (one struct per table), the package is 169 lines. A representative method:
func (q *Queries) AccountByEmail(ctx context.Context, email string) (Account, error) {
row := q.db.QueryRow(ctx, accountByEmail, email)
var i Account
err := row.Scan(
&i.ID,
&i.Email,
&i.Name,
&i.CreatedAt,
)
return i, err
}That is the function you would write. The SQL lives in a constant, the parameters are typed (email string), the result is a named struct, and the columns scanned are the columns selected, in order, because the tool read them from the query. For the list query the generated code opens the rows, defers Close, scans into a []Order and checks rows.Err(), as a careful developer would, every time. The value of generation is that "every time" is guaranteed.
Parameter and row types come from the schema. The join returns an AccountWithOrdersRow with an int32 for total_cents because the column is a PostgreSQL integer. If someone changes the column to bigint in a migration, the next generation changes the field to int64 and the compiler flags every caller that assumed 32 bits.
Speed
| Operation (µs) | database/sql | sqlc |
|---|---|---|
| Insert | 442 | 438 |
| Lookup by email | 210 | 222 |
| List 50 orders | 983 | 965 |
| Account with 50 orders | 322 | 301 |
The two are within about 5 percent in both directions, which is run to run noise on one machine. The generated code adds no layer. It calls the pgx pool, scans, and returns. A claim that sqlc costs anything measurable over hand written code is wrong, and a claim that it is faster than hand written code is also wrong in any way that matters: what differs is the driver path, below.
Allocations
| Allocations per call | database/sql | sqlc |
|---|---|---|
| Insert | 19 | 11 |
| Lookup | 31 | 11 |
| List 50 | 416 | 162 |
| Account with 50 orders | 416 | 162 |
sqlc made fewer allocations, by a factor of two to three. The reason is not the generated code. It is the driver interface the code uses. database/sql converts each column to a driver.Value and then to the destination type; the generated pgx code scans straight into the typed destination. A hand written program that used pgx natively would likely land near sqlc's numbers, which I did not measure. The lab compares against the stdlib path because that is what most hand written Go code uses.
A caution about these numbers: the hand written baseline here counts rows and discards them, and the sqlc version returns a slice of structs. That is why sqlc allocated more bytes for the 50 row list (12156 B against 5392 B) while making fewer allocations: it kept the result. Reading the table as "sqlc does the same work with fewer allocations" would be too generous. Reading it as "the driver path and the generated scan code are cheap" is right.
Mistakes the compiler catches
What generation buys is correctness work moved from run time to build time, and the comparison to hand written code is clearest there:
- A column name typo in hand written SQL is a run time error on the first call. In sqlc it fails
generatewith the file, line and column of the typo (the same experiment, with a rename). - A scan order mismatch (
Scan(&name, &email)forselect email, name) type checks if both are strings and swaps the data silently. The generated scan follows the select list. - A wrong parameter count or type compiles in hand written code (
QueryRow(q, a, b)for one placeholder) and fails when it runs. The generated method has typed parameters and the right number. - A forgotten
rows.Close()orrows.Err()leaks a connection or hides a mid stream error. Generated code does both. - A schema change that drops a column used by a query fails the next generation. Hand written SQL fails in production.
Hand written code can reach the same safety with a test for every query against a real database. Generation makes the check part of the build and removes the need to remember it.
Where hand written code is better
- Dynamic SQL. A search with optional filters, a variable
INlist, a column chosen by the user. Generated code is a fixed set of statements. sqlc has patterns for optional filters (dynamic queries), and a statement assembled at run time is still easier in hand written code or with a builder (dynamic SQL builders). - A handful of queries. For three queries, the tool, the config, the generation step and the CI check cost more than 30 lines of scanning code.
- Unusual types and drivers. Where the schema uses types sqlc does not map, or you need a driver feature the generator does not expose, hand written code gives full control. Overrides cover many cases (sqlc overrides).
- Anything that is not SQL the generator can parse. Rare extensions and exotic syntax can trip the parser. Check your schema against the tool in an hour, before you commit to it.
A middle path
Keep both. A service often has 40 ordinary queries that sqlc handles well and 3 dynamic ones that do not. Put the 40 in query.sql, write the 3 by hand over the same DBTX (the generated Queries accepts the pool or a transaction, so hand written code can use the same connection and the same transaction). The two live together without friction, which is also what makes adopting sqlc an incremental step.
Checklist
- Do not expect speed from generation. Expect fewer mistakes and less boilerplate.
- Use the pgx native interface, not the stdlib adapter, if allocations matter.
- Compare like with like: a baseline that discards rows is not doing the same work.
- Keep dynamic queries hand written or built, and everything else generated.
- Run generation and a diff check in CI so the generated code never drifts from the SQL.
The tool is documented at docs.sqlc.dev, and the driver interface at pkg.go.dev for database/sql.
