Microservices in Go: Accessing PostgreSQL - Putting it all together

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 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.