First Part: SQL first Data Model using sqlc
This article is the first in a series where we will cover all the aspects of implementing a modern REST API microservice from scratch:
- Defining a SQL first data model using sqlc
- Implementing the REST API with Gin
- Running and configuring the server locally
- Testing & containerizing tests
- Building a Docker image for deployment
All the code for the series is available at https://github.com/bquenin/go-modern-rest-api-tutorial
The Source of Truth for our Data Model
There are two main approaches for synchronizing your database models and queries with your code:
- 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.
- 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.
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 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’re writing. Still, you need to write all the 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 use the SQL first approach and use sqlc to generate the code from our schemas and queries.
The SQL First Data Model
Defining a data model is a key step in implementing a REST API microservice. You must manipulate the right concepts and understand your consumer domain well.
However, this is beyond this tutorial’s scope, and we will focus solely on how to build a REST API microservice step by step. Therefore, we will use the same schema file as in the sqlc tutorial:
CREATE TABLE authors (
id BIGSERIAL PRIMARY KEY,
name text NOT NULL,
bio text NOT NULL
And the queries:
-- name: CreateAuthor :one
INSERT INTO authors (name, bio)
VALUES ($1, $2)
RETURNING *;-- name: GetAuthor :one
WHERE id = $1
LIMIT 1;-- name: UpdateAuthor :one
set name = $2,
bio = $3
WHERE id = $1
RETURNING *;-- name: DeleteAuthor :exec
WHERE id = $1;-- name: ListAuthors :many
ORDER BY name;
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 consult the sqlc documentation if your model is more complex.
Generating the Golang code
sqlc needs to be configured using the sqlc.yaml configuration file: