Go: working with PostgreSQL

Foreword:

If you see that I'm wrong somewhere or if you are aware of any cool and valuable resources relevant to this topic, please let me know in the comments. I'd love to learn from you!

I will start by exploring database/sql and then dive into other packages as I go, namely jmoiron/sql and jackc/pgx packages. If this article doesn't mention the default package at all, it's because the internet told me that everything can be done in a better, easier, and faster way with additional packages.

Best Text Material

Additional Read

Query vs Exec vs Prepare in Go

tl;dr

In Go, you use sql.DB to access databases, although it isn't a direct representation of a specific database or schema. It serves as a handler for opening and closing connections to the database using the selected driver, while also managing the connection pool as needed.

The sqlx package provides several quality-of-life extensions compared to the standard library. For example:

StructScan allows you to inject the retrieved data directly into a struct without the need for manual column-to-field mapping.
NamedQuery allows you to use field names as query placeholders.

Please note that the usage of sqlx is optional but can greatly enhance your experience when working with databases in Go.

Content

In Go, you use sql.DB to access databases, it serves as a handler for opening and closing connections to the database using the selected driver, while also managing the connection pool as needed.

The abstraction provided by the sql.DB simplifies the management of concurrent access to the data store.

Once an instance of the sql.DB is created, you will need to import a driver package to establish a connection to the database:

import (
    _ "github.com/jackc/pgx/v5" // driver package will register itself to be available to the 'database/sql' (or the package of your choice) package
)

driverName := "postgres"
connString := "host=localhost port=5432 user=dev password=dev dbname=dev sslmode=disable"

// sql.Open does not establish ant connections to the database.
// it prepares the database abstration for later use
db, err := sql.Open(driverName, connString)
// if err removed to save space, always check the err

// db.Ping checks that the database is available (network) and accessible (login)
err = db.Ping()
// if err removed to save space, always check the err

💡 A common good practice is not to use driver packages to interact with the database. You should use the types defined in the database/sql package. That way you are not dependent on the driver

💡 sql.DB is designed to be long-lived. You should not Open() and Close() it from short-lived functions, instead - pass the sql.DB over and reuse it.

Retrieving Data

When using db.Query, which retrieves multiple rows, you need to perform the following operations:

Always use defer rows.Close(). This is a no-op operation if the connection is already closed.
Iterate over the returned rows using rows.Next().
Read the column content into variables using rows.Scan() or rows.StructScan() when using sqlx package
Always check for errors, both after rows.Scan() and after rows.Next().

query := "SELECT * FROM cities WHERE name=$1"
rows, err := db.Queryx(query, "London")
if err != nil {
    log.Fatal("failed to query database: ", err)
}
defer rows.Close() // make sure that the connection will be closed
for rows.Next() { // iterate over
    var ac City
    err = rows.StructScan(&ac)
    if err != nil { // check for data injection errors
        if err == sql.ErrNoRows {
            log.Fatal("city not found")
        }
        log.Fatal("database error: ", err)
    }
    log.Println(c)
}
err = rows.Err() // check for the looping errors

Use db.QueryRow when you only expect to retrieve a single row. You can pair it with .Scan() to directly cast the data into a struct.

QueryRow does not return an error. The best practice is to pair it with .Scan(). If there was an error during the query, .Scan() will return it. If the query doesn't return any rows, .Scan() will return sql.ErrNoRows.

sqlx.Rows (returned by db.Queryx) has extended scanning capabilities compared to the regular sql.Rows. It includes methods such as StructScan, SliceScan, and MapScan. The same extended scanning capabilities apply to db.QueryRowx().

In sqlx, Query() and QueryRow() have alternatives in the form of .Select() and .Get(), respectively. These methods allow you to scan the query result into variables within a single statement. They also close the rows they create during the query execution and return any encountered errors.

💡 Unlike Queryx, .Select() loads the entire result set into memory at once. If the data set is not bounded by the query, this can lead to significant performance implications. It's better to use Queryx followed by StructScan() instead.

Query Helpers

Since the database/sql package doesn't inspect your queries and only passes the arguments to the driver, passing a slice into an bindvar in the query won't work as expected.

// Bad approach
var levels = []int{4, 6, 7}
rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)

To handle this situation, sqlx provides an .In() helper. It expands the bindvars that correspond to a slice and appends the slice elements to a new arglist.

💡 .In() works only with the ? bindvar. If you need to use a different bindvar syntax for your backend, you can use .Rebind() to get a query that works accordingly.

//'args' extends the 'cities' slice into an arglist
//the value of 'query' is a query with extended bindvars
var cities = []string{"Riga", "Tallinn", "Vilnius"}
query, args, err := sqlx.In("SELECT * FROM cities WHERE name IN (?);", cities)

query = db.Rebind(query) // rebind the query to work with your backend
rows, err := db.Queryx(query, args...) // use queryx to enable StructScan

Named Queries

Named query helpers, such as NamedQuery, NamedExec, and NamedStmt, allow you to use field names in your queries.

type City struct {
    ID   string `db:"id"`
    Name string `db:"name"`
}

c := City{ID: 1, Name: "Berlin"}
db.NamedQuery(`SELECT * FROM cities WHERE name=:id`, c)

Using named queries and In statement together.

arg := map[string]interface{}{
    "published": true,
    "authors": []int{8, 19, 32, 44},
}
query, args, err := sqlx.Named("SELECT * FROM articles WHERE published=:published AND author_id IN (:authors)", arg)
query, args, err := sqlx.In(query, args...)
query = db.Rebind(query)
db.Query(query, args...)

Modifying Data

Use prepared statements where possible, and use .Exec() when performing INSERT, UPDATE, DELETE, or any other statement that doesn't return rows.

Normally, .Exec() returns a Result type with LastInsertId() and RowsAffected() functions available. The availability of LastInsertId() depends on the driver. In PostgreSQL, this information can only be retrieved from a row using the RETURNING clause (source).

Bindvars are used solely for query parameterization and should not change the structure of the SQL statement.

Advanced Scanning

This topic is quite extensive and could warrant a separate article. In the meantime, you can refer to the Illustrated Guide to SQLX: Advanced Scanning for more information.

Artemijs Pavlovs