Microservices in Go: Accessing PostgreSQL - Putting it all together
Tags: go, postgres
Published: 2021-04-07 22:15:50

## Putting it All Together

The last three posts covered different ways to interact with PostgreSQL databases in Go, deciding which one to use when starting a project is not an easy choice because in the end it depends on different factors related to the project we are trying to build, like deadlines, length of service or experience, just to mention a few.

However after building some projects myself here are a few the guidelines I like to follow when starting from scratch:

## Use `golang-migrate/migrate`

Some packages have their own way to define migrations, usually tied together to their corresponding internal API, I recommend using [`golang-migrate/migrate`](https://mariocarrion.com/2021/01/10/golang-tools-for-database-schema-migrations.html) instead, that way in case there's the need to change how to access PostgreSQL your already defined schema changes will stay the same.

* Pros
  * Allows us to use plain SQL.
  * Allows us to use it with any other package for accessing PostgreSQL.
* Cons
  * Requires SQL experience.

## Start simple: `database/sql` and `sqlx` 

When starting a brand new project I try to keep it as simple as possible by using the standard library as much as possible, however I do understand during ramp up it's nice to deliver features as fast as possible, that's why using both `database/sql` and `sqlx` is my preference.

* Pros
  * Allows us to use plain SQL.
  * `database/sql` Allows us to know the exact SQL query to run.
  * `sqlx` Allows us to quickly scan results into struct types.
* Cons
  * Requires SQL experience.
  * `database/sql` Too verbose.
  * `sqlx` Although really well supported, it's still a external dependency.

## Replace boilerplate: `sqlc` 

In cases where the project start to become large and requires more than a dozen of queries I like to pivot and start using `sqlc`.

* Pros
  * Allows us to use plain SQL.
  * `sqlc` Allows us to know the exact SQL query to run.
  * `sqlc` Generates type safe code with no reflection involved.
* Cons
  * Requires SQL experience
  * `sqlc` Extra step needed to generate code.
  * `sqlc` Although really well supported, it's still a external dependency.
  * `sqlc` For _dynamic queries_ it requires a bit of work.

## For dynamic queries: `squirrel`

There are cases where dynamic queries are needed, dynamic in the context of that they may be built dynamically because of different conditions.

* Pros
  * Building dynamic queries is easier than concatenating strings.
  * Generates plain SQL.
  * `database/sql` can still be used.
  * Uses its own API.
* Cons
  * Uses its own API.
  * Although really well supported, it's still a external dependency.

## Final thoughts

You may have noticed that I didn't mention ORMs at all, **that's intentionally**, not because I don't think they are useful, of course they are!, _however_ I think in cases were SQL is not your expertise it makes sense to invest a bit of time learning it because in the end it helps you understand exactly the decisions you're making.

Depending on the project complexity most of the times I like using the _right tool for the job_ and in the end I usually combine `sqlc`, `squirrel` and `database/sql` to deliver the final product.

I do believe the _guidelines_ I listed above are just that: _guidelines_, what could be working for me may not work for you so keep that in mind all the time and make sure to research your options, determine trade-offs and document your decision.

Talk to you later.

**Keep it up. Don't give up.**