Crud - A mysql crud code generate tool from table DDL sql file

Related tags

ORM mysql orm mariadb
Overview

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

You might also like...
CURD using go fiber - gorm - mysql

GO Fiber - CRUD - GORM - Mysql Folder Structure - database | database config |- migration | migration config - middleware | mid

Very simple example of golang buffalo CRUD

Buffalo CRUD exemple Introduction Site du projet : https://gobuffalo.io/fr Documentation générale : https://gobuffalo.io/fr/docs/overview/ Documentati

Simple project in Go to play around with some CRUD operations using a PostgreSQL database and pgx

Record Store November 2021 I started learning Go a few weeks ago and this is my first proper project using Go. I wanted to use it to get to grips with

Simple-crm-system - Simple CRM system CRUD backend using Go, Fiber, SQLite, Gorm

Simple CRM system CRUD backend using GO, Fiber, Gorm, SQLite Developent go mod t

Basic-crud-with-go - API Rest utilizando go

basic-crud-with-go API Rest utilizando go API Docs Postman file at docs/Books.po

A simple CRUD app built with Go for Shopify Backend Developer Intern Challenge - Summer 2022

Shopify Backend Developer Intern Challenge - Summer 2022 A simple CRUD app built with Go and Gin. This let you update track your inventory, add new it

A simple CRUD API made with Go, Postgres, FIber, Gorm and Docker.

golang-test-api A simple CRUD API made with Go, Postgres, FIber, Gorm and Docker. Cloning the repository To clone the repository run the following com

A simple wrapper around sql.DB to help with structs. Not quite an ORM.

go-modeldb A simple wrapper around sql.DB to help with structs. Not quite an ORM. Philosophy: Don't make an ORM Example: // Setup require "modeldb" db

A pure golang SQL database for learning database theory

Go SQL DB 中文 "Go SQL DB" is a relational database that supports SQL queries for research purposes. The main goal is to show the basic principles and k

Releases(v1.0.8)
Owner
null
100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood.

go-queryset 100% type-safe ORM for Go (Golang) with code generation and MySQL, PostgreSQL, Sqlite3, SQL Server support. GORM under the hood. Contents

Denis Isaev 679 Sep 27, 2022
Golang mysql orm, a personal learning project, dedicated to easy use of mysql

golang mysql orm 个人学习项目, 一个易于使用的mysql-orm mapping struct to mysql table golang结构

magacy 1 Dec 30, 2021
Go tool for generating sql scanners, sql statements and other helper functions

sqlgen generates SQL statements and database helper functions from your Go structs. It can be used in place of a simple ORM or hand-written SQL. See t

drone.io 183 Sep 4, 2022
go-api-crud: API Rest CRUD

go-api-crud API Rest CRUD Estrutura de pacotes cfg (config) Utilizando variáveis de ambiente. Arquivo [cfg/config.go] é possível "setar" configuração.

Jair 1 Oct 27, 2021
beedb is a go ORM,support database/sql interface,pq/mysql/sqlite

Beedb ❗ IMPORTANT: Beedb is being deprecated in favor of Beego.orm ❗ Beedb is an ORM for Go. It lets you map Go structs to tables in a database. It's

astaxie 707 Sep 6, 2022
Golang struct-to-table database mapper

Structable: Struct-Table Mapping for Go Warning: This is the Structable 4 development branch. For a stable release, use version 3.1.0. Structable deve

null 264 Sep 25, 2022
A example of a join table using liquibase and gorm

A example of a join table using liquibase and gorm. Additionally the join table's composite key is used as a composite foreign key for another table.

null 1 Feb 4, 2022
Gorm-sharding - High performance table sharding plugin for Gorm

Gorm Sharding This project has moved to Gorm offical organization: https://githu

Longbridge 69 Sep 7, 2022
CRUDist Model Driven Web Development. Automagically generate CRUD APIs from your model.

CRUDist - Model Driven API Development Automagicaly create CRUD APIs for your gorm models. Example Model definition type BaseModel struct { ID

null 1 Nov 15, 2021
Simple and Powerful ORM for Go, support mysql,postgres,tidb,sqlite3,mssql,oracle, Moved to https://gitea.com/xorm/xorm

xorm HAS BEEN MOVED TO https://gitea.com/xorm/xorm . THIS REPOSITORY WILL NOT BE UPDATED ANY MORE. 中文 Xorm is a simple and powerful ORM for Go. Featur

null 6.6k Sep 27, 2022