How to use sqlx
You have a Go struct representing a user. You have a SQL query to fetch that user. Now you need to bridge the gap. The standard library's database/sql package forces you to manually map every column to every field. You write a loop. You call rows.Scan with arguments in the exact order the database returns them. Change the query order and your code breaks. Add a column and your code panics. sqlx exists to remove that manual mapping. It reads your struct tags and fills the fields automatically.
The concept
Think of database/sql as a raw pipe. Data flows out as a stream of values. You have to catch each value and decide where it belongs. sqlx is a wrapper that adds intelligence to that pipe. It looks at your Go struct, reads the tags you attached to the fields, and matches them to the column names in the result set. You define the shape of the data once in your struct, and sqlx handles the rest.
The library uses reflection to inspect your struct at runtime. It matches column names to struct fields based on db tags. If a column has no matching tag, sqlx ignores it. If a struct field has no matching column, it stays at its zero value. This decouples your Go code from the exact column order in your SQL.
Minimal example
Here's the simplest way to fetch a row into a struct. You define the struct with db tags, connect to the database, and call db.Get.
package main
import (
"log"
"github.com/jmoiron/sqlx"
)
// Person maps to the person table in the database.
// The db tags tell sqlx which column matches which field.
type Person struct {
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
}
func main() {
// Connect opens a connection pool. sqlx wraps database/sql.
db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
if err != nil {
log.Fatalln(err)
}
var p Person
// Get fetches a single row and scans it directly into the struct.
// No manual rows.Next or rows.Scan loops needed.
err = db.Get(&p, "SELECT first_name, last_name FROM person WHERE first_name=$1", "Jason")
if err != nil {
log.Fatalln(err)
}
log.Println(p.FirstName)
}
What happens under the hood
When you call sqlx.Connect, it creates a connection pool under the hood. This pool manages multiple connections to the database so your program doesn't block waiting for a single connection. The pool is shared across all queries.
When you call db.Get, the library executes the query. It receives the result set and inspects the column names. It then looks at your Person struct, reads the db tags, and matches the columns to the fields. If a match is found, the value is assigned. If the query returns multiple rows, db.Get uses the first one and discards the rest.
sqlx does not generate code at compile time. The reflection happens at runtime. The performance cost is negligible for most applications. The overhead is tiny compared to the network latency of the database query.
Realistic example
In a real application, you need context for cancellation, error wrapping, and proper HTTP handling. Here's how a handler looks with sqlx.
package main
import (
"context"
"fmt"
"net/http"
"github.com/jmoiron/sqlx"
)
// Person represents a row in the person table.
// Fields must be exported (capitalized) for reflection to work.
type Person struct {
ID int `db:"id"`
FirstName string `db:"first_name"`
LastName string `db:"last_name"`
}
// GetUserByID fetches a single user.
// Context is always the first parameter in Go functions that do I/O.
func GetUserByID(ctx context.Context, db *sqlx.DB, id int) (*Person, error) {
var p Person
// GetContext respects deadlines and cancellation signals.
// This prevents the goroutine from hanging if the client disconnects.
err := db.GetContext(ctx, &p, "SELECT * FROM person WHERE id=$1", id)
if err != nil {
// Wrap the error to add context.
// The %w verb allows callers to unwrap the original error later.
return nil, fmt.Errorf("fetch user %d: %w", id, err)
}
return &p, nil
}
func handler(w http.ResponseWriter, r *http.Request) {
// Pass the request context to the database call.
// This links the database query to the HTTP request lifecycle.
user, err := GetUserByID(r.Context(), db, 1)
if err != nil {
http.Error(w, err.Error(), http.StatusInternalServerError)
return
}
fmt.Fprintln(w, user.FirstName)
}
Context is plumbing. Run it through every long-lived call site.
Named queries
Positional arguments like $1 or ? are error-prone. If you add a column to your query, you have to update the argument list in the exact same order. sqlx supports named queries using placeholders that match struct field names.
// Named queries use placeholders that match struct field names.
// This prevents argument order errors and makes the query self-documenting.
query := `
INSERT INTO person (first_name, last_name)
VALUES (:first_name, :last_name)
`
// NamedExec expands the named placeholders into positional arguments.
// It matches the struct fields to the query names automatically.
result, err := db.NamedExec(query, p)
if err != nil {
log.Fatalln(err)
}
Named queries save you from index errors. Use them for inserts and updates.
Pitfalls and errors
Unexported fields. If your struct fields start with a lowercase letter, sqlx cannot read them. Go's reflection package respects visibility rules. Export the fields with a capital letter, or sqlx will silently ignore them. The compiler won't catch this. Your data will just be empty.
No rows. db.Get returns sql.ErrNoRows if the query returns zero rows. You must check for this explicitly. The compiler doesn't know your query might return nothing.
if errors.Is(err, sql.ErrNoRows) {
// Handle the missing user case.
return nil, fmt.Errorf("user %d not found", id)
}
Connection pooling. sqlx inherits the connection pool from database/sql. You can configure it using the underlying *sql.DB object. Call db.DB() to unwrap the standard library object.
// Unwrap to access standard library pool settings.
db.DB().SetMaxOpenConns(10)
db.DB().SetMaxIdleConns(5)
Column name mismatch. sqlx matches column names to struct tags. If you rename a column in SQL but forget the tag, sqlx might map it wrong or ignore it. Use explicit db tags to lock the mapping. Don't rely on case-insensitive matching in production.
The worst goroutine bug is the one that never logs. Check your errors.
Decision matrix
Use database/sql when you need zero external dependencies and want full control over how rows are scanned. Use sqlx when you want automatic struct scanning and named queries without the overhead of a full ORM. Use an ORM like GORM when your application relies heavily on complex relationships and you prefer code generation over raw SQL. Use sqlc when you want type safety at compile time by generating Go code from your SQL files.