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

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

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

    1. model
    2. ├── error.go
    3. └── usermodel.go
  • Generated by datasource

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

    1. package model
    2. import (
    3. "database/sql"
    4. "fmt"
    5. "strings"
    6. "time"
    7. "github.com/zeromicro/go-zero/core/stores/builder"
    8. "github.com/zeromicro/go-zero/core/stores/cache"
    9. "github.com/zeromicro/go-zero/core/stores/sqlc"
    10. "github.com/zeromicro/go-zero/core/stores/sqlx"
    11. "github.com/zeromicro/go-zero/core/stringx"
    12. )
    13. var (
    14. userFieldNames = builder.RawFieldNames(&User{})
    15. userRows = strings.Join(userFieldNames, ",")
    16. userRowsExpectAutoSet = strings.Join(stringx.Remove(userFieldNames, "`id`", "`create_time`", "`update_time`"), ",")
    17. userRowsWithPlaceHolder = strings.Join(stringx.Remove(userFieldNames, "`id`", "`create_time`", "`update_time`"), "=?,") + "=?"
    18. cacheUserNamePrefix = "cache#User#name#"
    19. cacheUserMobilePrefix = "cache#User#mobile#"
    20. cacheUserIdPrefix = "cache#User#id#"
    21. cacheUserPrefix = "cache#User#user#"
    22. )
    23. type (
    24. UserModel interface {
    25. Insert(data User) (sql.Result, error)
    26. FindOne(id int64) (*User, error)
    27. FindOneByUser(user string) (*User, error)
    28. FindOneByName(name string) (*User, error)
    29. FindOneByMobile(mobile string) (*User, error)
    30. Update(data User) error
    31. Delete(id int64) error
    32. }
    33. defaultUserModel struct {
    34. sqlc.CachedConn
    35. table string
    36. }
    37. User struct {
    38. Id int64 `db:"id"`
    39. User string `db:"user"` // user
    40. Name string `db:"name"` // user name
    41. Password string `db:"password"` // user password
    42. Mobile string `db:"mobile"` // mobile
    43. Gender string `db:"gender"` // male|female|secret
    44. Nickname string `db:"nickname"` // nickname
    45. CreateTime time.Time `db:"create_time"`
    46. UpdateTime time.Time `db:"update_time"`
    47. }
    48. )
    49. func NewUserModel(conn sqlx.SqlConn, c cache.CacheConf) UserModel {
    50. return &defaultUserModel{
    51. CachedConn: sqlc.NewConn(conn, c),
    52. table: "`user`",
    53. }
    54. }
    55. func (m *defaultUserModel) Insert(data User) (sql.Result, error) {
    56. userNameKey := fmt.Sprintf("%s%v", cacheUserNamePrefix, data.Name)
    57. userMobileKey := fmt.Sprintf("%s%v", cacheUserMobilePrefix, data.Mobile)
    58. userKey := fmt.Sprintf("%s%v", cacheUserPrefix, data.User)
    59. ret, err := m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
    60. query := fmt.Sprintf("insert into %s (%s) values (?, ?, ?, ?, ?, ?)", m.table, userRowsExpectAutoSet)
    61. return conn.Exec(query, data.User, data.Name, data.Password, data.Mobile, data.Gender, data.Nickname)
    62. }, userNameKey, userMobileKey, userKey)
    63. return ret, err
    64. }
    65. func (m *defaultUserModel) FindOne(id int64) (*User, error) {
    66. userIdKey := fmt.Sprintf("%s%v", cacheUserIdPrefix, id)
    67. var resp User
    68. err := m.QueryRow(&resp, userIdKey, func(conn sqlx.SqlConn, v interface{}) error {
    69. query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", userRows, m.table)
    70. return conn.QueryRow(v, query, id)
    71. })
    72. switch err {
    73. case nil:
    74. return &resp, nil
    75. case sqlc.ErrNotFound:
    76. return nil, ErrNotFound
    77. default:
    78. return nil, err
    79. }
    80. }
    81. func (m *defaultUserModel) FindOneByUser(user string) (*User, error) {
    82. userKey := fmt.Sprintf("%s%v", cacheUserPrefix, user)
    83. var resp User
    84. err := m.QueryRowIndex(&resp, userKey, m.formatPrimary, func(conn sqlx.SqlConn, v interface{}) (i interface{}, e error) {
    85. query := fmt.Sprintf("select %s from %s where `user` = ? limit 1", userRows, m.table)
    86. if err := conn.QueryRow(&resp, query, user); err != nil {
    87. return nil, err
    88. }
    89. return resp.Id, nil
    90. }, m.queryPrimary)
    91. switch err {
    92. case nil:
    93. return &resp, nil
    94. case sqlc.ErrNotFound:
    95. return nil, ErrNotFound
    96. default:
    97. return nil, err
    98. }
    99. }
    100. func (m *defaultUserModel) FindOneByName(name string) (*User, error) {
    101. userNameKey := fmt.Sprintf("%s%v", cacheUserNamePrefix, name)
    102. var resp User
    103. err := m.QueryRowIndex(&resp, userNameKey, m.formatPrimary, func(conn sqlx.SqlConn, v interface{}) (i interface{}, e error) {
    104. query := fmt.Sprintf("select %s from %s where `name` = ? limit 1", userRows, m.table)
    105. if err := conn.QueryRow(&resp, query, name); err != nil {
    106. return nil, err
    107. }
    108. return resp.Id, nil
    109. }, m.queryPrimary)
    110. switch err {
    111. case nil:
    112. return &resp, nil
    113. case sqlc.ErrNotFound:
    114. return nil, ErrNotFound
    115. default:
    116. return nil, err
    117. }
    118. }
    119. func (m *defaultUserModel) FindOneByMobile(mobile string) (*User, error) {
    120. userMobileKey := fmt.Sprintf("%s%v", cacheUserMobilePrefix, mobile)
    121. var resp User
    122. err := m.QueryRowIndex(&resp, userMobileKey, m.formatPrimary, func(conn sqlx.SqlConn, v interface{}) (i interface{}, e error) {
    123. query := fmt.Sprintf("select %s from %s where `mobile` = ? limit 1", userRows, m.table)
    124. if err := conn.QueryRow(&resp, query, mobile); err != nil {
    125. return nil, err
    126. }
    127. return resp.Id, nil
    128. }, m.queryPrimary)
    129. switch err {
    130. case nil:
    131. return &resp, nil
    132. case sqlc.ErrNotFound:
    133. return nil, ErrNotFound
    134. default:
    135. return nil, err
    136. }
    137. }
    138. func (m *defaultUserModel) Update(data User) error {
    139. userIdKey := fmt.Sprintf("%s%v", cacheUserIdPrefix, data.Id)
    140. _, err := m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
    141. query := fmt.Sprintf("update %s set %s where `id` = ?", m.table, userRowsWithPlaceHolder)
    142. return conn.Exec(query, data.User, data.Name, data.Password, data.Mobile, data.Gender, data.Nickname, data.Id)
    143. }, userIdKey)
    144. return err
    145. }
    146. func (m *defaultUserModel) Delete(id int64) error {
    147. data, err := m.FindOne(id)
    148. if err != nil {
    149. return err
    150. }
    151. userNameKey := fmt.Sprintf("%s%v", cacheUserNamePrefix, data.Name)
    152. userMobileKey := fmt.Sprintf("%s%v", cacheUserMobilePrefix, data.Mobile)
    153. userIdKey := fmt.Sprintf("%s%v", cacheUserIdPrefix, id)
    154. userKey := fmt.Sprintf("%s%v", cacheUserPrefix, data.User)
    155. _, err = m.Exec(func(conn sqlx.SqlConn) (result sql.Result, err error) {
    156. query := fmt.Sprintf("delete from %s where `id` = ?", m.table)
    157. return conn.Exec(query, id)
    158. }, userNameKey, userMobileKey, userIdKey, userKey)
    159. return err
    160. }
    161. func (m *defaultUserModel) formatPrimary(primary interface{}) string {
    162. return fmt.Sprintf("%s%v", cacheUserIdPrefix, primary)
    163. }
    164. func (m *defaultUserModel) queryPrimary(conn sqlx.SqlConn, v, primary interface{}) error {
    165. query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", userRows, m.table)
    166. return conn.QueryRow(v, query, primary)
    167. }

用法

  1. $ goctl model mysql -h
  1. NAME:
  2. goctl model mysql - generate mysql model"
  3. USAGE:
  4. goctl model mysql command [command options] [arguments...]
  5. COMMANDS:
  6. ddl generate mysql model from ddl"
  7. datasource generate model from datasource"
  8. OPTIONS:
  9. --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

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

      help

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

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

      help

      1. NAME:
      2. goctl model mysql datasource - generate model from datasource
      3. USAGE:
      4. goctl model mysql datasource [command options] [arguments...]
      5. OPTIONS:
      6. --url value the data source of database,like "root:password@tcp(127.0.0.1:3306)/database
      7. --table value, -t value the table or table globbing patterns in the database
      8. --cache, -c generate code with cache [optional]
      9. --dir value, -d value the target dir
      10. --style value the file naming format, see [https://github.com/zeromicro/go-zero/tree/master/tools/goctl/config/readme.md]
      11. --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

      1. $ goctl model -src={patterns} -dir={dir}
    • datasource

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

    or

    • ddl

      1. $ goctl model -src={patterns} -dir={dir}
    • datasource

      1. $ 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

mysql dataType golang dataType golang dataType(if null&&default null)
bool int64 sql.NullInt64
boolean int64 sql.NullInt64
tinyint int64 sql.NullInt64
smallint int64 sql.NullInt64
mediumint int64 sql.NullInt64
int int64 sql.NullInt64
integer int64 sql.NullInt64
bigint int64 sql.NullInt64
float float64 sql.NullFloat64
double float64 sql.NullFloat64
decimal float64 sql.NullFloat64
date time.Time sql.NullTime
datetime time.Time sql.NullTime
timestamp time.Time sql.NullTime
time string sql.NullString
year time.Time sql.NullInt64
char string sql.NullString
varchar string sql.NullString
binary string sql.NullString
varbinary string sql.NullString
tinytext string sql.NullString
text string sql.NullString
mediumtext string sql.NullString
longtext string sql.NullString
enum string sql.NullString
set string sql.NullString
json string sql.NullString