Building Microservices in Go: Accessing PostgreSQL - ORM/ORM-like
Tags: go, postgres
Published: 2021-03-23 06:56:01

The full code example mentioned in this post is available [on Github](https://github.com/MarioCarrion/videos/tree/main/2021/02/05-go-database-postgresql-part-2), please make sure to read the [README](https://github.com/MarioCarrion/videos/blob/main/2021/02/05-go-database-postgresql-part-2/README.md) for specifics.

Similar to the previous post, the examples below use a database table with the following structure:

```sql
-- db/migrations/20210126023417_create_names_table.up.sql
CREATE TABLE names (
	nconst              varchar(255),
	primary_name        varchar(255),
	birth_year          varchar(4),
	death_year          varchar(4) DEFAULT '',
	primary_professions varchar[],
	known_for_titles    varchar[]
);
```

## Using ORM/ORM-like packages for accessing PostgreSQL databases

Object Relational Mapping tools are chosen because of our familiarity in other programming languages, lack of SQL knowledge, not wanting to write SQL statements manually or not enough time because we do need to build the _actual software_; all of those are fair reasons and it's a way to speed up initial development for sure.

Understanding the pros and cons of any ORM is important because depending on the length of service of our microservice of the complexity of the instructions we execute we could face performance issues in the long term.

I will be covering two of the most popular ORM packages out there, both of them are not PostgreSQL-specific and they do support other databases:

* [go-gorm/gorm](https://pkg.go.dev/github.com/go-gorm/gorm), and
* [volatiletech/sqlboiler](https://pkg.go.dev/github.com/volatiletech/sqlboiler)

## go-gorm/gorm

[`go-gorm/gorm`](https://github.com/go-gorm/gorm) is one of the most popular ORMs in Go, mature and with good documentation, to date it supports _sqlite_, _mysql_, _postgres_ and _sqlserver_.

The way `gorm` works is by implementing struct types meant to represent database tables, or models; there are [a few conventions](https://gorm.io/docs/conventions.html) they suggest that should allow you to quickly implement the minimum needed for interacting with the database; nothing is really enforced and there are options to override those conventions.

For example, if the struct type defines an `ID` field that one is assumed to be the primary key, however there's the option to explicitly indicate `gorm` the field to use and even the column name as well, this is better demonstrated with the code below:

```go
// postgresql_gorm.go
type gormNames struct {
	NConst    string `gorm:"primaryKey;column:nconst"`
	Name      string `gorm:"column:primary_name"`
	BirthYear string
	DeathYear string
}

func (gormNames) TableName() string {
	return "names"
}
```

Above we declared our struct type `gormNames`, it represents the database table `names` via the explicitly implemented `TableName` method, and we are telling `gorm` some field names (`NConst` and `Name`) happen to have different column names and those field names shouldn't be used to infer the final column names.

Using it is as easy as: 

```go
var result gormNames

if tx := p.db.Where("nconst = ?", nconst).First(&result); tx.Error != nil {
	return Name{}, tx.Error
}

return Name{
	NConst:    result.NConst,
	Name:      result.Name,
	BirthYear: result.BirthYear,
	DeathYear: result.DeathYear,
}, nil
```

## volatiletech/sqlboiler

[`volatiletech/sqlboiler`](https://github.com/volatiletech/sqlboiler) is another ORM available for Go, it's _database-first_ instead of _code-first_ like gorm, what this means in practice is that `sqlboiler` generates type-safe Go code to act as an ORM, to date it supports _postgres_, _mysql_, _sqlserver_, _sqlite_ and _cockroach db_.

The way `sqlboiler` works is better explained with the following code:

```go
// postgresql_boilerl.go

//go:generate sqlboiler --wipe --no-tests psql

func (p *PostgreSQLboiler) FindByNConst(nconst string) (Name, error) {
	result, err := models.FindName(context.Background(), p.db, nconst)
	if err != nil {
		return Name{}, err
	}

	return Name{
		NConst:    result.Nconst,
		Name:      result.PrimaryName.String,
		BirthYear: result.BirthYear.String,
		DeathYear: result.DeathYear.String,
	}, nil
}
```

`sqlboiler` uses a [configuration file](https://github.com/MarioCarrion/videos/blob/main/2021/02/05-go-database-postgresql-part-2/sqlboiler.toml) to [generate models](https://github.com/MarioCarrion/videos/tree/main/2021/02/05-go-database-postgresql-part-2/models) specific to the tables available in the database, you can see this in action in the call referencing `models.FindName`, this `models` package contains all the autogenerated code that `sqlboiler` builds for us.

As you can see, compared to `gorm`, there's no need to manually define the struct types meant to represent database tables, `sqlboiler` generates all that code, however it enforces [a few rules](https://github.com/volatiletech/sqlboiler#requirements--pro-tips) to make everything work.

## Final thoughts

ORMs are a way to abstract accessing databases, making the decision to use them depends on different things, they clearly have their pros (like speeding up development) but also have their cons (like learning a concrete package to talk to the database), what is important when choosing to use ORMs (or not) is to know why, and more importantly make a note somewhere of that decision, because perhaps it's needed to revisit that decision in the future.

Talk to you later.

**Keep it up. Don't give up.**