Building Microservices in Go: Accessing PostgreSQL - Statements Builder and Code generators
Tags: go, postgres
Published: 2021-04-01 02:56:05

The full code example mentioned in this post is available [on Github](https://github.com/MarioCarrion/videos/tree/main/2021/02/09-go-database-postgresql-part-3), please make sure to read the [README](https://github.com/MarioCarrion/videos/blob/main/2021/02/09-go-database-postgresql-part-3/README.md) for specifics.

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

```sql
-- 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](https://pkg.go.dev/github.com/Masterminds/squirrel), and
* [kyleconroy/sqlc](https://pkg.go.dev/github.com/kyleconroy/sqlc)

## Masterminds/squirrel

[`Masterminds/squirrel`](https://github.com/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: 

```go
// 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`](https://github.com/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,
1. SQL Statements include annotations to indicate instructions `sqlc` understands,
1. `sqlc` is executed, with the SQL files as input (via `go:generate`)
1. Go types are generated.

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

```go
// 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.**