Go Tool: (exploring) sqlc (Part 1)
Tags: go
Published: 2021-02-15 19:09:44

Last month I attended [GopherCon 2020](https://mariocarrion.com/2020/11/15/gophercon-govircon-2020.html) (virtually, like everyone else :smile:), and just like the [first time I attended](https://mariocarrion.com/2018/08/27/gophercon-2018-day-1.html) I was able to get answers to some of the problems I was trying to solve.

This time it was related to speeding up our development (**and** testing!) workflow when using PostgreSQL as a persistent datastore. For context, we have been successfully using `database/sql` and [jmoiron/sqlx](https://github.com/jmoiron/sqlx), and both can get the work done _but_ I'm always trying to find better tools to improve what we have. I've been doing this investigation for a few months already, and I did find some packages and tools, but because of the amount of options I haven't really had time to try them all. In the end what I'm looking for is tools/packages that:

* Integrate nicely with PostgreSQL, and
* Avoid _too much magic_ (`reflect`-based ORMs are out of the picture).

With all of that being said... I got answers to those questions from [Johan Brandhorst-Satzkorn](https://www.gophercon.com/agenda/speakers/609392), specifically the talk [A Journey to Postgres Productivity with Go](https://www.gophercon.com/agenda/session/227419), during this talk Johan mentions the following 5 tools/packages:

* [jackc/pgx](https://github.com/jackc/pgx) _PostgreSQL driver and toolkit for Go_

  This driver has been on my radar for months already, I actually used it for the blog series [Implementing Complex Pipelines in Go](https://mariocarrion.com/2020/07/23/go-implementing-complex-pipelines-part-1.html), the cool thing about `pgx` is that it also implements the interface types defined in `database/sql` so replacing what we have (which uses [`lib/pq`](https://github.com/lib/pq)) should be, in theory, not that difficult.

* [golang-migrate/migrate](https://github.com/golang-migrate/migrate) _Database migrations. CLI and Golang library._

  We have been using this package for a while already, simple yet powerful, it satisfies our needs.

* [kyleconroy/sqlc](https://github.com/kyleconroy/sqlc) _Generate type safe Go from SQL_

  This is one of the tools I found during my investigation, it made a great first impression because it integrates nicely with PostgreSQL and allows you to interact with your schema via type-safe Go, however it does lack some _dynamism_ we sometimes need.

* [Masterminds/squirrel](https://github.com/Masterminds/squirrel) _Fluent SQL generation for golang_ 

  Another package I found while exploring alternatives, it seemed nice but a tiny bit magic; but it does answer the question I had after reading about `sqlc`, this one provides the _dynamism_ we need.

* [ory/dockertest](https://github.com/ory/dockertest) _Write better integration tests! Dockertest helps you boot up ephemeral docker images for your Go tests with minimal work_

  I believe I heard about this one previously but I never had the chance of using it before, however just recently I started adding it to our projects and it seems to work nicely. It does remove that extra boilerplate in [GitlabCI](https://mariocarrion.com/2017/10/16/gitlab-ci-services.html) for launching the services required for running the tests. More importantly it defines the actual service requirements in code, any team member can refer to the actual code to run locally a docker container if needed.

In the end the important thing about those 5 tools/packages is how they integrate and how improve (or not) the workflow we currently have; specifically about `sqlc` I found it really easy to integrate, no real concerns or complains so far. Making a decision about adding a new step to the process or adding a new tool always takes time so although I like what I've seen so far about `sqlc` I'm still exploring how it integrates with all the other needs we have, specifically instrumentation (via [NewRelic](https://newrelic.com/golang)) and error details, so far the current version still uses, by default, a combination of `database/sql` and `lib/pq` so there's that as well.

### Final thoughts

I will continue exploring `sqlc` as well as the other tools/packages mentioned above, another thing I'm also investigating is something similar to [`factory_bot`](https://github.com/thoughtbot/factory_bot) for speeding up the default values required when testing PostgreSQL-related methods.

More to come.