A Golang SQL ORM which anti-toxic query and focus on the latest features.
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
.
SQLike did support mysql 5.7 as well. For better compatibility, we suggest you to use at least mysql 8.0.
- mysql 8.0 and above
- golang 1.15 and above
- 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)
- Support
ENUM
andSET
- Support
UUID
(^8.0) - Support
JSON
- Support
descending index
(^8.0) - Support
multi-valued
index (^8.0.17) - Support
Spatial
with package orb, such asPoint
,LineString
- Support
generated column
ofstored column
andvirtual column
- Extra custom type such as
Date
,Key
,Boolean
- Support
struct
onFind
,FindOne
,InsertOne
,Insert
,ModifyOne
,DeleteOne
,Delete
,DestroyOne
andPaginate
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)
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
andOffset
) - 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)
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.
Thanks to these awesome companies for their support of Open Source developers β€
Copyright (c) 2019-present, SianLoong Lee