# sq

**Repository Path**: unsafe-rust/sq

## Basic Information

- **Project Name**: sq
- **Description**: 操作SQL数据库的轻量级orm

只在 sqlx上做简单的封装。
- **Primary Language**: Go
- **License**: Apache-2.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2021-03-26
- **Last Updated**: 2023-05-30

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

#一、sq简介:
* sq 是对 [sqlx](https://gitee.com/gopher2011/sqlx) 的简单封装。这意味着`sq`能直接调用`sqlx`的`API`。
  

* 大部分`API`与`database/sql`包下的`API`同名。
  

* 只有`Take()`和`Select()`这2个`API`是自定义`API`。
##二、使用方法:

```go
package main

import (
    _ "github.com/go-sql-driver/mysql" //mysql driver
    "gitee.com/gopher2011/sq"
)

func main(){
    configs := make(map[string]*sq.Config)

    configs["default"] = &sq.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
    sq.Open(configs)
    sq.QueryRowX("select * from users where id = 1")
}

```

###1、使用 `default` 数据库, 就能使用`sq`内置的函数`CRUD`数据库。
* 直接使用`SQL语句`操作数据库

```go
//Exec
sq.Exec("insert into users(name,email,created_at,updated_at) value(?,?,?,?)","test","test@gmail.com",time.Now(),time.Now())

//QueryX
rows,err := sq.QueryX("select * from users")
for rows.Next() {
    user := &Users{}
    err = rows.StructScan(user)
}
rows.Close()

//QueryRowX
user := &Users{}
err := sq.QueryRowX("select * from users where id = ?",1).StructScan(user)

//Take
user := &Users{}
err := sq.Take(user,"select * from users where id = ?",1)

//Select
users := make([]*Users)
err := sq.Select(&users,"select * from users")

//Change database
db := sq.Use("test")
db.QueryX("select * from tests")
```

也可以设置默认的数据库连接名，例如: 以下操作设置默认数据库为`log`

```go
sq.SetDefaultLink("log")
sq.Open(configs)
```

> `gosql.Get` etc., will use the configuration with the connection name `log` 

###2、使用结构体操作数据库。
* 使用`ModelStruct`的`API`操作数据库。

```go
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) TableName() string {
	return "users"
}

func (u *Users) PK() string {
	return "id"
}

//Take
user := &Users{}
sq.Model(user).Where("id=?",1).Take()

//Select
user := make([]*Users,0)
sq.Model(&user).Select()

//Insert and auto set CreatedAt
sq.Model(&User{Name:"test",Email:"test@gmail.com"}).Insert()

//Update
sq.Model(&User{Name:"test2",Email:"test@gmail.com"}).Where("id=?",1).Update()
//If you need to update the zero value, you can do so
sq.Model(&User{Status:0}).Where("id=?",1).Update("status")

//Delete
sq.Model(&User{}).Where("id=?",1).Delete()

```

使用结构体构造`where`条件

```go
//Take where id = 1 and name = "test1"
user := &Users{Id:1,Name:"test1"}
sq.Model(&user).Take()

//Update default use primary key as the condition
sq.Model(&User{Id:1,Name:"test2"}).Update()
//Use custom conditions
//Builder => UPDATE users SET `id`=?,`name`=?,`updated_at`=? WHERE (status = ?)
sq.Model(&User{Id:1,Name:"test2"}).Where("status = ?",1).Update()

//Delete
sq.Model(&User{Id:1}).Delete()
```

But the zero value is filtered by default, you can specify fields that are not filtered. For example

```go
user := &Users{Id:1,Status:0}
sq.Model(&user).Take("status")
```

> You can use the [genstruct](https://github.com/fifsky/genstruct) tool to quickly generate database structs

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

```go
sq.Tx(func(tx *sq.DB) error {
    for id := 1; id < 10; id++ {
        user := &Users{
            Id:    id,
            Name:  "test" + strconv.Itoa(id),
            Email: "test" + strconv.Itoa(id) + "@test.com",
        }
		
		//v2 support, do some database operations in the transaction (use 'tx' from this point, not 'gosql')
        tx.Model(user).Insert()

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

    //query with transaction
    var num int
    err := 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`

Now support gosql.Begin() or gosql.Use("other").Begin() for example:
```go
tx, err := sq.Begin()
if err != nil {
    return err
}

for id := 1; id < 10; id++ {
    _, err := tx.Exec("INSERT INTO users(id,name,status,created_at,updated_at) VALUES(?,?,?,?,?)", id, "test"+strconv.Itoa(id), 1, time.Now(), time.Now())
    if err != nil {
        return tx.Rollback()
    }
}

return tx.Commit()
```

## 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:

```go
//Insert
sq.Table("users").Insert(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
sq.Table("users").Where("id = ?", 1).Update(map[string]interface{}{
    "name":  "fifsky",
    "email": "fifsky@test.com",
})

//Delete
sq.Table("users").Where("id = ?", 1).Delete()

//Count
sq.Table("users").Where("id = ?", 1).Count()

//Change database
sq.Use("db2").Table("users").Where("id = ?", 1).Count()

//Transaction `tx`
tx.Table("users").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

```go
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 := sq.Model(user).Take()
```

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


## sq.Expr
Reference GORM Expr, Resolve update field self-update problem
```go
sq.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:

```go
SELECT * FROM users WHERE level IN (?);
```

`sqlx.In` is encapsulated In `gosql` and can be queried using the following schema

```go
var levels = []int{4, 6, 7}
rows, err := sq.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

⚠️ Since version v2, the relation query across library connections needs to be specified using `connection` tag


```go
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" connection:"db2"`     //one-to-many
}
```

Get single result

```go
moment := &MomentList{}
err := sq.Model(moment).Where("status = 1 and id = ?",14).Take()
//output User and Photos and you get the result
```

SQL:

```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

```go
var moments = make([]*MomentList, 0)
err := sq.Model(&moments).Where("status = 1").Limit(10).Select()
//You get the total result  for *UserMoment slice
```

SQL:

```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:

```go
moment := &MomentList{}
err := sq.Relation("User" , func(b *sq.ModelStruct) {
    //this is builder instance,
    b.Where("gender = 0")
}).Take(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:

```go
func (u *Users) BeforeCreate() (err error) {
  if u.IsValid() {
    err = errors.New("can't save invalid data")
  }
  return
}

func (u *Users) AfterCreate(tx *gosql.DB) (err error) {
  if u.Id == 1 {
    u.Email = "after@test.com"
    tx.Model(u).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 *gosql.DB)
func (u *Users) BeforeCreate(tx *gosql.DB) (err error)
```


## Thanks

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