DEV Community: Tangpanqing

Go is also able to use objects to operate databases. Many users have commented that this ensures robustness in the code

Tangpanqing — Tue, 17 Jan 2023 03:58:09 +0000

Hi, all my friends who are using go, happy new year.

Have you all tried using the Aorm library that I introduced before? It is, so far, the best database operation library I've seen in the go field.

Last year (in fact, half a month ago), I wrote an article introducing the Aorm's chain operation, showing its ease of use.

"You never know, go's database operation can be as smooth as php"

Many friends added me, expressing their support and making earnest requests. One of the requests was for the ability to use objects to operate on the database.

After half a month of research and effort, it's here now, it's here, it's come with devilish steps.

What is object-oriented database operation?

Many friends may not have heard of this, so I'll give a simple example in another language's code first.

$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->orderBy('name', 'desc')
->get();

As shown above, in PHP code, a simple database query statement, familiar to PHP friends.

The users, id, and name that appear here are all strings and need to be manually coded in, and the disadvantages are obvious:

During development, it is easy to produce spelling errors.
When refactoring or when field and table names are changed, it can be a headache. Because they are distributed in various places in the project, you need to be extremely careful and find them correctly and then modify them. Missing one is like a timed bomb.

Let's take a look at how this problem is solved in .NET

dbContext
.Person
.GroupJoin(
     dbContext.Products, 
     person => person.Id, 
     product => product.Id, 
     (person, products) => new { Person = person, Products = products })
.SelectMany(
        combination => combination.Products.DefaultIfEmpty(), 
        (person, products) => new { 
            PersonId = person.Person.Id, 
            PersonName = person.Person.Name, 
            ProductsId = products.Id, 
            ProductsName = products.Product 
        }
    )
    .ToList();

In the code you saw above, there are no manually written strings, they are all object types or object properties.

During development, you need to define objects and properties in advance, which is indeed more work than PHP development. But maintenance is super easy.

If you have a table or field that needs to be modified, just modify it and the editor will automatically prompt you which fields are missing, highlight and display them, not a single mistake, and not missing any.

If you do not modify it, it will not pass compilation. As a result, the robustness of your program is guaranteed. After development, you can also sleep soundly.

What is Aorm?

Aorm is a Go-based database operation library.

The project address is github.com/tangpanqing/aorm

Core advantages:

Support using structs (objects) to operate databases, making your system more robust

Support for MySQL, MsSQL, Postgres, Sqlite3 databases, making your system easier to expand

Support for chain operations, making your development more efficient

Support for null value queries or updates, making your development experience better

Support for migrating data structures, making your data migration more convenient

Currently, the number of stars on Github is not high, but the author is quite thoughtful and the documentation is still complete. If you are interested, you can take a look.

Aorm uses objects to operate databases

below is an example of association query that I found from Aorm's documentation.

aorm.Db(db).
        Table(&article).
        LeftJoin(
            &person,
            []builder.JoinCondition{
                builder.GenJoinCondition(&person.Id, builder.RawEq, &article.PersonId),
            },
        ).
        SelectAll(&article).
        SelectAs(&person.Name, &articleVO.PersonName).
        WhereEq(&article.Type, 0).
        WhereIn(&person.Age, []int{18, 20}).
        GetMany(&list2)

It generates the following SQL statement:

    SELECT article.*,person.name as person_name 
    FROM article 
    LEFT JOIN person ON person.id=article.person_id 
    WHERE article.type = ? 
    AND article.age IN (?,?)

    0 18 20

By comparing the code and the SQL, if you have a good understanding of SQL, you should be able to understand the function of each method in the code and what each parameter represents. As you can see, the &article in the code corresponds to the article table in the SQL, &person.Id corresponds to the person.id field in the SQL, and so on.

Once you understand the principle, you can look at the code again and see that there is no hard-coded field name or table name. Like the .NET example above, all operations are object-oriented, making your code more robust and making maintenance and refactoring more convenient.

End
through this article, we have briefly introduced using objects (structs) to operate databases in Go and provided some examples.

For more features or documentation on Aorm, you can check out the Aorm documentation at the project address

github.com/tangpanqing/aorm

I also strongly invite friends to use Aorm, if you encounter any problems during use, please feel free to contact me through various channels.

Aorm, A GoLang library for operate database

Tangpanqing — Tue, 13 Dec 2022 09:36:40 +0000

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
Define data struct
Connect database
Migrate
Basic CRUD
- Insert one record
- Get one record
- Get many record
- Update record
- Delete record
Advanced Query
- Table
- Select
- Where
- Where Operate
- Join
- GroupBy
- Having
- OrderBy
- Limit and Page
- Lock
- Increment
- Decrement
- Value
- Pluck
Aggregation Function
- Count
- Sum
- AVG
- Min
- Max
Common
- Query
- Exec
Transaction
Truncate
Utils

Import

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

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

go get -u github.com/tangpanqing/aorm

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

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

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

by Migrate function, You can also use other table name

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

by ShowCreateTable function, You can get the create table sql

    showCreate := aorm.Use(db).ShowCreateTable("person")
    fmt.Println(showCreate)

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='人员表'

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)

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

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)

then get the sql and params like this

    SELECT * FROM person WHERE id = ? Limit ?,?
    1 0 1

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

then get the sql and params like this

    SELECT * FROM person WHERE type = ?
    0

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)

then get the sql and params like this

    UPDATE person SET name=? WHERE id = ?
    Bob 1

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)

then get the sql and params like this

    DELETE FROM person WHERE id = ?
    1

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

then get the sql and params like this

    INSERT INTO person_1 (name) VALUES (?)
    Cherry

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)

then get the sql and params like this

    SELECT name,age FROM person WHERE age = ?
    18

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

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

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)

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

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)

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

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)

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

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)

then get the sql and params like this

    SELECT * FROM person WHERE type = ? Order BY age DESC
    0

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)

then get the sql and params like this

    SELECT * FROM person WHERE type = ? Limit ?,?
    0 50 10

    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)

then get the sql and params like this

    SELECT * FROM person WHERE type = ? Limit ?,?
    0 20 10

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)

then get the sql and params like this

    SELECT * FROM person WHERE id = ? Limit ?,?  FOR UPDATE
    2 0 1

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)

then get the sql and params like this

    UPDATE person SET age=age+? WHERE id = ?
    1 2

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)

then get the sql and params like this

    UPDATE person SET age=age-? WHERE id = ?
    2 2

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)

then get the sql and params like this

    SELECT name FROM person WHERE id = ? Limit ?,?
    2 0 1

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

then get the sql and params like this

    SELECT name FROM person WHERE type = ? Limit ?,?
    0 0 5

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)

then get the sql and params like this

    SELECT count(*) as c FROM person WHERE age = ?
    18

Sum

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

then get the sql and params like this

    SELECT sum(age) as c FROM person WHERE age = ?
    18

Avg

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

then get the sql and params like this

    SELECT avg(age) as c FROM person WHERE age = ?
    18

min

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

then get the sql and params like this

    SELECT min(age) as c FROM person WHERE age = ?
    18

Max

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

then get the sql and params like this

    SELECT max(age) as c FROM person WHERE age = ?
    18

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)

then get the sql and params like this

    SELECT * FROM person WHERE id=? AND type=?
    1 3

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

then get the sql and params like this

    UPDATE person SET name = ? WHERE id=?
    Bob 3

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

then get the sql and params like this

    INSERT INTO person (name) VALUES (?)
    Alice

    UPDATE person SET name=? WHERE id = ?
    Bob 3

Truncate

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

then get the sql and params like this

    TRUNCATE TABLE person

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)

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

Author

👤 tangpanqing

Twitter: @tangpanqing
Github: @tangpanqing

Show Your Support

Give a ⭐ if this project helped you!