GORM/GEN

The code generator base on GORM, aims to be developer friendly.

Overview

• CRUD or DIY query method code generation
• Auto migration from database to code
• Transactions, Nested Transactions, Save Point, RollbackTo to Saved Point
• Competely compatible with GORM
• Developer Friendly

Installation

To install Gen package, you need to install Go and set your Go workspace first.

1. The first need Go installed(version 1.14+ is required), then you can use the below Go command to install Gen.

go get -u gorm.io/gen

2. Import it in your code:

import "gorm.io/gen"

Quick start

# assume the following code in generate.go file
$ cat generate.go

package main

import "gorm.io/gen"

// generate code
func main() {
  // specify the output directory (default: "./query")
	g := gen.NewGenerator(gen.Config{OutPath: "../dal/query"})
  
  // reuse the database connection in Project or create a connection here
	// db, _ := gorm.Open(mysql.Open("root:@(127.0.0.1:3306)/demo?charset=utf8mb4&parseTime=True&loc=Local"))
	g.UseDB(db)
  
  // apply basic crud api on structs or table models which is specified by table name with function
  // GenerateModel/GenerateModelAs. And generator will generate table models' code when calling Excute.
	g.ApplyBasic(model.User{}, g.GenerateModel("company"), g.GenerateModelAs("people", "Person"),)
	
  // apply diy interfaces on structs or table models
	g.ApplyInterface(func(method model.Method) {}, model.User{}, g.GenerateModel("company"))
  // apply several interface on one struct or table models
	g.ApplyInterfaces(model.User{}, func(model.Method, model.Method_another) {})

	// execute the action of code generation
	g.Execute()
}

Project Directory

Here is a template for best practices:

demo
├── cmd
│   └── generate
│       └── generate.go # execute it will generate codes
├── dal
│   ├── dal.go # create connections with database server here
│   └── model
│       ├── method.go # DIY method interfaces
│       └── model.go  # store struct which corresponding to the database table
│   └── query  # generated code's directory
│       └── gorm_generated.go # generated code
├── biz
│   └── query.go # call function in dal/gorm_generated.go and query databases
├── config
│   └── config.go # DSN for database server
├── generate.sh # a shell to execute cmd/generate
├── go.mod
├── go.sum
└── main.go

API Examples

Field Expression

Create Field

Actually, you're not supposed to create a new field variable, cause it will be accomplished in generated code.

Field Type	Detail Type	Crerate Function	Supported Query Method
generic	field	NewField	IsNull/IsNotNull/Count
int	int/int8/.../int64	NewInt/NewInt8/.../NewInt64	Eq/Neq/Gt/Gte/Lt/Lte/In/NotIn/Between/NotBetween/Like/NotLike/Add/Sub/Mul/Div/Mod/FloorDiv/RightShift/LeftShift/BitXor/BitAnd/BitOr/BitFlip
uint	uint/uint8/.../uint64	NewUint/NewUint8/.../NewUint64	same with int
float	float32/float64	NewFloat32/NewFloat64	Eq/Neq/Gt/Gte/Lt/Lte/In/NotIn/Between/NotBetween/Like/NotLike/Add/Sub/Mul/Div/FloorDiv
string	string/[]byte	NewString/NewBytes	Eq/Neq/Gt/Gte/Lt/Lte/Between/NotBetween/In(val/NotIn(val/Like/NotLike/Regexp/NotRegxp
bool	bool	NewBool	Not/Is/And/Or/Xor/BitXor/BitAnd/BitOr
time	time.Time	NewTime	Eq/Neq/Gt/Gte/Lt/Lte/Between/NotBetween/In/NotIn/Add/Sub

Create field examples:

import "gorm.io/gen/field"

// create a new generic field map to `generic_a`
a := field.NewField("table_name", "generic_a")

// create a field map to `id`
i := field.NewInt("user", "id")

// create a field map to `address`
s := field.NewString("user", "address")

// create a field map to `create_time`
t := field.NewTime("user", "create_time")

CRUD API

Here is a basic struct user and struct DB.

// generated code
// generated code
// generated code
package query

import "gorm.io/gen"

// struct map to table `users` 
type user struct {
	gen.DO
	ID       field.Uint
	Name     field.String
	Age      field.Int
	Address  field.Field
  Birthday field.Time
}

// struct collection
type DB struct {
	db 			 *gorm.DB
	User     *user
}

Create

Create Record

// u refer to query.user
user := model.User{Name: "Modi", Age: 18, Birthday: time.Now()}

u := query.Query.User
err := u.Create(&user) // pass pointer of data to Create

err // returns error

Create Record With Selected Fields

Create a record and assgin a value to the fields specified.

u := query.Query.User
u.Select(u.Name, u.Age).Create(&user)
// INSERT INTO `users` (`name`,`age`) VALUES ("modi", 18)

Create a record and ignore the values for fields passed to omit

u := query.Query.User
u.Omit(u.Name, u.Age).Create(&user)
// INSERT INTO `users` (`Address`, `Birthday`) VALUES ("2021-08-17 20:54:12.000", 18)

Batch Insert

To efficiently insert large number of records, pass a slice to the Create method. GORM will generate a single SQL statement to insert all the data and backfill primary key values.

var users = []model.User{{Name: "modi"}, {Name: "zhangqiang"}, {Name: "songyuan"}}
query.Query.User.Create(&users)

for _, user := range users {
  user.ID // 1,2,3
}

You can specify batch size when creating with CreateInBatches, e.g:

var users = []User{{Name: "modi_1"}, ...., {Name: "modi_10000"}}

// batch size 100
query.Query.User.CreateInBatches(users, 100)

It will works if you set CreateBatchSize in gorm.Config / gorm.Session

db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
  CreateBatchSize: 1000,
})
// OR
db = db.Session(&gorm.Session{CreateBatchSize: 1000})

u := query.NewUser(db)

var users = []User{{Name: "modi_1"}, ...., {Name: "modi_5000"}}

u.Create(&users)
// INSERT INTO users xxx (5 batches)

Query

Retriving a single object

Generated code provides First, Take, Last methods to retrieve a single object from the database, it adds LIMIT 1 condition when querying the database, and it will return the error ErrRecordNotFound if no record is found.

u := query.Query.User

// Get the first record ordered by primary key
user, err := u.First()
// SELECT * FROM users ORDER BY id LIMIT 1;

// Get one record, no specified order
user, err := u.Take()
// SELECT * FROM users LIMIT 1;

// Get last record, ordered by primary key desc
user, err := db.Last()
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

// check error ErrRecordNotFound
errors.Is(err, gorm.ErrRecordNotFound)

Retrieving objects with primary key

u := query.Query.User

user, err := u.First(u.ID.Eq(10))
// SELECT * FROM users WHERE id = 10;

users, err := u.Find(u.ID.In(1,2,3))
// SELECT * FROM users WHERE id IN (1,2,3);

If the primary key is a string (for example, like a uuid), the query will be written as follows:

user, err := db.First(u.ID.Eq("1b74413f-f3b8-409f-ac47-e8c062e3472a"))
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";

Retrieving all objects

u := query.Query.User

// Get all records
users, err := u.Find()
// SELECT * FROM users;

Conditions

String Conditions

u := query.Query.User

// Get first matched record
user, err := u.Where(u.Name.Eq("modi")).First()
// SELECT * FROM users WHERE name = 'modi' ORDER BY id LIMIT 1;

// Get all matched records
users, err := u.Where(u.Name.Neq("modi")).Find()
// SELECT * FROM users WHERE name <> 'modi';

// IN
users, err := u.Where(u.Name.In("modi", "zhangqiang")).Find()
// SELECT * FROM users WHERE name IN ('modi','zhangqiang');

// LIKE
users, err := u.Where(u.Name.Like("%modi%")).Find()
// SELECT * FROM users WHERE name LIKE '%modi%';

// AND
users, err := u.Where(u.Name.Eq("modi"), u.Age.Gte(17)).Find()
// SELECT * FROM users WHERE name = 'modi' AND age >= 17;

// Time
users, err := u.Where(u.Birthday.Gt(birthTime).Find()
// SELECT * FROM users WHERE birthday > '2000-01-01 00:00:00';

// BETWEEN
users, err := u.Where(u.Birthday.Between(lastWeek, today)).Find()
// SELECT * FROM users WHERE birthday BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

Inline Condition

// Get by primary key if it were a non-integer type
db.First(&user, "id = ?", "string_primary_key")
// SELECT * FROM users WHERE id = 'string_primary_key';

// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
// SELECT * FROM users WHERE name = "jinzhu";

db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

// Struct
db.Find(&users, User{Age: 20})
// SELECT * FROM users WHERE age = 20;

// Map
db.Find(&users, map[string]interface{}{"age": 20})
// SELECT * FROM users WHERE age = 20;

Not Conditions

Build NOT conditions, works similar to Where

db.Not("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;

// Not In
db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)
// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

// Struct
db.Not(User{Name: "jinzhu", Age: 18}).First(&user)
// SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;

// Not In slice of primary keys
db.Not([]int64{1,2,3}).First(&user)
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;

Or Conditions

db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

// Struct
db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

Group Conditions

Easier to write complicated SQL query with Group Conditions

db.Where(
  db.Where("pizza = ?", "pepperoni").Where(db.Where("size = ?", "small").Or("size = ?", "medium")),
).Or(
  db.Where("pizza = ?", "hawaiian").Where("size = ?", "xlarge"),
).Find(&Pizza{}).Statement

// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")

Selecting Specific Fields

Select allows you to specify the fields that you want to retrieve from database. Otherwise, GORM will select all fields by default.

db.Select("name", "age").Find(&users)
// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;

Order

Specify order when retrieving records from the database

db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

// Multiple orders
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

db.Clauses(clause.OrderBy{
  Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&User{})
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)

Limit & Offset

Limit specify the max number of records to retrieve Offset specify the number of records to skip before starting to return the records

db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;

// Cancel limit condition with -1
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
// SELECT * FROM users LIMIT 10; (users1)
// SELECT * FROM users; (users2)

db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;

db.Limit(10).Offset(5).Find(&users)
// SELECT * FROM users OFFSET 5 LIMIT 10;

// Cancel offset condition with -1
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)

Group By & Having

type result struct {
  Date  time.Time
  Total int
}

db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name`


db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
for rows.Next() {
  ...
}

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
for rows.Next() {
  ...
}

type Result struct {
  Date  time.Time
  Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

Distinct

Selecting distinct values from the model

db.Distinct("name", "age").Order("name, age desc").Find(&results)

Distinct works with Pluck and Count too

Joins

Specify Joins conditions

type result struct {
  Name  string
  Email string
}
db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
  ...
}

db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

// multiple joins with parameter
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "[email protected]").Joins("JOIN credit_cards ON cre

SubQuery

From SubQuery

Table SubQuery

Advanced Query

FirstOrInit

FirstOrCreate

Optimizer/Index Hints

Iteration

FindInBatches

Pluck

Scopes

Count

Uqdate

Delete

DIY method

Method interface

Smart Select Fields

Advanced Topics

• Hints

Contributing

You can help to deliver a better GORM/GEN

License

Released under the MIT License