REST API with Echo, Sqlc, Migrate and Postgres

Source

1. Project Overview

In this project we are gonna built a simple rest api with following structure

POST /authors
GET /authors
GET /authors/len
GET /authors/:id
PATCH /authors/:id
DELETE /authors/:id
POST /authors/:id/books
GET /authors/:id/books
GET /authors-and-books

Database Table

CREATE TABLE author (
	id UUID PRIMARY KEY,
	name VARCHAR NOT NULL,
	bio TEXT NOT NULL DEFAULT '',
	created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE book (
	id SERIAL PRIMARY KEY,
	name VARCHAR NOT NULL,
	author_id UUID NOT NULL REFERENCES author(id) ON DELETE CASCADE
);

2. Tool Installation

Install Docker

Mac Installation of other tools

brew install make
brew install migrate
brew install sqlc

3. Repo and Environment Setup

a. Repo Structure

author-api/
├── cmd/
│   └── server/
│       └── main.go                    
├── database/
│   ├── migrations/
│   │   ├── 00001_initial.up.sql       
│   │   └── 00001_initial.down.sql
│   └── queries/
│       └── author.sql                 
├── internal/
│   ├── handler/
│   │   ├── handler.go
│   │   └── author.go
│   ├── service/
│   │   └── author.go
│   └── repository/
│       ├── db.go                      
│       ├── models.go                  
│       └── author.sql.go
├── .env
├── sqlc.yaml                          
├── Makefile
├── go.mod                             
├── go.sum
└── README.md

b. Makefile

Create a Makefile to easily spin up the development environment

DATABASE_URL = postgres://postgres:111111@localhost:5432/book?sslmode=disable

.PHONY: postgres migrate sqlc run

postgres:
	@echo "==> 1/4 starting postgres"
	@docker start book-postgres 2>/dev/null || docker run -d --name book-postgres \
		-p 5432:5432 -e POSTGRES_PASSWORD=111111 -e POSTGRES_DB=book postgres
	@sleep 3

migrate:
	@echo "==> 2/4 running migrations"
	migrate -path ./database/migrations -database "$(DATABASE_URL)" up

sqlc:
	@echo "==> 3/4 generating sqlc code"
	sqlc generate

run: postgres migrate sqlc
	@echo "==> 4/4 starting server on :1323"
	go run ./cmd/server

Docker with Postgres

  • Run the postgres inside docker container with name=book=postgres, with expose port 5432, with postgres password 111111 and postgres db book
docker run -d --name book-postgres \
		-p 5432:5432 -e POSTGRES_PASSWORD=111111 -e POSTGRES_DB=book postgres

Migrate

  • Migrate the migrations file in database/migrations to the docker postgres db
migrate -path ./database/migrations -database "$(DATABASE_URL)" up

Sqlc

  • Generate the go repository code based on the config in sqlc.yaml
sqlc generate

Run the server

go run ./cmd/server

4. Development

a. initialize project and download dependencies

go mod init github.com/<github-name>/author-api
go get github.com/labstack/echo/v5
go get github.com/jackc/pgx/v5
go get github.com/joho/godotenv
go get github.com/google/uuid

a. repository

Create .env file

touch .env

.env

DATABASE_URL="postgresql://postgres:111111@localhost:5432/author?sslmode=disable"

Create migration file

migrate create -ext sql -seq -digits 5 -dir database/migrations initial

00001_initial.up.sql

CREATE TABLE author (
	id UUID PRIMARY KEY,
	name VARCHAR NOT NULL,
	bio TEXT NOT NULL DEFAULT '',
	created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE book (
	id SERIAL PRIMARY KEY,
	name VARCHAR NOT NULL,
	author_id UUID NOT NULL REFERENCES author(id) ON DELETE CASCADE
);

00001_initial.down.sql

DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS author;

Create sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    queries: "database/queries"
    schema: "database/migrations"
    gen:
      go:
        package: "repository"
        out: "internal/repository"
        sql_package: "pgx/v5"
        emit_json_tags: true                    # to add json:"id" to the model
        emit_pointers_for_null_types: true      # change to *string type where the zero value is nil to only partial update provided column
        overrides:
        - db_type: "uuid"
          go_type:
            import: "github.com/google/uuid"
            type: "UUID"
        - db_type: "timestamptz"
          go_type: "time.Time"

For more information about sqlc, can take a look on https://docs.sqlc.dev/en/latest/tutorials/getting-started-postgresql.html

b. service

internal/service/author.go

package service

import (
	"context"
	"errors"

	"github.com/Fulim13/author-api/internal/repository"
	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
)

var (
	ErrAuthorNotFound = errors.New("author not found")
)

type DB interface {
	repository.DBTX
	Begin(ctx context.Context) (pgx.Tx, error)
}

type AuthorService struct {
	db   DB
	repo *repository.Queries
}

func NewAuthorService(db DB) *AuthorService {
	return &AuthorService{
		db:   db,
		repo: repository.New(db),
	}
}

func (s *AuthorService) withTx(ctx context.Context, fn func(q *repository.Queries) error) error {
	tx, err := s.db.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx)

	if err := fn(s.repo.WithTx(tx)); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

func (s *AuthorService) CreateAuthor(ctx context.Context, name string, bio string) (repository.Author, error) {
	return s.repo.CreateAuthor(ctx, repository.CreateAuthorParams{
		ID:   uuid.New(),
		Name: name,
		Bio:  bio,
	})
}

func (s *AuthorService) ListAuthors(ctx context.Context) ([]repository.Author, error) {
	authors, err := s.repo.ListAuthors(ctx)
	if err != nil {
		return nil, err
	}
	if authors == nil {
		authors = []repository.Author{}
	}
	return authors, nil
}

func (s *AuthorService) CountAuthors(ctx context.Context) (int64, error) {
	return s.repo.CountAuthors(ctx)
}

func (s *AuthorService) GetAuthor(ctx context.Context, id uuid.UUID) (repository.Author, error) {
	author, err := s.repo.GetAuthor(ctx, id)
	if errors.Is(err, pgx.ErrNoRows) {
		return repository.Author{}, ErrAuthorNotFound
	}
	return author, err
}

func (s *AuthorService) UpdateAuthor(ctx context.Context, id uuid.UUID, name, bio *string) error {
	rows, err := s.repo.UpdateAuthor(ctx, repository.UpdateAuthorParams{
		ID:   id,
		Name: name,
		Bio:  bio,
	})
	if err != nil {
		return err
	}
	if rows == 0 {
		return ErrAuthorNotFound
	}
	return nil
}

func (s *AuthorService) DeleteAuthor(ctx context.Context, id uuid.UUID) error {
	rows, err := s.repo.DeleteAuthor(ctx, id)
	if err != nil {
		return err
	}
	if rows == 0 {
		return ErrAuthorNotFound
	}
	return nil
}

func (s *AuthorService) CreateBook(ctx context.Context, authorId uuid.UUID, name string) (repository.Book, error) {
	var book repository.Book

	err := s.withTx(ctx, func(q *repository.Queries) error {
		if _, err := q.GetAuthorForShare(ctx, authorId); err != nil {
			if errors.Is(err, pgx.ErrNoRows) {
				return ErrAuthorNotFound
			}
			return err
		}

		var err error
		book, err = q.CreateBook(ctx, repository.CreateBookParams{
			Name:     name,
			AuthorID: authorId,
		})
		return err
	})

	if err != nil {
		return repository.Book{}, err
	}
	return book, nil
}

func (s *AuthorService) ListBooks(ctx context.Context, authorId uuid.UUID) ([]repository.Book, error) {
	books, err := s.repo.ListBooks(ctx, authorId)
	if err != nil {
		return nil, err
	}
	if books == nil {
		books = []repository.Book{}
	}
	return books, nil
}

func (s *AuthorService) ListAuthorsAndBooks(ctx context.Context) ([]repository.ListAuthorsAndBooksRow, error) {
	booksAndAuthors, err := s.repo.ListAuthorsAndBooks(ctx)
	if err != nil {
		return nil, err
	}
	if booksAndAuthors == nil {
		booksAndAuthors = []repository.ListAuthorsAndBooksRow{}
	}
	return booksAndAuthors, nil
}

c. handler

internal/handler/author.go

package handler

import (
	"errors"
	"net/http"

	"github.com/Fulim13/author-api/internal/service"
	"github.com/google/uuid"
	"github.com/labstack/echo/v5"
)

type AuthorHandler struct {
	service *service.AuthorService
}

func NewAuthorHandler(service *service.AuthorService) *AuthorHandler {
	return &AuthorHandler{
		service: service,
	}
}

type CreateAuthorRequest struct {
	Name string `json:"name"`
	Bio  string `json:"bio"`
}

type UpdateAuthorRequest struct {
	Name *string `json:"name"`
	Bio  *string `json:"bio"`
}

type CreateBookRequest struct {
	Name string `json:"name"`
}

func authorID(c *echo.Context) (uuid.UUID, error) {
	id, err := uuid.Parse(c.Param("id"))
	if err != nil {
		return uuid.Nil, echo.NewHTTPError(http.StatusBadRequest, "invalid author id")
	}
	return id, nil
}

func (h *AuthorHandler) createAuthor(c *echo.Context) error {
	var reqBody CreateAuthorRequest
	if err := c.Bind(&reqBody); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, "invalid request body")
	}

	if reqBody.Name == "" {
		return echo.NewHTTPError(http.StatusBadRequest, "name is required")
	}

	if reqBody.Bio == "" {
		return echo.NewHTTPError(http.StatusBadRequest, "bio is required")
	}

	author, err := h.service.CreateAuthor(c.Request().Context(), reqBody.Name, reqBody.Bio)
	if err != nil {
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.JSON(http.StatusCreated, author)
}

func (h *AuthorHandler) listAuthors(c *echo.Context) error {
	authors, err := h.service.ListAuthors(c.Request().Context())
	if err != nil {
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.JSON(http.StatusOK, authors)
}

func (h *AuthorHandler) countAuthors(c *echo.Context) error {
	count, err := h.service.CountAuthors(c.Request().Context())
	if err != nil {
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.JSON(http.StatusOK, map[string]int64{"count": count})
}

func (h *AuthorHandler) getAuthor(c *echo.Context) error {
	id, err := authorID(c)
	if err != nil {
		return err
	}

	author, err := h.service.GetAuthor(c.Request().Context(), id)
	if err != nil {
		if errors.Is(err, service.ErrAuthorNotFound) {
			return echo.NewHTTPError(http.StatusNotFound, "author not found")
		}
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.JSON(http.StatusOK, author)
}

func (h *AuthorHandler) updateAuthor(c *echo.Context) error {
	id, err := authorID(c)
	if err != nil {
		return err
	}

	var reqBody UpdateAuthorRequest
	if err := c.Bind(&reqBody); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, "invalid request body")
	}

	if reqBody.Name == nil && reqBody.Bio == nil {
		return echo.NewHTTPError(http.StatusBadRequest, "name or bio is required")
	}

	if reqBody.Name != nil && *reqBody.Name == "" {
		return echo.NewHTTPError(http.StatusBadRequest, "name cannot be empty")
	}

	err = h.service.UpdateAuthor(c.Request().Context(), id, reqBody.Name, reqBody.Bio)
	if err != nil {
		if errors.Is(err, service.ErrAuthorNotFound) {
			return echo.NewHTTPError(http.StatusNotFound, "author not found")
		}
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.NoContent(http.StatusNoContent)
}

func (h *AuthorHandler) deleteAuthor(c *echo.Context) error {
	id, err := authorID(c)
	if err != nil {
		return err
	}

	err = h.service.DeleteAuthor(c.Request().Context(), id)
	if err != nil {
		if errors.Is(err, service.ErrAuthorNotFound) {
			return echo.NewHTTPError(http.StatusNotFound, "author not found")
		}
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.NoContent(http.StatusNoContent)
}

func (h *AuthorHandler) createBook(c *echo.Context) error {
	authorId, err := authorID(c)
	if err != nil {
		return err
	}

	var reqBody CreateBookRequest
	if err := c.Bind(&reqBody); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, "invalid request body")
	}

	if reqBody.Name == "" {
		return echo.NewHTTPError(http.StatusBadRequest, "name is required")
	}

	book, err := h.service.CreateBook(c.Request().Context(), authorId, reqBody.Name)

	if err != nil {
		if errors.Is(err, service.ErrAuthorNotFound) {
			return echo.NewHTTPError(http.StatusNotFound, "author not found")
		}
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}

	return c.JSON(http.StatusCreated, book)
}

func (h *AuthorHandler) listBooks(c *echo.Context) error {
	authorId, err := authorID(c)
	if err != nil {
		return err
	}
	books, err := h.service.ListBooks(c.Request().Context(), authorId)
	if err != nil {
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.JSON(http.StatusOK, books)
}

func (h *AuthorHandler) listAuthorsAndBooks(c *echo.Context) error {
	authorsAndBooks, err := h.service.ListAuthorsAndBooks(c.Request().Context())
	if err != nil {
		return echo.NewHTTPError(http.StatusInternalServerError, "internal server error")
	}
	return c.JSON(http.StatusOK, authorsAndBooks)
}

internal/handler/handler.go

package handler

import "github.com/labstack/echo/v5"

func Routes(e *echo.Echo, authorHandler *AuthorHandler) {
	e.POST("/authors", authorHandler.createAuthor)
	e.GET("/authors", authorHandler.listAuthors)
	e.GET("/authors/len", authorHandler.countAuthors)
	e.GET("/authors/:id", authorHandler.getAuthor)
	e.PATCH("/authors/:id", authorHandler.updateAuthor)
	e.DELETE("/authors/:id", authorHandler.deleteAuthor)
	e.POST("/authors/:id/books", authorHandler.createBook)
	e.GET("/authors/:id/books", authorHandler.listBooks)
	e.GET("/authors-and-books", authorHandler.listAuthorsAndBooks)
}

d. main.go

cmd/server/main.go

package main

import (
	"context"
	"log"
	"os"

	"github.com/Fulim13/author-api/internal/handler"
	"github.com/Fulim13/author-api/internal/service"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/joho/godotenv"
	"github.com/labstack/echo/v5"
	"github.com/labstack/echo/v5/middleware"
)

func main() {
	err := godotenv.Load()
	if err != nil {
		log.Fatal("Error loading .env file")
	}

	ctx := context.Background()
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatalf("Failed to connect db: %v", err)
	}
	defer pool.Close()

	authorService := service.NewAuthorService(pool)
	authorHandler := handler.NewAuthorHandler(authorService)

	e := echo.New()
	e.Use(middleware.RequestLogger())
	e.Use(middleware.Recover())
	handler.Routes(e, authorHandler)

	if err := e.Start(":1323"); err != nil {
		e.Logger.Error("failed to start server", "error", err)
	}
}

5. Test the Endpoint

Run make run then can start test the endpoints

# Create Author
curl -s localhost:1323/authors \
	-H 'Content-Type: application/json' \
	-d '{"name": "fulim", "bio": "An Author of this tutorial"}' | jq

# List Authors
curl -s localhost:1323/authors | jq

# Count Authors
curl -s localhost:1323/authors/len | jq

# Get Author
curl -s localhost:1323/authors/author-id | jq

# Update Author
curl -s -i -X PATCH localhost:1323/authors/author-id \
	-H 'Content-Type: application/json' \
	-d '{"name": "Fu Lim Wong", "bio": "New Author"}'

# Delete Author
curl -s -i -X DELETE localhost:1323/authors/author-id

# Create Book
curl -s localhost:1323/authors/author-id/books \
	-H 'Content-Type: application/json' \
	-d '{"name": "Go Web Tutorial"}' | jq

# List Book
curl -s localhost:1323/authors/author-id/books | jq

# List Authors and Books
curl -s localhost:1323/authors-and-books | jq

# Create/Update Author / Create Book - invalid request body
curl -s -i localhost:1323/authors \
	-H 'Content-Type: application/json' \
	-d '{"na}'
curl -s -i -X PATCH localhost:1323/authors/98843538-6cd4-4811-b16e-cb58dbb30218 \
	-H 'Content-Type: application/json' \
	-d '{"na}'
curl -s -i localhost:1323/authors/98843538-6cd4-4811-b16e-cb58dbb30218/books \
	-H 'Content-Type: application/json' \
	-d '{"na}'

# Get/Update/Delete Author / Create/List Book - invalid author id
curl -s -i localhost:1323/authors/author-id
curl -s -i -X PATCH localhost:1323/authors/author-id \
	-H 'Content-Type: application/json' \
	-d '{"name": "Fu Lim Wong", "bio": "New Author"}'
curl -s -i -X DELETE localhost:1323/authors/author-id
curl -s -i localhost:1323/authors/author-id/books \
	-H 'Content-Type: application/json' \
	-d '{"name": "Go Web Tutorial"}'
curl -s -i localhost:1323/authors/author-id/books

# Get/Update/Delete Author / Create Book - author not found
curl -s -i localhost:1323/authors/98843538-6cd4-4811-b16e-cb58dbb30218
curl -s -i localhost:1323/authors/98843538-6cd4-4811-b16e-cb58dbb30218/books \
	-H 'Content-Type: application/json' \
	-d '{"name": "Go Web Tutorial"}'
curl -s -i localhost:1323/authors/98843538-6cd4-4811-b16e-cb58dbb30218/books \
	-H 'Content-Type: application/json' \
	-d '{"name": "Go Web Tutorial"}'
curl -s -i localhost:1323/authors/98843538-6cd4-4811-b16e-cb58dbb30218/books \
	-H 'Content-Type: application/json' \
	-d '{"name": "Go Web Tutorial"}'

References

  1. https://echo.labstack.com/
  2. https://github.com/golang-migrate/migrate
  3. https://docs.sqlc.dev/en/latest/
  4. https://github.com/jackc/pgx
  5. https://github.com/joho/godotenv