Comprehensive Guide to Using sqlx with Go for Database Operations

sqlx is a popular third‑party Go package that extends the standard database/sql library, offering a superset of its interfaces and simplifying SQL usage with richer functionality.

Installation

Install sqlx like any other Go package:

go get github.com/jmoiron/sqlx

sqlx Type Design

sqlx mirrors database/sql types, providing equivalents such as sqlx.DB , sqlx.Tx , sqlx.Stmt , sqlx.NamedStmt , sqlx.Rows , and sqlx.Row , each offering more convenient methods while keeping the original interfaces unchanged.

Preparing the Database

A sample MySQL user table is created to demonstrate sqlx operations.

Connecting to the Database

sqlx offers five ways to open a connection, all ultimately calling db.Ping() to verify connectivity:

db, err := sqlx.Open("mysql", dsn)
db = sqlx.MustOpen("mysql", dsn)
sqlDB, err := sql.Open("mysql", dsn)
db = sqlx.NewDb(sqlDB, "mysql")
db, err = sqlx.Connect("mysql", dsn)
db = sqlx.MustConnect("mysql", dsn)

Executing SQL Commands

Use *sqlx.DB.MustExec to insert a user record, which panics on error:

res := db.MustExec(`INSERT INTO user(name, email, age, birthday, salary) VALUES(?, ?, ?, ?, ?)`, user.Name, user.Email, user.Age, user.Birthday, user.Salary)
lastID, _ := res.LastInsertId()

Executing SQL Queries

sqlx extends *sql.DB.Query and *sql.DB.QueryRow with Queryx and QueryRowx , returning sqlx.Rows and sqlx.Row that support StructScan for direct struct mapping.

func QueryxUsers(db *sqlx.DB) ([]User, error) {
    var us []User
    rows, err := db.Queryx("SELECT * FROM user")
    if err != nil { return nil, err }
    defer rows.Close()
    for rows.Next() {
        var u User
        if err = rows.StructScan(&u); err != nil { return nil, err }
        us = append(us, u)
    }
    return us, nil
}

func QueryRowxUser(db *sqlx.DB, id int) (User, error) {
    var u User
    err := db.QueryRowx("SELECT * FROM user WHERE id = ?", id).StructScan(&u)
    return u, err
}

Additional helper methods Get and Select further simplify single‑row and multi‑row queries.

SQL IN Queries

Use sqlx.In to build dynamic IN clauses safely, then rebind placeholders for the target driver:

query, args, err := sqlx.In("SELECT * FROM user WHERE id IN (?)", ids)
query = db.Rebind(query)
rows, err := db.Query(query, args...)

Named Parameters

NamedExec and NamedQuery allow :name style parameters, improving readability and safety.

m := map[string]interface{}{ "email": "[email protected]", "age": 18 }
result, err := db.NamedExec(`UPDATE user SET age = :age WHERE email = :email`, m)

u := User{Email: "[email protected]", Age: 18}
rows, err := db.NamedQuery("SELECT * FROM user WHERE email = :email OR age = :age", u)

Transactions

sqlx provides MustBegin and standard transaction handling with Commit and Rollback (deferred).

tx := db.MustBegin()
tx.MustExec("UPDATE user SET age = 25 WHERE id = ?", 1)
return tx.Commit()

Prepared Statements

Use Preparex to obtain a *sqlx.Stmt that supports all sqlx query methods.

stmt, err := db.Preparex(`SELECT * FROM user WHERE id = ?`)
var u User
err = stmt.Get(&u, 1)

Unsafe Scanning

Calling db.Unsafe() returns a DB with unsafe = true , allowing scans that ignore unmatched columns.

udb := db.Unsafe()
err := udb.Get(&user, "SELECT id, name, email, age FROM user WHERE id = ?", 1)

Scan Variants

Beyond StructScan , sqlx offers MapScan (into map[string]interface{} ) and SliceScan (into []interface{} ).

r := make(map[string]interface{})
err := rows.MapScan(r)
cols, err := rows.SliceScan()

Controlling Field Mapping

By default, struct fields map to lower‑case column names; custom tags (e.g., `db:"created_at"` ) or a custom mapper via db.MapperFunc can change this behavior. You can also reuse json tags with reflectx.NewMapperFunc("json", strings.ToLower) .

Summary

sqlx builds on database/sql to simplify and enhance relational database interactions in Go, offering Must‑variants, extended query methods, IN‑query helpers, named parameters, transaction utilities, prepared statements, unsafe scanning, and flexible field‑mapping customization.

Reference links and full source code are provided at the end of the original article.