Golang Database Library and ORM ExampleDecember 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:
- Create - Create a single record
- List - Returns the first 30 records of a resource
- Get - Get a single record
- Update - Update a single field
- Delete - Delete a record
- One-To-Many - Eager load one to many relationship between two tables
- Many-To-Many - Eager load many to many relationships between two tables using a pivot table
- Dynamic List - Return a record with active users, sort by field, and pagination
- (coming!) Transaction - Handle transaction, rollback on error
The runnable code is available in the repository https://github.com/gmhafiz/golang-database-library-orm-example.
This is the most popular library for purists and often the most recommended one. It is the closest
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.
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 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 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 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.
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:
Simple CRUD operation
We will look at how easy/hard it is for create, read, update, and delete operations for
We list all addresses of a particular country.
Since a user can have many addresses, and an address can hold many user, we look at how we can list them all.
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
last namein ascending order. An example url query will look like this:
We will retrieve a new record after creating one.
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
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