go-models-mysql

Releases
1

go-models

go-models its lite and easy model. This repo just a example.

modules list as :

Features

That is querybuilder with data object models for SQLs.
And easy way to build your data logical layer for access redis.
This is a easy way to access data from database. That you focus on data processing logical.
Now support MySQL, MariaDB, Redis

TODO support: PostgreSQL, MSSQL, SQLite Mongodb, ...
  • Field scanning has become easier since the original driver was extended.
    Assumption: we have 5 fields to scan
type Tb struct {
	field0 sql.NullString,
	field1 sql.NullString,
	field2 sql.NullString,
	field3 sql.NullString,
	field4 sql.NullString,
}

In original driver, it can’t dynamic. how many fields, that you must write fields many how. if you have 20 fileds, you must write 20 times.

var tb Tb
err = rows.Scan(&tb.field0, &tb.field1, &tb.field2, &tb.field3, &tb.field4)

In go-models-mysql , you just fill struct nil pointer.

if val, err = myDao.ScanRowType(row, (*Tb)(nil)); err == nil {
	u, _ := val.(*Tb)
	fmt.Println("Tb", u)
}
  • DAO layer let you operate mysql more Intuitively.
    • Original driver (sql.DB) was extended, so you can operate original commands.
      • ex: Query, QueryRow, Exec …
    • Import the sqlbuilder that help access sql db easily.
    myDao.Select("Host", "User", "Select_priv").From("user").Where("User='root'").Limit(1)
    • Set the default table in DAO, that you can design your dao layer friendly.
    // set a struct for dao as default model (option)
// (*UserTb)(nil) : nil pointer of the UserTb struct
// "user" : is real table name in the db
myUserDao.SetDefaultModel((*UserTb)(nil), "user")

// call model's Get() , get all rows in user table
// return (rows *sql.Rows, err error)
rows, err = myDao.Get()

Requirements

* Go 1.12 or higher.
* [database/sql](https://golang.org/pkg/database/sql/) package
* [go-sql-driver/mysql](https://github.com/go-sql-driver/mysql) package
* [go-redis/redis](https://github.com/go-redis/redis) package

Go-Module

create go.mod file in your package folder, and fill below

module github.com/eehsiao/go-models-example

go 1.13

require (
	github.com/eehsiao/go-models-lib latest
	github.com/eehsiao/go-models-mysql latest
	github.com/eehsiao/go-models-redis latest
	github.com/eehsiao/sqlbuilder latest
	github.com/go-redis/redis v6.15.5+incompatible
	github.com/go-sql-driver/mysql v1.4.1
)

Docker

Easy to start the test evn. That you can run the example code.

$ docker-compose up -d

Usage

import (
    "database/sql"
	"fmt"

	mysql "github.com/eehsiao/go-models-mysql"
	redis "github.com/eehsiao/go-models-redis"
)

// UserTb : sql table struct that to store into mysql
type UserTb struct {
	Host       sql.NullString `TbField:"Host"`
	User       sql.NullString `TbField:"User"`
	SelectPriv sql.NullString `TbField:"Select_priv"`
}

// User : json struct that to store into redis
type User struct {
	Host       string `json:"host"`
	User       string `json:"user"`
	SelectPriv string `json:"select_priv"`
}

//new mysql dao
myUserDao := &MyUserDao{
    Dao: mysql.NewDao().SetConfig("root", "mYaDmin", "127.0.0.1:3306", "mysql").OpenDB(),
}

// example 1 : directly use the sqlbuilder
myUserDao.Select("Host", "User", "Select_priv").From("user").Where("User='root'").Limit(1)
fmt.Println("sqlbuilder", myUserDao.BuildSelectSQL())
if row, err = myUserDao.GetRow(); err == nil {
    if val, err = myUserDao.ScanRowType(row, (*UserTb)(nil)); err == nil {
        u, _ := val.(*UserTb)
        fmt.Println("UserTb", u)
    }
}
    
// set a struct for dao as default model (option)
// (*UserTb)(nil) : nil pointer of the UserTb struct
// "user" : is real table name in the db
myUserDao.SetDefaultModel((*UserTb)(nil), "user")

// call model's Get() , get all rows in user table
// return (rows *sql.Rows, err error)
rows, err = myDao.Get()

// call model's GetRow() , get first row in user rows
// return (row *sql.Row, err error)
row, err = myDao.GetRow()


//new redis dao
redUserModel := &RedUserModel{
    Dao: redis.NewDao().SetConfig("127.0.0.1:6379", "", 0).OpenDB(),
}

// set a struct for dao as default model (option)
// (*User)(nil) : nil pointer of the User struct
// "user" : is real table name in the db
SetDefaultModel((*User)(nil), "user")

Lib

lib.Iif : is a inline IF-ELSE logic
lib.Struct4Scan : transfer a object struct to poiter slces, that easy to scan the sql results.
lib.Struce4Query : transfer a struct to a string for sql select fields. ex "idx, name".
lib.Struce4QuerySlice : transfer a struct to a []string slice.
lib.Serialize : serialize a object to a json string.

SqlBuilder

sqlbuilder its recursive call function, that you can easy to build sql string

ex: dao.Select().From().Join().Where().Limit()

SqlBuilder functions

  • build select :
    • Select(s …string)
    • Distinct(b bool)
    • Top(i int)
    • From(s …string)
    • Where(s string)
    • WhereAnd(s …string)
    • WhereOr(s …string)
    • Join(s string, c string)
    • InnerJoin(s string, c string)
    • LeftJoin(s string, c string)
    • RightJoin(s string, c string)
    • FullJoin(s string, c string)
    • GroupBy(s …string)
    • OrderBy(s …string)
    • OrderByAsc(s …string)
    • OrderByDesc(s …string)
    • Having(s string)
    • BuildSelectSQL()
  • build update :
    • Set(s map[string]interface{})
    • FromOne(s string)
    • BuildUpdateSQL()
  • build insert :
    • Into(s string)
    • Fields(s …string)
    • Values(s …[]interface{})
    • BuildInsertSQL()
  • build delete :
    • BuildDeleteSQL()
  • common :
    • ClearBuilder()
    • BuildedSQL()
    • SetDbName(s string)
    • SetTbName(s string)
    • SwitchPanicToErrorLog(b bool)
    • PanicOrErrorLog(s string)

Example

1 build-in

example.go

The example will connect to local mysql and get user data.
Then connect to local redis and set user data, and get back.

2 example

https://github.com/eehsiao/go-models-example/

How-to

How to design model data logical

MySQL

1.

create a table struct, and add the tag TbField:"real table filed"

TbField the tag is musted. read table filed also be same the table field.

type UserTb struct {
	Host       sql.NullString `TbField:"Host"`
	User       sql.NullString `TbField:"User"`
	SelectPriv sql.NullString `TbField:"Select_priv"`
}

2.

use Struce4QuerySlice to gen the sqlbuilder select fields

m := mysql.NewDao().SetConfig("root", "mYaDmin", "127.0.0.1:3306", "mysql").OpenDB()
m.Select(lib.Struce4QuerySlice(m.DaoStructType)...).From(m.TbName).Limit(3)

3.

scan the sql result to the struct of object

row, err = m.GetRow()
if val, err = m.ScanRowType(row, (*UserTb)(nil)); err == nil {
    u, _ := val.(*UserTb)
    fmt.Println("UserTb", u)
}

Redis

1.

create a data struct, and add the tag json:"name"

type User struct {
	Host       string `json:"host"`
	User       string `json:"user"`
	SelectPriv string `json:"select_priv"`
	IntVal     int    `json:"user,string"`
}

if you have integer value, you can add a transfer type desc.
such as json:“user,string”

2.

create redis dao

m := redis.NewDao().SetConfig("127.0.0.1:6379", "", 0).OpenDB()

3.

directly use the go-redis command function

redBool, err = m.HSet(userTable, redKey, serialStr).Result()
1 Like
2

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.