- Notifications
You must be signed in to change notification settings - Fork39
🐥The very simple ORM library for Golang
License
ilibs/gosql
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
The package based onsqlx, It's simple and keep simple
- Remove the second argument to the Model() and Table() functions and replace it with WithTx(tx)
- Remove Model interface DbName() function,use the Use() function
- Uniform API design specification, seeAPIDESIGN
- Relation add
connection:"db2"struct tag, Solve the cross-library connection problem caused by deleting DbName() - Discard the WithTx function
Connection database and use sqlx original function,See thehttps://github.com/jmoiron/sqlx
import ( _"github.com/go-sql-driver/mysql"//mysql driver"github.com/ilibs/gosql/v2")funcmain(){configs:=make(map[string]*gosql.Config)configs["default"]=&gosql.Config{Enable:true,Driver:"mysql",Dsn:"root:123456@tcp(127.0.0.1:3306)/test?charset=utf8&parseTime=True&loc=Asia%2FShanghai",ShowSql:true, }//connection databasegosql.Connect(configs)gosql.QueryRowx("select * from users where id = 1")}
Usedefault database, So you can use wrapper function
//Execgosql.Exec("insert into users(name,email,created_at,updated_at) value(?,?,?,?)","test","test@gmail.com",time.Now(),time.Now())//Queryxrows,err:=gosql.Queryx("select * from users")forrows.Next() {user:=&Users{}err=rows.StructScan(user)}rows.Close()//QueryRowxuser:=&Users{}err:=gosql.QueryRowx("select * from users where id = ?",1).StructScan(user)//Getuser:=&Users{}err:=gosql.Get(user,"select * from users where id = ?",1)//Selectusers:=make([]Users)err:=gosql.Select(&users,"select * from users")//Change databasedb:=gosql.Use("test")db.Queryx("select * from tests")
You can also set the default database connection name
gosql.SetDefaultLink("log")gosql.Connect(configs)
gosql.Getetc., will use the configuration with the connection namelog
typeUsersstruct {Idint`db:"id"`Namestring`db:"name"`Emailstring`db:"email"`Statusint`db:"status"`CreatedAt time.Time`db:"created_at"`UpdatedAt time.Time`db:"updated_at"`}func (u*Users)TableName()string {return"users"}func (u*Users)PK()string {return"id"}//Getuser:=&Users{}gosql.Model(user).Where("id=?",1).Get()//Alluser:=make([]Users,0)gosql.Model(&user).All()//Create and auto set CreatedAtgosql.Model(&User{Name:"test",Email:"test@gmail.com"}).Create()//Updategosql.Model(&User{Name:"test2",Email:"test@gmail.com"}).Where("id=?",1).Update()//If you need to update the zero value, you can do sogosql.Model(&User{Status:0}).Where("id=?",1).Update("status")//Deletegosql.Model(&User{}).Where("id=?",1).Delete()
If you use struct to generate where conditions
//Get where id = 1 and name = "test1"user:=&Users{Id:1,Name:"test1"}gosql.Model(&user).Get()//Update default use primary key as the conditiongosql.Model(&User{Id:1,Name:"test2"}).Update()//Use custom conditions//Builder => UPDATE users SET `id`=?,`name`=?,`updated_at`=? WHERE (status = ?)gosql.Model(&User{Id:1,Name:"test2"}).Where("status = ?",1).Update()//Deletegosql.Model(&User{Id:1}).Delete()
But the zero value is filtered by default, you can specify fields that are not filtered. For example
user:=&Users{Id:1,Status:0}gosql.Model(&user).Get("status")
You can use thegenstruct tool to quickly generate database structs
TheTx function has a callback function, if an error is returned, the transaction rollback
gosql.Tx(func(tx*gosql.DB)error {forid:=1;id<10;id++ {user:=&Users{Id:id,Name:"test"+strconv.Itoa(id),Email:"test"+strconv.Itoa(id)+"@test.com", }//v2 support, do some database operations in the transaction (use 'tx' from this point, not 'gosql')tx.Model(user).Create()ifid==8 {returnerrors.New("interrupt the transaction") } }//query with transactionvarnuminterr:=tx.QueryRowx("select count(*) from user_id = 1").Scan(&num)iferr!=nil {returnerr }returnnil})
If you need to invoke context, you can use
gosql.Txx
Now support gosql.Begin() or gosql.Use("other").Begin() for example:
tx,err:=gosql.Begin()iferr!=nil {returnerr}forid:=1;id<10;id++ {_,err:=tx.Exec("INSERT INTO users(id,name,status,created_at,updated_at) VALUES(?,?,?,?,?)",id,"test"+strconv.Itoa(id),1,time.Now(),time.Now())iferr!=nil {returntx.Rollback() }}returntx.Commit()
If your fields contain the following field names, they will be updated automatically
AUTO_CREATE_TIME_FIELDS = []string{ "create_time", "create_at", "created_at", "update_time", "update_at", "updated_at",}AUTO_UPDATE_TIME_FIELDS = []string{ "update_time", "update_at", "updated_at",}CreateUpdateDeleteCount supportmap[string]interface,For example:
//Creategosql.Table("users").Create(map[string]interface{}{"id":1,"name":"test","email":"test@test.com","created_at":"2018-07-11 11:58:21","updated_at":"2018-07-11 11:58:21",})//Updategosql.Table("users").Where("id = ?",1).Update(map[string]interface{}{"name":"fifsky","email":"fifsky@test.com",})//Deletegosql.Table("users").Where("id = ?",1).Delete()//Countgosql.Table("users").Where("id = ?",1).Count()//Change databasegosql.Use("db2").Table("users").Where("id = ?",1).Count()//Transaction `tx`tx.Table("users").Where("id = ?",1}).Count()
Now Model support sql.Null* field's, Note, however, that if sql.Null* is also filtered by zero values,For example
typeUsersstruct {Idint`db:"id"`Namestring`db:"name"`Emailstring`db:"email"`Statusint`db:"status"`SuccessTime sql.NullString`db:"success_time" json:"success_time"`CreatedAt time.Time`db:"created_at" json:"created_at"`UpdatedAt time.Time`db:"updated_at" json:"updated_at"`}user:=&Users{Id:1,SuccessTime: sql.NullString{String:"2018-09-03 00:00:00",Valid:false, }}err:=gosql.Model(user).Get()
Builder SQL:
Query: SELECT * FROM users WHERE (id=?);Args: []interface {}{1}Time: 0.00082sIfsql.NullString ofValid attribute is false, SQL builder will ignore this zero value
Reference GORM Expr, Resolve update field self-update problem
gosql.Table("users").Update(map[string]interface{}{"id":2,"count":gosql.Expr("count+?",1)})//Builder SQL//UPDATE `users` SET `count`=count + ?,`id`=?; [1 2]
Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:
SELECT*FROMusersWHERElevelIN (?);
sqlx.In is encapsulated Ingosql and can be queried using the following schema
varlevels= []int{4,6,7}rows,err:=gosql.Queryx("SELECT * FROM users WHERE level IN (?);",levels)//oruser:=make([]Users,0)err:=gosql.Select(&user,"select * from users where id in(?)",[]int{1,2,3})
gosql used the golang structure to express the relationships between tables,You only need to use therelation Tag to specify the associated field, see example
connection tag
typeMomentListstruct {models.MomentsUser*models.Users`json:"user" db:"-" relation:"user_id,id"`//one-to-onePhotos []models.Photos`json:"photos" db:"-" relation:"id,moment_id" connection:"db2"`//one-to-many}
Get single result
moment:=&MomentList{}err:=gosql.Model(moment).Where("status = 1 and id = ?",14).Get()//output User and Photos and you get the result
SQL:
2018/12/0613:27:54Query:SELECT*FROM`moments`WHERE (status=1and id= ?);Args: []interface {}{14}Time:0.00300s2018/12/0613:27:54Query:SELECT*FROM`moment_users`WHERE (id=?);Args: []interface {}{5}Time:0.00081s2018/12/0613:27:54Query:SELECT*FROM`photos`WHERE (moment_id=?);Args: []interface {}{14}Time:0.00093s
Get list result, many-to-many
varmoments=make([]MomentList,0)err:=gosql.Model(&moments).Where("status = 1").Limit(10).All()//You get the total result for *UserMoment slice
SQL:
2018/12/0613:50:59Query:SELECT*FROM`moments`WHERE (status=1)LIMIT10;Time:0.00319s2018/12/0613:50:59Query:SELECT*FROM`moment_users`WHERE (idin(?));Args: []interface {}{[]interface {}{5}}Time:0.00094s2018/12/0613:50:59Query:SELECT*FROM`photos`WHERE (moment_idin(?, ?, ?, ?, ?, ?, ?, ?, ?, ?));Args: []interface {}{[]interface {}{1,2,3,4,5,6,7,8,9,10}}Time:0.00087s
Relation Where:
moment:=&MomentList{}err:=gosql.Relation("User" ,func(b*gosql.Builder) {//this is builder instance,b.Where("gender = 0")}).Get(moment ,"select * from moments")
Hooks are functions that are called before or after creation/querying/updating/deletion.
If you have defiend specified methods for a model, it will be called automatically when creating, updating, querying, deleting, and if any callback returns an error,gosql will stop future operations and rollback current transaction.
// begin transactionBeforeChangeBeforeCreate// update timestamp `CreatedAt`, `UpdatedAt`// saveAfterCreateAfterChange// commit or rollback transactionExample:
func (u*Users)BeforeCreate(ctx context.Context) (errerror) {ifu.IsValid() {err=errors.New("can't save invalid data") }return}func (u*Users)AfterCreate(ctx context.Context,tx*gosql.DB) (errerror) {ifu.Id==1 {u.Email=ctx.Value("email")tx.Model(u).Update() }return}
BeforeChange and AfterChange only used in create/update/delete
All Hooks:
BeforeChangeAfterChangeBeforeCreateAfterCreateBeforeUpdateAfterUpdateBeforeDeleteAfterDeleteBeforeFindAfterFindHook func type supports multiple ways:
func (u *Users) BeforeCreate()func (u *Users) BeforeCreate() (err error)func (u *Users) BeforeCreate(tx *gosql.DB)func (u *Users) BeforeCreate(tx *gosql.DB) (err error)func (u *Users) BeforeCreate(ctx context.Context)func (u *Users) BeforeCreate(ctx context.Context) (err error)func (u *Users) BeforeCreate(ctx context.Context, tx *rsql.DB)func (u *Users) BeforeCreate(ctx context.Context, tx *rsql.DB) (err error)If you want to usecontext feature, you need to use below function while start a sql, or the context in callback will be nil:
gosql.WithContext(ctx).Model(...)gosql.Use("xxx").WithContext(ctx).Model(...)
About
🐥The very simple ORM library for Golang