Installation⌗

go get xorm.io/xorm
# Install the corresponding database driver
go get github.com/go-sql-driver/mysql

Tips and Tricks⌗

I won’t repeat what’s already in the official documentation. Below, I’ll introduce some useful content that isn’t covered in the official docs.

Inheritance⌗

When multiple models in our business have some identical fields, the general approach is to repeatedly define them in each model. This is not only time-consuming and laborious but also prone to errors. XORM allows us to use the extends tag to implement struct inheritance. For example:

If we need to store statistical data from Google and Baidu in our business, many fields can be shared. We can define a separate struct to hold these common fields and then inherit from it in the required structs.

type Report struct {
  Date       string  `json:"date" xorm:"DATE NOT NULL COMMENT('Date')"` 
  Impression float64 `json:"impression" xorm:"INT(11) NOT NULL DEFAULT 0 COMMENT('Impressions')"`
  Click      float64 `json:"click" xorm:"INT(11) NOT NULL DEFAULT 0 COMMENT('Clicks')"`
  Cost       float64 `json:"cost" xorm:"FLOAT NOT NULL DEFAULT 0 COMMENT('Cost')"`
  Ctr        float64 `json:"ctr" xorm:"FLOAT NOT NULL DEFAULT 0 COMMENT('Click-through rate')"`
  Cpm        float64 `json:"cpm" xorm:"FLOAT NOT NULL DEFAULT 0 COMMENT('Average price')"`
  Pv         float64 `json:"pv" xorm:"INT(11) NOT NULL DEFAULT 0 COMMENT('Page views')"`
  Uv         float64 `json:"uv" xorm:"INT(11) NOT NULL DEFAULT 0 COMMENT('Unique visitors')"`
}

type ReportBaidu struct {
  Id         int64   `json:"id" xorm:"BIGINT(20) PK AUTOINCR COMMENT('ID')"`
  Report     `xorm:"extends"`
  Ar         float64 `json:"ar" xorm:"FLOAT NOT NULL DEFAULT 0 COMMENT('Arrival rate')"`
  CreatedAt  Time `json:"created_at" xorm:"DATETIME NOT NULL CREATED COMMENT('Creation time')"`
}

type ReportGoogle struct {
  Id         int64   `json:"id" xorm:"BIGINT(20) PK AUTOINCR COMMENT('ID')"`
  Report     `xorm:"extends"`
  CreatedAt  Time `json:"created_at" xorm:"DATETIME NOT NULL CREATED COMMENT('Creation time')"`
}

Ignoring Fields⌗

Use Omit() to specify fields to ignore, which can be used for both queries and updates.

_, err := Engine.Where(builder.Eq{"id": report.Id}).Omit("updated_at").Update(report)

Migration⌗

Those who have used Laravel know that Laravel uses migrations to manage project data tables. XORM also has an implementation for this, although it’s not ready to use out of the box. The migration logic needs to be implemented by yourself. As shown in the code below, we use the Sync2 method provided by XORM for migration:

package models

import (
	"xorm.io/xorm"
	"xorm.io/xorm/migrate"
)

var tables = []interface{}{
	&Job{},
	//&RawData{},
	ReportTotal{},
	&ReportBaidu{},
	&ReportUmeng{},
	&ReportApplet{},
	&ReportGoogle{},
}

var migrations = []*migrate.Migration{
	{
		ID: "2020071502",
		Migrate: func(engine *xorm.Engine) error {
			return engine.Charset("utf8mb4").Sync2(tables...)
		},
		Rollback: func(engine *xorm.Engine) error {
			return engine.DropTables(tables...)
		},
	},
}

func Migrate() error {
	m := migrate.New(Engine, &migrate.Options{
		TableName:    "migrations",
		IDColumnName: "id",
	}, migrations)

	return m.Migrate()
}

You only need to call the Migrate function in your business logic to implement migration.

Seed⌗

For this part, you can refer to my previous article Implementing Database Seeder in Go

Pitfalls Encountered⌗

Update⌗

In actual projects, I found that when we update a record, if a field has a zero value, that field will be ignored. For example, if the current Uv field value is 10, and the Uv value is set to 0 during the update, the result after the update is that Uv remains 10.

To avoid this issue, we need to specify the fields to update. We can use Cols to specify certain fields, or directly use AllCols to update all fields:

_, err := Engine.Where(builder.Eq{"id": report.Id}).Cols("uv","pv").Update(report)
_, err := Engine.Where(builder.Eq{"id": report.Id}).AllCols().Update(report)

Migrations⌗

The ID field of the Migrations table uses VARCHAR(255). In MySQL versions before 5.7, executing migrations will result in an error because the primary key length exceeds the maximum limit. In such cases, either use version 5.7 or later, or modify the default configuration.

I hope this is helpful, Happy hacking…