Building Microservices in Go: Accessing PostgreSQL - Statements Builder and Code generators

The full code example mentioned in this post is available on Github, please make sure to read the README for specifics.

Similar to the previous post, the examples below use a database table with the following structure:

-- db/migrations/20210126023417_create_names_table.up.sql
CREATE TABLE names (
    nconst              varchar(255),
    primary_name        varchar(255),
    birth_year          varchar(4),
    death_year          varchar(4) DEFAULT '',
    primary_professions varchar[],
    known_for_titles    varchar[]
);

Using Statements Builder and Code generators

Last two posts covered opposite ways of interacting with PostgreSQL Databases, either all the calls were manually written or all of them were autogenerated via some sort of mechanism, this post will bring both ideas together with two different approaches to handle both dynamically-built and statically-built SQL statements.

The two packages covered are:

• Masterminds/squirrel, and
• kyleconroy/sqlc

Masterminds/squirrel

Masterminds/squirrel is a package that allows you to generate SQL statements using a fluent API, it also supports making database calls directly using those built statements.

squirrel is not PostgreSQL specific, in theory it should work with any database engine that supports standard SQL, to use it we have to compose our queries in parts, this means that instead of manually writing the literal SQL statement to execute we take advantage of the types defined in the package, for example:

// postgresql_squirrel.go
var res Name

query := sq.
    Select("nconst", "primary_name", "birth_year", "death_year").
    From("names").
    Where("nconst = ?", nconst).
    PlaceholderFormat(sq.Dollar).
    RunWith(p.db)

if err := query.
    ScanContext(context.Background(), &res.NConst, &res.Name, &res.BirthYear, &res.DeathYear); err != nil {
    return Name{}, err
}

return res, nil

One thing to call out immediately is, although creating the SQL statement is more user friendly than before, we still need to explicitly call Scan() for each one of the returned fields, similar to the when using database/sql.

squirrel really shines when building dynamic statements, for example consider the case where the statement we are trying to build depends on certain conditions and those affect what columns or tables we are trying to interact with.

This is where this package is useful for because instead of us manually building a string by hand we can use squirrel to properly generate a statement with the proper placeholders we require. It saves us some time and makes the code easier to follow.

kyleconroy/sqlc

kyleconroy/sqlc is SQL Compiler that generates type-safe code from literal SQL statements, it supports PostgreSQL and MySQL.

The killer feature of sqlc is that because it uses the PostgreSQL engine it can detect SQL errors in our queries, like syntax errors or missing fields; and not only that the generated Go code is type-safe and uses concrete types as much as possible.

The way it works is:

1. SQL Statements are written explicitly indicating the fields and tables to use,
2. SQL Statements include annotations to indicate instructions sqlc understands,
3. sqlc is executed, with the SQL files as input (via go:generate)
4. Go types are generated.

After that we can easily interact with the database, for example:

// postgresql_sqlc.go
row, err := New(p.db).SelectName(context.Background(), sql.NullString{String: nconst})

if err != nil {
    return Name{}, err
}

return Name{
    NConst:    row.Nconst.String,
    Name:      row.PrimaryName.String,
    BirthYear: row.BirthYear.String,
    DeathYear: row.DeathYear.String,
}, nil

The autogenerated code not only takes care of properly passing in the arguments correctly but also scanning the results as needed.

Final thoughts

Both squirrel and sqlc are complementary packages meant to be used to improve our workflow when accessing databases, they definitely have different goals but in the end both allow us to reduce the manual boilerplate needed when interacting with PostgreSQL giving us some flexibility in cases where more difficult queries are needed.

Next is the final post where we will be putting it all together, I will share with you the packages I like using when interacting with PostgreSQL and more importantly why I use them in the end.

Talk to you later.

Keep it up. Don't give up.