gorose
v2.1.8
Go implementation of PHP Laravel ORM, consistent with laravel official documentation https://laravel.com/docs/10.x/queries.
It is divided into Go style (struct structure binding usage) and PHP style (map structure usage).
For PHP style usage, you can use the documentation of laravel query builder as a reference, and try to achieve a 1:1 restoration.
It is still in the beta stage, please use it with caution. Since it is not tagged, it can only be introduced using go mod.
# go.mod
require github.com/gohouse/gorose/v3 mastergo style usage
package main
import (
gorose "github.com/gohouse/gorose/v3"
// 引入mysql驱动
_ "github.com/go-sql-driver/mysql"
)
type User struct {
Id int64 `db:"id,pk"` // 这里的 pk 是指主键
Name string `db:"name"`
Email string `db:"email"`
// 定义表名字,等同于 func (User) TableName() string {return "users"}, 二选一即可
// TableName string `db:"users" json:"-"`
}
func ( User ) TableName () string {
return "users"
}
var rose = gorose . Open ( "mysql" , "root:123456@tcp(localhost:3306)/test?charset=utf8mb4&parseTime=true" )
func db () * gorose. Database {
return rose . NewDatabase ()
}
func main () {
// select id,name,email from users limit 1;
var user User
db (). To ( & user )
// insert into users (name,email) values ("test","[email protected]");
var user = User { Name : "test" , Email : "[email protected]" }}
db (). Insert ( & user )
// update users set name="test2" where id=1;
var user = User { Id : 1 , Name : "test2" }
db (). Update ( & user )
// delete from users where id=1;
var user = User { Id : 1 }
db (). Delete ( & user )
}PHP style usage and Go style usage query are as follows SQL comparison:
select id,name,email from users where id = 1 and name = " test " // php 风格写法
user , err := db (). Table ( "users" ).
Select ( "id" , "name" , "email" ).
Where ( "id" , "=" , 1 ). Where ( "name" , "test" ).
First ()
// go 风格写法
var user = User { Id : 1 , Name : "test" }
err := db (). To ( & user )The above two usages have the same results. It can be seen that except for the difference in binding to the table model, other methods are common.
Single database connection can be used directly in the same way as the official interface
var rose = gorose . Open ( "mysql" , "root:123456@tcp(localhost:3306)/test?charset=utf8mb4&parseTime=true" )Can also be used
var conf1 = gorose. Config {
Driver : "mysql" ,
DSN : "root:123456@tcp(localhost:3306)/test?charset=utf8mb4&parseTime=true" ,
Prefix : "tb_" ,
Weight : 0 ,
MaxIdleConns : 0 ,
MaxOpenConns : 0 ,
ConnMaxLifetime : 0 ,
ConnMaxIdleTime : 0 ,
}
var rose = gorose . Open ( conf )Or use a read-write separation cluster to automatically force data to be read from the write database within a transaction.
var rose = gorose . Open (
gorose. ConfigCluster {
WriteConf : []gorose. Config {
conf1 ,
conf2 ,
},
ReadConf : []gorose. Config {
conf3 ,
conf4 ,
}
},
) Currently, support for the above 5 databases has been implemented. For general databases, you only need to add more gorose/driver/dialect.IDialect interfaces. For non-universal databases, as long as the gorose/driver.IDriver interface is implemented, any database can be supported in theory. Including redis, mongo, etc., all ORM chain operations can be implemented through this interface. All ORM chain operations are in gorose/builder.Context , and the original data can be obtained directly.
// 全自动事务, 有错误会自动回滚, 无错误会自动提交
// Transaction 方法可以接收多个操作, 同用一个 tx, 方便在不同方法内处理同一个事务
db (). Transaction ( func ( tx gorose. TxHandler ) error {
tx (). Insert ( & user )
tx (). Update ( & user )
tx (). To ( & user )
})
// 手动事务
tx = db (). Begin ()
tx (). Insert ( & user )
tx (). Update ( & user )
tx (). To ( & user )
tx (). Rollback ()
tx (). Commit ()
// 全自动嵌套事务
db (). Transaction ( func ( tx gorose. TxHandler ) error {
tx (). Insert ( & user )
...
// 自动子事务
tx (). Transaction ( func ( tx2 gorose. TxHandler ) error {
tx2 (). Update ( & user )
...
}
}
// 手动嵌套事务
var tx = db (). Begin ()
// 自动子事务
tx (). Begin () // 自动 savepoint 子事务
...
tx (). Rollback () // 自动回滚到上一个 savepoint
...
// 手动子事务
tx (). SavePoint ( "savepoint1" ) // 手动 savepoint 到 savepoint1(自定义名字)
...
tx (). RollbackTo ( "savepoint1" ) // 手动回滚到自定义的 savepoint
tx (). Commit ()When inserting and updating data, if you use the struct model as the data object, the type zero value is ignored by default. If you want to force writing, you can pass in the fields that need to be forced to be written starting from the second parameter, such as:
var user = User { Id : 1 , Name : "test" }
// 这里不会对 sex 做任何操作,
//update user set name="test" where id=1
db (). Update ( & user )
// 这里会强制将sex更改为0
//update user set name="test", sex=0 where id=1
db (). Update ( & user , "sex" )
// 等同于
db (). Table ( & user ). Where ( "id" , 1 ). Update ( map [ string ] any { "name" : "test" , "sex" : 0 })) If there is no where condition, the field with pk specified in the tag will be automatically added as a condition, such as: db:"id,pk" . Because pk is specified, if the value of id is not 0, the id will be updated as the primary key condition.
Referenceupdate
var user = User { Id : 1 }
db (). Delete ( & user )
// 等同于
db (). Table ( & user ). Where ( "id" , 1 ). Delete ()
// 要加上字段0值条件,只需要传入第二个字段,如:
// delete from users where id=1 and sex=0 and name=""
db (). Delete ( & user , "sex" , "name" ) db (). Table ( User {})
db (). Table ( "users" ) db (). Table ( User {}, "u" )
db (). Table ( "users" , "u" ) sub := db (). Table ( "users" ). Select ( "id" , "name" )
db (). Table ( sub ). Where ( "id" , ">" , 1 ). Get () type UserInfo struct {
UserId int64 `db:"user_id"`
TableName string `db:"user_info"`
} db (). Table ( "users" ). Join ( UserInfo {}, "user.id" , "=" , "user_info.user_id" ). Get () // select * from users a inner join user_info b on a.id=b.user_id
db (). Table ( "users" , "u" ). Join ( gorose . As ( UserInfo {}, "b" ), "u.id" , "=" , "b.user_id" ). Get ()
// 等同于
db (). Table ( User {}, "u" ). Join ( gorose . As ( "user_info" , "b" ), "u.id" , "=" , "b.user_id" ). Get () In gorose.As(UserInfo{}, "b") , user_info takes the alias b
db (). Table ( "users" ). Join ( UserInfo {}, func ( wh builder. IJoinOn ) {
wh . On ( "a.id" , "b.user_id" ). OrOn ( "a.sex" , "b.sex" )
}). Get ()
// 等同于
db (). Table ( "users" ). JoinOn ( UserInfo {}, func ( wh builder. IJoinOn ) {
wh . On ( "a.id" , "b.user_id" ). OrOn ( "a.sex" , "b.sex" )
}). Get () When the second parameter of Join is builder.IJoinOn , it is equivalent to JoinOn usage (the second parameter has a strong type reminder, which is convenient for IDE quick prompts)
// where id in (select user_id from user_info)
sub := db (). Table ( "user_info" ). Select ( "user_id" )
db (). Table ( User {}). Where ( "id" , "in" , sub ). Get () // where id in (select user_id from user_info)
db (). Table ( User {}). WhereSub ( "id" , "in" , func ( tx * builder. Context ) {
tx . Table ( "user_info" ). Select ( "user_id" )
}). Get () // where id in (select user_id from user_info)
sub := db (). Table ( "user_info" ). Select ( "user_id" )
db (). Table ( User {}). WhereBuilder ( "id" , "in" , sub ). Get ()The above three usages are equivalent
// where id>1 and (sex=1 or sex=2)
db (). Table ( User {}). Where ( "id" , ">" , 1 ). Where ( func ( wh builder. IWhere ) {
wh . Where ( "sex" , 1 ). OrWhere ( "sex" , 2 )
})Where here is equivalent to WhereNested
// where id>1 and (sex=1 or sex=2)
db (). Table ( User {}). Where ( "id" , ">" , 1 ). WhereNested ( func ( wh builder. IWhere ) {
wh . Where ( "sex" , 1 ). OrWhere ( "sex" , 2 )
})The above two usages are equivalent
Subqueries can be used in Table, Where, and Join.
sub := db (). Table ( "user" ). Where (). OrWhere ()
sub2 := db (). Table ( "address" ). Select ( "user_id" ). Where (). OrWhere ()
sub3 := db (). Table ( "user_info" ). Select ( "user_id" ). Where (). OrWhere ()Used in Table, Where, Join
db ().
Table ( sub , "a" ).
Join ( gorose . As ( sub2 , "b" ), "a.id" , "=" , "b.user_id" )).
WhereIn ( "a.id" , sub3 ).
Get ()Used in Union, UnionAll
db ().
Table ( "users" ).
Union ( sub ).
Get () var sub2222 = db (). Table ( "user" ). Where (). OrWhere ()
var to [] User
db ().
Table ( "users" ).
UnionAll ( sub , sub2222 ).
To ( & to )Return two columns of data into a map, the first column is value and the second column is key
// select id,name from users
db (). Table ( "users" ). Pluck ( "name" , "id" )
// 返回 map[<id>]<name>, 实际得到 map[int64]string{1: "张三", 2: "李四"} Return a column of data into an array
// select id,name from users
db (). Table ( "users" ). List ( "id" )
// 返回 []<id>, 实际得到 []int64{1,2,3} Use the structure field as the select field. Use the structure field value as the where condition. The query result is bound to the structure, supporting one or more fields.
// 查询一条数据
var user User
db (). To ( & user )
// 查询条件,一条数据
// select id,name,email from users where id=1
var user = User { Id : 1 }
db (). To ( & user )
// 查询多条数据
var users [] User
db (). To ( & users )
// 查询条件,多条数据
var users [] User
db (). Where ( "id" , ">" , 1 ). To ( & users )It is only used as a binding structure field for query results. It is not used as query fields and conditions. It is often used for join or manually specified field query binding.
type Result struct {
Id int64 `db:"id"`
Aname string `db:"aname"`
Bname string `db:"bname"`
}
var res Result
// select a.id, a.name aname, b.name bname from a inner join b on a.id=b.aid where a.id>1
db (). Table ( "a" ). Join ( "b" , "a.id" , "b.aid" ). Select ( "a.id" , "a.name aname" , "b.name bname" ). Where ( "a.id" , ">" , 1 ). Bind ( & res )The display name of the query field must be the same as the field tag (db) name of the structure, otherwise the number of fields that will not be assigned can be different.
var list [] int
db (). Table ( "users" ). ListTo ( "age" , & list )
var pluck map [ int64 ] string
db (). Table ( "users" ). PluckTo ( "name" , "id" , & pluck )
var value int
db (). Table ( "users" ). ValueTo ( "age" , & value ) var sum int
db (). Table ( "users" ). SumTo ( "age" , & sum )
var max int
db (). Table ( "users" ). MaxTo ( "age" , & max )
var min int
db (). Table ( "users" ). MinTo ( "age" , & min )By default, the slog debug level of the official library is used. If you do not want to display the sql log, you only need to set the slog level to above debug, such as: Info, Warn, Error
type User struct {
Id int64 `db:"id,pk"`
Sex * int8 `db:"sex"` // 使用指针可以绑定 null 值
Age sql. NullInt64 `db:"age"` // 使用sql.NullInt64 可以绑定 null 值
}pointer assignment
var sex = int8 ( 1 )
var user = User { Id : 1 , Sex : & sex }
// 或者,快捷用法
var user = User { Id : 1 , Sex : gorose . Ptr ( int8 ( 1 ))}sql.Null* assignment
var age = sql. NullInt64 { Int64 : 18 , Valid : true }sql.Null* use
if age . Valid {
fmt . Println ( age . Int64 )
}It can be seen that it is still a bit troublesome to deal with null. Therefore, it is recommended not to allow null when designing the table. Default values are given, and most of the default values can just correspond to the type zero value of go.
Table
Select
SelectRaw
AddSelect
Join
GroupBy
Having
HavingRaw
OrHaving
OrHavingRaw
OrderBy
Limit
Offset
Where
WhereRaw
OrWhere
OrWhereRaw
WhereBetween
OrWhereBetween
WhereNotBetween
OrWhereNotBetween
WhereIn
OrWhereIn
WhereNotIn
OrWhereNotIn
WhereNull
OrWhereNull
WhereNotNull
OrWhereNotNull
WhereLike
OrWhereLike
WhereNotLike
OrWhereNotLike
WhereExists
WhereNotExists
WhereNot
Get
First
Find
Insert
Update
Delete
Max
Min
Sum
Avg
Count
InsertGetId
Upsert
InsertOrIgnore
Exists
DoesntExist
Pluck
List
Value
Paginate
Increment
Decrement
IncrementEach
DecrementEach
Truncate
Union
UnionAll
SharedLock
LockForUpdate
Replace
Page
LastSql
WhereBuilder
OrWhereBuilder
WhereSub
OrWhereSub
WhereNested
OrWhereNested
To
Bind
ListTo
PluckTo
ValueTo
SumTo
MaxTo
UnionTo
UnionAllTo
