The manual mapping trap
You are writing a Go service that talks to a database. You use the standard database/sql package. It works, but every query feels like manual labor. You write SELECT name, age FROM users. Then you write code to scan each column into variables. You add a new column to the table. Now you have to update the scan code. You miss one spot. The code compiles. The server starts. The request hits. The value comes back empty. You spend an hour debugging a missing column mapping.
You want to keep writing real SQL, but you want the compiler to help you catch mismatches before the request arrives. You don't want an ORM that hides queries behind method chains. You want a tool that respects your SQL while removing the boilerplate. sqlx fills that gap. It sits on top of database/sql and adds struct scanning, named queries, and extensions that make the glue code disappear.
Struct scanning and named queries
Think of database/sql as a reliable but low-level tool. It gives you rows and columns as generic values. It's up to you to decide which value goes where. sqlx is a wrapper that adds structure. It knows how to look at a Go struct, read its fields, and match them to database columns. If the names match, it fills the struct automatically. If you use tags, it follows the tags.
sqlx also lets you write queries with named parameters like :name instead of ? or $1. This makes the SQL readable and less prone to argument-order bugs. sqlx is not an ORM. You still write SQL. You still control the queries. You just get less glue code. The library uses reflection to inspect your structs at runtime. It caches the reflection data, so the overhead is paid once per query type. For most applications, the performance cost is negligible compared to the network latency of the database call.
Convention aside: sqlx embeds *sql.DB. This means you can pass an sqlx.DB to a function that expects *sql.DB. The embedding preserves the interface. This makes sqlx easy to drop into existing codebases without rewriting every dependency.
Fetching rows into structs
Here's the simplest way to fetch rows into a struct using sqlx.
package main
import (
"log"
"github.com/jmoiron/sqlx"
)
// Person maps to the person table.
// db tags handle column name mismatches.
type Person struct {
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
Email string
}
func main() {
// Connect creates a pool. Panics on error for brevity.
db := sqlx.MustConnect("postgres", "user=foo dbname=bar sslmode=disable")
var people []Person
// Select scans rows directly into the slice.
// Pass the address so sqlx can grow the slice.
err := db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")
if err != nil {
log.Fatal(err)
}
}
Struct scanning saves keystrokes. Verify your tags match your schema.
How scanning works under the hood
When you call db.Select, sqlx prepares the statement if needed, executes it, and iterates over the rows. For each row, it creates a new Person struct. It looks at the struct fields. It checks for db tags. If a tag exists, it uses that name to find the column. If no tag, it uses the field name. It matches the column index to the field. It converts the database type to the Go type. If the conversion fails, it returns an error. If everything succeeds, it appends the struct to the slice. The slice grows dynamically. You get back a populated slice without writing a single row.Scan call.
Convention aside: db tags are the standard for sqlx. If you use json tags, sqlx won't see them. Stick to db for database mapping. You can use both if needed, but db is the contract here. The tag format is db:"column_name". If the struct field name matches the column name exactly, you can omit the tag. Email string matches a column named email. FirstName string does not match first_name, so the tag is required.
Reflection is cached. The cost is paid once per query type.
Realistic usage with context
Real code needs context for cancellation, named parameters for safety, and proper error handling. Here's a handler that fetches a user by ID.
// GetUser retrieves a person by ID using a named query.
// Context allows the caller to cancel the request.
func GetUser(ctx context.Context, db *sqlx.DB, id int) (*Person, error) {
// NamedQuery uses :id which maps to the ID field.
// This avoids positional argument errors.
query := `SELECT first_name, last_name, email FROM person WHERE id = :id`
var p Person
// GetContext respects the context deadline.
// It scans exactly one row into the struct.
err := db.GetContext(ctx, &p, query, sqlx.Named("id", id))
if err != nil {
return nil, fmt.Errorf("get user %d: %w", id, err)
}
return &p, nil
}
In production, you pass context.Context as the first argument to every function that touches I/O. This lets the caller cancel the query if the HTTP request is aborted. sqlx provides GetContext and SelectContext for this. Use named parameters with sqlx.Named. The query uses :id. The helper matches the key to the value. This is safer than ? placeholders where you have to count arguments. The function returns a pointer to the struct. Returning a pointer avoids copying the struct if it's large, though for small structs a value is fine. The error is wrapped with fmt.Errorf and %w so the caller can unwrap it later.
Convention aside: Go error handling is verbose by design. The if err != nil block makes the failure path visible. Don't try to hide it. sqlx returns errors just like the standard library. Handle them or return them. The community accepts the boilerplate because it makes the unhappy path explicit.
Context is plumbing. Pass it through every call that touches the database.
Dynamic queries and batch operations
Sometimes you have a list of IDs. You need WHERE id IN (1, 2, 3). Writing this manually is messy. You have to build the placeholder string and the argument list. sqlx.In handles this safely. It generates the placeholders and the arguments. You must rebind the query for the driver.
// FindUsersByIDs fetches users using a dynamic IN clause.
// sqlx.In builds the placeholders and arguments safely.
func FindUsersByIDs(db *sqlx.DB, ids []int) ([]Person, error) {
// In generates "WHERE id IN (?, ?, ?)" and the argument list.
// It handles empty slices gracefully.
query, args, err := sqlx.In("SELECT * FROM person WHERE id IN (?)", ids)
if err != nil {
return nil, err
}
// Rebind converts placeholders to the driver's format.
// Postgres uses $1, MySQL uses ?.
query = db.Rebind(query)
var people []Person
err = db.Select(&people, query, args...)
if err != nil {
return nil, err
}
return people, nil
}
sqlx.In takes the query with a single ? for the list and the slice. It expands the ? into multiple placeholders. It returns the new query and a slice of arguments. You spread the arguments with args.... The Rebind call is critical. sqlx.In uses ? as a generic placeholder. The database driver expects its own format. Postgres wants $1, $2. MySQL wants ?. Rebind translates the query to the driver's syntax. Without rebinding, the query fails with a syntax error.
Convention aside: Don't pass *string for non-null reasons. Strings are cheap to pass by value. Use *string only when the field can be null. If a column is nullable, you must scan into sql.NullString or a pointer *string. sqlx does not auto-convert nulls to pointers. You still need the right type. If you scan a null into a string, you get an error.
sqlx.In handles the syntax. Rebind handles the driver.
Pitfalls and error handling
sqlx adds safety, but it doesn't remove all traps. If you scan a row into a struct and a column is missing, sqlx ignores the field. It does not error on missing columns by default. This can hide bugs where the database schema changed but the struct didn't. If you add a column to the table and forget to add the field to the struct, the code still works. The new data is just discarded. Test your schema migrations. Write tests that verify the struct matches the table.
If you pass a value instead of a pointer to Select, the compiler rejects the program with cannot use people (variable of type []Person) as *[]Person value in argument. sqlx needs the address to append to the slice. If you use Get and the query returns zero rows, sqlx returns sql.ErrNoRows. You must check for this error explicitly. The compiler won't catch it. If you forget to import the driver, you get sql: unknown driver "postgres". The driver must be imported, usually via a blank import like _ "github.com/lib/pq". sqlx relies on the standard driver registration.
Convention aside: Run gofmt on your code. The formatting is standardized. Don't argue about indentation. Most editors run it on save. Trust gofmt. Argue logic, not formatting.
Missing columns don't panic. Test your schema migrations.
When to use sqlx
Use database/sql when you need zero dependencies and are writing simple scripts or benchmarks. Use sqlx when you want struct scanning and named queries without giving up control over SQL. Use an ORM like GORM or Ent when you have complex relationships and want the framework to generate queries for you. Use sqlc when you want type safety generated from SQL files and don't want runtime reflection overhead. Use sqlx extensions when you need to bind parameters to complex types or run batch operations with sqlx.In.
Keep SQL visible. Wrap it, don't hide it.