Students who write background business often joke that their work is CRUD around data tables. Although the actual work is not so simple, there are many similar repetitive and uncreative work in daily life. And this kind of work can be automated to some extent. In order to provide efficiency for business developers, we developed a back-end development workflow tool some time ago, which mainly provides the following functions:

  • Generate server API base code and Swagger documentation comments (gin framework only)
  • Generate server API client code
  • Go struct Add tags in batches
  • Generate gorM Model struct
  • Model struct generates SQL

Because there is some coupling between these functions and our internal public libraries, the entire tool may not be open source. Here, we take model struct to generate SQL function as an example, talk about our ideas and tools to do this tool.

task

Jinzhu’s GORM, which we used in the project, is used as orM library here. If you are using Golang’s other ORM lib, the implementation should be much the same.

Our task is defined from the following model struct:

Mysql > create table (‘ file ‘);

Train of thought

Generating SQL from A Model struct is A matter of translating language A into language B. This process is no different in principle from compiling source code into binary executables. Therefore, the problem is essentially a compilation problem. A complete compilation consists of the following steps:

For the tasks to be completed in this paper, the main completion of lexical analysis, grammar analysis, object code generation.

tool

For lexical and grammatical analysis, we have Lex and Yacc, Yet Another Compiler-compiler. We’re just trying to create a table file, so we need to customize the syntax and write lex and yACC files by ourselves.

Golang has a lot of tools that other languages wouldn’t envy, such as Go Pprof, Go List, Go Vet, etc. In terms of language metaprogramming, GO 1.4 implements bootstract; Compile-time lexical analysis and syntactic analysis have long been included in the go/ AST standard library. AST stands for Abstract Syntax Tree. With the AST, we can write a GO program to parse the GO source code. To be specific, write a program that parses the Model struct that defines the data table and then generates SQL table building sentences.

implementation

Specific to our task realization, it can be divided into the following steps:

  • Load the source code to generate an AST Tree
  • Get and parse the Model struct AST
  • Struct field name/tag create_definition, table_options

Complete code implementation, can move to Github Gorm2SQL.

Effect:

user_email.go:

type UserBase struct { UserId string `sql:"index:idx_ub"` Ip string `sql:"unique_index:uniq_ip"` } type UserEmail struct  { Id int64 `gorm:"primary_key"` UserBase Email string Sex bool Age int Score float64 UpdateTime time.Time `sql:"default:CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"` CreateTime time.Time `sql:"default:CURRENT_TIMESTAMP"` }Copy the code
gorm2sql sql -f user_email.go -s UserEmail -o db.sql
Copy the code

Result:

CREATE TABLE `user_email`
(
  `id` bigint AUTO_INCREMENT NOT NULL ,
  `user_id` varchar(128) NOT NULL ,
  `ip` varchar(128) NOT NULL ,
  `email` varchar(128) NOT NULL ,
  `sex` boolean NOT NULL ,
  `age` int NOT NULL ,
  `score` double NOT NULL ,
  `update_time` datetime NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` datetime NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ub (`user_id`),
  UNIQUE INDEX uniq_ip (`ip`),
  PRIMARY KEY (`id`)
) engine=innodb DEFAULT charset=utf8mb4;
Copy the code

Further reading

  • Generating code
  • The ultimate guide to writing a Go tool
  • Go automatically generates SDK practices