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
connection:"db2"
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") }
default
//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.Getlog
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
Tx
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 })
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
CreateUpdateDeleteCountmap[string]interface
//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
sql.NullStringValid
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.Ingosql
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
relation
connection
type MomentList struct { models.Moments User *models.Users `json:"user" db:"-" relation:"user_id,id"` //one-to-one Photos []*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/06 13:27:54 Query: SELECT * FROM `moments` WHERE (status = 1 and id = ?); Args: []interface {}{14} Time: 0.00300s 2018/12/06 13:27:54 Query: SELECT * FROM `moment_users` WHERE (id=?); Args: []interface {}{5} Time: 0.00081s 2018/12/06 13:27:54 Query: SELECT * FROM `photos` WHERE (moment_id=?); Args: []interface {}{14} Time: 0.00093s
Get list result, many-to-many
var moments = make([]*MomentList, 0) err := gosql.Model(&moments).Where("status = 1").Limit(10).All() //You get the total result for *UserMoment slice
SQL:
2018/12/06 13:50:59 Query: SELECT * FROM `moments` WHERE (status = 1) LIMIT 10; Time: 0.00319s 2018/12/06 13:50:59 Query: SELECT * FROM `moment_users` WHERE (id in(?)); Args: []interface {}{[]interface {}{5}} Time: 0.00094s 2018/12/06 13:50:59 Query: SELECT * FROM `photos` WHERE (moment_id in(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)); 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.ModelStruct) { //this is builder instance, b.Where("gender = 0") }).Get(moment , "select * from moments")
Hooks
Hooks are functions that are called before or after creation/querying/updating/deletion.
gosql
// begin transaction BeforeChange BeforeCreate // update timestamp `CreatedAt`, `UpdatedAt` // save AfterCreate AfterChange // commit or rollback transaction
Example:
func (u *Users) BeforeCreate() (err error) { if u.IsValid() { err = errors.New("can't save invalid data") } return } func (u *Users) AfterCreate(tx *gosql.DB) (err error) { if u.Id == 1 { u.Email = "after@test.com" tx.Model(u).Update() } return }
BeforeChange and AfterChange only used in create/update/delete
All Hooks:
BeforeChange AfterChange BeforeCreate AfterCreate BeforeUpdate AfterUpdate BeforeDelete AfterDelete BeforeFind AfterFind
Hook 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)