DEV Community

Cover image for Aorm, A GoLang library for operate database
Tangpanqing
Tangpanqing

Posted on

Aorm, A GoLang library for operate database

#go #aorm #mysql

Aorm

Aorm is a GoLang library for operate database.

Give a ⭐ if this project helped you!

🌟 Feature

  • [x] Simply And Fast
  • [x] Support MySQL DataBase
  • [x] Support Null Value When Query Or Exec
  • [x] Support Auto Migrate
  • [X] Support SQL Builder
  • [ ] Support Other DataBase, like MSSQL

🌟 Usage

Import 

    import (
        "database/sql"
        _ "github.com/go-sql-driver/mysql" 
        "github.com/tangpanqing/aorm"
    )
Enter fullscreen mode Exit fullscreen mode

database/sql the go std package, provide sql operate database interface

github.com/go-sql-driver/mysql the driver for mysql database

github.com/tangpanqing/aorm wrapper of the sql operate, make it easy for use

you can download them like this

go get -u github.com/go-sql-driver/mysql
Enter fullscreen mode Exit fullscreen mode 
go get -u github.com/tangpanqing/aorm
Enter fullscreen mode Exit fullscreen mode

Define data struct

you should define data struct before operate database, like this

    type Person struct {
        Id         aorm.Int    `aorm:"primary;auto_increment" json:"id"`
        Name       aorm.String `aorm:"size:100;not null;comment:名字" json:"name"`
        Sex        aorm.Bool   `aorm:"index;comment:性别" json:"sex"`
        Age        aorm.Int    `aorm:"index;comment:年龄" json:"age"`
        Type       aorm.Int    `aorm:"index;comment:类型" json:"type"`
        CreateTime aorm.Time   `aorm:"comment:创建时间" json:"createTime"`
        Money      aorm.Float  `aorm:"comment:金额" json:"money"`
        Test       aorm.Float  `aorm:"type:double;comment:测试" json:"test"`
    }
Enter fullscreen mode Exit fullscreen mode

first, notice that like aorm.Int, aorm.String, aorm.Bool, aorm.Float, aorm.Time, which is a struct that wrapper of the sql.NUll* struct

second, notice that like aorm: tag, this will be used when migrate data struct to database, some info you need know

key name key value info example
primary none set a primary column primary
unique none set a unique column unique
index none set a index column index
auto_increment none set a column auto increment auto_increment
not null none set a column allow null or not not null
type string set a column's data type type:double
size int set a column's length or size size:100
comment string set a column's comment comment:名字
default string set a column's default value default:2

Connect database

by sql.Open function, you can connect the database, and then you should ping test

    //replace this database param
    username := "root"
    password := "root"
    hostname := "localhost"
    port := "3306"
    dbname := "database_name"

    //connect
    db, err := sql.Open("mysql", username+":"+password+"@tcp("+hostname+":"+port+")/"+dbname+"?charset=utf8mb4&parseTime=True&loc=Local")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    //ping test
    err1 := db.Ping()
    if err1 != nil {
        panic(err1)
    }
Enter fullscreen mode Exit fullscreen mode

Migrate

by AutoMigrate function, the table name will be person, underline style string with the struct name

    aorm.Use(db).Opinion("ENGINE", "InnoDB").Opinion("COMMENT", "用户表").AutoMigrate(&Person{})
Enter fullscreen mode Exit fullscreen mode

by Migrate function, You can also use other table name

    aorm.Use(db).Opinion("ENGINE", "InnoDB").Opinion("COMMENT", "用户表").Migrate("person_1", &Person{})
Enter fullscreen mode Exit fullscreen mode

by ShowCreateTable function, You can get the create table sql

    showCreate := aorm.Use(db).ShowCreateTable("person")
    fmt.Println(showCreate)
Enter fullscreen mode Exit fullscreen mode

like this

    CREATE TABLE `person` (
        `id` int NOT NULL AUTO_INCREMENT,
        `name` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '名字',
        `sex` tinyint DEFAULT NULL COMMENT '性别',
        `age` int DEFAULT NULL COMMENT '年龄',
        `type` int DEFAULT NULL COMMENT '类型',
        `create_time` datetime DEFAULT NULL COMMENT '创建时间',
        `money` float DEFAULT NULL COMMENT '金额',
        `article_body` text COLLATE utf8mb4_general_ci COMMENT '文章内容',
        `test` double DEFAULT NULL COMMENT '测试',
        PRIMARY KEY (`id`),
        KEY `idx_person_sex` (`sex`),
        KEY `idx_person_age` (`age`),
        KEY `idx_person_type` (`type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='人员表'
Enter fullscreen mode Exit fullscreen mode

Basic CRUD

Insert one record

by Insert function, you can insert one record from data struct

    id, errInsert := aorm.Use(db).Debug(true).Insert(&Person{
        Name:       aorm.StringFrom("Alice"),
        Sex:        aorm.BoolFrom(false),
        Age:        aorm.IntFrom(18),
        Type:       aorm.IntFrom(0),
        CreateTime: aorm.TimeFrom(time.Now()),
        Money:      aorm.FloatFrom(100.15987654321),
        Test:       aorm.FloatFrom(200.15987654321987654321),
    })
    if errInsert != nil {
        fmt.Println(errInsert)
    }
    fmt.Println(id)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    INSERT INTO person (name,sex,age,type,create_time,money,test) VALUES (?,?,?,?,?,?,?)
    Alice false 18 0 2022-12-07 10:10:26.1450773 +0800 CST m=+0.031808801 100.15987654321 200.15987654321987
Enter fullscreen mode Exit fullscreen mode

Get one record

by GetOne function, you can get one record

    var person Person
    errFind := aorm.Use(db).Debug(true).Where(&Person{Id: aorm.IntFrom(id)}).GetOne(&person)
    if errFind != nil {
        fmt.Println(errFind)
    }
    fmt.Println(person)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE id = ? Limit ?,?
    1 0 1
Enter fullscreen mode Exit fullscreen mode

Get many record

by GetMany function, you can get many record

    var list []Person
    errSelect := aorm.Use(db).Debug(true).Where(&Person{Type: aorm.IntFrom(0)}).GetMany(&list)
    if errSelect != nil {
        fmt.Println(errSelect)
    }
    for i := 0; i < len(list); i++ {
        fmt.Println(list[i])
    }
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE type = ?
    0
Enter fullscreen mode Exit fullscreen mode

Update record

by Update function, you can update record

    countUpdate, errUpdate := aorm.Use(db).Debug(true).Where(&Person{Id: aorm.IntFrom(id)}).Update(&Person{Name: aorm.StringFrom("Bob")})
    if errUpdate != nil {
        fmt.Println(errUpdate)
    }
    fmt.Println(countUpdate)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    UPDATE person SET name=? WHERE id = ?
    Bob 1
Enter fullscreen mode Exit fullscreen mode

Delete record

by Delete function, you can delete record

    countDelete, errDelete := aorm.Use(db).Debug(true).Where(&Person{Id: aorm.IntFrom(id)}).Delete()
    if errDelete != nil {
        fmt.Println(errDelete)
    }
    fmt.Println(countDelete)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    DELETE FROM person WHERE id = ?
    1
Enter fullscreen mode Exit fullscreen mode

Advanced Query

Table

by Table function, you can set table name easy

    aorm.Use(db).Debug(true).Table("person_1").Insert(&Person{Name: aorm.StringFrom("Cherry")})
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    INSERT INTO person_1 (name) VALUES (?)
    Cherry
Enter fullscreen mode Exit fullscreen mode

Select

by Select function, you can select field name easy

    var listByFiled []Person
    aorm.Use(db).Debug(true).Select("name,age").Where(&Person{Age: aorm.IntFrom(18)}).GetMany(&listByFiled)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT name,age FROM person WHERE age = ?
    18
Enter fullscreen mode Exit fullscreen mode

Where 

    var listByWhere []Person

    var where1 []aorm.WhereItem
    where1 = append(where1, aorm.WhereItem{Field: "type", Opt: aorm.Eq, Val: 0})
    where1 = append(where1, aorm.WhereItem{Field: "age", Opt: aorm.In, Val: []int{18, 20}})
    where1 = append(where1, aorm.WhereItem{Field: "money", Opt: aorm.Between, Val: []float64{100.1, 200.9}})
    where1 = append(where1, aorm.WhereItem{Field: "money", Opt: aorm.Eq, Val: 100.15})
    where1 = append(where1, aorm.WhereItem{Field: "name", Opt: aorm.Like, Val: []string{"%", "li", "%"}})

    aorm.Use(db).Debug(true).Table("person").WhereArr(where1).GetMany(&listByWhere)
    for i := 0; i < len(listByWhere); i++ {
        fmt.Println(listByWhere[i])
    }
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE type = ? AND age IN (?,?) AND money BETWEEN (?) AND (?) AND CONCAT(money,'') = ? AND name LIKE concat('%',?,'%')
    0 18 20 100.1 200.9 100.15 li
Enter fullscreen mode Exit fullscreen mode

Where Operate

there are some other operates you should know

Opt Name Same As
aorm.Eq =
aorm.Ne !=
aorm.Gt >
aorm.Ge >=
aorm.Lt <
aorm.Le <=
aorm.In In
aorm.NotIn Not In
aorm.Like LIKE
aorm.NotLike Not Like
aorm.Between Between
aorm.NotBetween Not Between

JOIN 

    var list2 []ArticleVO

    var where2 []aorm.WhereItem
    where2 = append(where2, aorm.WhereItem{Field: "o.type", Opt: aorm.Eq, Val: 0})
    where2 = append(where2, aorm.WhereItem{Field: "p.age", Opt: aorm.In, Val: []int{18, 20}})

    aorm.Use(db).Debug(true).
        Table("article o").
        LeftJoin("person p", "p.id=o.person_id").
        Select("o.*").
        Select("p.name as person_name").
        WhereArr(where2).
        GetMany(&list2)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT o.*,p.name as person_name FROM article o LEFT JOIN person p ON p.id=o.person_id WHERE o.type = ? AND p.age IN (?,?)
    0 18 20
Enter fullscreen mode Exit fullscreen mode

some other join function like this RightJoin, Join

GroupBy 

    type PersonAge struct {
        Age         aorm.Int
        AgeCount    aorm.Int
    }

    var personAge PersonAge

    var where []aorm.WhereItem
    where = append(where, aorm.WhereItem{Field: "type", Opt: aorm.Eq, Val: 0})

    err := aorm.Use(db).Debug(true).
        Table("person").
        Select("age").
        Select("count(age) as age_count").
        GroupBy("age").
        WhereArr(where).
        GetOne(&personAge)
    if err != nil {
        panic(err)
    }
    fmt.Println(personAge)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT age,count(age) as age_count FROM person WHERE type = ? GROUP BY age Limit ?,?
    0 0 1
Enter fullscreen mode Exit fullscreen mode

Having 

    var listByHaving []PersonAge

    var where3 []aorm.WhereItem
    where3 = append(where3, aorm.WhereItem{Field: "type", Opt: aorm.Eq, Val: 0})

    var having []aorm.WhereItem
    having = append(having, aorm.WhereItem{Field: "age_count", Opt: aorm.Gt, Val: 4})

    err := aorm.Use(db).Debug(true).
        Table("person").
        Select("age").
        Select("count(age) as age_count").
        GroupBy("age").
        WhereArr(where3).
        HavingArr(having).
        GetMany(&listByHaving)
    if err != nil {
        panic(err)
    }
    fmt.Println(listByHaving)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT age,count(age) as age_count FROM person WHERE type = ? GROUP BY age Having age_count > ?
    0 4
Enter fullscreen mode Exit fullscreen mode

OrderBy 

    var listByOrder []Person

    var where []aorm.WhereItem
    where = append(where, aorm.WhereItem{Field: "type", Opt: aorm.Eq, Val: 0})

    err := aorm.Use(db).Debug(true).
        Table("person").
        WhereArr(where).
        OrderBy("age", aorm.Desc).
        GetMany(&listByOrder)
    if err != nil {
        panic(err)
    }
    fmt.Println(listByOrder)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE type = ? Order BY age DESC
    0
Enter fullscreen mode Exit fullscreen mode

Limit and Page 

    var list3 []Person

    var where1 []aorm.WhereItem
    where1 = append(where1, aorm.WhereItem{Field: "type", Opt: aorm.Eq, Val: 0})

    err1 := aorm.Use(db).Debug(true).
        Table("person").
        WhereArr(where1).
        Limit(50, 10).
        GetMany(&list3)
    if err1 != nil {
        panic(err1)
    }
    fmt.Println(list3)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE type = ? Limit ?,?
    0 50 10
Enter fullscreen mode Exit fullscreen mode 
    var list4 []Person

    var where2 []aorm.WhereItem
    where2 = append(where2, aorm.WhereItem{Field: "type", Opt: aorm.Eq, Val: 0})

    err := aorm.Use(db).Debug(true).
        Table("person").
        WhereArr(where2).
        Page(3, 10).
        GetMany(&list4)
    if err != nil {
        panic(err)
    }
    fmt.Println(list4)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE type = ? Limit ?,?
    0 20 10
Enter fullscreen mode Exit fullscreen mode

Lock

by Lock function, you can lock the query

    var itemByLock Person
    err := aorm.Use(db).Debug(true).LockForUpdate(true).Where(&Person{Id: aorm.IntFrom(id)}).GetOne(&itemByLock)
    if err != nil {
        panic(err)
    }
    fmt.Println(itemByLock)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE id = ? Limit ?,?  FOR UPDATE
    2 0 1
Enter fullscreen mode Exit fullscreen mode

Increment 

    count, err := aorm.Use(db).Debug(true).Where(&Person{Id: aorm.IntFrom(id)}).Increment("age", 1)
    if err != nil {
        panic(err)
    }
    fmt.Println(count)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    UPDATE person SET age=age+? WHERE id = ?
    1 2
Enter fullscreen mode Exit fullscreen mode

Decrement 

    count, err := aorm.Use(db).Debug(true).Where(&Person{Id: aorm.IntFrom(id)}).Decrement("age", 2)
    if err != nil {
        panic(err)
    }
    fmt.Println(count)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    UPDATE person SET age=age-? WHERE id = ?
    2 2
Enter fullscreen mode Exit fullscreen mode

Value 

    var name string
    errName := aorm.Use(db).Debug(true).Where(&Person{Id: aorm.IntFrom(id)}).Value("name", &name)
    if errName != nil {
        panic(errName)
    }
    fmt.Println(name)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT name FROM person WHERE id = ? Limit ?,?
    2 0 1
Enter fullscreen mode Exit fullscreen mode

then print the value Alice

Pluck 

    var nameList []string
    err := aorm.Use(db).Debug(true).Where(&Person{Type: aorm.IntFrom(0)}).Limit(0, 5).Pluck("name", &nameList)
    if err != nil {
        panic(err)
    }
    for i := 0; i < len(nameList); i++ {
        fmt.Println(nameList[i])
    }
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT name FROM person WHERE type = ? Limit ?,?
    0 0 5
Enter fullscreen mode Exit fullscreen mode

Aggregation Function

Count 

    count, err := aorm.Use(db).Debug(true).Where(&Person{Age: aorm.IntFrom(18)}).Count("*")
    if err != nil {
        panic(err)
    }
    fmt.Println(count)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT count(*) as c FROM person WHERE age = ?
    18
Enter fullscreen mode Exit fullscreen mode

Sum 

    sum, err := aorm.Use(db).Debug(true).Where(&Person{Age: aorm.IntFrom(18)}).Sum("age")
    if err != nil {
        panic(err)
    }
    fmt.Println(sum)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT sum(age) as c FROM person WHERE age = ?
    18
Enter fullscreen mode Exit fullscreen mode

Avg 

    avg, err := aorm.Use(db).Debug(true).Where(&Person{Age: aorm.IntFrom(18)}).Avg("age")
    if err != nil {
        panic(err)
    }
    fmt.Println(avg)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT avg(age) as c FROM person WHERE age = ?
    18
Enter fullscreen mode Exit fullscreen mode

min 

    min, err := aorm.Use(db).Debug(true).Where(&Person{Age: aorm.IntFrom(18)}).Min("age")
    if err != nil {
        panic(err)
    }
    fmt.Println(min)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT min(age) as c FROM person WHERE age = ?
    18
Enter fullscreen mode Exit fullscreen mode

Max 

    max, err := aorm.Use(db).Debug(true).Where(&Person{Age: aorm.IntFrom(18)}).Max("age")
    if err != nil {
        panic(err)
    }
    fmt.Println(max)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT max(age) as c FROM person WHERE age = ?
    18
Enter fullscreen mode Exit fullscreen mode

Common

Query 

    resQuery, err := aorm.Use(db).Debug(true).Query("SELECT * FROM person WHERE id=? AND type=?", 1, 3)
    if err != nil {
        panic(err)
    }
    fmt.Println(resQuery)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT * FROM person WHERE id=? AND type=?
    1 3
Enter fullscreen mode Exit fullscreen mode

Exec 

    resExec, err := aorm.Use(db).Debug(true).Exec("UPDATE person SET name = ? WHERE id=?", "Bob", 3)
    if err != nil {
        panic(err)
    }
    fmt.Println(resExec.RowsAffected())
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    UPDATE person SET name = ? WHERE id=?
    Bob 3
Enter fullscreen mode Exit fullscreen mode

Transaction 

    tx, _ := db.Begin()

    id, errInsert := aorm.Use(tx).Insert(&Person{
        Name: aorm.StringFrom("Alice"),
    })

    if errInsert != nil {
        fmt.Println(errInsert)
        tx.Rollback()
        return
    }

    countUpdate, errUpdate := aorm.Use(tx).Where(&Person{
        Id: aorm.IntFrom(id),
    }).Update(&Person{
        Name: aorm.StringFrom("Bob"),
    })

    if errUpdate != nil {
        fmt.Println(errUpdate)
        tx.Rollback()
        return
    }

    fmt.Println(countUpdate)
    tx.Commit()
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    INSERT INTO person (name) VALUES (?)
    Alice

    UPDATE person SET name=? WHERE id = ?
    Bob 3
Enter fullscreen mode Exit fullscreen mode

Truncate 

    count, err := aorm.Use(db).Table("person").Truncate()
    if err != nil {
        panic(err)
    }
    fmt.Println(count)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    TRUNCATE TABLE person
Enter fullscreen mode Exit fullscreen mode

Utils

by Ul or UnderLine, you can transform camel case string to underline case

for example, transform personId to person_id

    var list2 []ArticleVO
    var where2 []aorm.WhereItem
    where2 = append(where2, aorm.WhereItem{Field: "o.type", Opt: aorm.Eq, Val: 0})
    where2 = append(where2, aorm.WhereItem{Field: "p.age", Opt: aorm.In, Val: []int{18, 20}})

    aorm.Use(db).Debug(true).
        Table("article o").
        LeftJoin("person p", aorm.Ul("p.id=o.personId")).
        Select("o.*").
        Select(aorm.Ul("p.name as personName")).
        WhereArr(where2).
        GetMany(&list2)
Enter fullscreen mode Exit fullscreen mode

then get the sql and params like this

    SELECT o.*,p.name as person_name FROM article o LEFT JOIN person p ON p.id=o.person_id WHERE o.type = ? AND p.age IN (?,?)
    0 18 20
Enter fullscreen mode Exit fullscreen mode

Author

👤 tangpanqing

Show Your Support

Give a ⭐ if this project helped you!

Top comments (0)

Read next

aixart profile image

Understanding Go through the Lens of TypeScript/JavaScript

Dhruv Kumar -

mohamedfawas1 profile image

Building a Robust E-Commerce Platform with Go, Clean Architecture, and PostgreSQL

MOHAMED FAWAS -

pankaj892 profile image

Devops pipeline on a Golang Web App with Gitops and Kubernetes

pankaj892 -

manojspace profile image

Adding a Serial ID to MySQL Tables Based on Creation Date: A Step-by-Step Guide

Manoj Swami -