Go Tutorial #19: Database Access with sqlx

In the previous tutorial, you built JWT authentication for your API. But we stored users in memory — they disappear when the server restarts. It is time to use a real database.

Go has a clean database interface in the standard library. And sqlx makes it even better by adding struct scanning and named queries. In this tutorial, you will learn how to use sqlx with PostgreSQL.

database/sql — The Standard Interface

Go’s database/sql package defines a standard interface for SQL databases. It works with any SQL database through drivers:

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib" // PostgreSQL driver
)

db, err := sql.Open("pgx", "postgres://user:password@localhost:5432/mydb")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

The underscore import (_) registers the driver. You never call it directly.

The standard database/sql package works, but has some pain points:

You must scan each column manually
No named parameters
No struct scanning

This is where sqlx helps.

Why sqlx?

sqlx is a library that extends database/sql. It adds features without changing the interface:

Feature	database/sql	sqlx
Query execution	Yes	Yes
Row scanning	Manual per column	Automatic to structs
Named parameters	No	Yes
In-clause expansion	No	Yes
Select into slice	No	Yes (one line)

Install sqlx and the PostgreSQL driver:

go get github.com/jmoiron/sqlx
go get github.com/jackc/pgx/v5/stdlib

Connecting to PostgreSQL

First, make sure you have PostgreSQL running. You can use Docker:

docker run -d \
  --name postgres \
  -e POSTGRES_USER=user \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=tutorial \
  -p 5432:5432 \
  postgres:17

Now connect with sqlx:

package main

import (
    "fmt"
    "log"

    "github.com/jmoiron/sqlx"
    _ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
    db, err := sqlx.Connect("pgx", "postgres://user:password@localhost:5432/tutorial?sslmode=disable")
    if err != nil {
        log.Fatalf("Failed to connect: %v", err)
    }
    defer db.Close()

    // Verify connection
    err = db.Ping()
    if err != nil {
        log.Fatalf("Failed to ping: %v", err)
    }

    fmt.Println("Connected to PostgreSQL!")
}

sqlx.Connect opens the connection and pings the database. If either fails, it returns an error.

Creating Tables

func createTables(db *sqlx.DB) error {
    schema := `
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS posts (
        id SERIAL PRIMARY KEY,
        user_id INTEGER REFERENCES users(id),
        title VARCHAR(255) NOT NULL,
        content TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );`

    _, err := db.Exec(schema)
    return err
}

Defining Structs

Map database columns to Go structs with the db tag:

import "time"

type User struct {
    ID        int       `db:"id" json:"id"`
    Name      string    `db:"name" json:"name"`
    Email     string    `db:"email" json:"email"`
    Password  string    `db:"password" json:"-"`
    CreatedAt time.Time `db:"created_at" json:"created_at"`
}

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

The db tag tells sqlx which column maps to which field. The json tag controls JSON serialization. We use json:"-" for the password field so it never appears in API responses.

CRUD Operations

Create — Insert a Row

func createUser(db *sqlx.DB, name, email, password string) (*User, error) {
    user := User{}

    query := `
        INSERT INTO users (name, email, password)
        VALUES ($1, $2, $3)
        RETURNING id, name, email, password, created_at`

    err := db.QueryRowx(query, name, email, password).StructScan(&user)
    if err != nil {
        return nil, fmt.Errorf("failed to create user: %w", err)
    }

    return &user, nil
}

$1, $2, $3 are parameter placeholders for PostgreSQL. Never use fmt.Sprintf to build queries — that opens you to SQL injection.

RETURNING is a PostgreSQL feature that returns the inserted row. Combined with StructScan, you get the complete user including the auto-generated id and created_at.

Read — Get One Row

func getUserByID(db *sqlx.DB, id int) (*User, error) {
    user := User{}

    err := db.Get(&user, "SELECT * FROM users WHERE id = $1", id)
    if err != nil {
        return nil, fmt.Errorf("user not found: %w", err)
    }

    return &user, nil
}

func getUserByEmail(db *sqlx.DB, email string) (*User, error) {
    user := User{}

    err := db.Get(&user, "SELECT * FROM users WHERE email = $1", email)
    if err != nil {
        return nil, fmt.Errorf("user not found: %w", err)
    }

    return &user, nil
}

db.Get runs a query and scans the result into a struct. It returns an error if no rows are found.

Read — Get Multiple Rows

func listUsers(db *sqlx.DB) ([]User, error) {
    var users []User

    err := db.Select(&users, "SELECT id, name, email, created_at FROM users ORDER BY id")
    if err != nil {
        return nil, fmt.Errorf("failed to list users: %w", err)
    }

    return users, nil
}

func getUserPosts(db *sqlx.DB, userID int) ([]Post, error) {
    var posts []Post

    err := db.Select(&posts, "SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC", userID)
    if err != nil {
        return nil, fmt.Errorf("failed to get posts: %w", err)
    }

    return posts, nil
}

db.Select scans multiple rows into a slice. One line instead of the manual loop you would need with database/sql.

Update

func updateUser(db *sqlx.DB, id int, name, email string) error {
    result, err := db.Exec(
        "UPDATE users SET name = $1, email = $2 WHERE id = $3",
        name, email, id,
    )
    if err != nil {
        return fmt.Errorf("failed to update user: %w", err)
    }

    rows, _ := result.RowsAffected()
    if rows == 0 {
        return fmt.Errorf("user not found")
    }

    return nil
}

Delete

func deleteUser(db *sqlx.DB, id int) error {
    result, err := db.Exec("DELETE FROM users WHERE id = $1", id)
    if err != nil {
        return fmt.Errorf("failed to delete user: %w", err)
    }

    rows, _ := result.RowsAffected()
    if rows == 0 {
        return fmt.Errorf("user not found")
    }

    return nil
}

Named Queries

sqlx supports named parameters using struct fields:

func createUserNamed(db *sqlx.DB, user *User) error {
    query := `
        INSERT INTO users (name, email, password)
        VALUES (:name, :email, :password)
        RETURNING id, created_at`

    rows, err := db.NamedQuery(query, user)
    if err != nil {
        return fmt.Errorf("failed to create user: %w", err)
    }
    defer rows.Close()

    if rows.Next() {
        err = rows.StructScan(user)
    }

    return err
}

Named queries use :field_name instead of $1, $2. The values come from the struct’s db tags. This is cleaner when you have many parameters.

Transactions

Transactions ensure that multiple operations succeed or fail together:

func createUserWithPost(db *sqlx.DB, userName, email, password, postTitle, postContent string) error {
    tx, err := db.Beginx()
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }

    // If anything fails, rollback
    defer func() {
        if err != nil {
            tx.Rollback()
        }
    }()

    // Create user
    var userID int
    err = tx.QueryRowx(
        "INSERT INTO users (name, email, password) VALUES ($1, $2, $3) RETURNING id",
        userName, email, password,
    ).Scan(&userID)
    if err != nil {
        return fmt.Errorf("failed to create user: %w", err)
    }

    // Create post for the user
    _, err = tx.Exec(
        "INSERT INTO posts (user_id, title, content) VALUES ($1, $2, $3)",
        userID, postTitle, postContent,
    )
    if err != nil {
        return fmt.Errorf("failed to create post: %w", err)
    }

    // Commit both operations
    err = tx.Commit()
    if err != nil {
        return fmt.Errorf("failed to commit: %w", err)
    }

    return nil
}

If the post insert fails, the user insert is also rolled back. Both operations succeed or neither does.

Connection Pooling

sqlx.DB (and sql.DB) manages a pool of connections automatically. Configure it for production:

func setupDB(dsn string) (*sqlx.DB, error) {
    db, err := sqlx.Connect("pgx", dsn)
    if err != nil {
        return nil, err
    }

    // Connection pool settings
    db.SetMaxOpenConns(25)              // Maximum open connections
    db.SetMaxIdleConns(10)              // Maximum idle connections
    db.SetConnMaxLifetime(5 * time.Minute) // Maximum connection lifetime

    return db, nil
}

MaxOpenConns — limits total connections to the database. Set this based on your database plan. A good starting point is 25.
MaxIdleConns — keeps connections ready for reuse. Set lower than MaxOpenConns.
ConnMaxLifetime — closes connections after this duration. Prevents stale connections.

Complete Example — API with Database

Here is a complete Gin API with sqlx:

package main

import (
    "log"
    "net/http"
    "strconv"

    "github.com/gin-gonic/gin"
    "github.com/jmoiron/sqlx"
    _ "github.com/jackc/pgx/v5/stdlib"
)

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

var db *sqlx.DB

func main() {
    var err error
    db, err = sqlx.Connect("pgx", "postgres://user:password@localhost:5432/tutorial?sslmode=disable")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(10)

    // Create table
    db.MustExec(`
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL
        )
    `)

    r := gin.Default()
    r.GET("/users", handleListUsers)
    r.GET("/users/:id", handleGetUser)
    r.POST("/users", handleCreateUser)
    r.PUT("/users/:id", handleUpdateUser)
    r.DELETE("/users/:id", handleDeleteUser)
    r.Run(":8080")
}

func handleListUsers(c *gin.Context) {
    var users []User
    err := db.Select(&users, "SELECT id, name, email FROM users ORDER BY id")
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to fetch users"})
        return
    }
    c.JSON(http.StatusOK, users)
}

func handleGetUser(c *gin.Context) {
    id, err := strconv.Atoi(c.Param("id"))
    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": "invalid id"})
        return
    }

    var user User
    err = db.Get(&user, "SELECT id, name, email FROM users WHERE id = $1", id)
    if err != nil {
        c.JSON(http.StatusNotFound, gin.H{"error": "user not found"})
        return
    }
    c.JSON(http.StatusOK, user)
}

func handleCreateUser(c *gin.Context) {
    var user User
    if err := c.ShouldBindJSON(&user); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    err := db.QueryRowx(
        "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id",
        user.Name, user.Email,
    ).Scan(&user.ID)

    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to create user"})
        return
    }

    c.JSON(http.StatusCreated, user)
}

func handleUpdateUser(c *gin.Context) {
    id, err := strconv.Atoi(c.Param("id"))
    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": "invalid id"})
        return
    }

    var user User
    if err := c.ShouldBindJSON(&user); err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
        return
    }

    result, err := db.Exec(
        "UPDATE users SET name = $1, email = $2 WHERE id = $3",
        user.Name, user.Email, id,
    )
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to update user"})
        return
    }

    rows, _ := result.RowsAffected()
    if rows == 0 {
        c.JSON(http.StatusNotFound, gin.H{"error": "user not found"})
        return
    }

    user.ID = id
    c.JSON(http.StatusOK, user)
}

func handleDeleteUser(c *gin.Context) {
    id, err := strconv.Atoi(c.Param("id"))
    if err != nil {
        c.JSON(http.StatusBadRequest, gin.H{"error": "invalid id"})
        return
    }

    result, err := db.Exec("DELETE FROM users WHERE id = $1", id)
    if err != nil {
        c.JSON(http.StatusInternalServerError, gin.H{"error": "failed to delete user"})
        return
    }

    rows, _ := result.RowsAffected()
    if rows == 0 {
        c.JSON(http.StatusNotFound, gin.H{"error": "user not found"})
        return
    }

    c.JSON(http.StatusOK, gin.H{"message": "user deleted"})
}

sqlx vs GORM — When to Use Each

Feature	sqlx	GORM
SQL control	Full — you write SQL	Abstracted — generates SQL
Learning curve	Low (just SQL)	Medium (ORM concepts)
Performance	Fast — minimal overhead	Slower — reflection + generation
Migrations	Manual (or use a tool)	Built-in auto-migrate
Complex queries	Easy — just write SQL	Can be harder than raw SQL

Use sqlx when you want full control over your SQL, care about performance, or have complex queries. Most Go teams prefer sqlx.

Use GORM when you want rapid prototyping, auto-migrations, and do not mind giving up some control.

Common Mistakes

1. Using string formatting for queries.

// WRONG — SQL injection vulnerability!
query := fmt.Sprintf("SELECT * FROM users WHERE email = '%s'", email)

// CORRECT — use parameterized queries
db.Get(&user, "SELECT * FROM users WHERE email = $1", email)

Always use parameterized queries. Never build SQL with string concatenation.

2. Not closing rows.

rows, err := db.Queryx("SELECT * FROM users")
if err != nil {
    return err
}
defer rows.Close() // Always close rows!

With db.Get and db.Select, rows are closed automatically. But if you use db.Queryx, always defer rows.Close().

3. Not handling connection pool exhaustion.

If all connections are in use and a new query arrives, it blocks until a connection is available. Set MaxOpenConns to a reasonable number and monitor your connection pool in production.

Source Code

You can find the complete source code for this tutorial on GitHub:

GO-19 Source Code on GitHub

Go Tutorial #18: Middleware and JWT Authentication — Authentication for your API
Go Tutorial #20: File I/O — Reading and writing files

What’s Next?

In the next tutorial, Go Tutorial #20: File I/O — Reading and Writing Files, you will learn:

Reading and writing files with os and bufio
The io.Reader and io.Writer interfaces
Working with directories
JSON and CSV file processing

This is part 19 of the Go Tutorial series. Follow along to learn Go from scratch.

database/sql — The Standard Interface#

Why sqlx?#

Connecting to PostgreSQL#

Creating Tables#

Defining Structs#

CRUD Operations#

Create — Insert a Row#

Read — Get One Row#

Read — Get Multiple Rows#

Update#

Delete#

Named Queries#

Transactions#

Connection Pooling#

Complete Example — API with Database#

sqlx vs GORM — When to Use Each#

Common Mistakes#

Source Code#

Related Articles#

What’s Next?#