Running SQL Queries

After configuration you can run querys using goeloquent.Eloquent
From now on ,we use DB as a shortcut for goeloquent.Eloquent

Select Query

Select map


var row = make(map[string]interface{})
r, err := DB.Select("select * from users where id = ? ", []interface{}{4}, &row)
if err != nil {
    panic(err.Error())
}
fmt.Printf("%#v\n",row)
//map[string]interface {}{"age":0, "balance":100, "email":[]uint8{0x6a, 0x6f, 0x68, 0x6e, 0x40, 0x68, 0x6f, 0x74, 0x6d, 0x61, 0x69, 0x6c, 0x2e, 0x63, 0x6f, 0x6d}, "id":4, "username":[]uint8{0x6a, 0x6f, 0x68, 0x6e}}

Select slice of maps

var rows []map[string]interface{}
r, err = DB.Select("select * from users limit 10 ", nil, &rows)

Select struct

Struct fileds will convent to datablse column name use snakecase,in this exmaple,we will assume that table users has a id,nick_name,age column.

If you have a different name, use goelo:"column:name" to assign it,just remeber ,either you don’t use tag or use tag for every database column.


type User struct {
	Id       int64
	Name     string 
	Age      int
	
}
type MappingUser struct {
    Id       int64  `goelo:"column:id"`
    UserName string `goelo:"column:name"`
    RealAge  int    `goelo:"column:age"`
}

var user User
var mapUser MappingUser
DB.Select("select * from users where id = ? ", []interface{}{1}, &user)
DB.Select("select * from users where id = ? ", []interface{}{2}, &mapUser)
fmt.Printf("%#v\n%#v\n", user,mapUser)
//tests.User{Id:1, Name:"Alice", Age:33}
//tests.MappingUser{Id:2, UserName:"John", RealAge:12}

Select slice of structs

var users []User
DB.Select("select * from users limit 10 ", nil, &users)
fmt.Printf("%#v\n", users)

Select method first parameter is the sql string, second one is the query bindings, if no bindings needed ,can be nil or empty []interface{}{}, third one is the dest.
For third parameter dest ,you can pass by a pointer of map,a pointer of slice of map , a pointer of struct , a pointer of slice of struct, we will convert it for you .

Usually every query of this package returns two value. The first is always the Golang standard liberary database/sql package sql.Result.Second is the raised error if there is one.

When you run insert query , you can call result.LastInsertId() to get the created record’s id. When you select,update,delete, you can call result.RowsAffected() to get the number of affected rows.

Insert Query

Insert Row&Rows

result, err := DB.Insert("insert into users (nick_name,email,age) values (?,?,?)", []interface{}{"John Doe", "john@hotmail.com", 50})

if err != nil {
    panic(err.Error())
}
fmt.Println(result.LastInsertId())

Update Query

Update Rows

DB.Update("update users set age = 18 where age < 18 ", nil)

Delete Query

Delete Rows

result, err := DB.Delete("delete from users where id  = ?", []interface{}{100})
if err != nil {
    panic(err.Error())
}
fmt.Println(result.RowsAffected())

Statement

_, err := DB.Statement("drop table tests", nil)
	if err != nil {
		panic(err.Error())
	}

Switch Connections

You may have mutiple connections.
If you want change the connection or specify use another connection instead of defautl, use Connection(), it takes a string as the connection name which you defined in config.

_, err := DB.Connection("chat").Statement("drop table test", nil)
if err != nil {
    panic(err.Error())
}

Get Raw Connection

In case you need original connection you can use Raw,this method return a *sql.DB

DB.Raw("default").Exec("insert into `users` (`name`,`age`) values ('Alice',33)")

Transactions

Closure

if panic happens inside closure,transaction will roll back

_, err := DB.Transaction(func(tx *goeloquent.Transaction) (interface{}, error) {
		_, errInsert := tx.Table("users").Insert(u1)
		var c int
		_,err := tx.Table("users").Where("name", "TestTransaction").Count(&c)
		if err!=nil{
		    panic(errors.New("test roll back"))
		}
		return true, nil
	})

Hexo

Basic Usage