github.com/RevenueMonster/sqlike@v1.0.6/README.md

# sqlike

[![Build](https://github.com/RevenueMonster/sqlike/workflows/build/badge.svg?branch=main)](https://github.com/RevenueMonster/sqlike/actions?query=workflow%3Abuild)
[![Release](https://img.shields.io/github/v/tag/si3nloong/sqlike)](https://github.com/RevenueMonster/sqlike/releases)
[![Go Report](https://goreportcard.com/badge/github.com/RevenueMonster/sqlike)](https://goreportcard.com/report/github.com/RevenueMonster/sqlike)
[![Go Coverage](https://codecov.io/gh/si3nloong/sqlike/branch/main/graph/badge.svg)](https://codecov.io/gh/si3nloong/sqlike)
[![LICENSE](https://img.shields.io/github/license/si3nloong/sqlike)](https://github.com/RevenueMonster/sqlike/blob/main/LICENSE)
[![FOSSA Status](https://app.fossa.com/api/projects/git%2Bgithub.com%2Fsi3nloong%2Fsqlike.svg?type=shield)](https://app.fossa.com/projects/git%2Bgithub.com%2Fsi3nloong%2Fsqlike?ref=badge_shield)

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

## 🔨 Installation

```console
go get github.com/RevenueMonster/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](https://github.com/paulmach/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](https://cloud.google.com/go/civil#Date), [civil.Time](https://cloud.google.com/go/civil#Time) and [time.Location](https://pkg.go.dev/time#Time)
- Support [language.Tag](https://godoc.org/golang.org/x/text/language#example-Tag--Values) and [currency.Unit](https://godoc.org/golang.org/x/text/currency#Unit)
- Support authorization plugin [Casbin](https://github.com/casbin/casbin)
- Support tracing plugin [OpenTracing](https://github.com/opentracing/opentracing-go)
- Developer friendly, (query is highly similar to native sql query)
- Support `sqldump` for backup purpose **(experiment)**

<!-- You can refer to [examples](https://github.com/RevenueMonster/sqlike/tree/main/examples) folder to see what apis we offer and learn how to use those apis -->

## ⚠️ 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

```go
package main

import (
    "time"
    "github.com/RevenueMonster/sqlike/sqlike/actions"
    "github.com/RevenueMonster/sqlike/sqlike"
    "github.com/RevenueMonster/sqlike/sqlike/options"
    "github.com/RevenueMonster/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](https://github.com/jinzhu/gorm), [mongodb-go-driver](https://github.com/mongodb/mongo-go-driver) and [sqlx](https://github.com/jmoiron/sqlx).

## 🎉 Big Thanks To

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

[![GitHub](https://jstools.dev/img/badges/github.svg)](https://github.com/open-source)
[![NPM](https://jstools.dev/img/badges/npm.svg)](https://www.npmjs.com/)

## 🧲 Adopters 

<img src="https://camo.githubusercontent.com/2c975785e2902b4ca4d1a9e9fd4bd21b3e576395631f1dc7c49f170cdeb7f1f8/68747470733a2f2f61737365742e77657469782e6d792f696d616765732f6c6f676f2f77657469782e706e67"
width="120px" style="margin-right: 10px" />
<img src="https://user-images.githubusercontent.com/28108597/188676138-59b5fa89-9788-48ce-9a12-bdcc378bb8b7.png" width="150px" style="margin-right: 10px" />
<img src="https://user-images.githubusercontent.com/28108597/188675656-af6fb714-0460-40b3-8935-f0eeaa0886b1.png" width="130px" style="margin-right: 10px" />
<img src="https://user-images.githubusercontent.com/28108597/188676512-59a6f5bd-5c57-4051-bbd9-9275402b415f.png" width="70px" />

## 📄 License

[MIT](https://github.com/RevenueMonster/sqlike/blob/main/LICENSE)

Copyright (c) 2019-present, SianLoong Lee

[![FOSSA Status](https://app.fossa.com/api/projects/git%2Bgithub.com%2Fsi3nloong%2Fsqlike.svg?type=large)](https://app.fossa.com/projects/git%2Bgithub.com%2Fsi3nloong%2Fsqlike?ref=badge_large)