Golang Database Library and ORM Example - Introduction

There are a multitude of ways to interact with a SQL database in Go. The most obvious path is to simply use database/sql package from the standard library plus a database driver. It is easy to use and can be sufficient to meet all of your requirements. Using raw SQL directly means you can leverage what you already know, SQL, and craft complex queries in many ways that an ORM may not support. However, retrieving (scanning in Go lingo) results from database is verbose and can be tedious - something that alternatives shine. This series of posts will show how different popular libraries and ORM available of Go are used. They are sqlx, sqlc, squirrel, gorm, sqlboiler, and ent.

This post is only an introduction to these packages. I will briefly touch on each of them on what it is and database operations that we are going to test with. In the next posts, we will cover common use cases for each. Full source code is available at https://github.com/gmhafiz/golang-database-library-orm-example.

Table of Contents

sqlx

This is the most popular library for purists and often the most recommended one. 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 generates a lot of Go boilerplate that you would otherwise have to write if you were using sqlx. You only need to install sqlc once, and set a config file. Then for each of your query, it needs to be annotated with a desired method name, and the expected number of record - one or many. Everytime you run sqlc generate, it will create Go methods from your sql queries that both queries (or execute), and scans.

squirrel

update(12 July 2022)

Squirrel is an sql query builder. Unlike both sqlx and sqlx, you do not need to manually write sql queries. Instead, squirrel provides helper functions like Select(), Where(), From(), as well assq.Eq{} for equality.

gorm

Gorm is among the earliest and the most popular ORM library that still actively maintained till today. Instead of writing SQL queries, you write a Go struct with correct struct tags for each of your tables and gorm will handle creation of the tables in the database. Further tuning is done using struct tags. Among many ORMs, this is an example of code-first library. Query building is fairly decent - a lot of (type) guessing is needed because of interface[} usage everywhere, and order or methods are important. Scanning database results however is easy thanks to reflection.

sqlboiler

Sqlboiler is opposite in the approach from gorm where it is a database-first approach. You provide a config file (sqlboiler.toml) with your database credentials, and it will learn the schema and generate custom-tailored ORM specific to your database, including all structs for all CRUD operations. As a result, you can rely and on generated methods and constants which mean you hardly need any magic strings.

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. 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 which can take some learning curve. Like both sqlc and sqlboiler, you must remember to re-generate the ORM.

You get a more statically typed ORM compared to sqlboiler. Handling one-to-many or many-to-many is easy. Query building however, can be a lot more verbose.

Spectrum

With the many libraries and ORMs available for Go, it can be hard to visualise where do these tools stand from, ranging from pure sql (hand-rolled) queries to traditional ORM.

Above is an illustration comparing the mentioned libraries in a spectrum between close-to-the-metal, or raw SQL queries to full ORM capabilities.

Both sqlx and sqlc are very close to the standard database/sql library with sqlc inches ahead. Anything you give into these libraries are sent to the wire almost without any changes.

I put squirrel in the middle where it is the most builder-ish library amongst all.

Ent has to be the most ORM-ish amongst all, so it is placed on the furthest right. Sqlboiler is not too far off because it still requires some raw SQL in some cases.

That leaves gorm, a weak-ish ORM (full of interface{} everywhere!) but with excellent query builder capabilities.

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 test two things:

   • We will look at how easy/hard it is for create, read, update, and delete operations for users table.
   • Given an array of IDs, how it deals with WHERE IN(?, ...)

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

• We may want to return a list with specified property. For example, we want to return users whose last name is ‘Campbell’. Going further, we may want to combine several properties, say last name is ‘Campbell’ and favourite colour is ‘blue’. A URL can look like this:

https://localhost:3080/api/users?last_name=Campbell&favourite_colour=blue

6. Transaction

   We will retrieve a new record after creating one.

7. SQL Injection

Nowadays, safety against sql injections should not be an issue to any developers. Correct use of placeholders and prepared statement protect against this security vulnerabilities. Sanitisation of user input will also help in preventing this. Yet, we will still look over how they behave when we purposely try to give malicious user input.

Schema

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

• A country has many addresses
• A user can have many addresses; and an address can hold many users. These tables are joined by the pivot user_addresses table.

To make things interesting, we have nullable columns denoted with ? in the erd above, so we can see how serialization to json works.

The enum is an interesting case especially when it comes to libraries or ORM that generate codes, will they create type for that enum or not? If yes, how do they handle it when Go does not have a strong enum support.

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. Generally, we want to make a data transform so that we can control what the client receives. This transform technique also prevents accidental leaking of data when we create new columns in a table. Nevertheless, we will see if these libraries or ORMs has any mechanism to prevent such sensitive data leak.

Files

In the repository, each library/orm are placed inside /db folder. Each folder under /db contains a file for each section in this blog series such as crud.go for CRUD stuff. Each folder contains helper functions such as database.go for library initialisation, handler.go for controller/handler that receives a request, transform into internal request struct, call data access layer, then finally transform into a JSON response.

Outside of this folder, we have several files shared by all. A model.go contains any database structs. Any query param or JSON payload are parsed by filter.go.

A quick note on the handler layer - real world API must have validation performed on client requests; but is omitted for simplicity.

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

Next > Golang Database Library Orm Example Create