- 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
Topics
Resources
License
Uh oh!
There was an error while loading.Please reload this page.
Stars
Watchers
Forks
Packages0
Uh oh!
There was an error while loading.Please reload this page.
Contributors7
Uh oh!
There was an error while loading.Please reload this page.