sqlike

A Golang SQL ORM which anti-toxic query and focus on the latest features.

🔨 Installation

go get github.com/si3nloong/sqlike

Fully compatible with native library database/sql, which mean you are allow to use driver.Valuer and sql.Scanner.

📻 Legacy Support

SQLike did support mysql 5.7 as well. For better compatibility, we suggest you to use at least mysql 8.0.

🪣 Minimum Requirements

• mysql 8.0 and above
• golang 1.15 and above

❓ Why another ORM?

• We don't really care about legacy support, we want latest feature that mysql and golang offer us
• We want to get rid from toxic query (also known as slow query)

✨ What do we provide apart from native package (database/sql)?

• Support ENUM and SET
• Support UUID (^8.0)
• Support JSON
• Support descending index (^8.0)
• Support multi-valued index (^8.0.17)
• Support Spatial with package orb, such as Point, LineString
• Support generated column of stored column and virtual column
• Extra custom type such as Date, Key, Boolean
• Support struct on Find, FindOne, InsertOne, Insert, ModifyOne, DeleteOne, Delete, DestroyOne and Paginate apis
• Support Transactions
• Support cursor based pagination
• Support advance and complex query statement
• Support civil.Date, civil.Time and time.Location
• Support language.Tag and currency.Unit
• Support authorization plugin Casbin
• Support tracing plugin OpenTracing
• Developer friendly, (query is highly similar to native sql query)
• Support sqldump for backup purpose (experiment)

⚠️ Limitation

Our main objective is anti toxic query, that why some functionality we doesn't offer out of box

• offset based pagination (but you may achieve this by using Limit and Offset)
• eager loading (we want to avoid magic function, you should handle this by your own using goroutines)
• join (eg. left join, outer join, inner join), join clause is consider as toxic query, you should alway find your record using primary key
• left wildcard search using Like is not allow (but you may use expr.Raw to bypass it)
• bidirectional sorting is not allow (except mysql 8.0 and above)
• currently only support mysql driver (postgres and sqlite yet to implement)

General APIs

package main

import (
    "time"
    "github.com/si3nloong/sqlike/sqlike/actions"
    "github.com/si3nloong/sqlike/sqlike"
    "github.com/si3nloong/sqlike/sqlike/options"
    "github.com/si3nloong/sqlike/sql/expr"
    "github.com/google/uuid"
    "context"

    _ "github.com/go-sql-driver/mysql"
)

// UserStatus :
type UserStatus string

const (
    UserStatusActive  UserStatus = "ACTIVE"
    UserStatusSuspend UserStatus = "SUSPEND"
)

type User struct {
    ID        uuid.UUID  `sqlike:",primary_key"`
    ICNo      string     `sqlike:",generated_column"` // generated column generated by virtual column `Detail.ICNo`
    Name      string     `sqlike:",size=200,charset=latin1"` // you can set the data type length and charset with struct tag
    Email     string     `sqlike:",unique"` // set to unique
    Address   string     `sqlike:",longtext"` // `longtext` is an alias of long text data type in mysql
    Detail    struct {
        ICNo    string `sqlike:",virtual_column=ICNo"` // virtual column
        PhoneNo string
        Age     uint
    }
    Status    UserStatus `sqlike:",enum=ACTIVE|SUSPEND"` // enum data type
    CreatedAt time.Time
    UpdatedAt time.Time
}

func newUser() (user User) {
    now := time.Now()
    user.ID = uuid.New()
    user.CreatedAt = now
    user.UpdatedAt = now
    return
}

func main() {
    ctx := context.Background()
    client := sqlike.MustConnect(
        ctx,
        "mysql",
        options.Connect().
        SetUsername("root").
        SetPassword("").
        SetHost("localhost").
        SetPort("3306"),
    )

    client.SetPrimaryKey("ID") // Change default primary key name
    version := client.Version() // Display driver version
    dbs, _ := client.ListDatabases(ctx) // List databases

    userTable := client.Database("sqlike").Table("User")

    // Drop Table
    userTable.Drop(ctx)

    // Migrate Table
    userTable.Migrate(ctx, User{})

    // Truncate Table
    userTable.Truncate(ctx)

    // Insert one record
    {
        user := newUser()
        if _, err := userTable.InsertOne(ctx, &user); err != nil {
            panic(err)
        }
    }

    // Insert multiple record
    {
        users := [...]User{
            newUser(),
            newUser(),
            newUser(),
        }
        if _, err := userTable.Insert(ctx, &users); err != nil {
            panic(err)
        }
    }

    // Find one record
    {
        user := User{}
        err := userTable.FindOne(ctx, nil).Decode(&user)
        if err != nil {
            // `sqlike.ErrNoRows` is an alias of `sql.ErrNoRows`
            if err != sqlike.ErrNoRows {  // or you may check with sql.ErrNoRows
                panic(err)
            }
            // record not exist
        }
    }

    // Find multiple records
    {
        users := make([]User, 0)
        result, err := userTable.Find(
            ctx,
            actions.Find().
                Where(
                    expr.Equal("ID", result.ID),
                ).
                OrderBy(
                    expr.Desc("UpdatedAt"),
                ),
        )
        if err != nil {
            panic(err)
        }
        // map into the struct of slice
        if err:= result.All(&users); err != nil {
            panic(err)
        }
    }

    // Update one record with all fields of struct
    {
        user.Name = `🤖 Hello World!`
        if err := userTable.ModifyOne(ctx, &user); err != nil {
            panic(err)
        }
    }

    // Update one record with selected fields
    {
        userTable.UpdateOne(
            ctx,
            actions.UpdateOne().
                Where(
                    expr.Equal("ID", 100),
                ).Set(
                    expr.ColumnValue("Name", "SianLoong"),
                    expr.ColumnValue("Email", "[email protected]"),
                ),
            options.UpdateOne().SetDebug(true), // debug the query
        )
    }

    {
        limit := uint(10)
        pg, err := userTable.Paginate(
            ctx,
            actions.Paginate().
                OrderBy(
                    expr.Desc("CreatedAt"),
                ).
                Limit(limit + 1),
             options.Paginate().SetDebug(true),
        )
        if err != nil {
            panic(err)
        }

        for {
            var users []User
            if err := pg.All(&users); err != nil {
                panic(err)
            }
            length := uint(len(users))
            if length == 0 {
                break
            }
            cursor := users[length-1].ID
            if err := pg.NextCursor(ctx, cursor); err != nil {
                if err == sqlike.ErrInvalidCursor {
                    break
                }
                panic(err)
            }
            if length <= limit {
                break
            }
        }

    }
}

Inspired by gorm, mongodb-go-driver and sqlx.

🎉 Big Thanks To

Thanks to these awesome companies for their support of Open Source developers ❤

🧲 Adopters

📄 License

MIT

Copyright (c) 2019-present, SianLoong Lee