How to write a REST API with Go and PostgreSQL

18. August 2022
| No Comments

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
SELECT *
FROM authors
WHERE id = $1
LIMIT 1;-- name: UpdateAuthor :one
UPDATE authors
set name = $2,
    bio  = $3
WHERE id = $1
RETURNING *;-- name: DeleteAuthor :exec
DELETE
FROM authors
WHERE id = $1;-- name: ListAuthors :many
SELECT *
FROM authors
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:

Full Article
Golang Tutorials
| Tags: , ,