sqlx Go: Methods, Examples, Installation

Introduction to sqlx: A Powerful Extension of database/sql for Go

The sqlx library is a popular extension of the standard database/sql package in the Go language. It does not replace the standard driver but builds on top of it, adding convenient functions for working with databases. If you have ever written Go code and faced the need to manually scan query result rows into structures, sqlx will seem like a real lifesaver.

The main goal of sqlx is to simplify and speed up development by eliminating boilerplate code. The library allows you to:

Automatically map SQL query results to Go structures.
Work with named parameters in queries.
Perform batch inserts.
Use convenient methods for working with prepared statements.
Integrate with any driver compatible with database/sql (PostgreSQL, MySQL, SQLite, etc.).

On the PythonLib.ru website, we often talk about useful tools for developers. Today, we will thoroughly examine sqlx — a library that every Go developer working with databases must learn.

Installing sqlx

Installing sqlx in a Go project is done in the standard way using the go get package manager:

go get github.com/jmoiron/sqlx

You will also need to install a driver for your specific database. For example, for PostgreSQL:

go get github.com/lib/pq

For MySQL:

go get github.com/go-sql-driver/mysql

For SQLite:

go get github.com/mattn/go-sqlite3

After installation, you can import the library into your code:

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

Quick Start: First Example with sqlx

Let's write a simple program that connects to a PostgreSQL database, creates a table, inserts a few records, and displays them on the screen.

package main

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

type User struct {    ID   int    `db:"id"`    Name string `db:"name"`    Age  int    `db:"age"`}

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

    // Creating a table    schema := `CREATE TABLE IF NOT EXISTS users (        id SERIAL PRIMARY KEY,        name VARCHAR(100) NOT NULL,        age INTEGER NOT NULL    );`    db.MustExec(schema)

    // Inserting data    db.MustExec("INSERT INTO users (name, age) VALUES ($1, $2)", "Alice", 30)    db.MustExec("INSERT INTO users (name, age) VALUES ($1, $2)", "Bob", 25)

    // Selecting data    var users []User    err = db.Select(&users, "SELECT id, name, age FROM users")    if err != nil {        log.Fatalln(err)    }

    fmt.Printf("Users: %+v", users)}

In this example, we see the key features of sqlx: automatic mapping of structure fields (thanks to db tags), the Select method for retrieving multiple rows, and MustExec for executing queries without error handling (convenient for prototyping).