A safer orm base on GORM, aims to be developer friendly.
- 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
- Multiple Generate modes
- GORM/GEN
- Overview
- Contents
- Installation
- Quick start
- API Examples
- Maintainers
- Contributing
- License
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"
Emphasis: All use cases in this doc are generated under WithContext
mode. And if you generate code under WithoutContext
mode, please remove WithContext(ctx)
before you call any query method, it helps you make code more concise.
# 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")
// ### if you want to query without context constrain, set mode gen.WithoutContext ###
g := gen.NewGenerator(gen.Config{
OutPath: "../dal/query",
/* Mode: gen.WithoutContext|gen.WithDefaultQuery*/
//if you want the nullable field generation property to be pointer type, set FieldNullable true
/* FieldNullable: true,*/
//If you need to generate index tags from the database, set FieldWithIndexTag true
/* FieldWithIndexTag: true,*/
})
// reuse the database connection in Project or create a connection here
// if you want to use GenerateModel/GenerateModelAs, UseDB is necessray or it will panic
// 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", gen.FieldIgnore("address")))
// apply diy interfaces on structs or table models
g.ApplyInterface(func(method model.Method) {}, model.User{}, g.GenerateModel("company"))
// execute the action of code generation
g.Execute()
}
Generate Mode:
gen.WithoutContext
generate code withoutWithContext
contraingen.WithDefaultQuery
generate code with a default global variableQ
as a singleton
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
| ├── user.gen.go # generated code for user
│ └── gen.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
// generate a model struct map to table `people` in database
g.GenerateModel("people")
// generate a struct and specify struct's name
g.GenerateModelAs("people", "People")
// add option to ignore field
g.GenerateModel("people", gen.FieldIgnore("address"), gen.FieldType("id", "int64"))
Field Generate Options
FieldNew // create new field
FieldIgnore // ignore field
FieldIgnoreReg // ignore field (match with regexp)
FieldRename // rename field in struct
FieldType // specify field type
FieldTypeReg // specify field type (match with regexp)
FieldTag // specify gorm and json tag
FieldJSONTag // specify json tag
FieldGORMTag // specify gorm tag
FieldNewTag // append new tag
FieldTrimPrefix // trim column prefix
FieldTrimSuffix // trim column suffix
FieldAddPrefix // add prefix to struct member's name
FieldAddSuffix // add suffix to struct member's name
FieldRelate // specify relationship with other tables
FieldRelateModel // specify relationship with exist models
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/Eq/Neq/Gt/Gte/Lt/Lte/Like |
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")
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
}
// u refer to query.user
user := model.User{Name: "Modi", Age: 18, Birthday: time.Now()}
u := query.Use(db).User
err := u.WithContext(ctx).Create(&user) // pass pointer of data to Create
err // returns error
Create a record and assign a value to the fields specified.
u := query.Use(db).User
u.WithContext(ctx).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.Use(db).User
u.WithContext(ctx).Omit(u.Name, u.Age).Create(&user)
// INSERT INTO `users` (`Address`, `Birthday`) VALUES ("2021-08-17 20:54:12.000", 18)
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.Use(db).User.WithContext(ctx).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.Use(db).User.WithContext(ctx).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.WithContext(ctx).Create(&users)
// INSERT INTO users xxx (5 batches)
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.Use(db).User
// Get the first record ordered by primary key
user, err := u.WithContext(ctx).First()
// SELECT * FROM users ORDER BY id LIMIT 1;
// Get one record, no specified order
user, err := u.WithContext(ctx).Take()
// SELECT * FROM users LIMIT 1;
// Get last record, ordered by primary key desc
user, err := u.WithContext(ctx).Last()
// SELECT * FROM users ORDER BY id DESC LIMIT 1;
// check error ErrRecordNotFound
errors.Is(err, gorm.ErrRecordNotFound)
u := query.Use(db).User
user, err := u.WithContext(ctx).Where(u.ID.Eq(10)).First()
// SELECT * FROM users WHERE id = 10;
users, err := u.WithContext(ctx).Where(u.ID.In(1,2,3)).Find()
// 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 := u.WithContext(ctx).Where(u.ID.Eq("1b74413f-f3b8-409f-ac47-e8c062e3472a")).First()
// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";
u := query.Use(db).User
// Get all records
users, err := u.WithContext(ctx).Find()
// SELECT * FROM users;
u := query.Use(db).User
// Get first matched record
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).First()
// SELECT * FROM users WHERE name = 'modi' ORDER BY id LIMIT 1;
// Get all matched records
users, err := u.WithContext(ctx).Where(u.Name.Neq("modi")).Find()
// SELECT * FROM users WHERE name <> 'modi';
// IN
users, err := u.WithContext(ctx).Where(u.Name.In("modi", "zhangqiang")).Find()
// SELECT * FROM users WHERE name IN ('modi','zhangqiang');
// LIKE
users, err := u.WithContext(ctx).Where(u.Name.Like("%modi%")).Find()
// SELECT * FROM users WHERE name LIKE '%modi%';
// AND
users, err := u.WithContext(ctx).Where(u.Name.Eq("modi"), u.Age.Gte(17)).Find()
// SELECT * FROM users WHERE name = 'modi' AND age >= 17;
// Time
users, err := u.WithContext(ctx).Where(u.Birthday.Gt(birthTime).Find()
// SELECT * FROM users WHERE birthday > '2000-01-01 00:00:00';
// BETWEEN
users, err := u.WithContext(ctx).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';
u := query.Use(db).User
// Get by primary key if it were a non-integer type
user, err := u.WithContext(ctx).Where(u.ID.Eq("string_primary_key")).First()
// SELECT * FROM users WHERE id = 'string_primary_key';
// Plain SQL
users, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).Find()
// SELECT * FROM users WHERE name = "modi";
users, err := u.WithContext(ctx).Where(u.Name.Neq("modi"), u.Age.Gt(17)).Find()
// SELECT * FROM users WHERE name <> "modi" AND age > 17;
Build NOT conditions, works similar to Where
u := query.Use(db).User
user, err := u.WithContext(ctx).Not(u.Name.Eq("modi")).First()
// SELECT * FROM users WHERE NOT name = "modi" ORDER BY id LIMIT 1;
// Not In
users, err := u.WithContext(ctx).Not(u.Name.In("modi", "zhangqiang")).Find()
// SELECT * FROM users WHERE name NOT IN ("modi", "zhangqiang");
// Not In slice of primary keys
user, err := u.WithContext(ctx).Not(u.ID.In(1,2,3)).First()
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
u := query.Use(db).User
users, err := u.WithContext(ctx).Where(u.Role.Eq("admin")).Or(u.Role.Eq("super_admin")).Find()
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';
Easier to write complicated SQL query with Group Conditions
p := query.Use(db).Pizza
pizzas, err := p.WithContext(ctx).Where(
p.WithContext(ctx).Where(p.Pizza.Eq("pepperoni")).
Where(p.Where(p.Size.Eq("small")).Or(p.Size.Eq("medium"))),
).Or(
p.WithContext(ctx).Where(p.Pizza.Eq("hawaiian")).Where(p.Size.Eq("xlarge")),
).Find()
// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")
Select
allows you to specify the fields that you want to retrieve from database. Otherwise, GORM will select all fields by default.
u := query.Use(db).User
users, err := u.WithContext(ctx).Select(u.Name, u.Age).Find()
// SELECT name, age FROM users;
u.WithContext(ctx).Select(u.Age.Avg()).Rows()
// SELECT Avg(age) FROM users;
u := query.Use(db).User
users, err := u.WithContext(ctx).Where(u.Columns(u.ID, u.Name).In(field.Values([][]inferface{}{{1, "modi"}, {2, "zhangqiang"}}))).Find()
// SELECT * FROM `users` WHERE (`id`, `name`) IN ((1,'humodi'),(2,'tom'));
u := query.Use(db).User
users, err := u.WithContext(ctx).Where(gen.Cond(datatypes.JSONQuery("attributes").HasKey("role"))...).Find()
// SELECT * FROM `users` WHERE JSON_EXTRACT(`attributes`,'$.role') IS NOT NULL;
Specify order when retrieving records from the database
u := query.Use(db).User
users, err := u.WithContext(ctx).Order(u.Age.Desc(), u.Name).Find()
// SELECT * FROM users ORDER BY age DESC, name;
// Multiple orders
users, err := u.WithContext(ctx).Order(u.Age.Desc()).Order(u.Name).Find()
// SELECT * FROM users ORDER BY age DESC, name;
Limit
specify the max number of records to retrieve
Offset
specify the number of records to skip before starting to return the records
u := query.Use(db).User
urers, err := u.WithContext(ctx).Limit(3).Find()
// SELECT * FROM users LIMIT 3;
// Cancel limit condition with -1
users, err := u.WithContext(ctx).Limit(10).Limit(-1).Find()
// SELECT * FROM users;
users, err := u.WithContext(ctx).Offset(3).Find()
// SELECT * FROM users OFFSET 3;
users, err := u.WithContext(ctx).Limit(10).Offset(5).Find()
// SELECT * FROM users OFFSET 5 LIMIT 10;
// Cancel offset condition with -1
users, err := u.WithContext(ctx).Offset(10).Offset(-1).Find()
// SELECT * FROM users;
u := query.Use(db).User
type Result struct {
Date time.Time
Total int
}
var result Result
err := u.WithContext(ctx).Select(u.Name, u.Age.Sum().As("total")).Where(u.Name.Like("%modi%")).Group(u.Name).Scan(&result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "%modi%" GROUP BY `name`
err := u.WithContext(ctx).Select(u.Name, u.Age.Sum().As("total")).Group(u.Name).Having(u.Name.Eq("group")).Scan(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"
rows, err := u.WithContext(ctx).Select(u.Birthday.As("date"), u.Age.Sum().As("total")).Group(u.Birthday).Rows()
for rows.Next() {
...
}
o := query.Use(db).Order
rows, err := o.WithContext(ctx).Select(o.CreateAt.Date().As("date"), o.Amount.Sum().As("total")).Group(o.CreateAt.Date()).Having(u.Amount.Sum().Gt(100)).Rows()
for rows.Next() {
...
}
var results []Result
o.WithContext(ctx).Select(o.CreateAt.Date().As("date"), o.WithContext(ctx).Amount.Sum().As("total")).Group(o.CreateAt.Date()).Having(u.Amount.Sum().Gt(100)).Scan(&results)
Selecting distinct values from the model
u := query.Use(db).User
users, err := u.WithContext(ctx).Distinct(u.Name, u.Age).Order(u.Name, u.Age.Desc()).Find()
Distinct
works with Pluck
and Count
too
Specify Joins conditions
u := query.Use(db).User
e := query.Use(db).Email
c := query.Use(db).CreditCard
type Result struct {
Name string
Email string
}
var result Result
err := u.WithContext(ctx).Select(u.Name, e.Email).LeftJoin(e, e.UserID.EqCol(u.ID)).Scan(&result)
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id
rows, err := u.WithContext(ctx).Select(u.Name, e.Email).LeftJoin(e, e.UserID.EqCol(u.ID)).Rows()
for rows.Next() {
...
}
var results []Result
err := u.WithContext(ctx).Select(u.Name, e.Email).LeftJoin(e, e.UserID.EqCol(u.ID)).Scan(&results)
// multiple joins with parameter
users := u.WithContext(ctx).Join(e, e.UserID.EqCol(u.id), e.Email.Eq("[email protected]")).Join(c, c.UserID.EqCol(u.ID)).Where(c.Number.Eq("411111111111")).Find()
A subquery can be nested within a query, GEN can generate subquery when using a Dao
object as param
o := query.Use(db).Order
u := query.Use(db).User
orders, err := o.WithContext(ctx).Where(u.Columns(o.Amount).Gt(o.Select(o.Amount.Avg())).Find()
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");
subQuery := u.WithContext(ctx).Select(u.Age.Avg()).Where(u.Name.Like("name%"))
users, err := u.WithContext(ctx).Select(u.Age.Avg().As("avgage")).Group(u.Name).Having(u.Columns(u.Age.Avg()).Gt(subQuery).Find()
// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")
GORM allows you using subquery in FROM clause with method Table
, for example:
u := query.Use(db).User
p := query.Use(db).Pet
users, err := gen.Table(u.WithContext(ctx).Select(u.Name, u.Age).As("u")).Where(u.Age.Eq(18)).Find()
// SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18
subQuery1 := u.WithContext(ctx).Select(u.Name)
subQuery2 := p.WithContext(ctx).Select(p.Name)
users, err := gen.Table(subQuery1.As("u"), subQuery2.As("p")).Find()
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p
Update a table by using SubQuery
u := query.Use(db).User
c := query.Use(db).Company
u.WithContext(ctx).Update(u.CompanyName, c.Select(c.Name).Where(c.ID.EqCol(u.CompanyID)))
// UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);
u.WithContext(ctx).Where(u.Name.Eq("modi")).Update(u.CompanyName, c.Select(c.Name).Where(c.ID.EqCol(u.CompanyID)))
To perform a set of operations within a transaction, the general flow is as below.
q := query.Use(db)
q.Transaction(func(tx *query.Query) error {
if _, err := tx.User.WithContext(ctx).Where(tx.User.ID.Eq(100)).Delete(); err != nil {
return err
}
if _, err := tx.Article.WithContext(ctx).Create(&model.User{Name:"modi"}); err != nil {
return err
}
return nil
})
GEN supports nested transactions, you can rollback a subset of operations performed within the scope of a larger transaction, for example:
q := query.Use(db)
q.Transaction(func(tx *query.Query) error {
tx.User.WithContext(ctx).Create(&user1)
tx.Transaction(func(tx2 *query.Query) error {
tx2.User.WithContext(ctx).Create(&user2)
return errors.New("rollback user2") // Rollback user2
})
tx.Transaction(func(tx2 *query.Query) error {
tx2.User.WithContext(ctx).Create(&user3)
return nil
})
return nil
})
// Commit user1, user3
q := query.Use(db)
// begin a transaction
tx := q.Begin()
// do some database operations in the transaction (use 'tx' from this point, not 'db')
tx.User.WithContext(ctx).Create(...)
// ...
// rollback the transaction in case of error
tx.Rollback()
// Or commit the transaction
tx.Commit()
For example:
q := query.Use(db)
func doSomething(ctx context.Context, users ...*model.User) (err error) {
tx := q.Begin()
defer func() {
if recover() != nil || err != nil {
_ = tx.Rollback()
}
}()
err = tx.User.WithContext(ctx).Create(users...)
if err != nil {
return
}
return tx.Commit()
}
GEN provides SavePoint
, RollbackTo
to save points and roll back to a savepoint, for example:
tx := q.Begin()
txCtx = tx.WithContext(ctx)
txCtx.User.Create(&user1)
tx.SavePoint("sp1")
txCtx.Create(&user2)
tx.RollbackTo("sp1") // Rollback user2
tx.Commit() // Commit user1
GEN supports iterating through Rows
u := query.Use(db).User
do := u.WithContext(ctx)
rows, err := do.Where(u.Name.Eq("modi")).Rows()
defer rows.Close()
for rows.Next() {
var user User
// ScanRows is a method of `gorm.DB`, it can be used to scan a row into a struct
do.ScanRows(rows, &user)
// do something
}
Query and process records in batch
u := query.Use(db).User
// batch size 100
err := u.WithContext(ctx).Where(u.ID.Gt(9)).FindInBatches(&results, 100, func(tx gen.Dao, batch int) error {
for _, result := range results {
// batch processing found records
}
// build a new `u` to use it's api
// queryUsery := query.NewUser(tx.UnderlyingDB())
tx.Save(&results)
batch // Batch 1, 2, 3
// returns error will stop future batches
return nil
})
Query single column from database and scan into a slice, if you want to query multiple columns, use Select
with Scan
instead
u := query.Use(db).User
var ages []int64
u.WithContext(ctx).Pluck(u.Age, &ages)
var names []string
u.WithContext(ctx).Pluck(u.Name, &names)
// Distinct Pluck
u.WithContext(ctx).Distinct().Pluck(u.Name, &names)
// SELECT DISTINCT `name` FROM `users`
// Requesting more than one column, use `Scan` or `Find` like this:
db.WithContext(ctx).Select(u.Name, u.Age).Scan(&users)
users, err := db.Select(u.Name, u.Age).Find()
Scopes
allows you to specify commonly-used queries which can be referenced as method calls
o := query.Use(db).Order
func AmountGreaterThan1000(tx gen.Dao) gen.Dao {
return tx.Where(o.Amount.Gt(1000))
}
func PaidWithCreditCard(tx gen.Dao) gen.Dao {
return tx.Where(o.PayModeSign.Eq("C"))
}
func PaidWithCod(tx gen.Dao) gen.Dao {
return tx.Where(o.PayModeSign.Eq("C"))
}
func OrderStatus(status []string) func (tx gen.Dao) gen.Dao {
return func (tx gen.Dao) gen.Dao {
return tx.Where(o.Status.In(status...))
}
}
orders, err := o.WithContext(ctx).Scopes(AmountGreaterThan1000, PaidWithCreditCard).Find()
// Find all credit card orders and amount greater than 1000
orders, err := o.WithContext(ctx).Scopes(AmountGreaterThan1000, PaidWithCod).Find()
// Find all COD orders and amount greater than 1000
orders, err := o.WithContext(ctx).Scopes(AmountGreaterThan1000, OrderStatus([]string{"paid", "shipped"})).Find()
// Find all paid, shipped orders that amount greater than 1000
Get matched records count
u := query.Use(db).User
count, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).Or(u.Name.Eq("zhangqiang")).Count()
// SELECT count(1) FROM users WHERE name = 'modi' OR name = 'zhangqiang'
count, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).Count()
// SELECT count(1) FROM users WHERE name = 'modi'; (count)
// Count with Distinct
u.WithContext(ctx).Distinct(u.Name).Count()
// SELECT COUNT(DISTINCT(`name`)) FROM `users`
Get first matched record or initialize a new instance with given conditions
u := query.Use(db).User
// User not found, initialize it with give conditions
user, err := u.WithContext(ctx).Where(u.Name.Eq("non_existing")).FirstOrInit()
// user -> User{Name: "non_existing"}
// Found user with `name` = `modi`
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).FirstOrInit()
// user -> User{ID: 1, Name: "modi", Age: 17}
initialize struct with more attributes if record not found, those Attrs
won’t be used to build SQL query
u := query.Use(db).User
// User not found, initialize it with give conditions and Attrs
user, err := u.WithContext(ctx).Where(u.Name.Eq("non_existing")).Attrs(u.Age.Value(20)).FirstOrInit()
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}
// User not found, initialize it with give conditions and Attrs
user, err := u.WithContext(ctx).Where(u.Name.Eq("non_existing")).Attrs(u.Age.Value(20)).FirstOrInit()
// SELECT * FROM USERS WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// user -> User{Name: "non_existing", Age: 20}
// Found user with `name` = `modi`, attributes will be ignored
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).Attrs(u.Age.Value(20)).FirstOrInit()
// SELECT * FROM USERS WHERE name = modi' ORDER BY id LIMIT 1;
// user -> User{ID: 1, Name: "modi", Age: 17}
Assign
attributes to struct regardless it is found or not, those attributes won’t be used to build SQL query and the final data won’t be saved into database
// User not found, initialize it with give conditions and Assign attributes
user, err := u.WithContext(ctx).Where(u.Name.Eq("non_existing")).Assign(u.Age.Value(20)).FirstOrInit()
// user -> User{Name: "non_existing", Age: 20}
// Found user with `name` = `modi`, update it with Assign attributes
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).Assign(u.Age.Value(20)).FirstOrInit()
// SELECT * FROM USERS WHERE name = modi' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "modi", Age: 20}
Get first matched record or create a new one with given conditions
u := query.Use(db).User
// User not found, create a new record with give conditions
user, err := u.WithContext(ctx).Where(u.Name.Eq("non_existing")).FirstOrCreate()
// INSERT INTO "users" (name) VALUES ("non_existing");
// user -> User{ID: 112, Name: "non_existing"}
// Found user with `name` = `modi`
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).FirstOrCreate()
// user -> User{ID: 111, Name: "modi", "Age": 18}
Create struct with more attributes if record not found, those Attrs
won’t be used to build SQL query
u := query.Use(db).User
// User not found, create it with give conditions and Attrs
user, err := u.WithContext(ctx).Where(u.Name.Eq("non_existing")).Attrs(u.Age.Value(20)).FirstOrCreate()
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}
// Found user with `name` = `modi`, attributes will be ignored
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).Attrs(u.Age.Value(20)).FirstOrCreate()
// SELECT * FROM users WHERE name = 'modi' ORDER BY id LIMIT 1;
// user -> User{ID: 111, Name: "modi", Age: 18}
Assign
attributes to the record regardless it is found or not and save them back to the database.
u := query.Use(db).User
// User not found, initialize it with give conditions and Assign attributes
user, err := u.WithContext(ctx).Where(u.Name.Eq("non_existing")).Assign(u.Age.Value(20)).FirstOrCreate()
// SELECT * FROM users WHERE name = 'non_existing' ORDER BY id LIMIT 1;
// INSERT INTO "users" (name, age) VALUES ("non_existing", 20);
// user -> User{ID: 112, Name: "non_existing", Age: 20}
// Found user with `name` = `modi`, update it with Assign attributes
user, err := u.WithContext(ctx).Where(u.Name.Eq("modi")).Assign(u.Age.Value(20)).FirstOrCreate(&user)
// SELECT * FROM users WHERE name = 'modi' ORDER BY id LIMIT 1;
// UPDATE users SET age=20 WHERE id = 111;
// user -> User{ID: 111, Name: "modi", Age: 20}
GEN will auto-save associations as GORM do. The relationships (BelongsTo/HasOne/HasMany/Many2Many) reuse GORM's tag. This feature only support exist model for now.
There are 4 kind of relationship.
const (
HasOne RelationshipType = RelationshipType(schema.HasOne) // HasOneRel has one relationship
HasMany RelationshipType = RelationshipType(schema.HasMany) // HasManyRel has many relationships
BelongsTo RelationshipType = RelationshipType(schema.BelongsTo) // BelongsToRel belongs to relationship
Many2Many RelationshipType = RelationshipType(schema.Many2Many) // Many2ManyRel many to many relationship
)
package model
// exist model
type Customer struct {
gorm.Model
CreditCards []CreditCard `gorm:"foreignKey:CustomerRefer"`
}
type CreditCard struct {
gorm.Model
Number string
CustomerRefer uint
}
GEN will detect model's associations:
// specify model
g.ApplyBasic(model.Customer{}, model.CreditCard{})
// assoications will be detected and converted to code
package query
type customer struct {
...
CreditCards customerHasManyCreditCards
}
type creditCard struct{
...
}
The association have to be speified by gen.FieldRelate
card := g.GenerateModel("credit_cards")
customer := g.GenerateModel("customers", gen.FieldRelate(field.HasMany, "CreditCards", b,
&field.RelateConfig{
// RelateSlice: true,
GORMTag: "foreignKey:CustomerRefer",
}),
)
g.ApplyBasic(card, custormer)
GEN will generate models with associated field:
// customers
type Customer struct {
ID int64 `gorm:"column:id;type:bigint(20) unsigned;primaryKey" json:"id"`
CreatedAt time.Time `gorm:"column:created_at;type:datetime(3)" json:"created_at"`
UpdatedAt time.Time `gorm:"column:updated_at;type:datetime(3)" json:"updated_at"`
DeletedAt gorm.DeletedAt `gorm:"column:deleted_at;type:datetime(3)" json:"deleted_at"`
CreditCards []CreditCard `gorm:"foreignKey:CustomerRefer" json:"credit_cards"`
}
// credit_cards
type CreditCard struct {
ID int64 `gorm:"column:id;type:bigint(20) unsigned;primaryKey" json:"id"`
CreatedAt time.Time `gorm:"column:created_at;type:datetime(3)" json:"created_at"`
UpdatedAt time.Time `gorm:"column:updated_at;type:datetime(3)" json:"updated_at"`
DeletedAt gorm.DeletedAt `gorm:"column:deleted_at;type:datetime(3)" json:"deleted_at"`
CustomerRefer int64 `gorm:"column:customer_refer;type:bigint(20) unsigned" json:"customer_refer"`
}
If associated model already exists, gen.FieldRelateModel
can help you build associations between them.
customer := g.GenerateModel("customers", gen.FieldRelateModel(field.HasMany, "CreditCards", model.CreditCard{},
&field.RelateConfig{
// RelateSlice: true,
GORMTag: "foreignKey:CustomerRefer",
}),
)
g.ApplyBasic(custormer)
type RelateConfig struct {
// specify field's type
RelatePointer bool // ex: CreditCard *CreditCard
RelateSlice bool // ex: CreditCards []CreditCard
RelateSlicePointer bool // ex: CreditCards []*CreditCard
JSONTag string // related field's JSON tag
GORMTag string // related field's GORM tag
NewTag string // related field's new tag
OverwriteTag string // related field's tag
}
user := model.User{
Name: "modi",
BillingAddress: Address{Address1: "Billing Address - Address 1"},
ShippingAddress: Address{Address1: "Shipping Address - Address 1"},
Emails: []Email{
{Email: "[email protected]"},
{Email: "[email protected]"},
},
Languages: []Language{
{Name: "ZH"},
{Name: "EN"},
},
}
u := query.Use(db).User
u.WithContext(ctx).Select(u.Name).Create(&user)
// INSERT INTO "users" (name) VALUES ("jinzhu", 1, 2);
u.WithContext(ctx).Omit(u.BillingAddress.Field()).Create(&user)
// Skip create BillingAddress when creating a user
u.WithContext(ctx).Omit(u.BillingAddress.Field("Address1")).Create(&user)
// Skip create BillingAddress.Address1 when creating a user
u.WithContext(ctx).Omit(field.AssociationFields).Create(&user)
// Skip all associations when creating a user
Method Field
will join a serious field name with ''.", for example: u.BillingAddress.Field("Address1", "Street")
equals to BillingAddress.Address1.Street
Find matched associations
u := query.Use(db).User
languages, err = u.Languages.Model(&user).Find()
Find associations with conditions
q := query.Use(db)
u := q.User
languages, err = u.Languages.Where(q.Language.Name.In([]string{"ZH","EN"})).Model(&user).Find()
Append new associations for many to many
, has many
, replace current association for has one
, belongs to
u := query.Use(db).User
u.Languages.Model(&user).Append(&languageZH, &languageEN)
u.Languages.Model(&user).Append(&Language{Name: "DE"})
u.CreditCards.Model(&user).Append(&CreditCard{Number: "411111111111"})
Replace current associations with new ones
u.Languages.Model(&user).Replace(&languageZH, &languageEN)
Remove the relationship between source & arguments if exists, only delete the reference, won’t delete those objects from DB.
u := query.Use(db).User
u.Languages.Model(&user).Delete(&languageZH, &languageEN)
u.Languages.Model(&user).Delete([]*Language{&languageZH, &languageEN}...)
Remove all reference between source & association, won’t delete those associations
u.Languages.Model(&user).Clear()
Return the count of current associations
u.Languages.Model(&user).Count()
You are allowed to delete selected has one/has many/many2many relations with Select
when deleting records, for example:
u := query.Use(db).User
// delete user's account when deleting user
u.Select(u.Account).Delete(&user)
// delete user's Orders, CreditCards relations when deleting user
db.Select(u.Orders.Field(), u.CreditCards.Field()).Delete(&user)
// delete user's has one/many/many2many relations when deleting user
db.Select(field.AssociationsFields).Delete(&user)
This feature only support exist model for now.
GEN allows eager loading relations in other SQL with Preload
, for example:
type User struct {
gorm.Model
Username string
Orders []Order
}
type Order struct {
gorm.Model
UserID uint
Price float64
}
q := query.Use(db)
u := q.User
o := q.Order
// Preload Orders when find users
users, err := u.WithContext(ctx).Preload(u.Orders).Find()
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4);
users, err := u.WithContext(ctx).Preload(u.Orders).Preload(u.Profile).Preload(u.Role).Find()
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4); // has many
// SELECT * FROM profiles WHERE user_id IN (1,2,3,4); // has one
// SELECT * FROM roles WHERE id IN (4,5,6); // belongs to
clause.Associations
can work with Preload
similar like Select
when creating/updating, you can use it to Preload
all associations, for example:
type User struct {
gorm.Model
Name string
CompanyID uint
Company Company
Role Role
Orders []Order
}
users, err := u.WithContext(ctx).Preload(field.Associations).Find()
clause.Associations
won’t preload nested associations, but you can use it with Nested Preloading together, e.g:
users, err := u.WithContext(ctx).Preload(u.Orders.OrderItems.Product).Find()
GORM allows Preload associations with conditions, it works similar to Inline Conditions.
q := query.Use(db)
u := q.User
o := q.Order
// Preload Orders with conditions
users, err := u.WithContext(ctx).Preload(u.Orders.On(o.State.NotIn("cancelled")).Find()
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4) AND state NOT IN ('cancelled');
users, err := u.WithContext(ctx).Where(u.State.Eq("active")).Preload(u.Orders.On(o.State.NotIn("cancelled")).Find()
// SELECT * FROM users WHERE state = 'active';
// SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled');
users, err := u.WithContext(ctx).Preload(u.Orders.Order(o.ID.Desc(), o.CreateTime).Find()
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2) Order By id DESC, create_time;
users, err := u.WithContext(ctx).Preload(u.Orders.On(o.State.Eq("on")).Order(o.ID.Desc()).Find()
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2) AND state = "on" Order By id DESC;
users, err := u.WithContext(ctx).Preload(u.Orders.Clauses(hints.UseIndex("idx_order_id"))).Find()
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2) USE INDEX (`idx_order_id`);
GEN supports nested preloading, for example:
db.Preload(u.Orders.OrderItems.Product).Preload(u.CreditCard).Find(&users)
// Customize Preload conditions for `Orders`
// And GEN won't preload unmatched order's OrderItems then
db.Preload(u.Orders.On(o.State.Eq("paid"))).Preload(u.Orders.OrderItems).Find(&users)
When updating a single column with Update
, it needs to have any conditions or it will raise error ErrMissingWhereClause
, for example:
u := query.Use(db).User
// Update with conditions
u.WithContext(ctx).Where(u.Activate.Is(true)).Update(u.Name, "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;
// Update with conditions
u.WithContext(ctx).Where(u.Activate.Is(true)).Update(u.Age, u.Age.Add(1))
// or
u.WithContext(ctx).Where(u.Activate.Is(true)).UpdateSimple(u.Age.Add(1))
// UPDATE users SET age=age+1, updated_at='2013-11-17 21:34:10' WHERE active=true;
u.WithContext(ctx).Where(u.Activate.Is(true)).UpdateSimple(u.Age.Zero())
// UPDATE users SET age=0, updated_at='2013-11-17 21:34:10' WHERE active=true;
Updates
supports update with struct
or map[string]interface{}
, when updating with struct
it will only update non-zero fields by default
u := query.Use(db).User
// Update attributes with `map`
u.WithContext(ctx).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
// Update attributes with `struct`
u.WithContext(ctx).Where(u.ID.Eq(111)).Updates(model.User{Name: "hello", Age: 18, Active: false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
// Update with expression
u.WithContext(ctx).Where(u.ID.Eq(111)).UpdateSimple(u.Age.Add(1), u.Number.Add(1))
// UPDATE users SET age=age+1,number=number+1, updated_at='2013-11-17 21:34:10' WHERE id=111;
u.WithContext(ctx).Where(u.Activate.Is(true)).UpdateSimple(u.Age.Value(17), u.Number.Zero(), u.Birthday.Null())
// UPDATE users SET age=17, number=0, birthday=NULL, updated_at='2013-11-17 21:34:10' WHERE active=true;
NOTE When update with struct, GEN will only update non-zero fields, you might want to use
map
to update attributes or useSelect
to specify fields to update
If you want to update selected fields or ignore some fields when updating, you can use Select
, Omit
u := query.Use(db).User
// Select with Map
// User's ID is `111`:
u.WithContext(ctx).Select(u.Name).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello' WHERE id=111;
u.WithContext(ctx).Omit(u.Name).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;
result, err := u.WithContext(ctx).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
result.RowsAffected // affect rows number
err // error
e := query.Use(db).Email
// Email's ID is `10`
e.WithContext(ctx).Where(e.ID.Eq(10)).Delete()
// DELETE from emails where id = 10;
// Delete with additional conditions
e.WithContext(ctx).Where(e.ID.Eq(10), e.Name.Eq("modi")).Delete()
// DELETE from emails where id = 10 AND name = "modi";
result, err := e.WithContext(ctx).Where(e.ID.Eq(10), e.Name.Eq("modi")).Delete()
result.RowsAffected // affect rows number
err // error
GEN allows to delete objects using primary key(s) with inline condition, it works with numbers.
u.WithContext(ctx).Where(u.ID.In(1,2,3)).Delete()
// DELETE FROM users WHERE id IN (1,2,3);
The specified value has no primary value, GEN will perform a batch delete, it will delete all matched records
e := query.Use(db).Email
e.WithContext(ctx).Where(e.Name.Like("%modi%")).Delete()
// DELETE from emails where email LIKE "%modi%";
If your model includes a gorm.DeletedAt
field (which is included in gorm.Model
), it will get soft delete ability automatically!
When calling Delete
, the record WON’T be removed from the database, but GORM will set the DeletedAt
‘s value to the current time, and the data is not findable with normal Query methods anymore.
// Batch Delete
u.WithContext(ctx).Where(u.Age.Eq(20)).Delete()
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;
// Soft deleted records will be ignored when querying
users, err := u.WithContext(ctx).Where(u.Age.Eq(20)).Find()
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;
If you don’t want to include gorm.Model
, you can enable the soft delete feature like:
type User struct {
ID int
Deleted gorm.DeletedAt
Name string
}
You can find soft deleted records with Unscoped
users, err := db.WithContext(ctx).Unscoped().Where(u.Age.Eq(20)).Find()
// SELECT * FROM users WHERE age = 20;
You can delete matched records permanently with Unscoped
o.WithContext(ctx).Unscoped().Where(o.ID.Eq(10)).Delete()
// DELETE FROM orders WHERE id=10;
Method interface is an abstraction of query methods, all functions it contains are query methods and above comments describe the specific query conditions or logic.
SQL supports simple where
query or execute raw SQL. Simple query conditions wrapped by where()
, and raw SQL wrapped by sql()
(not required)
Method interface supports descriptive comment that describes how the method works.It starts with method name and followed descriptive message (not required). It is distinguished from query comment by blank line (with descriptive message) or space (without descriptive message).
type Method interface {
// where("name=@name and age=@age")
SimpleFindByNameAndAge(name string, age int) (gen.T, error)
// FindUserToMap query by id and return id->instance
//
// sql(select * from users where id=@id)
FindUserToMap(id int) (gen.M, error)
// InsertValue insert into users (name,age) values (@name,@age)
InsertValue(age int, name string) error
}
Return values must contains less than 1 gen.T
/gen.M
/gen.RowsAffected
and less than 1 error. You can also use bulitin type (like string
/ int
) as the return parameter,gen.T
represents return a single result struct's pointer, []gen.T
represents return an array of result structs' pointer,
gen.T
represents specifiedstruct
ortable
gen.M
representsmap[string]interface
gen.RowsAffected
represents SQL executedrowsAffected
(type:int64)@@table
represents table's name (if method's parameter doesn't contains variabletable
, GEN will generatetable
from model struct)@@<columnName>
represents column's name or table's name@<name>
represents normal query variable
Logical operations must be wrapped in {{}}
,and end must used {{end}}
, All templates support nesting
if
/else if
/else
the condition accept a bool parameter or operation expression which conforms to Golang syntax.where
Thewhere
clause will be inserted only if the child elements return something. The key wordand
oror
in front of clause will be removed. Andand
will be added automatically when there is no junction keyword between query condition clause.Set
Theset
clause will be inserted only if the child elements return something. The,
in front of columns array will be removed.And,
will be added automatically when there is no junction keyword between query coulmns....
Coming soon
{{if cond1}}
// do something here
{{else if cond2}}
// do something here
{{else}}
// do something here
{{end}}
Use case in raw SQL:
// select * from users where {{if name !=""}} name=@name{{end}}
methond(name string) (gen.T,error)
Use case in raw SQL template:
select * from @@table where
{{if age>60}}
status="older"
{{else if age>30}}
status="middle-ager"
{{else if age>18}}
status="younger"
{{else}}
{{if sex=="male"}}
status="boys"
{{else}}
status="girls"
{{end}}
{{end}}
{{where}}
// do something here
{{end}}
Use case in raw SQL
// select * from {{where}}id=@id{{end}}
methond(id int) error
Use case in raw SQL template
select * from @@table
{{where}}
{{if cond}}id=@id {{end}}
{{if name != ""}}@@key=@value{{end}}
{{end}}
{{set}}
// sepecify update expression here
{{end}}
Use case in raw SQL
// update users {{set}}name=@name{{end}}
methond() error
Use case in raw SQL template
update @@table
{{set}}
{{if name!=""}} name=@name {{end}}
{{if age>0}} age=@age {{end}}
{{end}}
where id=@id
type Method interface {
// Where("name=@name and age=@age")
SimpleFindByNameAndAge(name string, age int) (gen.T, error)
// select * from users where id=@id
FindUserToMap(id int) (gen.M, error)
// sql(insert into @@table (name,age) values (@name,@age) )
InsertValue(age int, name string) error
// select name from @@table where id=@id
FindNameByID(id int) string
// select * from @@table
// {{where}}
// id>0
// {{if cond}}id=@id {{end}}
// {{if key!="" && value != ""}} or @@key=@value{{end}}
// {{end}}
FindByIDOrCustom(cond bool, id int, key, value string) ([]gen.T, error)
// update @@table
// {{set}}
// update_time=now()
// {{if name != ""}}
// name=@name
// {{end}}
// {{end}}
// {{where}}
// id=@id
// {{end}}
UpdateName(name string, id int) (gen.RowsAffected,error)
}
GEN allows select specific fields with Select
, if you often use this in your application, maybe you want to define a smaller struct for API usage which can select specific fields automatically, for example:
type User struct {
ID uint
Name string
Age int
Gender string
// hundreds of fields
}
type APIUser struct {
ID uint
Name string
}
type Method interface{
// select * from user
FindSome() ([]APIUser, error)
}
apiusers, err := u.WithContext(ctx).Limit(10).FindSome()
// SELECT `id`, `name` FROM `users` LIMIT 10
Optimizer hints allow to control the query optimizer to choose a certain query execution plan, GORM supports it with gorm.io/hints
, e.g:
import "gorm.io/hints"
u := query.Use(db).User
users, err := u.WithContext(ctx).Clauses(hints.New("MAX_EXECUTION_TIME(10000)")).Find()
// SELECT * /*+ MAX_EXECUTION_TIME(10000) */ FROM `users`
Index hints allow passing index hints to the database in case the query planner gets confused.
import "gorm.io/hints"
u := query.Use(db).User
users, err := u.WithContext(ctx).Clauses(hints.UseIndex("idx_user_name")).Find()
// SELECT * FROM `users` USE INDEX (`idx_user_name`)
users, err := u.WithContext(ctx).Clauses(hints.ForceIndex("idx_user_name", "idx_user_id").ForJoin()).Find()
// SELECT * FROM `users` FORCE INDEX FOR JOIN (`idx_user_name`,`idx_user_id`)"
You can help to deliver a better GORM/GEN
Released under the MIT License