sqlike
A golang SQL ORM which anti toxic query and focus on 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/slow query
What do we provide apart from native package (database/sql)?
- Support
ENUMandSET - Support
UUID - Support
JSON - Support
descending index - Support
multi-valuedindex - Support
Spatialwith package orb, such asPoint,LineString - Support
generated columnofstored columnandvirtual column - Extra custom type such as
Date,Key,Boolean - Support
structonFind,FindOne,InsertOne,Insert,ModifyOne,DeleteOne,Delete,DestroyOneandPaginateapis - Support
Transactions - Support cursor based pagination
- Support advance and complex query statement
- 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
sqldumpfor backup purpose (experiment)
Missing DOC?
You can refer to 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
LimitandOffset) - 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.Rawto bypass it) - bidirectional sorting is not allow (except mysql 8.0 and above)
- currently only support
mysqldriver (postgres and sqlite yet to implement)
General APIs
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"
)
// 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
}
}
}
}
Integrate with OpenTracing
Tracing is become very common in practice. And you may integrate your OpenTracing as such :
import (
"context"
"github.com/go-sql-driver/mysql"
"github.com/si3nloong/sqlike/plugin/opentracing"
"github.com/si3nloong/sqlike/sql/instrumented"
"github.com/si3nloong/sqlike/sqlike"
)
func main() {
ctx := context.Background()
driver := "mysql"
cfg := mysql.NewConfig()
cfg.User = "root"
cfg.Passwd = "abcd1234"
cfg.ParseTime = true
conn, err := mysql.NewConnector(cfg)
if err != nil {
panic(err)
}
itpr := opentracing.NewInterceptor(
opentracing.WithDBInstance("sqlike"),
opentracing.WithDBUser(cfg.User),
opentracing.WithExec(true),
opentracing.WithQuery(true),
)
client, err := sqlike.ConnectDB(
ctx,
driver,
instrumented.WrapConnector(conn, itpr),
)
if err != nil {
panic(err)
}
defer client.Close()
}
Inspired by gorm, mongodb-go-driver and sqlx.
Special Sponsors
Big Thanks To
Thanks to these awesome companies for their support of Open Source developers
License
Copyright (c) 2019-present, SianLoong Lee
