Model Commands

[!TIP] This document is machine-translated by Google. If you find grammatical and semantic errors, and the document description is not clear, please PR

goctl model is one of the components in the tool module under go-zero. It currently supports the recognition of mysql ddl for model layer code generation. It can be selectively generated with or without redis cache through the command line or idea plug-in (supported soon) The code logic.

Quick start

• Generated by ddl

    $ goctl model mysql ddl -src="./*.sql" -dir="./sql/model" -c

  CURD code can be quickly generated after executing the above command.

    model
    │   ├── error.go
    │   └── usermodel.go

• Generated by datasource

    $ goctl model mysql datasource -url="user:password@tcp(127.0.0.1:3306)/database" -table="*"  -dir="./model"

• Example code

    package model
    import (
        "database/sql"
        "fmt"
        "strings"
        "time"
        "github.com/zeromicro/go-zero/core/stores/builder"
        "github.com/zeromicro/go-zero/core/stores/cache"
        "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`", "`create_time`", "`update_time`"), ",")
        userRowsWithPlaceHolder = strings.Join(stringx.Remove(userFieldNames, "`id`", "`create_time`", "`update_time`"), "=?,") + "=?"
        cacheUserNamePrefix   = "cache#User#name#"
        cacheUserMobilePrefix = "cache#User#mobile#"
        cacheUserIdPrefix     = "cache#User#id#"
        cacheUserPrefix       = "cache#User#user#"
    )
    type (
        UserModel interface {
            Insert(data User) (sql.Result, error)
            FindOne(id int64) (*User, error)
            FindOneByUser(user string) (*User, error)
            FindOneByName(name string) (*User, error)
            FindOneByMobile(mobile string) (*User, error)
            Update(data User) error
            Delete(id int64) error
        }
        defaultUserModel struct {
            sqlc.CachedConn
            table string
        }
        User struct {
            Id         int64     `db:"id"`
            User       string    `db:"user"`     // user
            Name       string    `db:"name"`     // user name
            Password   string    `db:"password"` // user password
            Mobile     string    `db:"mobile"`   // mobile
            Gender     string    `db:"gender"`   // male｜female｜secret
            Nickname   string    `db:"nickname"` // nickname
            CreateTime time.Time `db:"create_time"`
            UpdateTime time.Time `db:"update_time"`
        }
    )
    func NewUserModel(conn sqlx.SqlConn, c cache.CacheConf) UserModel {
        return &defaultUserModel{
            CachedConn: sqlc.NewConn(conn, c),
            table:      "`user`",
        }
    }
    func (m *defaultUserModel) Insert(data User) (sql.Result, error) {
        userNameKey := fmt.Sprintf("%s%v", cacheUserNamePrefix, data.Name)
        userMobileKey := fmt.Sprintf("%s%v", cacheUserMobilePrefix, data.Mobile)
        userKey := fmt.Sprintf("%s%v", cacheUserPrefix, data.User)
        ret, err := m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
            query := fmt.Sprintf("insert into %s (%s) values (?, ?, ?, ?, ?, ?)", m.table, userRowsExpectAutoSet)
            return conn.Exec(query, data.User, data.Name, data.Password, data.Mobile, data.Gender, data.Nickname)
        }, userNameKey, userMobileKey, userKey)
        return ret, err
    }
    func (m *defaultUserModel) FindOne(id int64) (*User, error) {
        userIdKey := fmt.Sprintf("%s%v", cacheUserIdPrefix, id)
        var resp User
        err := m.QueryRow(&resp, userIdKey, func(conn sqlx.SqlConn, v interface{}) error {
            query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", userRows, m.table)
            return conn.QueryRow(v, query, id)
        })
        switch err {
        case nil:
            return &resp, nil
        case sqlc.ErrNotFound:
            return nil, ErrNotFound
        default:
            return nil, err
        }
    }
    func (m *defaultUserModel) FindOneByUser(user string) (*User, error) {
        userKey := fmt.Sprintf("%s%v", cacheUserPrefix, user)
        var resp User
        err := m.QueryRowIndex(&resp, userKey, m.formatPrimary, func(conn sqlx.SqlConn, v interface{}) (i interface{}, e error) {
            query := fmt.Sprintf("select %s from %s where `user` = ? limit 1", userRows, m.table)
            if err := conn.QueryRow(&resp, query, user); err != nil {
                return nil, err
            }
            return resp.Id, nil
        }, m.queryPrimary)
        switch err {
        case nil:
            return &resp, nil
        case sqlc.ErrNotFound:
            return nil, ErrNotFound
        default:
            return nil, err
        }
    }
    func (m *defaultUserModel) FindOneByName(name string) (*User, error) {
        userNameKey := fmt.Sprintf("%s%v", cacheUserNamePrefix, name)
        var resp User
        err := m.QueryRowIndex(&resp, userNameKey, m.formatPrimary, func(conn sqlx.SqlConn, v interface{}) (i interface{}, e error) {
            query := fmt.Sprintf("select %s from %s where `name` = ? limit 1", userRows, m.table)
            if err := conn.QueryRow(&resp, query, name); err != nil {
                return nil, err
            }
            return resp.Id, nil
        }, m.queryPrimary)
        switch err {
        case nil:
            return &resp, nil
        case sqlc.ErrNotFound:
            return nil, ErrNotFound
        default:
            return nil, err
        }
    }
    func (m *defaultUserModel) FindOneByMobile(mobile string) (*User, error) {
        userMobileKey := fmt.Sprintf("%s%v", cacheUserMobilePrefix, mobile)
        var resp User
        err := m.QueryRowIndex(&resp, userMobileKey, m.formatPrimary, func(conn sqlx.SqlConn, v interface{}) (i interface{}, e error) {
            query := fmt.Sprintf("select %s from %s where `mobile` = ? limit 1", userRows, m.table)
            if err := conn.QueryRow(&resp, query, mobile); err != nil {
                return nil, err
            }
            return resp.Id, nil
        }, m.queryPrimary)
        switch err {
        case nil:
            return &resp, nil
        case sqlc.ErrNotFound:
            return nil, ErrNotFound
        default:
            return nil, err
        }
    }
    func (m *defaultUserModel) Update(data User) error {
        userIdKey := fmt.Sprintf("%s%v", cacheUserIdPrefix, data.Id)
        _, err := m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
            query := fmt.Sprintf("update %s set %s where `id` = ?", m.table, userRowsWithPlaceHolder)
            return conn.Exec(query, data.User, data.Name, data.Password, data.Mobile, data.Gender, data.Nickname, data.Id)
        }, userIdKey)
        return err
    }
    func (m *defaultUserModel) Delete(id int64) error {
        data, err := m.FindOne(id)
        if err != nil {
            return err
        }
        userNameKey := fmt.Sprintf("%s%v", cacheUserNamePrefix, data.Name)
        userMobileKey := fmt.Sprintf("%s%v", cacheUserMobilePrefix, data.Mobile)
        userIdKey := fmt.Sprintf("%s%v", cacheUserIdPrefix, id)
        userKey := fmt.Sprintf("%s%v", cacheUserPrefix, data.User)
        _, err = m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
            query := fmt.Sprintf("delete from %s where `id` = ?", m.table)
            return conn.Exec(query, id)
        }, userNameKey, userMobileKey, userIdKey, userKey)
        return err
    }
    func (m *defaultUserModel) formatPrimary(primary interface{}) string {
        return fmt.Sprintf("%s%v", cacheUserIdPrefix, primary)
    }
    func (m *defaultUserModel) queryPrimary(conn sqlx.SqlConn, v, primary interface{}) error {
        query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", userRows, m.table)
        return conn.QueryRow(v, query, primary)
    }

用法

$ goctl model mysql -h

NAME:
   goctl model mysql - generate mysql model"
USAGE:
   goctl model mysql command [command options] [arguments...]
COMMANDS:
   ddl         generate mysql model from ddl"
   datasource  generate model from datasource"
OPTIONS:
   --help, -h  show help

Generation rules

• Default rule

  By default, users will create createTime and updateTime fields (ignoring case and underscore naming style) when creating a table, and the default values are both CURRENT_TIMESTAMP, and updateTime supports ON UPDATE CURRENT_TIMESTAMP. For these two fields, insert, It will be removed when update is not in the assignment scope. Of course, if you don’t need these two fields, it does not matter.

• With cache mode

  • ddl

    $ goctl model mysql -src={patterns} -dir={dir} -cache

    help

    NAME:
       goctl model mysql ddl - generate mysql model from ddl
    USAGE:
       goctl model mysql ddl [command options] [arguments...]
    OPTIONS:
       --src value, -s value  the path or path globbing patterns of the ddl
       --dir value, -d value  the target dir
       --style value          the file naming format, see [https://github.com/zeromicro/go-zero/tree/master/tools/goctl/config/readme.md]
       --cache, -c            generate code with cache [optional]
       --idea                 for idea plugin [optional]

  • datasource

    $ goctl model mysql datasource -url={datasource} -table={patterns}  -dir={dir} -cache=true

    help

    NAME:
       goctl model mysql datasource - generate model from datasource
    USAGE:
       goctl model mysql datasource [command options] [arguments...]
    OPTIONS:
       --url value              the data source of database,like "root:password@tcp(127.0.0.1:3306)/database
       --table value, -t value  the table or table globbing patterns in the database
       --cache, -c              generate code with cache [optional]
       --dir value, -d value    the target dir
       --style value            the file naming format, see [https://github.com/zeromicro/go-zero/tree/master/tools/goctl/config/readme.md]
       --idea                   for idea plugin [optional]

    [!TIP] Goctl model mysql ddl/datasource has added a new --style parameter to mark the file naming style.

  Currently, only redis cache is supported. If you select the cache mode, the generated FindOne(ByXxx)&Delete code will generate code with cache logic. Currently, only single index fields (except full-text index) are supported. For joint index By default, we believe that there is no need to bring a cache, and it is not a general-purpose code, so it is not placed in the code generation ranks. For example, the id, name, and mobile fields in the user table in the example are all single-field indexes.

• Without cache mode

  • ddl

      $  goctl model -src={patterns} -dir={dir}

  • datasource

      $  goctl model mysql datasource -url={datasource} -table={patterns}  -dir={dir}

  or

  • ddl

      $  goctl model -src={patterns} -dir={dir}

  • datasource

      $  goctl model mysql datasource -url={datasource} -table={patterns}  -dir={dir}

Generate code only basic CURD structure.

Cache

For the cache, I chose to list it in the form of question and answer. I think this can more clearly describe the function of the cache in the model.

• What information will the cache?

  For the primary key field cache, the entire structure information will be cached, while for the single index field (except full-text index), the primary key field value will be cached.

• Does the data update (update) operation clear the cache?

  Yes, but only clear the information in the primary key cache, why? I won’t go into details here.

• Why not generate updateByXxx and deleteByXxx codes based on single index fields?

  There is no problem in theory, but we believe that the data operations of the model layer are based on the entire structure, including queries. I do not recommend querying only certain fields (no objection), otherwise our cache will be meaningless.

• Why not support the code generation layer of findPageLimit and findAll?

  At present, I think that in addition to the basic CURD, the other codes are all business-type codes. I think it is better for developers to write according to business needs.

Type conversion rules