mysql code generation

Overview

mysql code generation support is generated from sql files and database links and supports generating a cache logic code.

mysql generated code content with golang structure corresponding to the data table, CURD operation methods, cache logic, etc. More detailed database code generated reference goctl model

Task Targets

1. familiar with goctl generating mysql code use commands to learn about currently supported instructions and features
2. Preliminary understanding of the format of goctl generating mysql code
3. Preliminary master build process from sql file to mysql code

Preparing

1. Complete golang installation
2. Complete goctl installation

Code Generation

1. Execute the following instructions to store sample sql files in local user.sql.

   CREATE TABLE user (
       id bigint AUTO_INCREMENT,
       name varchar(255) NULL COMMENT 'The username',
       password varchar(255) NOT NULL DEFAULT '' COMMENT 'The user password',
       mobile varchar(255) NOT NULL DEFAULT '' COMMENT 'The mobile phone number',
       gender char(10) NOT NULL DEFAULT 'male' COMMENT 'gender,male|female|unknown',
       nickname varchar(255) NULL DEFAULT '' COMMENT 'The nickname',
       type tinyint(1) NULL DEFAULT 0 COMMENT 'The user type, 0:normal,1:vip, for test golang keyword',
       create_at timestamp NULL,
       update_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       UNIQUE mobile_index (mobile),
       UNIQUE name_index (name),
       PRIMARY KEY (id)
   ) ENGINE = InnoDB COLLATE utf8mb4_general_ci COMMENT 'user table';

2. Create workspace and directory

   $ mkdir -p ~/workspace/model/mysql

3. Move the user.sql file stored above to ~/workspace/model/mysql directory

4. Generate model code

   $ cd ~/workspace/model/mysql
   $ goctl model mysql ddl --src user.sql --dir .
   Done.

   When you see Done. output is generated successfully, then we see the code content generated：

   # list files in current directory
   $ ls
   user.sql         usermodel.go     usermodel_gen.go vars.go
   # view directory tree
   $ tree
   .
   ├── user.sql
   ├── usermodel.go
   ├── usermodel_gen.go
   └── vars.go
   
   0 directories, 4 files

5. Code View

   user.sql

   CREATE TABLE user (
       id bigint AUTO_INCREMENT,
       name varchar(255) NULL COMMENT 'The username',
       password varchar(255) NOT NULL DEFAULT '' COMMENT 'The user password',
       mobile varchar(255) NOT NULL DEFAULT '' COMMENT 'The mobile phone number',
       gender char(10) NOT NULL DEFAULT 'male' COMMENT 'gender,male|female|unknown',
       nickname varchar(255) NULL DEFAULT '' COMMENT 'The nickname',
       type tinyint(1) NULL DEFAULT 0 COMMENT 'The user type, 0:normal,1:vip, for test golang keyword',
       create_at timestamp NULL,
       update_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       UNIQUE mobile_index (mobile),
       UNIQUE name_index (name),
       PRIMARY KEY (id)
   ) ENGINE = InnoDB COLLATE utf8mb4_general_ci COMMENT 'user table';

   usermodel.go

   package mysql
   
   import "github.com/zeromicro/go-zero/core/stores/sqlx"
   
   var _ UserModel = (*customUserModel)(nil)
   
   type (
       // UserModel is an interface to be customized, add more methods here,
       // and implement the added methods in customUserModel.
       UserModel interface {
           userModel
       }
   
       customUserModel struct {
           *defaultUserModel
       }
   )
   
   // NewUserModel returns a model for the database table.
   func NewUserModel(conn sqlx.SqlConn) UserModel {
       return &customUserModel{
           defaultUserModel: newUserModel(conn),
       }
   }

   usermodel_gen.go

   // Code generated by goctl. DO NOT EDIT.
   
   package mysql
   
   import (
       "context"
       "database/sql"
       "fmt"
       "strings"
       "time"
   
       "github.com/zeromicro/go-zero/core/stores/builder"
       "github.com/zeromicro/go-zero/core/stores/sqlc"
       "github.com/zeromicro/go-zero/core/stores/sqlx"
       "github.com/zeromicro/go-zero/core/stringx"
   )
   
   var (
       userFieldNames          = builder.RawFieldNames(&User{})
       userRows                = strings.Join(userFieldNames, ",")
       userRowsExpectAutoSet   = strings.Join(stringx.Remove(userFieldNames, "`id`", "`update_time`", "`create_at`", "`created_at`", "`create_time`", "`update_at`", "`updated_at`"), ",")
       userRowsWithPlaceHolder = strings.Join(stringx.Remove(userFieldNames, "`id`", "`update_time`", "`create_at`", "`created_at`", "`create_time`", "`update_at`", "`updated_at`"), "=?,") + "=?"
   )
   
   type (
       userModel interface {
           Insert(ctx context.Context, data *User) (sql.Result, error)
           FindOne(ctx context.Context, id int64) (*User, error)
           FindOneByMobile(ctx context.Context, mobile string) (*User, error)
           FindOneByName(ctx context.Context, name sql.NullString) (*User, error)
           Update(ctx context.Context, data *User) error
           Delete(ctx context.Context, id int64) error
       }
   
       defaultUserModel struct {
           conn  sqlx.SqlConn
           table string
       }
   
       User struct {
           Id       int64          `db:"id"`
           Name     sql.NullString `db:"name"`     // The username
           Password string         `db:"password"` // The user password
           Mobile   string         `db:"mobile"`   // The mobile phone number
           Gender   string         `db:"gender"`   // gender,male|female|unknown
           Nickname string         `db:"nickname"` // The nickname
           Type     int64          `db:"type"`     // The user type, 0:normal,1:vip, for test golang keyword
           CreateAt sql.NullTime   `db:"create_at"`
           UpdateAt time.Time      `db:"update_at"`
       }
   )
   
   func newUserModel(conn sqlx.SqlConn) *defaultUserModel {
       return &defaultUserModel{
           conn:  conn,
           table: "`user`",
       }
   }
   
   func (m *defaultUserModel) Delete(ctx context.Context, id int64) error {
       query := fmt.Sprintf("delete from %s where `id` = ?", m.table)
       _, err := m.conn.ExecCtx(ctx, query, id)
       return err
   }
   
   func (m *defaultUserModel) FindOne(ctx context.Context, id int64) (*User, error) {
       query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", userRows, m.table)
       var resp User
       err := m.conn.QueryRowCtx(ctx, &resp, query, id)
       switch err {
       case nil:
           return &resp, nil
       case sqlc.ErrNotFound:
           return nil, ErrNotFound
       default:
           return nil, err
       }
   }
   
   func (m *defaultUserModel) FindOneByMobile(ctx context.Context, mobile string) (*User, error) {
       var resp User
       query := fmt.Sprintf("select %s from %s where `mobile` = ? limit 1", userRows, m.table)
       err := m.conn.QueryRowCtx(ctx, &resp, query, mobile)
       switch err {
       case nil:
           return &resp, nil
       case sqlc.ErrNotFound:
           return nil, ErrNotFound
       default:
           return nil, err
       }
   }
   
   func (m *defaultUserModel) FindOneByName(ctx context.Context, name sql.NullString) (*User, error) {
       var resp User
       query := fmt.Sprintf("select %s from %s where `name` = ? limit 1", userRows, m.table)
       err := m.conn.QueryRowCtx(ctx, &resp, query, name)
       switch err {
       case nil:
           return &resp, nil
       case sqlc.ErrNotFound:
           return nil, ErrNotFound
       default:
           return nil, err
       }
   }
   
   func (m *defaultUserModel) Insert(ctx context.Context, data *User) (sql.Result, error) {
       query := fmt.Sprintf("insert into %s (%s) values (?, ?, ?, ?, ?, ?)", m.table, userRowsExpectAutoSet)
       ret, err := m.conn.ExecCtx(ctx, query, data.Name, data.Password, data.Mobile, data.Gender, data.Nickname, data.Type)
       return ret, err
   }
   
   func (m *defaultUserModel) Update(ctx context.Context, newData *User) error {
       query := fmt.Sprintf("update %s set %s where `id` = ?", m.table, userRowsWithPlaceHolder)
       _, err := m.conn.ExecCtx(ctx, query, newData.Name, newData.Password, newData.Mobile, newData.Gender, newData.Nickname, newData.Type, newData.Id)
       return err
   }
   
   func (m *defaultUserModel) tableName() string {
       return m.table
   }

   vars.go

   package mysql
   
   import "github.com/zeromicro/go-zero/core/stores/sqlx"
   
   var ErrNotFound = sqlx.ErrNotFound