Go Utility: sql

Installation

go get -u github.com/go-ginseng/sql

Connection

Connect to MySQL

// import "github.com/go-ginseng/sql/conn"

// param: (host, port, username, password, database string, silent bool)
// return: (*sql.DB, error)
db, err := conn.MySql("127.0.0.1", "3306", "root", "password", "database", true)
if err != nil {
    panic(err)
}

Connect to disk-based SQLite

// import "github.com/go-ginseng/sql/conn"

// param: (dsn string, silent bool)
// return: (*sql.DB, error)
db, err := conn.Sqlite("./database.db", true)
if err != nil {
    panic(err)
}

Connect to memory-based SQLite

// import "github.com/go-ginseng/sql/conn"

// param: (shared, silent bool)
// return: (*sql.DB, error)
db, err := conn.SqliteMemory(":memory:", true)
if err != nil {
    panic(err)
}

Encryption

AES encrypt and decrypt

// import "github.com/go-ginseng/sql/encrypt"

encryption, err := encrypt.NewAesEncrypt("aes key")
data := "data to encrypt"

// param: (plainText string)
// return: (string, error)
encrypted, err := encryption.Encrypt(data)
if err != nil {
    panic(err)
}

// param: (cipherText string)
// return: (string, error)
decrypted, err := encryption.Decrypt(encrypted)
if err != nil {
    panic(err)
}

Clause Builder

// import "github.com/go-ginseng/sql"

// id = ?
cls := sql.Eq("id", 1)

// id <> ?
cls := sql.Neq("id", 1)

// id > ?
cls := sql.Gt("id", 1)

// id >= ?
cls := sql.Gte("id", 1)

// id < ?
cls := sql.Lt("id", 1)

// id <= ?
cls := sql.Lte("id", 1)

// id IN (?, ?, ?)
cls := sql.In("id", []int{1, 2, 3})

// id NOT IN (?, ?, ?)
cls := sql.Nin("id", []int{1, 2, 3})

// id LIKE ?
cls := sql.Lk("id", "value")

// id NOT LIKE ?
cls := sql.Nlk("id", "value")

// id IS NULL
cls := sql.Null("id")

// id IS NOT NULL
cls := sql.NotNull("id")

// id BETWEEN ? AND ?
cls := sql.Between("id", 1, 10)

// (id = ? AND name <> ?)
cls := sql.And(sql.Eq("id", 1), sql.Neq("name", "name"))

// (id = ? OR name <> ?)
cls := sql.Or(sql.Eq("id", 1), sql.Neq("name", "name"))

// build clause and use it
stm, values := cls.Build()
db.Where(stm, values...).Find(&data)

Generic Query Functions

FindOne

// import "github.com/go-ginseng/sql"

m, err := FindOne[Model](db, sql.Eq("id", 1))

FindAll

// import "github.com/go-ginseng/sql"

ms, err := FindAll[Model](
    db,
    sql.Eq("id", 1),
    &sql.Pagination{Page: 1, Size: 10},
    &sql.Sort{By: "id", Asc: true})

Count

// import "github.com/go-ginseng/sql"

count, err := Count[Model](db, sql.Eq("id", 1))

Generic Write Functions

Create

// import "github.com/go-ginseng/sql"

m := &Model{Id: 1, Name: "name"}
m, err := Create[Model](db, m)

Update

// import "github.com/go-ginseng/sql"

m := &Model{Id: 1, Name: "name"}
m, err := Update[Model](db, m)

Delete

// import "github.com/go-ginseng/sql"

m := &Model{Id: 1, Name: "name"}
err := Delete[Model](db, m)

DeleteBy

// import "github.com/go-ginseng/sql"

err := DeleteBy[Model](db, sql.Eq("id", 1))