go-mysql-orm

README

Base on go-sql-driver/mysql.

Get Started

import (
    "database/sql"
    "github.com/folospace/go-mysql-orm/orm"
)



//connect mysql db
var db, _ = orm.OpenMysql("user:password@tcp(127.0.0.1:3306)/mydb?parseTime=true&charset=utf8mb4&loc=Asia%2FShanghai")

//user table
var UserTable = new(User)

type User struct {
    Id   int    `json:"id"`
    Name string `json:"name"`
}

func (User) Connections() []*sql.DB {
    return []*sql.DB{db}
}
func (User) DatabaseName() string {
    return "mydb"
}
func (User) TableName() string {
    return "user"
}
func (u *User) Query() *orm.Query[User] {
    return orm.NewQuery(u)
}

func main() {
    //create db table, add new columns if table already exist.
    UserTable.Query().CreateTable()  
    
    //create struct from db table
    UserTable.Query().CreateStruct() 

    //select * from user where id in (1,2,3)
    users, query = UserTable.Query().Gets(1, 2, 3)
}

select

    //get first user (name='join') as struct
    user, query := UserTable.Query().Where(&UserTable.Name, "john").Get()
    
    //get users count(*)
    count, query := UserTable.Query().GetCount()
    
    //get user names map key by id
    var userNameKeyById map[int]string
    UserTable.Query().Select(&UserTable.Id, &UserTable.Name).GetTo(&userNameKeyById)
    
    //get users map key by name
    //useful when find has-many relations
    var usersMapkeyByName map[string][]User
    UserTable.Query().Select(&UserTable.Name, UserTable).GetTo(&usersMapkeyByName)
    

update | delete | insert

    //update user set name="hello" where id = 1
    UserTable.Query().WherePrimary(1).Update(&UserTable.Name, "hello")
    
    //query delete
    UserTable.Query().Delete(1, 2, 3)
    
    //query insert
    _ = UserTable.Query().Insert(User{Name: "han"}).LastInsertId //insert one row and get id

join

    //query join 
    UserTable.Query().Join(OrderTable, func (query *orm.Query[User]) *orm.Query[User] {
            return query.Where(&UserTable.Id, &OrderTable.UserId)
    }).Select(UserTable).Gets()

transaction

    //transaction
    _ = UserTable.Query().Transaction(func (query *orm.Query[User]) error {
        newId := query.Insert(User{Name: "john"}).LastInsertId //insert
        //newId := orm.NewQuery(UserTable).UseTx(query.Tx()).Insert(User{Name: "john"}).LastInsertId
        fmt.Println(newId)
        return errors.New("I want rollback") //rollback
    })

subquery

    //subquery
    subquery := UserTable.Query().Where(&UserTable.Id, 1).SubQuery()
    
    //where in suquery
    UserTable.Query().Where(&UserTable.Id, orm.WhereIn, subquery).Gets()
    
    //insert subquery
    UserTable.Query().InsertSubquery(subquery, nil)
    
    //join subquery
    UserTable.Query().Join(subquery, func (query *orm.Query[User]) *orm.Query[User] {
        return query.Where(&UserTable.Id, orm.Raw("sub.id"))
    }).Gets()