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/[email protected]

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:[email protected](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

  1. During batch insertion, the structure will not take the lastinsertid returned by the database.

  2. 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.

  3. 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.

It is inspired by facebook/ent and uses part of its code

GitHub

View Github