Golang Database Library and ORM Example

December 14, 2021

There are a multitude of ways to interact with the database in Go. This series of posts will show how different popular libraries and ORM available of Go are used. They are sqlx, sqlc, Gorm, sqlboiler, and ent

Table of Contents

This is part of a series of blog posts including:

The runnable code is available in the repository https://github.com/gmhafiz/golang-database-library-orm-example.

sqlx

This is the most popular library for purists and often the most recommended one. It is the closest to the database/sql package in the standard library. It requires you to write your own SQL queries, yet it has some convenience method to make scanning results to a Go struct easier.

sqlc

Like sqlx, you also write your own SQL, but sqlc can generate a lot of boilerplate that you would have to write if you were using sqlx. You only need to install sqlc with go install github.com/kyleconroy/sqlc/cmd/sqlc@latest and set the config file so that sqlc knows where to find your queries and where to write the generated code. Each of your query needs to be annotated with a desired method name, as well the expected number of record - one or many. The command sqlc generate generates many boilerplate that you would have write otherwise.

gorm

Gorm is the earliest and the most popular ORM library that still actively maintained till today. Instead of writing SQL queries, you write a Go struct for each of your tables and Gorm will handle creation of the tables in the database. Further tuning is done using struct tags. This is an example of code-first library.

sqlboiler

sqlboiler is opposite in the approach from gorm where is it a database-first approach. You provide a config file (sqlboiler.toml) with your database credentials, and it will learn the schema and generate tailored ORMs, including all structs for all CRUD operations. Hardly any magic strings are necessary. Like sqlc, you must remember to re-generate to update this ORM. It this is a good idea to put this command in your build pipeline.

ent

Ent is the newest entry in the list, used to be part of facebook community repository, now it is in its own repository. It has the most features and the easiest ORM to use.

It uses a code-first approach. You write Go methods to define a table fields and its relationships.

Operations

The blog series will demonstrate several operations that I think most applicable to a common CRUD Go api. The operations that we will compare and contrast are:

  1. Simple CRUD operation

    We will look at how easy/hard it is for create, read, update, and delete operations for users table.

  2. 1-to-Many queries

    We list all addresses of a particular country.

  3. Many-to-many queries

    Since a user can have many addresses, and an address can hold many user, we look at how we can list them all.

  4. Dynamic list filter from query parameter

    Pagination is a common use case. We will see how these libraries and ORMs deal with limiting the number of records being returned. Say the client want to sort our users list by last name in ascending order. An example url query will look like this:

https://localhost:3080/api/users?sort=last_name,asc
  1. Transaction

    We will retrieve a new record after creating one.

Schema

Database

We have a simple schema but still allows us to query one-to-many and many-to-many relationships.

To make things interesting, we have nullable columns, so we can wee how serialization to json works.

We will also see how we prevent Password field from being serialized and sent to the client. That is something that we do not want to leak.

First up, we see common CRUD operations are done, starting with Create:

Next > Golang Database Library Orm Example Create