Library

sqlx

Overview

sqlx is a powerful extension library for Go's standard database/sql package. It positions itself between raw SQL and full ORMs, providing additional convenience features while maintaining the performance and flexibility of database/sql. sqlx adds missing features like named parameters, struct scanning, and result mapping without sacrificing control over SQL queries.

Details

sqlx enhances the standard database/sql package with features that reduce boilerplate code while preserving the ability to write and optimize SQL queries directly. It's particularly favored by developers who want more convenience than database/sql offers but don't want the overhead and abstraction of a full ORM.

Key Features

• Struct Scanning: Automatically scan query results into structs
• Named Parameters: Use named parameters in queries for better readability
• Get and Select: Convenient methods for single row and multiple row queries
• In Queries: Built-in support for IN clauses with slice expansion
• Rebind: Automatic query rebinding for different SQL dialects
• Transaction Support: Enhanced transaction handling with the same convenient methods

Pros and Cons

Pros

• Minimal learning curve for developers familiar with database/sql
• Full control over SQL queries with performance optimization capabilities
• Lower overhead compared to full ORMs
• Excellent for complex queries and reporting
• Compatible with existing database/sql code
• Support for multiple database drivers

Cons

• No automatic schema generation or migrations
• Manual SQL writing required for all queries
• No built-in relationship handling
• Less suitable for rapid prototyping compared to full ORMs
• Requires SQL knowledge for effective use

Reference Pages

• sqlx GitHub Repository
• sqlx Documentation
• Go database/sql Tutorial

Code Examples

Basic Setup

# Initialize project
go mod init sqlx-example
go get github.com/jmoiron/sqlx
go get github.com/lib/pq          # PostgreSQL driver
go get github.com/go-sql-driver/mysql # MySQL driver
go get github.com/mattn/go-sqlite3    # SQLite driver

package main

import (
    "log"
    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq" // PostgreSQL driver
)

// Define structs that match your database schema
type User struct {
    ID        int    `db:"id"`
    Name      string `db:"name"`
    Email     string `db:"email"`
    Age       int    `db:"age"`
    CreatedAt string `db:"created_at"`
}

type Post struct {
    ID        int    `db:"id"`
    UserID    int    `db:"user_id"`
    Title     string `db:"title"`
    Content   string `db:"content"`
    CreatedAt string `db:"created_at"`
}

func main() {
    // Connect to database
    db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
    if err != nil {
        log.Fatalln(err)
    }
    defer db.Close()

    // Create tables (schema must be managed manually)
    schema := `
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        age INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    
    CREATE TABLE IF NOT EXISTS posts (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        title VARCHAR(200) NOT NULL,
        content TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`
    
    db.MustExec(schema)
}

Basic Operations (CRUD)

// Create - Insert a user
user := User{
    Name:  "John Doe",
    Email: "[email protected]",
    Age:   30,
}

// Using named parameters
result, err := db.NamedExec(`
    INSERT INTO users (name, email, age) 
    VALUES (:name, :email, :age)`, user)
if err != nil {
    log.Fatal(err)
}
id, _ := result.LastInsertId()
fmt.Printf("Created user with ID: %d\n", id)

// Alternative: Using positional parameters
_, err = db.Exec(`
    INSERT INTO users (name, email, age) 
    VALUES ($1, $2, $3)`, "Jane Smith", "[email protected]", 25)

// Read - Get a single user
var user User
err = db.Get(&user, "SELECT * FROM users WHERE id = $1", 1)
if err != nil {
    log.Fatal(err)
}
fmt.Printf("User: %+v\n", user)

// Get multiple users
var users []User
err = db.Select(&users, "SELECT * FROM users WHERE age > $1", 20)
if err != nil {
    log.Fatal(err)
}
for _, u := range users {
    fmt.Printf("User: %+v\n", u)
}

// Update - Update user
_, err = db.Exec(`
    UPDATE users SET age = $1 WHERE id = $2`, 31, 1)
if err != nil {
    log.Fatal(err)
}

// Using named parameters for update
user.Age = 32
_, err = db.NamedExec(`
    UPDATE users SET age = :age WHERE id = :id`, user)

// Delete - Delete user
_, err = db.Exec("DELETE FROM users WHERE id = $1", 1)
if err != nil {
    log.Fatal(err)
}

Advanced Querying

// Using IN clause with query expansion
ids := []int{1, 2, 3, 4, 5}
query, args, err := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
if err != nil {
    log.Fatal(err)
}
// Rebind for the specific database (PostgreSQL uses $1, $2, etc.)
query = db.Rebind(query)
var users []User
err = db.Select(&users, query, args...)

// Named queries with maps
m := map[string]interface{}{
    "name": "John%",
    "age":  25,
}
var users []User
nstmt, err := db.PrepareNamed(`
    SELECT * FROM users 
    WHERE name LIKE :name AND age > :age`)
err = nstmt.Select(&users, m)

// Queryx for flexible result handling
rows, err := db.Queryx("SELECT * FROM users")
for rows.Next() {
    var u User
    err = rows.StructScan(&u)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", u)
}

// Using sqlx.DB methods with existing database/sql code
var name string
row := db.QueryRow("SELECT name FROM users WHERE id = $1", 1)
err = row.Scan(&name)

Transactions

// Begin a transaction
tx, err := db.Beginx()
if err != nil {
    log.Fatal(err)
}

// Use all sqlx methods within transaction
var user User
err = tx.Get(&user, "SELECT * FROM users WHERE id = $1", 1)
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

// Update within transaction
_, err = tx.Exec("UPDATE users SET age = age + 1 WHERE id = $1", user.ID)
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

// Create related record
_, err = tx.Exec(`
    INSERT INTO posts (user_id, title, content) 
    VALUES ($1, $2, $3)`, 
    user.ID, "New Post", "This is a transaction test")
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

// Commit transaction
err = tx.Commit()
if err != nil {
    log.Fatal(err)
}

// Using transaction helper function
err = sqlx.Transactional(db, func(tx *sqlx.Tx) error {
    // All operations here are in a transaction
    _, err := tx.Exec("INSERT INTO users (name, email) VALUES ($1, $2)", 
        "Bob Wilson", "[email protected]")
    if err != nil {
        return err // Automatically triggers rollback
    }
    
    var count int
    err = tx.Get(&count, "SELECT COUNT(*) FROM users")
    if err != nil {
        return err
    }
    
    if count > 100 {
        return fmt.Errorf("too many users")
    }
    
    return nil // Automatically commits
})

Prepared Statements and Named Queries

// Prepared statement
stmt, err := db.Preparex("SELECT * FROM users WHERE age > $1")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

var users []User
err = stmt.Select(&users, 25)

// Named prepared statement
nstmt, err := db.PrepareNamed("SELECT * FROM users WHERE name = :name")
if err != nil {
    log.Fatal(err)
}
defer nstmt.Close()

var user User
err = nstmt.Get(&user, map[string]interface{}{"name": "John Doe"})

// Batch operations with prepared statements
stmt, err = db.Preparex("INSERT INTO users (name, email, age) VALUES ($1, $2, $3)")
defer stmt.Close()

users := []User{
    {Name: "User1", Email: "[email protected]", Age: 20},
    {Name: "User2", Email: "[email protected]", Age: 25},
    {Name: "User3", Email: "[email protected]", Age: 30},
}

for _, u := range users {
    _, err = stmt.Exec(u.Name, u.Email, u.Age)
    if err != nil {
        log.Printf("Failed to insert user %s: %v", u.Name, err)
    }
}

Working with NULLs and Custom Types

import (
    "database/sql"
    "github.com/jmoiron/sqlx/types"
)

type UserWithNulls struct {
    ID          int            `db:"id"`
    Name        string         `db:"name"`
    Email       string         `db:"email"`
    Age         sql.NullInt64  `db:"age"`        // Nullable integer
    Bio         sql.NullString `db:"bio"`        // Nullable string
    Preferences types.JSONText `db:"preferences"` // JSON column
}

// Insert with NULL values
user := UserWithNulls{
    Name:  "John Doe",
    Email: "[email protected]",
    Age:   sql.NullInt64{Valid: false}, // NULL
    Bio:   sql.NullString{String: "Developer", Valid: true},
    Preferences: types.JSONText(`{"theme": "dark", "notifications": true}`),
}

_, err = db.NamedExec(`
    INSERT INTO users (name, email, age, bio, preferences) 
    VALUES (:name, :email, :age, :bio, :preferences)`, user)

// Query with NULL handling
var users []UserWithNulls
err = db.Select(&users, "SELECT * FROM users WHERE age IS NULL OR age > 25")
for _, u := range users {
    if u.Age.Valid {
        fmt.Printf("User %s is %d years old\n", u.Name, u.Age.Int64)
    } else {
        fmt.Printf("User %s age is unknown\n", u.Name)
    }
}

// Custom scanner implementation
type Email string

func (e *Email) Scan(src interface{}) error {
    switch s := src.(type) {
    case string:
        *e = Email(s)
    case []byte:
        *e = Email(s)
    case nil:
        *e = ""
    default:
        return fmt.Errorf("unsupported type: %T", src)
    }
    return nil
}

Performance Optimization

// Bulk insert with single query
users := []User{
    {Name: "User1", Email: "[email protected]", Age: 20},
    {Name: "User2", Email: "[email protected]", Age: 25},
    {Name: "User3", Email: "[email protected]", Age: 30},
}

// Build bulk insert query
valueStrings := make([]string, 0, len(users))
valueArgs := make([]interface{}, 0, len(users) * 3)
for i, u := range users {
    valueStrings = append(valueStrings, fmt.Sprintf("($%d, $%d, $%d)", 
        i*3+1, i*3+2, i*3+3))
    valueArgs = append(valueArgs, u.Name, u.Email, u.Age)
}

query := fmt.Sprintf("INSERT INTO users (name, email, age) VALUES %s", 
    strings.Join(valueStrings, ","))
_, err = db.Exec(query, valueArgs...)

// Connection pooling configuration
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(5 * time.Minute)

// Query with context for timeout control
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()

var users []User
err = db.SelectContext(ctx, &users, "SELECT * FROM users")
if err != nil {
    if err == context.DeadlineExceeded {
        log.Println("Query timeout")
    }
}