Intricacies of working with GORM

In this article I am going to show you how to fight all sorts of problems like bugs and stuff and also hopefully make working with GORM a little easier.

Introduction

GORM developers claim it is a fantastic ORM, and in a way, it is indeed fantastic, but a lack of a proper documentation and a bunch of inaccuracies here and there may complicate your experience. In this article I am going to tell you all about problems I encountered while trying to do some pretty ordinary things. Speaking of which, I made a module that contains a few common helper functions I use on daily basis. Feel free to check it out: https://github.com/Wissance/gwuu.

Creating new objects

I write my code in such a way that it is easy to create new model objects using ORM and DBMS (auto-incrementing primary key, default column values and so on).

GORM provides us with a convenient embeddable struct gorm.Model we can use to include all the basic fields such as id, creation date, update date and delete date into each and every model we are going to create. It looks pretty much like this:

type Group struct { 
gorm.Model
// other fields
}

Group instance identifier is assigned to ID field.

func CreateGroupInfoDto (model *model.Group) dto.GroupInfo {
    dto := dto.GroupInfo{Id: model.ID, Name: model.Name,   Description: model.Description}
    return dto
}

A major problem I ran into is that when I call Save() a new object does not get created. Debugging this reveals that a primary key is assigned 0:

db .Debug .Save (&model )

Unfortunately, I could not solve this problem, because frankly it is not entirely clear what is causing it (maybe it is somehow connected to related models). In order to go around this problem, we can do the following:

1. Get maximum identifier from the given table using an aggregate function sql MAX (), add one and use the result as a new ID

2. Pass our model to Create method

It is worth mentioning that the actual process of getting a value from aggregate functions is a little different from what was described in documentation. The difference is you can only get a value using struct type. (function GetNextTableId () in https ://github .com /Wissance /gwuu /blob /master /gorm /db _utils .go ) It’s a generalized function which can be used in any projects to get a new primary key for any table. All you got to do is, pass a table name as its parameter. Although this code is available on github I will show a piece of it here. Because it demonstrates another problem.

type Internal struct {
    Id uint
}
var maxId Internal
getMaxIdQuery := stringFormatter.Format(“SELECT MAX(id) As Id FROM {0};”, table);
db.Raw(getMaxIdQuery).Scan(&maxId)

The thing is, I cannot use integer variable (var maxId int ) to call aggregate functions using Raw (). In order to get a value from Scan you always have to use struct. This is precisely what was implemented inside the aforementioned function. You should also use struct in order to get an average (AVG), a sum (SUM ), a minimum (MIN) and a count (COUNT).

Working with nullable columns

In order to be able to assign NULL to table columns we have to use types: sql sql.NullInt 32 и sql .NullString instead of int/uint and string in our models.

Suppose, the field GroupId has a type sql .NulInt 32, then if you want it to be NULL in a new model you create, just leave it unassigned. But if you want to assign some value to it(different from NULL):

if group.ID > 0 {
    playbook.GroupId.Int32 = int32(machine.GroupId)
    playbook .GroupId .Valid = true
}

It is a little bit more complicated when we want to update the model. Suppose we had some value assigned to GroupId, but we need to set it to NULL now. I could not reset it to NULL using Updates () method, so I had to explicitly assign NULL to all the fields I wanted to update and call Save () method:

var groupId sql.NullInt32
    if group.ID > 0 {
       groupId.Valid = true
       groupId.Int32 = int32(machine.GroupId)
} else {
    groupId.Int32 = 0
}
// …
playbook.GroupId = groupId
// …
db.Save(&playbook)

Intricacies of creating many-to-many relationships

Normally, creating many-to-many relationships you expect it to be implemented using a junction table which would be configured in such a way that when you delete a record from either of your tables the associated record in junction table also gets deleted. But according to GORM documentation, this should suffice:

type Playbook struct {
    gorm.Model
    // …
    Tags []Tag `gorm:”many2many:playbook_tags; constraint:OnUpdate:CASCADE, OnDelete:CASCADE;”`
    // …
}

However, such a definition along with using AutoMigrate:

db.AutoMigrate(&Tag{})
db.AutoMigrate(&Playbook{})

led to creation of a third table, but without foreign keys referencing the tables (playbooks и tags ). I managed to resolve this situation using Table function intended for explicit table specification (this approach is not so good though, because we interfere with ORM internal machinery):

db.Table(“playbook_tags”).AddForeignKey(“playbook_id”, “playbooks(id)”, “CASCADE”,“CASCADE”)
db.Table(“playbook_tags”).AddForeignKey(“tag_id”, “tags(id)”, “CASCADE”, “CASCADE”)

Now foreign keys get created with the correct configuration.

Tools to facilitate working with GORM and databases

Now I would like to discuss the second part of this article, which is creating an additional functionality that is going to make working with GORM much easier, namely:

· Getting a database connection string.

· Creating a database.

· Connecting to a database, creating it if it does not already exist.

· Deleting a database.

· Data extraction with pagination.

· A new primary key value generation.

We (Wissance , wissance .com ) created a module that implements all of the above: https://github.com/Wissance/gwuu

It is worth mentioning that functions to get connection string, create, open and delete database were thoroughly tested with Postgres, MySQL and MSSQL.

I am not going to show you usage examples for each and every function now. Most if not all of them you can find here: https://github.com/Wissance/gwuu/blob/master/gorm/db_context_test.go

Let us consider a usage example for functions to get connection string, create and delete database from functional test written for a different project. Testing process is utilizing AAA paradigm (Assign -Act -Assert). Each application test uses a separate database which it deletes once it is done. So, the code looks like this:

package managers
import (
   “database/sql”
   “encoding/json”
   “github.com/stretchr/testify/assert”
   “github.com/wissance/gwuu/gorm”
   “soar/stateMachineService/dto”
   “soar/stateMachineService/model”
   “testing”
)
func TestGetPlaybookById(t *testing.T) {
    testDbName := “test_playbook_model”
    connStr := gorm.BuildConnectionString(gorm.Postgres, “127.0.0.1”, 5432, testDbName, “developer”, “123”, “disable”)
    db := gorm.OpenDb2(gorm.Postgres, connStr, true)
    assert.NotNil(t, db)
    model.PrepareDb(db)
    insertTestData(t, db)
    expectedPlaybook := model.Playbook{Name: “scheduleExam”, Comment: “Srudent exam assignment”, Script: “..\\..\\exampleMachines\\testMachine1\\scheduleExam.json”}
    expectedPlaybook.ID = 1
    actualPlaybook := GetPlaybookById(db, 1)
    assert.NotNil(t, actualPlaybook)
    checkPlaybooks(t, &expectedPlaybook, &actualPlaybook)
    gorm.CloseDb(db)
    gorm.DropDb(gorm.Postgres, connStr)
}

As you can see from my example above, first, we build a connection string for Postgres by using BuildConnectionString function, then we use the connection string to connect to a database. The database gets created the moment we establish the connection. When we are done, we delete the database. In general, it is all pretty straightforward.

Next let us talk about a partial data extraction. Fortunately, GORM allows us to do it using its native functions and they work quite well. In this particular case we generalized data extraction in order to get universal code that behaves the same way regardless of a page number and a page size.

Paginate, https://github.com/Wissance/gwuu/blob/master/gorm/db_utils.go ). This function returns *gorm .DB to which paging was applied. To select a page with data we should use it the following way:

var playbooks []model.Playbook
db.Scopes(gorm.Paginate(page, size)).Find(&playbooks)
return playbooks

Conclusion

In this article we considered intricacies of working with GORM and despite all its flaws and documentation inaccuracies this ORM is pretty good. If you found our module useful https://github.com/Wissance/gwuu , please star our repository or create a new issue with something you would like us to implement next. Thank you for reading!