高效便捷的Go语言数据库操作库 - gosql基于sqlx封装

gosql Go Go 共40个文件 go: 30 md: 3 yml: 1 gitignore: 1 LICENSE: 1 Makefile: 1 mod: 1 sum: 1 sql: 1 基于sqlx封装的golang数据库操作 gosql The package based on sqlx, It's simple and keep simple ⚠️ Because of some disruptive changes, The current major version is upgraded to V2，If you continue with V1, you can check out the v1 branches https://github.com/ilibs/gosql/tree/v1 V2 ChangeLog 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, see APIDESIGN Relation add connection:"db2" struct tag, Solve the cross-library connection problem caused by deleting DbName() Discard the WithTx function Usage Connection database and use sqlx original function,See the https://github.com/jmoiron/sqlx import ( _ "github.com/go-sql-driver/mysql" //mysql driver "github.com/ilibs/gosql/v2" ) func main(){ 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 database gosql.Connect(configs) gosql.QueryRowx("select * from users where id = 1") } Use default database, So you can use wrapper function //Exec gosql.Exec("insert into users(name,email,created_at,updated_at) value(?,?,?,?)","test","test@gmail.com",time.Now(),time.Now()) //Queryx rows,err := gosql.Queryx("select * from users") for rows.Next() { user := &Users{} err = rows.StructScan(user) } rows.Close() //QueryRowx user := &Users{} err := gosql.QueryRowx("select * from users where id = ?",1).StructScan(user) //Get user := &Users{} err := gosql.Get(user,"select * from users where id = ?",1) //Select users := make([]*Users) err := gosql.Select(&users,"select * from users") //Change database db := gosql.Use("test") db.Queryx("select * from tests") You can also set the default database connection name gosql.SetDefaultLink("log") gosql.Connect(configs) gosql.Get etc., will use the configuration with the connection name log Using struct type Users struct { Id int `db:"id"` Name string `db:"name"` Email string `db:"email"` Status int `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" } //Get user := &Users{} gosql.Model(user).Where("id=?",1).Get() //All user := make([]*Users,0) gosql.Model(&user).All() //Create and auto set CreatedAt gosql.Model(&User{Name:"test",Email:"test@gmail.com"}).Create() //Update gosql.Model(&User{Name:"test2",Email:"test@gmail.com"}).Where("id=?",1).Update() //If you need to update the zero value, you can do so gosql.Model(&User{Status:0}).Where("id=?",1).Update("status") //Delete gosql.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 condition gosql.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() //Delete gosql.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 the genstruct tool to quickly generate database structs Transaction The Tx function has a callback function, if an error is returned, the transaction rollback gosql.Tx(func(tx *gosql.DB) error { for id := 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() if id == 8 { return errors.New("interrupt the transaction") } } //query with transaction var num int err := tx.QueryRowx("select count(*) from user_id = 1").Scan(&num) if err != nil { return err } return nil }) 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() if err != nil { return err } for id := 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()) if err != nil { return tx.Rollback() } } return tx.Commit() Automatic time 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", } Using Map Create Update Delete Count support map[string]interface,For example: //Create gosql.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", }) //Update gosql.Table("users").Where("id = ?", 1).Update(map[string]interface{}{ "name": "fifsky", "email": "fifsky@test.com", }) //Delete gosql.Table("users").Where("id = ?", 1).Delete() //Count gosql.Table("users").Where("id = ?", 1).Count() //Change database gosql.Use("db2").Table("users").Where("id = ?", 1).Count() //Transaction `tx` tx.Table("users").Where("id = ?", 1}).Count() sql.Null* Now Model support sql.Null* field's, Note, however, that if sql.Null* is also filtered by zero values,For example type Users struct { Id int `db:"id"` Name string `db:"name"` Email string `db:"email"` Status int `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.00082s If sql.NullString of Valid attribute is false, SQL builder will ignore this zero value gosql.Expr 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] "In" Queries 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 * FROM users WHERE level IN (?); sqlx.In is encapsulated In gosql and can be queried using the following schema var levels = []int{4, 6, 7} rows, err := gosql.Queryx("SELECT * FROM users WHERE level IN (?);", levels) //or user := make([]*Users, 0) err := gosql.Select(&user, "select * from users where id in(?)",[]int{1,2,3}) Relation gosql used the gola