This article is the first in a series covering all the aspects of implementing a modern REST API microservice step by step:

1. Defining a SQL first data model with sqlc
2. Implementing the REST API with Gin
3. Configuring with Viper
4. Building and running in a container
5. Containerized integration tests

All the code for the series is available at https://github.com/bquenin/go-modern-rest-api-tutorial

What is The Source of Truth for our Data Model?

There are usually two main approaches for synchronizing your database models and queries with your code:

• Code first: The code is the source of truth and the database schema is generated from it. This is what ORM libraries such as gorm do.
• SQL first: Conversely, the SQL schema is the source of truth, and the code is generated from it. The most popular library for this is sqlc.

Both approaches have their pros and cons. Code first is usually better when you want the ORM library to handle all the boilerplate CRUD operations for you. However, it could get in the way if you want to fine-tune your database queries. In this case, the SQL first approach may be more appropriate, allowing you to have complete control over your database schema and fine-tuning your SQL queries, which could be necessary depending on the type of APIs you are building, however, you will have to write all the SQL queries yourself with this approach.

There is no right or wrong answer on which is better. It entirely depends on what you feel more comfortable with, as you can build production-grade applications using both. In this tutorial, we will focus on the SQL first approach and use sqlc to generate the code from our SQL schemas and queries.

SQL First Data Model

Defining the data model is a key step in implementing a REST API microservice. You need to make sure to manipulate the right concepts and understand your consumer domain very well.

However, this is beyond the scope of this tutorial and we are going to focus solely on how to build a REST API microservice step by step. Therefore, we will use the same model as in the sqlc tutorial:

schema.sqlqueries.sql

The schema.sql file describes a simple Author object and the queries.sql file provides all the CRUDL (Create, Read, Update, Delete, and List) queries. As you can notice, each query is preceded by a comment providing the name of the function to be generated and how many results it returns (one of exec, one, and many). This example is trivial but make sure to consult the sqlc documentation if your model is more complex.

Generating the Go code

First, we need to configure sqlc:

sqlc.yaml

In this case, we are configuring sqlc to procude Postgres compatible code. We’re also specifying the schema and queries files as well as the output path and package name for the generated code. Our project structure looks like this:

├── sql
│   ├── queries.sql
│   └── schema.sql
└── sqlc.yaml

We can now generate the code by running the following command:

sqlc generate

It generates the following files:

├── internal
│   └── database
│       ├── db.go
│       ├── models.go
│       └── queries.sql.go
├── sql
│   ├── queries.sql
│   └── schema.sql
└── sqlc.yaml

models.goqueries.sql.go

As you can see from the header, this code is not to be edited manually. If anything needs to be modified, it must be done either in the schema.sql or queries.sql files. You can also change the sqlc.yaml configuration to tweak the code generation parameters.

Using the Generated Code

The code generated by sqlc relies on the Go standard library SQL abstraction. It means we need to use the appropriate Go SQL driver to instantiate a connection to the database. The following file does just this:

postgres.go

We can now instantiate our Postgres database:

main.go

What’s Next?

Now that we have completed our database implementation, we will have a look at how to implement our REST API.