Overview of the sqlx package

You are already familiar with the database/sql package and how it provides a lightweight interface for working with SQL databases. While it is a powerful tool, using it can be cumbersome when dealing with more complex queries and data structures.

In this topic, you will explore the sqlx package. It extends the standard database/sql package by providing more convenient methods and functions, such as struct tags and named parameters for database interactions. Let's get started!

Getting started with the sqlx package

First, ensure that the SQLite driver is installed, as it's required to interact with SQLite databases in Go.

go get -u github.com/mattn/go-sqlite3

import (
    _ "github.com/mattn/go-sqlite3"
)

Installing the sqlx package

With the SQLite driver in place, the next step is to install the sqlx package. You can do this by running the following command in your terminal:

go get -u github.com/jmoiron/sqlx

Now, we can connect to our database from Go code. Create a file, add the following code to it, and run:

db, err := sqlx.Connect("sqlite3", "mydatabase.db")
if err != nil {
	log.Fatalln(err)
}
fmt.Println("Successfully connected to the database!")
defer db.Close()
// Output: Successfully connected to the database!

When working with sqlx, you can create tables using the same approach as the standard database/sql package—by executing SQL queries. We'll add a few SQL queries to our Go file to create the tables we'll be working with:

createTableQueries := []string{
	`CREATE TABLE products (
	id INTEGER PRIMARY KEY,
	name TEXT NOT NULL,
	price NUMERIC NOT NULL,
	amount INTEGER NOT NULL
)`,
	`CREATE TABLE addresses (
	id INTEGER PRIMARY KEY,
	city TEXT,
	street TEXT,
	house TEXT
)`,
	`CREATE TABLE clients (
	id INTEGER PRIMARY KEY,
	name TEXT,
	email TEXT UNIQUE,
	address_id INTEGER,
	deposit NUMERIC NOT NULL,
	FOREIGN KEY(address_id) REFERENCES addresses(id)
)`,
}

Next, let's use the Exec() method and a loop to execute the above queries and create the tables:

for _, query := range createTableQueries {
	_, err := db.Exec(query)
	if err != nil {
		log.Fatalln(err)
	}
}
fmt.Println("Tables successfully created!")

Done! The tables have been successfully created!

Structures and Tags

Structures (struct types) are often used to represent table data, and the sqlx package provides enhanced capabilities for interacting with the database, including using structure tags to map query results and named parameters in SQL queries.

In Go, structure tags are metadata assigned to structure fields. Database tags in sqlx are used to link structure fields with columns in the database table. For example, the db:"name" tag in the Name string field of a structure indicates that this field should be linked to the name column in the database table. This allows the structure fields to be automatically populated with data from the database when executing queries.

Tags can also be used to insert values into queries. When using certain functions, such as NamedExec or NamedQuery, you can insert values from structure fields directly into the SQL query using named parameters corresponding to these fields' tags.

Named parameters are a feature of sqlx that allows using variable names in SQL queries instead of traditional placeholders like ?. In methods supporting named parameters, SQL query parameters are directly mapped to structure fields or map keys, eliminating the need to remember the order of parameters as with positional placeholders.

Let's add structures that reflect the data to be stored in the tables we created earlier:

type Product struct {
	ID     int     `db:"id"`
	Name   string  `db:"name"`
	Price  float64 `db:"price"`
	Amount int     `db:"amount"`
}
type Address struct {
	ID     int    `db:"id"`
	City   string `db:"city"`
	Street string `db:"street"`
	House  string `db:"house"`
}
type Client struct {
	ID        int     `db:"id"`
	Name      string  `db:"name"`
	Email     string  `db:"email"`
	AddressID int64   `db:"address_id"`
	Deposit   float64 `db:"deposit"`
}

In the above structures, the db tags specify the name of the database column that corresponds to each field of the structure. This allows sqlx to automatically fill the structures with data from queries.

Named parameters in sqlx package methods

After defining the structures for our data tables, we can start using sqlx's capabilities for database operations. Let's look at the main methods that assist in this task.

You might already be familiar with the Exec() method from the database/sql package, which is also available in sqlx. It's used for executing SQL queries that do not return query results, such as INSERT, UPDATE, and DELETE.

MustExec() is a method provided by sqlx, similar to Exec(), but instead of returning an error, it panics if the query fails to execute successfully. This method is suitable for scenarios where an error is considered fatal.

However, these two methods do not interact with the structure tags we added and do not use named parameters. Instead, you'll use the following methods:

• NamedExec() (extends sql.DB.Exec)—This method executes an SQL query with named parameters and is intended for operations that do not return results or when you do not need to handle query results. NamedExec() returns a sql.Result object, which can be used to obtain information about the operation outcome, such as the number of rows affected.

• NamedQuery() (extends sql.DB.Query)—This method executes an SQL query with named parameters and is intended for queries that return data (e.g., executing SELECT queries). NamedQuery() returns a Rowsx object, allowing you to iterate through the query results. You can use the StructScan() method on this object to automatically scan result rows and populate your structures. This method is suitable when you expect one or more results and want to process them.

Inserting records

Now that you know how to add data using named parameters, let's look at how you can insert multiple objects into the database. For convenience, let's write a function that takes a client object and an address object and inserts them into the database tables.

First, you need to add the address to its table, retrieve the assigned ID, and then add the address ID to the client before saving the client in the clients table. Here's how you can implement this:

func insertClientWithAddress(db *sqlx.DB, client Client, address Address) error {
	// Inserting the address and getting its ID using NamedExec
	addressQuery := `INSERT INTO addresses (city, street, house) VALUES (:city, :street, :house)`
	result, err := db.NamedExec(addressQuery, address)
	if err != nil {
		return err
	}

	addressID, err := result.LastInsertId()
	if err != nil {
		return err
	}

	client.AddressID = addressID // Assigning the address ID to the client
	// Inserting the client with the obtained address ID using NamedExec
	clientQuery := `INSERT INTO clients (name, email, address_id, deposit) VALUES (:name, :email, :address_id, :deposit)`
	_, err = db.NamedExec(clientQuery, client)
	if err != nil {
		return err
	}
	return nil
}

To test the above function, let's create a couple of client and address records and pass them to insertClientWithAddress():

// Creating clients and addresses
alice := Client{Name: "Alice", Email: "[email protected]", Deposit: 100}
aliceAddress := Address{City: "CityA", Street: "StreetA", House: "1"}
err = insertClientWithAddress(db, alice, aliceAddress)
if err != nil {
	log.Fatalln(err)
}
bob := Client{Name: "Bob", Email: "[email protected]", Deposit: 85}
bobAddress := Address{City: "CityB", Street: "StreetB", House: "2"}
err = insertClientWithAddress(db, bob, bobAddress)
if err != nil {
	log.Fatalln(err)
}

Next, let's create several records of the Product struct and add them to the products table:

products := []Product{
	{Name: "Milk", Price: 2.50, Amount: 8},
	{Name: "Bread", Price: 1.25, Amount: 5},
	{Name: "Apply", Price: 0.99, Amount: 24},
	{Name: "Cream", Price: 3.50, Amount: 3},
}
for _, product := range products {
	_, err = db.NamedExec(`INSERT INTO products (name, price, amount) VALUES (:name, :price, :amount)`, product)
	if err != nil {
		log.Fatalln(err)
	}
}

Retrieving records

Great! Now that you've inserted data into the database tables, let's move on to retrieving records. Verifying that the data is stored correctly is crucial for ensuring successful data addition and update operations.

The sqlx package offers various methods to retrieve data from the database, including analogs to the database/sql package methods and unique features. Let's explore a couple of these methods and their characteristics:

Get(): This method retrieves a single record from a table and has no direct analog in the database/sql package. Get() can automatically map query results to a struct using struct tags. If the query returns multiple rows, Get() will return only the first record found.

Let's retrieve and print details about a specific product from the products table. We want to find a product record that costs less than 1 dollar:

var product Product
err = db.Get(&product, "SELECT * FROM products WHERE price < ?", 1)
if err != nil {
	log.Fatalln(err)
}
fmt.Printf("Product: ID: %d, Name: %s, Price: %.2f\n", product.ID, product.Name, product.Price)
// Output: Product: ID: 3, Name: Apply, Price: 0.99

The Get() method is optimal when you need a single record from the table. Also, we noticed a mistake in the product name when adding it to the table. We'll correct this issue in the upcoming sections.

Select(): This method is used to retrieve multiple records. It automatically maps the query results to a slice of structs or variables. Let's see which records are stored in the products table:

var existProducts []Product
err = db.Select(&existProducts, "SELECT * FROM products")
if err != nil {
	log.Fatalln(err)
}
fmt.Println("Products:", existProducts)
// Output: Products: [{1 Milk 2.5 8} {2 Bread 1.25 5} {3 Apply 0.99 24} {4 Cream 3.5 3}]

Using Select(), you can also retrieve records from multiple tables. Suppose you want to extract information about clients along with their addresses. First, let's create a new ClientWithAddress struct with an Address field instead of the AddressID:

type ClientWithAddress struct {
	ID      int     `db:"id"`
	Name    string  `db:"name"`
	Email   string  `db:"email"`
	Address Address `db:"address"` // Nested structure
	Deposit float64 `db:"deposit"`
}

Since clients and addresses are stored in different tables, let's write a query that combines data from both tables using the address ID:

query := `
SELECT c.id, c.name, c.email, c.deposit,
       a.id "address.id", a.city "address.city", a.street "address.street", a.house "address.house"
FROM clients c
JOIN addresses a ON c.address_id = a.id
`

Now, let's execute the above query using the Select() method:

var clients []ClientWithAddress
err = db.Select(&clients, query)
if err != nil {
	log.Fatalln(err)
}
for _, client := range clients {
	fmt.Printf("%+v\n", client)
}
// Output:
// {ID:1 Name:Alice Email:[email protected] Address:{ID:1 City:CityA Street:StreetA House:1} Deposit:100}
// {ID:2 Name:Bob Email:[email protected] Address:{ID:2 City:CityB Street:StreetB House:2} Deposit:85}

As you can see from the above output, Select() can also be used when you need to retrieve multiple records from the table.

Updating and Deleting records

Earlier, we noticed that we intended to insert a record "Apple" into the products table but accidentally inserted "Apply". To correct the product name, you can use NamedExec() with named parameters. This time, instead of using a struct with tags, let's use a map with keys corresponding to the column names:

_, err = db.NamedExec(`UPDATE products SET name = :name WHERE name = :old_name`, map[string]interface{}{
    "name": "Apple",
    "old_name": "Apply",
})
if err != nil {
    log.Fatalln(err)
}

In the above code snippet, we use named parameters (:name and :old_name) to specify the new and old product names. This way, we can accurately indicate which record to update and what value to set. Let's verify that the products table now contains "Apple":

var product Product
err = db.Get(&product, "SELECT * FROM products WHERE name = ?", "Apple")
if err != nil {
    log.Fatalln(err)
}
fmt.Printf("Product: ID: %d, Name: %s, Price: %.2f\n", product.ID, product.Name, product.Price)
// Output: Product: ID: 3, Name: Apple, Price: 0.99

Now imagine that after some research, the store management decided to discontinue selling certain products. Although it's a rare practice, sometimes data needs to be deleted from the table. Here's the query for deleting a row from the products table:

deleteQuery := `DELETE FROM products WHERE name = :name`

The NamedExec() method is perfect for deleting a record from the table. As you've seen before, we can do this in several ways:

_, err = db.NamedExec(deleteQuery, map[string]interface{}{
    "name": "Cream",
})

Or, if you have a ready-made object that you want to delete from the table, then you can also use the approach below:

cream := Product{Name: "Cream"}
_, err = db.NamedExec(deleteQuery, cream)
if err != nil {
    log.Fatalln(err)
}

Conclusion

In this topic, you learned:

• How to install and start using with the sqlx package, as well as how to connect to a SQLite database.

• How to create tables using SQL queries in Go with the sqlx package.

• Advanced interaction with data through structures and tags, including the use of named parameters for easier query handling.

• How to apply various methods provided by the sqlx package, such as Exec(), NamedExec(), Get(), and Select() for inserting, updating, and retrieving data, as well as ways to use them for working with database data.

These skills will help you effectively manage databases in Go and use the sqlx package to simplify and optimize data handling. Now, let's test your newly acquired knowledge with some practical tasks!