CODEWITHSUNDEEP
postgresqlgoormgo-gorm
Kaveh Shahbazian
Kaveh Shahbazian

Reputation: 13513

`gorm` Ignoring `sql:"index"` Tags

Why gorm is ignoring sql:"index" tags? No indexes got created.

Database in use here is PostgreSQL (importing _ "github.com/lib/pq"). This Model struct is used (because default gorm.Model uses an auto increment number - serial - as primary key and I wanted to set id myself):

type Model struct {
    ID        int64 `sql:"type:bigint PRIMARY KEY;default:0"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt *time.Time `sql:"index"`
}

And one of actual models is:

type TUHistory struct {
    Model

    TUID        int64  `json:"tu_id,string" gorm:"column:tu_id" sql:"index"`
}

func (x *TUHistory) TableName() string {
    return "tu_history"
}

And the table is created by db.CreateTable(&TUHistory{}) which creates the table correctly except for indexes.

As a temporary work around, I do db.Model(&TUHistory{}).AddIndex("ix_tuh_tu_id", "tu_id") to create indexes.

Upvotes: 1

Views: 2607

Answers (1)

abm
abm

Reputation: 629

From my experience, the db.CreateTable only creates the table and it's fields. You are better off using the AutoMigrate function with the model structure that you want to migrate:

db, err := gorm.Open("postgres", connectionString)
...
// error checking
...

db.AutoMigrate(&Model)

Also, I tried AutoMigrating the model you posted and got an error saying that multiple primary keys are not allowed, so I changed the model to:

type Model struct {
    Id        int64 `sql:"type:bigint;default:0"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt *time.Time `sql:"index"`
}

and the AutoMigration created all PKs and indexes just fine.

Edit:

Checking the GORM's README, on this example, the Email structure goes as:

type Email struct {
    ID      int
    UserID  int     `sql:"index"` // Foreign key (belongs to), tag `index` will create index for this field when using AutoMigrate
    Email   string  `sql:"type:varchar(100);unique_index"` // Set field's sql type, tag `unique_index` will create unique index
    Subscribed bool
}

Notice the comment on the UserId field saying it will create the index when using AutoMigrate.

Also, it's worth taking a look at how the AutoMigrate does it's job:

// Automating Migration
db.AutoMigrate(&User{})
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&User{})
db.AutoMigrate(&User{}, &Product{}, &Order{})
// Feel free to change your struct, AutoMigrate will keep your database up-to-date.
// AutoMigrate will ONLY add *new columns* and *new indexes*,
// WON'T update current column's type or delete unused columns, to protect your data.
// If the table is not existing, AutoMigrate will create the table automatically.

Upvotes: 2

Related Questions