go-mysql-orm

golang mysql orm

A golang package dedicated to simplify developing with mysql database.

Completed Features:

• create mysql table from golang struct
• create golang struct from mysql table
• select to kinds of go types (struct,slice,map)
• update query, insert query, delete query
• transaction
• select (update | insert) with subquery
• join (left join | right join) table
• avoid using raw strings when query.

Planning Features:

• support golang generics to make select-query more easily

Get Started

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

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

//user table model
var UserTable = orm.NewQuery(User{}, db)

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

func (User) TableName() string {
    return "user"
}
func (User) DatabaseName() string {
    return "mydb"
}

migrate (create table from struct | create struct from table)

func main() {
    orm.CreateTableFromStruct(UserTable) //create db table, add new columns if table already exist.
    orm.CreateStructFromTable(UserTable) //create struct fields in code
}        

select query usage

    //query select
    {
        //get first user as struct
        user, query := UserTable.Get()
        fmt.Println(user, query.Sql(), query.Error())

        //get users as struct slice
        users, query := UserTable.Limit(5).Gets()
        fmt.Println(users, query.Sql(), query.Error())

        //get user first row as map[string]interface
        row, query := UserTable.GetRow()
        fmt.Println(row, query.Sql(), query.Error())

        //get user rows as []map[string]interface
        rows, query := UserTable.Limit(5).GetRows()
        fmt.Println(rows, query.Sql(), query.Error())

        //get users count(*)
        count, query := UserTable.GetCount()
        fmt.Println(count, query.Sql(), query.Error())

        //get users map key by id
        var usersKeyById map[int]User
        UserTable.GetTo(&usersKeyById)

        //get user names map key by id
        var userNameKeyById map[int]string
        UserTable.Select(&UserTable.T.Id, &UserTable.T.Name).GetTo(&userNameKeyById)

        //get users map key by name
        var usersMapkeyByName map[string][]User
        UserTable.Select(&UserTable.T.Name, UserTable.AllCols()).GetTo(&usersMapkeyByName)
    }

update | delete | insert

    //query update and delete and insert
    {
        //update user set name="hello" where id=1
        UserTable.Where(&UserTable.T.Id, 1).Update(&UserTable.T.Name, "hello")

        //query delete
        UserTable.Where(&UserTable.T.Id, 1).Delete()

        //query insert
        _ = UserTable.Insert(User{Name: "han"}).LastInsertId //insert one row and get id
    }

join and where

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

transaction

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

subquery

    {
        //subquery
        subquery := UserTable.Where(&UserTable.T.Id, 1).SubQuery()

        //where in suquery
        UserTable.Where(&UserTable.T.Id, orm.WhereIn, subquery).Gets()

        //insert subquery
        UserTable.InsertSubquery(subquery, nil)

        //join subquery
        UserTable.Join(subquery, func(query orm.Query[User]) orm.Query[User] {
            return query.Where(&UserTable.T.Id, orm.Raw("sub.id"))
        }).Gets()
    }

Relation (has many | belongs to)

    //each user has many orders
    func (*User) LoadOrders(users []User) {
        var userIds []int
        for _, v := range users {
            userIds = append(userIds, v.Id)
        }
        
        var userOrders map[int][]Order
        OrderTable.Where(&OrderTable.UserId, orm.WhereIn, userIds).
            Select(&OrderTable.UserId, OrderTable.AllCols()). 
            GetTo(&userOrders)
        
        for k := range users {
            users[k].Orders = userOrders[users[k].Id]
        }
    }   

details about migration

• use json tag by default
• orm tag will override json tag
• default: column default value
• comment: column comment
• first column auto mark as primary key
• created_at, updated_at: predefined columns

    type User struct {
            Id int `json:"id"`
            Email string `json:"email" orm:"email,varchar(64),null,unique,index_email_and_score" comment:"user email"`
            Score int `json:"score" orm:"score,index,index_email_and_score" comment:"user score"`
            Name string `json:"name" default:"john" comment:"user name"`
            CreatedAt time.Time `json:"created_at"`
            UpdatedAt time.Time `json:"updated_at"`
    }
    //create table IF NOT EXISTS `user` (
        //`id` int not null auto_increment,
        //`email` varchar(64) null comment 'user email',
        //`score` int not null default '0' comment 'user score',
        //`name` varchar(255) not null default 'john' comment 'user name',
        //`created_at` timestamp not null default CURRENT_TIMESTAMP,
        //`updated_at` timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        //primary key (`id`),
        //unique key `email` (`email`),
        //key `score` (`score`),
        //key `index_email_and_score` (`email`,`score`)
    //) 

contribute

Contribute to this project by submitting a(an) PR | issue. Any contribution will be Appreciated.

Thanks

Thanks to goland support