crud is a mysql crud code generate tool
中文文档
Getting Started
Overview
Crud is a very easy to learn and easy to use semi ORM framework. Using crud can enable you to complete business requirements quickly, gracefully and with high performance. Currently, MariaDB and MySQL are supported.
-
From SQL DDL table structure design to corresponding model and service generation, it conforms to the process of creating tables before writing code
-
Supports transactions, pessimistic locks, optimistic locks, for update, lock in share mode
-
Elegant API, no ugly hard coding, SQL fragments, all static method calls, and automatic prompt of IDE
-
It supports batch insertion, upsert, and automatic assignment of self incrementing ID to structure
-
Support context
-
High performance. When querying all fields in the table, no reflection is used to create objects, and the performance is consistent with that of native
-
Query support forceindex
-
Query supports flexible setting of query criteria
-
Query supports group by and having
-
Query supports scan query results to user-defined structures (using reflection)
-
Server code standardization
-
Support the generation of proto files and service semi implementation codes containing grpc interface definitions according to SQL DDL table structure definition files
install
go install github.com/hongshengjie/crud@latest
Using the command line
crud -h
Usage of crud:
-path string
.sql file path or dir that contain .sql files
-service
-service generate proto message that matching table and service implement
# Batch generation from directories containing SQL files
crud -path sql/
# Specify an SQL file generation
crud -path sql/user.sql
# According to the table structure, generate the proto file of grpc interface and service semi implementation code for the CRUD of the table
crud -path sql/user.sql -service
Init
Init db
db, _ = sql.Open("mysql","user:pwd@tcp(127.0.0.1:3306)/example?timeout=1s&readTimeout=1s&writeTimeout=1s&parseTime=true&loc=Local&charset=utf8mb4,utf8")
As user SQL table creation file as an example
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id字段',
`name` varchar(100) NOT NULL COMMENT '名称',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `ix_name` (`name`) USING BTREE,
KEY `ix_mtime` (`mtime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
crud -path user.sql
# The following directories and files will be generated
user
├── builder.gen.go // Builder containing insert, select, update, delete SQL statements
├── model.gen.go // Generate the golang struct corresponding to the table structure
└── where.gen.go // Query where condition constructor for each field
（=,>,>=,<>,<,<=,in,not in, like）
The user directory is generated above, and the package name is user.
CRUD API
Create
Single insert
u := &user.User{
ID: 0,
Name: "shengjie",
Age: 18,
Ctime: time.Now(),
Mtime: time.Now(),
}
effect, err := user.
Create(db).
SetUser(u).
Save(ctx)
fmt.Println(err, u, effect)
Insert a single record. Before inserting the above code, set id = 0 and ID field as auto_increment, crud will assign the self increasing ID generated by the database to u.ID, and the u.ID after insertion is the ID generated by DB.
Batch insert
u1 := &user.User{
ID: 0,
Name: "shengjie",
Age: 22,
Ctime: time.Now(),
Mtime: time.Now(),
}
u2 := &user.User{
ID: 0,
Name: "shengjie2",
Age: 22,
Ctime: time.Now(),
Mtime: time.Now(),
}
effect, err = user.
Create(db).
SetUser(u1,u2).
Save(ctx)
fmt.Println(effect, err, u1, u2)
The above two records will be inserted. The lastinsertid returned by each record cannot be obtained during batch insertion, so the ID of U1 and U2 after insertion are 0.
Upsert
a := &user.User{
ID: 1,
Name: "shengjie",
Age: 19,
}
effect, err := user.
Create(db).
SetUser(a).
Upsert(ctx)
fmt.Println(effect, err, a)
If a unique key conflict is encountered during insertion, all fields will be updated with the new value passed in.
Attention
-
During batch insertion, the structure will not take the lastinsertid returned by the database.
-
If the default value of the database is not the zero value of its type, and the corresponding structure does not set the value of this field in the insertion operation, crud will insert dB with the zero value of its type.
-
It is strongly recommended that the value type must use: not null default 0, and the string type must use: not null default ""
Query
Query a single record
u, err = user.
Find(db).
Where(user.IDEQ(1)).
One(ctx)
fmt.Println(u, err)
One(ctx) will automatically set the query statement limit = 1.
Query multiple records
list, err := user.
Find(db).
Where(
user.AgeIn(18, 20, 30),
).
All(ctx)
liststr, _ := json.Marshal(list)
fmt.Printf("%+v %+v \n", string(liststr), err)
Query all records with ages of 18, 20 and 30, and All(ctx) returns []*user.User .
list, err := user.Find(db)).
Where(user.Or(
user.IDGT(97),
user.AgeIn(10, 20, 30),
)).
OrderAsc(user.Age).
Offset(2).
Limit(20).
All(ctx)
fmt.Printf("%+v %+v \n", list, err)
Rich query criteria expression support
list, err := user.
Find(db).
Where(
user.NameContains("java"),
).
All(ctx)
list, err = user.
Find(db).
Where(
user.NameHasPrefix("java"),
).
All(ctx)
String field fuzzy query and prefix matching.
The query result is a single column
count, err := user.
Find(db).
Count().
Where(user.IDGT(0)).
Int64(ctx)
fmt.Println(count, err)
names, err := user.
Find(db).
Select(user.Name).
Limit(2).
Where(
user.IDIn(1, 2, 3, 4),
).
Strings(ctx)
fmt.Println(names, err)
Count() query the quantity of qualified records; If the returned result contains only one column and only one row, Int64 and String can be used; If the returned result contains only one column and multiple rows, you can use Int64s and Strings to get the list.
Transaction support
tx, err := db.Begin(ctx)
if err != nil {
return err
}
u1 := &user.User{
ID: 0,
Name: "shengjie",
Age: 18,
}
_, err = user.
Create(tx).
SetUser(u1).
Save(ctx)
if err != nil {
return tx.Rollback()
}
effect, err := user.
Update(tx).
SetAge(100).
Where(
user.IDEQ(u1.ID)
).
Save(ctx)
if err != nil {
return tx.Rollback()
}
fmt.Println(effect, err)
return tx.Commit()
Advanced Query
Custom query result acquisition
type GroupResutl struct {
Name string `json:"name"`
Cnt int64 `json:"cnt"`
}
result := []*GroupResutl{}
err := user.Find(db).
Select(
user.Name,
xsql.As(xsql.Count("*"), "cnt"),
).
ForceIndex(`ix_name`).
GroupBy(user.Name).
Having(xsql.GT(`cnt`, 1)).
Slice(ctx, &result)
// SELECT `name`, COUNT(*) AS `cnt` FROM `user` FORCE INDEX (`ix_name`) GROUP BY `name` HAVING `cnt` > ?
fmt.Println(err, result)
b, _ := json.Marshal(result)
fmt.Println(string(b))
The above uses force index, groupby, having, count and as to scan the user-defined query results into the user-defined structure. The JSON tag of the structure needs to be consistent with the column name returned from the query results, and the fields in the structure need to be capitalized.
Slice(context,interface{}):The second parameter of the method needs to be passed in: a pointer to a structure slice
Update
effect, err := user.
Update(db).
SetAge(10).
Where(user.NameEQ("java")).
Save(ctx)
fmt.Println(effect, err)
effect, err = user.
Update(db).
SetAge(100).
SetName("java").
SetName("python").
Where(user.IDEQ(97)).
Save(ctx)
fmt.Println(effect, err)
// update `user` set `age` = COALESCE(`age`, 0) + -100, `name` = 'java' where `id` = 5
effect, err = user.
Update(db).
AddAge(-100).
SetName("java").
Where(user.IDEQ(97)).
Save(ctx)
fmt.Println(effect, err)
Delete
effect, err = user.
Delete(db).
Where(
user.And(
user.IDEQ(3),
user.IDIn(1, 3),
)).
Exec(ctx)
It is only executed when the Exec method is called
Generate grpc interface definition proto file and service implementation code
This function helps us generate a lot of cumbersome code that needs to be written by ourselves. For example, a project needs to manage the background, and the interfaces for adding, deleting, modifying and querying need to be built. If we can complete the interface writing with a little modification on the basis of the generated code, the business interface will be realized quickly and with quality.
usage
scrud -path user.sql -service
cd user
protoc --go_out=. --go-grpc_out=. user.api.proto
user
├── api
│ └── user.api.pb.proto
│ └── user.api_grpc.pb.proto
├── service
│ └── user.service.go
├── builder.gen.go
├── model.gen.go
├── user.api.proto
└── where.gen.go
There are more api and service directories and proto files.
proto example
usr.api.proto
syntax="proto3";
option go_package = "/api";
import "google/protobuf/empty.proto";
service UserService {
rpc CreateUser(User)returns(User);
rpc DeleteUser(UserId)returns(google.protobuf.Empty);
rpc UpdateUser(UpdateUserReq)returns(User);
rpc GetUser(UserId)returns(User);
rpc ListUsers(ListUsersReq)returns(ListUsersResp);
}
message User {
//id字段
int64 id = 1 ;
//名称
string name = 2 ;
//年龄
int64 age = 3 ;
//创建时间
string ctime = 4 ;
//更新时间
string mtime = 5 ;
}
message UserId{
int64 id = 1 ;
}
message UpdateUserReq{
User user = 1 ;
repeated string update_mask = 2 ;
}
message ListUsersReq{
//
int64 page = 1 ;
// default 20
int64 page_size = 2 ;
// order by for example : [name] [-id] -表示：倒序排序
repeated string orderby = 4 ;
// 过滤条件需要自定义 for example query name has
string filter = 3 ;
// costom query filter
// string nameHas = 4 ; select * from user where name like '%{nameHas}%'
}
message ListUsersResp{
repeated User users = 1 ;
int64 total_count = 2 ;
int64 page_count = 3 ;
}
Generate a proto message corresponding to the table structure, and the generated API file conforms to Google API design specification.
service example
user.service.go
package service
import (
"context"
"database/sql"
"errors"
"math"
"strings"
"time"
"github.com/hongshengjie/crud/example/user"
"github.com/hongshengjie/crud/example/user/api"
"google.golang.org/protobuf/types/known/emptypb"
)
// UserServiceImpl UserServiceImpl
type UserServiceImpl struct {
db *sql.DB
}
// CreateUser CreateUser
func (s *UserServiceImpl) CreateUser(ctx context.Context, req *api.User) (*api.User, error) {
// do some parameter check
// if req.GetXXXX() != 0 {
// return nil, errors.New(-1, "parameter error")
// }
a := &user.User{
Id: 0,
Name: req.GetName(),
Age: req.GetAge(),
Ctime: time.Now(),
Mtime: time.Now(),
}
var err error
_, err = user.
Create(s.db).
SetUser(a).
Save(ctx)
if err != nil {
return nil, err
}
// query after create and return
a2, err := user.
Find(s.db).
Where(
user.IdEQ(a.Id),
).
One(ctx)
if err != nil {
return nil, err
}
return convertUser(a2), nil
}
// DeleteUser DeleteUser
func (s *UserServiceImpl) DeletesUser(ctx context.Context, req *api.UserId) (*emptypb.Empty, error) {
_, err := user.
Delete(s.db).
Where(
user.IdEQ(req.GetId()),
).
Exec(ctx)
if err != nil {
return nil, err
}
return &emptypb.Empty{}, nil
}
// Updateuser UpdateUser
func (s *UserServiceImpl) UpdateUser(ctx context.Context, req *api.UpdateUserReq) (*api.User, error) {
if len(req.GetUpdateMask()) == 0 {
return nil, errors.New("update_mask empty")
}
update := user.Update(s.db)
for _, v := range req.GetUpdateMask() {
switch v {
case "user.name":
update.SetName(req.GetUser().GetName())
case "user.age":
update.SetAge(req.GetUser().GetAge())
case "user.ctime":
t, err := time.ParseInLocation("2006-01-02 15:04:05", req.GetUser().GetCtime(), time.Local)
if err != nil {
return nil, err
}
update.SetCtime(t)
case "user.mtime":
t, err := time.ParseInLocation("2006-01-02 15:04:05", req.GetUser().GetMtime(), time.Local)
if err != nil {
return nil, err
}
update.SetMtime(t)
}
}
_, err := update.
Where(
user.IdEQ(req.GetUser().GetId()),
).
Save(ctx)
if err != nil {
return nil, err
}
// query after update and return
a, err := user.
Find(s.db).
Where(
user.IdEQ(req.GetUser().GetId()),
).
One(ctx)
if err != nil {
return nil, err
}
return convertUser(a), nil
}
// GetUser GetUser
func (s *UserServiceImpl) GetUser(ctx context.Context, req *api.UserId) (*api.User, error) {
a, err := user.
Find(s.db).
Where(
user.IdEQ(req.GetId()),
).
One(ctx)
if err != nil {
return nil, err
}
return convertUser(a), nil
}
// ListUsers ListUsers
func (s *UserServiceImpl) ListUsers(ctx context.Context, req *api.ListUsersReq) (*api.ListUsersResp, error) {
page := req.GetPage()
size := req.GetPageSize()
if size <= 0 {
size = 20
}
offset := size * (page - 1)
if offset < 0 {
offset = 0
}
find := user.Find(s.db).Offset(offset).Limit(size)
for _, v := range req.GetOrderby() {
if strings.HasPrefix(v, "-") {
find.OrderDesc(strings.TrimPrefix(v, "-"))
continue
}
find.OrderAsc(v)
}
// costom filter
// {
// find.Where(user.NameContains(req.GetFilter()))
// }
list, err := find.All(ctx)
if err != nil {
return nil, err
}
count, err := user.
Find(s.db).
Count().
Int64(ctx)
if err != nil {
return nil, err
}
pageCount := int64(math.Ceil(float64(count) / float64(size)))
return &api.ListUsersResp{Users: convertUserList(list), TotalCount: count, PageCount: pageCount}, nil
}
func convertUser(a *user.User) *api.User {
return &api.User{
Id: a.Id,
Name: a.Name,
Age: a.Age,
Ctime: a.Ctime.Format("2006-01-02 15:04:05"),
Mtime: a.Mtime.Format("2006-01-02 15:04:05"),
}
}
func convertUserList(list []*user.User) []*api.User {
ret := make([]*api.User, 0, len(list))
for _, v := range list {
ret = append(ret, convertUser(v))
}
return ret
}
The semi implementation code of the above service only needs to add some parameter verification, or automatically generate the message conversion code from the DB layer model structure to the API layer according to the code of the condition filter, which is convenient and flexible.
