sqlx in Go and What It Adds on Top of database sql

database/sql is deliberately small. Reading a row into a struct takes a Scan call with one pointer per column, a loop over Rows, a defer rows.Close() and a check of rows.Err(). That is a lot of text for "give me the user". sqlx removes the repetition without hiding the SQL or replacing the types underneath. This article runs its main features on Go 1.25.6 and says what it does not do, so you can decide how much of it to adopt.

What sqlx is

sqlx is a thin layer over database/sql. Its *sqlx.DB, *sqlx.Tx and *sqlx.Stmt embed the standard types, so everything the standard ones can do still works, and they add methods. You can pass a *sqlx.DB wherever *sql.DB is wanted by using its embedded field. Adoption is gradual: wrap your existing *sql.DB with sqlx.NewDb(db, "pgx") and start using Get and Select in new code.

The package is mature and in maintenance mode, with a stable API. It does not generate or build SQL, add an ORM or manage migrations.

The feature tour

package main

import (
	"fmt"
	"log"

	"github.com/jmoiron/sqlx"
	_ "modernc.org/sqlite"
)

type User struct {
	ID    int    `db:"id"`
	Name  string `db:"name"`
	Email string `db:"email"`
}

func main() {
	db, err := sqlx.Connect("sqlite", "file:lab592?mode=memory&cache=shared")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.MustExec(`create table users (id int primary key, name text, email text)`)

	// NamedExec: placeholders named after struct fields or map keys.
	_, err = db.NamedExec(`insert into users (id, name, email) values (:id, :name, :email)`,
		[]User{{1, "ada", "[email protected]"}, {2, "grace", "[email protected]"}})
	fmt.Println("NamedExec of a slice:", err)

	// Get: exactly one row into a struct. Select: many rows into a slice.
	var u User
	fmt.Println("Get:", db.Get(&u, `select * from users where id = ?`, 1), u)
	var all []User
	fmt.Println("Select:", db.Select(&all, `select * from users order by id`), all)

	// Get with no rows returns sql.ErrNoRows, like QueryRow.
	fmt.Println("Get with no match:", db.Get(&u, `select * from users where id = ?`, 99))

	// In expands a slice argument into placeholders.
	q, args, _ := sqlx.In(`select name from users where id in (?)`, []int{1, 2})
	var names []string
	_ = db.Select(&names, db.Rebind(q), args...)
	fmt.Println("In:", q, args, "->", names)

	// A column with no destination field is an error: the safety net.
	type Partial struct {
		Name string `db:"name"`
	}
	var p Partial
	fmt.Println("missing destination:", db.Get(&p, `select name, email from users where id = 1`))

	// The same with a single-column scan into a basic type.
	var count int
	fmt.Println("scalar:", db.Get(&count, `select count(*) from users`), count)

	// Transactions: MustBegin and Commit as in database/sql.
	tx := db.MustBegin()
	tx.MustExec(`update users set name = 'ADA' where id = 1`)
	_ = tx.Rollback()
	_ = db.Get(&u, `select * from users where id = 1`)
	fmt.Println("after Rollback:", u.Name)
}

go run . 2>&1

NamedExec of a slice: <nil>
Get: <nil> {1 ada [email protected]}
Select: <nil> [{1 ada [email protected]} {2 grace [email protected]}]
Get with no match: sql: no rows in result set
In: select name from users where id in (?, ?) [1 2] -> [ada grace]
missing destination: missing destination name email in *main.Partial
scalar: <nil> 2
after Rollback: ada

Struct mapping by tag. The User struct has db:"id", db:"name" and db:"email" tags. sqlx matches result columns to fields by the tag, or by the lower-cased field name when there is no tag. Embedded structs are supported, which helps with shared columns such as timestamps.

Get reads exactly one row. db.Get(&u, "select * from users where id = ?", 1) fills u with {1 ada [email protected]}. If nothing matches, the error is sql: no rows in result set, the same sql.ErrNoRows as QueryRow().Scan, so errors.Is(err, sql.ErrNoRows) works. If several rows match, Get takes the first.

Select reads many rows into a slice. db.Select(&all, "select * from users order by id") filled a slice with both users. It opens the rows, scans, closes them and checks the iteration error, which is the whole loop from the rows article in one call. For a million rows, remember it loads everything into memory; use Queryx and StructScan to stream.

Scalar targets. db.Get(&count, "select count(*) from users") scans a single column into a basic type. Select into a []string works the same way for one column. Both avoid a one-field struct.

NamedExec and named queries. db.NamedExec("insert into users (id, name, email) values (:id, :name, :email)", users) took a slice of two structs and inserted both. Names after the colon come from the db tags, or from the keys of a map[string]any. The lab passes a slice of two structs and both rows are inserted. NamedQuery and NamedStmt are the read and prepared versions.

In expands slices. SQL has no placeholder for a list, and database/sql will not expand a slice argument. sqlx.In("select name from users where id in (?)", []int{1, 2}) returns select name from users where id in (?, ?) and the arguments [1 2]. The program then calls db.Rebind(query) to convert ? to the database's placeholder style ($1, $2 for PostgreSQL), which In does not do for you. Forgetting Rebind on PostgreSQL is a common error.

A strict missing-destination check. The program selects two columns, name and email, into a struct that only has Name. The result is:

missing destination name email in *main.Partial

sqlx refuses to ignore a column it cannot place. That catches a typo in a tag or a field you forgot to add, and the error message names the column. The escape hatch is db.Unsafe(), which returns a handle that ignores unknown columns; use it deliberately, for instance when select * returns columns you do not need.

Transactions and MustExec. db.MustBegin() and tx.MustExec(...) panic on error instead of returning one, which is convenient in scripts, tests and migrations and wrong in a request handler. The lab begins a transaction, updates a name, rolls back, and reads ada again. In service code, use BeginTxx and the error-returning forms and keep the defer tx.Rollback() pattern from the transaction article.

Before and after

The plain database/sql version of "load a user" is this:

var u User
err := db.QueryRowContext(ctx, `select id, name, email from users where id = $1`, id).
	Scan(&u.ID, &u.Name, &u.Email)

For one row that is fine. For a list it grows:

rows, err := db.QueryContext(ctx, `select id, name, email from users order by id`)
if err != nil {
	return nil, err
}
defer rows.Close()
var out []User
for rows.Next() {
	var u User
	if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
		return nil, err
	}
	out = append(out, u)
}
return out, rows.Err()

With sqlx the same function is two lines: var out []User; return out, db.SelectContext(ctx, &out, query). The sqlx methods have Context variants (GetContext, SelectContext, NamedExecContext) and you should use those everywhere, for the reasons in query timeouts. The saving is not only typing. Every hand-written loop is a chance to forget the defer, the rows.Err() check or the order of columns, and Select removes all three.

Streaming large results

Select reads the whole result set into a slice. When the result is large, iterate instead:

rows, err := db.QueryxContext(ctx, query)
defer rows.Close()
for rows.Next() {
	var u User
	if err := rows.StructScan(&u); err != nil { ... }
	process(u)
}
return rows.Err()

StructScan maps the current row onto a struct with the same tags, and the memory stays flat. The same rules about closing and checking Err apply, since *sqlx.Rows embeds *sql.Rows. For results that are huge, consider pushing the work into SQL, or paging with keyset pagination, before streaming a million rows through Go.

Embedded structs and prefixes

Structs often share columns: ID, CreatedAt, UpdatedAt. Define them once as an embedded struct with tags, and sqlx maps the embedded fields as if they were part of the outer struct. For joins, give the columns distinct aliases and tag the fields to match: select u.id as "user.id", o.id as "order.id" ... with a field of type Order whose tag is the word order maps names with dots into nested structs. This is the sqlx way of scanning a join into a nested struct, and it works for one-to-one relations. A one-to-many join produces repeated parent rows and needs grouping, as shown in the join article.

Prepared and named statements

db.Preparex returns a *sqlx.Stmt with Get and Select methods, and db.PrepareNamed returns a *sqlx.NamedStmt for named queries. Prepared statements are bound per connection, and whether they help is measured in the prepared statements article: usually not much for simple queries.

Common errors

Testing code that uses sqlx

Because sqlx wraps real database/sql connections, the testing options are the same: an in-memory SQLite database for portable SQL, a container for PostgreSQL features, and transaction rollback for isolation, as in rollback tests. Accept an interface in your repository, sqlx.ExtContext for functions that can run on a *sqlx.DB or a *sqlx.Tx, so a test can pass a transaction. The interface includes GetContext, SelectContext and ExecContext, which covers most repositories.

Adopting it in a code base

Start where the pain is. Find the repository functions with the longest Scan lists and convert them to Get and Select. Keep the SQL as it is; only the scanning changes. Add the db tags to the structs as you go, and run the tests. Each conversion is small and reversible. A few things to settle as a team:

1. Tag convention. Snake case column names with explicit tags on every field, and no reliance on the default lower-casing.
2. Where structs live. Database row structs belong in the data access package. Map them to domain types at the boundary, so a column rename does not leak into the API.
3. select *. Ban it in production code. The missing-destination check makes it brittle, and it moves bytes you do not need.
4. The mapper. sqlx has a global name mapper that you can change (db.MapperFunc(strings.ToLower)). Leave it at the default and rely on tags.
5. Which methods. Standardise on the Context variants.

The result is a repository layer that reads like a list of queries and structs, which is also the shape sqlc produces if you move on later.

What it does not do

It does not close what you open. Queryx returns *sqlx.Rows, which you must close. Get and Select close for you.

It does not manage the pool. The settings from the pool article still apply to the *sql.DB inside.

It does not build queries. Dynamic SQL is still string work; sqlx plays well with a builder: build with squirrel, then db.Select(&out, sql, args...). See query builders compared.

It does not handle relations. Nested structs and joins need either flat scanning and grouping in Go or JSON aggregation in SQL; the techniques are in scanning joins into nested structs.

It uses reflection, with a cache of struct mappings. The overhead is small next to a network round trip, and tiny compared with an ORM.

Where it fits

If you are on pgx's native API, pgx.CollectRows with pgx.RowToStructByName does what Select does and is the natural choice; see scanning rows into structs. If you are on database/sql, sqlx is the minimal step up. And sqlc takes a different path, generating typed functions from SQL files, which removes both the loop and the reflection.

Practical habits

1. Tag every field. Without tags the mapping depends on lower-casing the field name, which is fragile for names like ID or UserID.
2. Avoid select *. Name the columns. A schema change that adds a column should not break a scan, and with sqlx it would, because of the missing-destination check.
3. Put the struct next to the query, and test the pair against a real database. A cheap test calls each query once and asserts no error; it catches tag drift.
4. Use NamedExec for inserts with many columns. The named form is easier to read and less error-prone than twelve positional placeholders.
5. Wrap errors with context. fmt.Errorf("load user %d: %w", id, err) costs nothing and saves hours.
6. Do not share a struct between the database and the API. Database structs with db tags and nullable fields are not the shape a client wants. Map at the boundary.

NULL handling

sqlx inherits the standard library's rules for NULL, so a nullable column needs a pointer or a sql.Null type in the struct, or the scan fails with the error from the NULL article. With select * into a struct, a newly added nullable column that is missing from the struct triggers the missing-destination error first, a useful early warning.

Cost, honestly

Reflection is the price. sqlx looks up struct fields through a cached map and sets them with the reflect package, which adds allocations per row relative to a hand-written Scan. In a benchmark of a tight scan loop the difference is visible; in a request that makes a network round trip to a database, it disappears into the noise. If a profile ever shows scanning as a hot spot, the options are to select fewer columns, to stream with StructScan, or to replace that one query with a hand-written scan. Measure with the method in the benchmark guide before you give up the convenience.

The verdict

sqlx earns its place when you have many struct-shaped queries and want to cut the boilerplate without leaving database/sql, and when the team would rather read SQL than learn a query language. It is small, stable and easy to remove. It is not the answer to type safety, to relations or to SQL generation. The project is at github.com/jmoiron/sqlx, its API is on pkg.go.dev, and the standard package it builds on is described in the database/sql explainer.