GORM WITH MSSQL

Once you dive into a big project, you might look for an ORM since it handles all the mappings, generates the queries, creates tables(for code-first approach), makes migrations and more.

My previous apps developed using Go were relatively small-sized projects and were a good chance to learn the language. Therefore, I did not consider using an ORM and used denisenkom’s MSSQL driver and liked it! It is very close to ADO.NET.

Recently, I needed to use an ORM and started learning GORM. Although it is well documented, there is a lack of information and I wanted to share my experiences.

The first thing I noticed is GORM’s connection string for MS Sql Server(MSSQL) is different from Entity Framework and even from denissenkom’s library. The below example shows the difference. It’s better to be careful about it.

Entity Framework
Server=127.0.0.14;Database=Besiktas;User Id=Kartal;Password=1903;
GORM
sqlserver://Kartal:1903@127.0.0.14:1433?database=Besiktas

As you can notice the connection string should start with sqlserver:// following UserId:Password then add an @ and IpAddress:PortNumber and lastly add a question mark with database name(like a query string).

Notice that @ sign is a separator and using it in your password will cause problems.

The second thing I noticed is nearly in each example there is gorm.Model in the table model(Entity for .Net).

type User struct {
  gorm.Model
  Name         string
  Age          sql.NullInt64
  Gender       bool
}

gorm.Model is basically a struct which has common properties that every table model nearly has. Below is the definition of the gorm.Model. I personally don’t consider using it but I’ll explain the reason later.

type Model struct {
  ID        uint `gorm:"primary_key"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt *time.Time
}

If your tables name are different than your table model names, there are two ways to override it.

One is to override it while you’re querying, however, this method is not useful and possible to produce bugs.

// Since my main stack is .Net, I tend to name like below
type UserEntity struct {
  gorm.Model
  Name         string
  Age          sql.NullInt64
  Gender       bool
}
// Let's assume you opened your connection
err := db.Table("Users").Select("Name").Find(&user, "Id = ?", 1903).Error

In this situation, it is highly possible to forget specifying the table name.

Other method is to write an extension method for your model. The method must be named as TableName without any parameters, GORM handes the rest.

func (UserEntity) TableName() string {
    return "Users"
}
// Then the query will be simple
err := db.Select("Name").Find(&user, "Id = ?", 1903).Error

Moreover, you can change your default table naming with a method. For instance, websites developed using Able Commerce have table names starting with “ac_”. This can be a good solution for that.

gorm.DefaultTableNameHandler = func (db *gorm.DB, defaultTableName string) string  {
  return "ac_" + defaultTableName;
}

Other thing can drive you crazy. GORM converts every property into snail case. It took me a while to figure that out.

Turns out there is a magical method called Debug and it prints the generated query in the output pane.

err := db.Debug().Select("Name").Find(&user, "Id = ?", 1903).Error

To match your columns with your properties you should use GORM tags.

type UserEntity struct {
  ID           uint          `gorm:"primary_key"`
  Name         string        `gorm:"column:Name"`
  Age          sql.NullInt64 `gorm:"column:Age"`
  Gender       bool          `gorm:"column:Gender"`
}

If there are multiple properties that you want to assign, you need to separate them with a semicolon.

type UserEntity struct {
  ID           uint          `gorm:"primary_key;column:ID"`
  Name         string        `gorm:"column:Name;type:varchar(100)"`
  Age          sql.NullInt64 `gorm:"column:Age"`
  Gender       bool          `gorm:"column:Gender"`
}

As I mention above, you can check whether the generated query is right/efficient or not by using Debug method. If you don’t like it, there is a way to write raw queries.

userId := 1903
db.Raw("SELECT Name FROM Users WHERE Id = ?", userId).Find(&user)

The reason I would not prefer using gorm.Model is I won’t be able to give them special tags.

I tried to give a quick introduction to GORM and clarify some parts of it. Hope it helps you and solve some of your problems.