Gorm is a popular ORM library in Golang that supports the most commonly used Relational Databases. In this article, I want to talk about a subtle problem in the unique index support for Gorm. For more context, let's first briefly discuss Database Indexes and how Gorm supports them. Feel free to skip the next few sections if you already know about them, or you are not interested.

Indexes in Database

The Database Management Systems or DBMS implements a data-structure called Index to retrieve the records quickly without going through the entire Table. Depending on the specific database, there can be various types of indexes. For instance, one of the most commonly used indexes is the Primary Index that automatically gets created. It stores just the Primary key(s) of the Table and lets you quickly query the data using it.

Another use of Indexes is to put some constraints on the data. The Unique Index is one such example that enforces the uniqueness across one or more columns of the Table.

More on Unique Indexes

Let's take an example of an E-Commerce website to discuss Unique Indexes further. On such a website, there is generally a One-to-One or One-to-Many relationship between a User Account and a Credit Card. Let's say this relationship is stored in a Table called user_id_credit_cards. Then the constraint can be enforced in the Database using a Unique Index.

CREATE UNIQUE INDEX uidx_user_id_credit_card
  ON user_id_credit_cards (credit_card);

The website might choose to be a little lenient and allow the same Credit Card on multiple Accounts. However, one Credit Card can only be added to the Account once. In this case, there is a Many-to-Many relationship between User Account and Credit Cards. To enforce this constraint, we need to use a Composite Unique Index. A Composite Unique Index enforces the uniqueness of the set of columns instead of a single column. In this case, the Index will allow only a single pair of User Account and Credit Card relationship, but other Accounts can still add the same Credit Card.

CREATE UNIQUE INDEX uidx_user_id_credit_card
  ON user_id_credit_cards (user_id, credit_card);

I added a sample schema and the Unique Index on it in this Repl for you to try out: https://repl.it/@ankitrgadiya/UniqueIndex

How to use Unique Index in Gorm?

Gorm uses reflection to identify information like datatype. So, a regular Golang struct is already a Gorm model. However, Gorm defines a custom struct tag for passing in additional information about a specific column of the model. This additional information can be a constraint like NOT NULL, explicitly specifying the data type to use, and relation information, among other things.

Unique Index support is implemented by using a unique_index tag. The value of this tag will be the name of the Index. In the case of Composite Indexes, you need to set the same index name for multiple fields. Gorm will detect it, and it will create a Composite Index on those fields. The previous example can be modeled in Gorm as follows.

type User struct {
	ID   int `gorm:"not null;`
	Name string
}

type UserCreditCard struct {
	ID         int    `gorm:"not null;"`
	UserID     int    `gorm:"unique_index:uidx_user_card"`
	CreditCard string `gorm:"unique_index:uidx_user_card"`
}

func main() {
	...
	db.AutoMigrate(&User{}, &UserCreditCard{})
	...
}

The Pitfall

Notice the AutoMigrate method being invoked to perform the migration. According to the official documentation, this is the default way in Gorm for migrating the models. Now, the problem with this method is that it does not return an error. The documentation shows that it returns a pointer to the Database. So, if there are any failures while migrating, the users won't know about it.

Let's try to create a scenario where this can happen. I'll modify the models by adding the same Unique Index in both the models. This should not work because Indexes are database scoped, and so the names must be unique in a database. Gorm will try to create the Unique Index with the same name twice, which will result in an error.

type User struct {
	ID   int    `gorm:"not null;`
	Name string `gorm:"unique_index:uidx_user_card"`
}

type UserCreditCard struct {
	ID         int    `gorm:"not null;"`
	UserID     int    `gorm:"unique_index:uidx_user_card"`
	CreditCard string `gorm:"unique_index:uidx_user_card"`
}

func main() {
	...
	db.AutoMigrate(&User{}, &UserCreditCard{})
	...
}

But wait, how will we know if Gorm encounters an error? Turns out, Gorm has a debugging mode which logs all the SQL interactions. So, let's modify the call to enable the logs.

func main() {
	...
	db.Debug().AutoMigrate(&User{}, &UserCreditCard{})
	...
}

You should be able to see something like this in the log showing that the error was encountered. However, our program doesn't know about it and proceeds carelessly.

index uidx_user_card already exists

This means that the table now does not have the Unique constraint and multiple records with the same entries can exist in the database. I noticed a similar problem in one of the systems I worked on.

Mitigation

The mitigation for this problem is very simple. Gorm's DB struct has a field called Error which stores all the errors encountered in an operation. This is not an idiomatic way of handling errors in Golang and it might take you off-guard. Let's fix the AutoMigrate invocation to properly handle the error and fix the bug.

func main() {
	...
	err = db.AutoMigrate(&User{}, &UserCreditCard{}).Error
	if err != nil {
		panic(err)
	}
	...
}

The good news is that in Gorm v2, the error handling is improved, and now the AutoMigrate method directly returns an error.

I created another Repl with the code snippet. Feel free to experiment with it: https://repl.it/@ankitrgadiya/GormUniqueIndex

Conclusion

Gorm is one of the most powerful ORM implementations in Golang. But, there are some pitfalls that you might fall prey to. In this article, I discussed one of them I ran into recently. The aim of this article is not to scare you away from using Gorm altogether, but instead, I want to document it so that others don't fall for it.

Cheers!

P.S.: I want to make it into a series, so look out for more posts about Gorm.