gosql

package module
v1.1.9 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jul 9, 2019 License: MIT Imports: 16 Imported by: 1

README

gosql

The package based on sqlx, It's simple and keep simple

Build Status codecov Go Report Card GoDoc

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"
)

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.DB().QueryRowx("select * from users where id = 1")
}

Use default database, So you can use wrapper function

//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)
}

//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.Get etc., will use the configuration with the connection name log

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) DbName() string {
	return "default"
}

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

The Tx function has a callback function, if an error is returned, the transaction rollback

gosql.Tx(func(tx *sqlx.Tx) error {
    for id := 1; id < 10; id++ {
        user := &Users{
            Id:    id,
            Name:  "test" + strconv.Itoa(id),
            Email: "test" + strconv.Itoa(id) + "@test.com",
        }

        gosql.Model(user, tx).Create()

        if id == 8 {
            return errors.New("interrupt the transaction")
        }
    }

    //query with transaction
    var num int
    err := gosql.WithTx(tx).QueryRowx("select count(*) from user_id = 1").Scan(&num)

    if err != nil {
        return err
    }

    return nil
})

If you need to invoke context, you can use gosql.Txx

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

Create Update Delete Count support map[string]interface,For example:

//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` is *sqlx.Tx
gosql.Table("users",tx).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

If sql.NullString of Valid attribute is false, SQL builder will ignore this zero value

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.In is encapsulated In gosql and can be queried using the following schema

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

gosql used the golang structure to express the relationships between tables,You only need to use the relation Tag to specify the associated field, see example

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"`     //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 *Builder) {
    //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.

If you have defiend specified methods for a model, it will be called automatically when creating, updating, querying, deleting, and if any callback returns an error, gosql will stop future operations and rollback current transaction.

// 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 *sqlx.tx) (err error) {
  if u.Id == 1 {
    u.Email = "after@test.com"
    Model(u,tx).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 *sqlx.Tx)
func (u *Users) BeforeCreate(tx *sqlx.Tx) (err error)

Thanks

sqlx https://github.com/jmoiron/sqlx

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (

	//Insert database automatically updates fields
	AUTO_CREATE_TIME_FIELDS = []string{
		"create_time",
		"create_at",
		"created_at",
		"update_time",
		"update_at",
		"updated_at",
	}
	//Update database automatically updates fields
	AUTO_UPDATE_TIME_FIELDS = []string{
		"update_time",
		"update_at",
		"updated_at",
	}
)
View Source 
var FatalExit = true

If database fatal exit

Functions

func Connect 

func Connect(configs map[string]*Config) (err error)

Connect database

func DB 

func DB(name ...string) *sqlx.DB

DB gets the specified database engine, or the default DB if no name is specified.

func Exec 

func Exec(query string, args ...interface{}) (sql.Result, error)

Exec default database

func Expr added in v1.0.10 

func Expr(expression string, args ...interface{}) *expr

Expr generate raw SQL expression, for example: 

gosql.Table("user").Update(map[string]interface{}{"price", gorm.Expr("price * ? + ?", 2, 100)})

func Get 

func Get(dest interface{}, query string, args ...interface{}) error

Get default database

func Import 

func Import(f string) ([]sql.Result, error)

Import SQL DDL from io.Reader

func IsZero 

func IsZero(val reflect.Value) bool

IsZero assert value is zero value

func List 

func List() map[string]*sqlx.DB

List gets the list of database engines

func QueryRowx 

func QueryRowx(query string, args ...interface{}) *sqlx.Row

QueryRowx default database

func Queryx 

func Queryx(query string, args ...interface{}) (*sqlx.Rows, error)

Queryx default database

func RelationAll added in v1.1.0 

func RelationAll(data interface{}, chains map[string]BuilderChainFunc) error

RelationAll is gets the associated relational data for multiple pieces of data

func RelationOne added in v1.1.0 

func RelationOne(data interface{}, chains map[string]BuilderChainFunc) error

RelationOne is get the associated relational data for a single piece of data

func Select 

func Select(dest interface{}, query string, args ...interface{}) error

Select default database 

func SetDefaultLink(db string)

SetDefaultLink set default link name

func SetLogger 

func SetLogger(l Logger)

func SetLogging added in v1.0.1 

func SetLogging(logging bool)

SetLogging set default logger

func Tx 

func Tx(fn func(tx *sqlx.Tx) error) error

Tx default database the transaction

func Txx 

func Txx(ctx context.Context, fn func(ctx context.Context, tx *sqlx.Tx) error) error

Txx default database the transaction with context

Types

type Builder 

type Builder struct {
	SQLBuilder
	// contains filtered or unexported fields
}

func Model 

func Model(model interface{}, tx ...*sqlx.Tx) *Builder

Model construct SQL from Struct

func (*Builder) All 

func (b *Builder) All() (err error)

All get data rows from to Struct

func (*Builder) Count 

func (b *Builder) Count(zeroValues ...string) (num int64, err error)

gosql.Model(&User{}).Where("status = 0").Count()

func (*Builder) Create 

func (b *Builder) Create() (lastInsertId int64, err error)

Create data from to Struct

func (*Builder) Delete 

func (b *Builder) Delete(zeroValues ...string) (affected int64, err error)

gosql.Model(&User{Id:1}).Delete()

func (*Builder) ForceIndex added in v1.1.7 

func (b *Builder) ForceIndex(i string) *Builder

ForceIndex

func (*Builder) Get 

func (b *Builder) Get(zeroValues ...string) (err error)

All get data row from to Struct

func (*Builder) Hint 

func (b *Builder) Hint(hint string) *Builder

Hint is set TDDL "/*+TDDL:slave()*/"

func (*Builder) Limit 

func (b *Builder) Limit(i int) *Builder

Limit

func (*Builder) Offset 

func (b *Builder) Offset(i int) *Builder

Offset

func (*Builder) OrderBy 

func (b *Builder) OrderBy(str string) *Builder

OrderBy for example "id desc"

func (*Builder) Relation added in v1.1.0 

func (b *Builder) Relation(fieldName string, fn BuilderChainFunc) *Builder

Relation association table builder handle

func (*Builder) Select added in v1.1.4 

func (b *Builder) Select(fields string) *Builder

func (*Builder) ShowSQL 

func (b *Builder) ShowSQL() *Builder

ShowSQL output single sql

func (*Builder) Update 

func (b *Builder) Update(zeroValues ...string) (affected int64, err error)

gosql.Model(&User{Id:1,Status:0}).Update("status")

func (*Builder) Where 

func (b *Builder) Where(str string, args ...interface{}) *Builder

Where for example Where("id = ? and name = ?",1,"test")

func (*Builder) WithTx added in v1.1.5 

func (b *Builder) WithTx(tx *sqlx.Tx) *Builder

WithTx model use tx

type BuilderChainFunc added in v1.1.1 

type BuilderChainFunc func(b *Builder)

type Config 

type Config struct {
	Enable       bool   `toml:"enable" json:"enable"`
	Driver       string `toml:"driver" json:"driver"`
	Dsn          string `toml:"dsn" json:"dsn"`
	MaxOpenConns int    `toml:"max_open_conns" json:"max_open_conns"`
	MaxIdleConns int    `toml:"max_idle_conns" json:"max_idle_conns"`
	MaxLifetime  int    `toml:"max_lefttime" json:"max_lefttime"`
	ShowSql      bool   `toml:"show_sql" json:"show_sql"`
}

Config is database connection configuration

type Hook added in v1.0.1 

type Hook struct {
	Errs []error
	// contains filtered or unexported fields
}

func NewHook added in v1.0.1 

func NewHook(wrapper *Wrapper) *Hook

func (*Hook) Err added in v1.0.1 

func (h *Hook) Err(err error) error

Err add error

func (*Hook) Error added in v1.0.1 

func (h *Hook) Error() error

Error format happened errors

func (*Hook) HasError added in v1.0.1 

func (h *Hook) HasError() int

HasError has errors

type IModel 

type IModel interface {
	TableName() string
	DbName() string
	PK() string
}

Model interface

type ISqlx 

type ISqlx interface {
	Queryx(query string, args ...interface{}) (*sqlx.Rows, error)
	QueryRowx(query string, args ...interface{}) *sqlx.Row
	Get(dest interface{}, query string, args ...interface{}) error
	Select(dest interface{}, query string, args ...interface{}) error
	Exec(query string, args ...interface{}) (sql.Result, error)
	Rebind(query string) string
}

type Logger 

type Logger interface {
	Printf(format string, v ...interface{})
}

Logger represents a logging collector. You can pass a logging collector to gosql.SetLogger(myCollector) to make it collect QueryStatus messages after executing a query.

type Mapper 

type Mapper struct {
	SQLBuilder
	// contains filtered or unexported fields
}

func Table 

func Table(t string, tx ...*sqlx.Tx) *Mapper

Table select table name

func (*Mapper) Count 

func (m *Mapper) Count() (num int64, err error)

Count data from to map[string]interface

func (*Mapper) Create 

func (m *Mapper) Create(data map[string]interface{}) (lastInsertId int64, err error)

Create data from to map[string]interface

func (*Mapper) Delete 

func (m *Mapper) Delete() (affected int64, err error)

Delete data from to map[string]interface

func (*Mapper) ShowSQL 

func (m *Mapper) ShowSQL() *Mapper

func (*Mapper) Update 

func (m *Mapper) Update(data map[string]interface{}) (affected int64, err error)

Update data from to map[string]interface

func (*Mapper) Where 

func (m *Mapper) Where(str string, args ...interface{}) *Mapper

Where

func (*Mapper) WithTx added in v1.1.5 

func (m *Mapper) WithTx(tx *sqlx.Tx) *Mapper

WithTx Table use tx

type QueryStatus 

type QueryStatus struct {
	Query string
	Args  interface{}

	Start time.Time
	End   time.Time

	Err error
}

QueryStatus represents the status of a query after being executed.

func (*QueryStatus) String 

func (q *QueryStatus) String() string

String returns a formatted log message.

type ReflectMapper 

type ReflectMapper struct {
	// contains filtered or unexported fields
}

func NewReflectMapper 

func NewReflectMapper(tagName string) *ReflectMapper

func (*ReflectMapper) FieldByName added in v1.1.0 

func (r *ReflectMapper) FieldByName(v reflect.Value, name string) reflect.Value

FieldByName returns a field by its mapped name as a reflect.Value. Panics if v's Kind is not Struct or v is not Indirectable to a struct Kind. Returns zero Value if the name is not found.

func (*ReflectMapper) FieldMap 

func (r *ReflectMapper) FieldMap(v reflect.Value) map[string]reflect.Value

FieldMap returns the mapper's mapping of field names to reflect values. Panics if v's Kind is not Struct, or v is not Indirectable to a struct kind.

type SQLBuilder 

type SQLBuilder struct {
	// contains filtered or unexported fields
}

func (*SQLBuilder) Where 

func (s *SQLBuilder) Where(str string, args ...interface{})

type Wrapper 

type Wrapper struct {
	RelationMap map[string]BuilderChainFunc
	// contains filtered or unexported fields
}

func Relation added in v1.1.1 

func Relation(name string, fn BuilderChainFunc) *Wrapper

Relation association table builder handle

func ShowSql 

func ShowSql() *Wrapper

func Use 

func Use(db string) *Wrapper

Use is change database

func WithTx 

func WithTx(tx *sqlx.Tx) *Wrapper

WithTx use the specified transaction session

func (*Wrapper) Exec 

func (w *Wrapper) Exec(query string, args ...interface{}) (result sql.Result, err error)

Exec wrapper sqlx.Exec

func (*Wrapper) Get 

func (w *Wrapper) Get(dest interface{}, query string, args ...interface{}) (err error)

Get wrapper sqlx.Get

func (*Wrapper) Import 

func (w *Wrapper) Import(f string) ([]sql.Result, error)

Import SQL DDL from sql file

func (*Wrapper) QueryRowx 

func (w *Wrapper) QueryRowx(query string, args ...interface{}) (rows *sqlx.Row)

QueryRowx wrapper sqlx.QueryRowx

func (*Wrapper) Queryx 

func (w *Wrapper) Queryx(query string, args ...interface{}) (rows *sqlx.Rows, err error)

Queryx wrapper sqlx.Queryx

func (*Wrapper) Rebind added in v1.0.12 

func (w *Wrapper) Rebind(query string) string

Rebind wrapper sqlx.Rebind

func (*Wrapper) Relation added in v1.1.1 

func (w *Wrapper) Relation(name string, fn BuilderChainFunc) *Wrapper

Relation association table builder handle

func (*Wrapper) Select 

func (w *Wrapper) Select(dest interface{}, query string, args ...interface{}) (err error)

Select wrapper sqlx.Select

func (*Wrapper) Table 

func (w *Wrapper) Table(t string) *Mapper

Table database handler from to table name for example gosql.Use("db2").Table("users")

func (*Wrapper) Tx 

func (w *Wrapper) Tx(fn func(tx *sqlx.Tx) error) (err error)

Tx the transaction

func (*Wrapper) Txx 

func (w *Wrapper) Txx(ctx context.Context, fn func(ctx context.Context, tx *sqlx.Tx) error) (err error)

Txx the transaction with context

Source Files

View all Source files

Directories

Path Synopsis
example
models

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.