Skip to content

si3nloong/sqlike

Folders and files

NameName
Last commit message
Last commit date

Latest commit

Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

sqlike

Build Release Go Report Go Coverage LICENSE FOSSA Status

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", "test@gmail.com"),
                ),
            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 ❀

GitHub NPM

🧲 Adopters

πŸ“„ License

MIT

Copyright (c) 2019-present, SianLoong Lee

FOSSA Status