sqlgen

README

sql gen

由 Gen Guides https://gorm.io/gen/index.html 改写。

功能

• 输出默认模板
• 指定模板路径
• 指定tag
• 生成 model 属性get方法
• 是否覆盖已生成的文件
• table 正则并生成 model query 路径
• table 路径风格
• 动态常量类型生成
• 自定义类型对应包的导入
• 动态模板生成代码
• 模板生成后的命令
• 模板增加参数

安装

go install github.com/githubzhaoqian/sqlgen/tools/gentool@latest

命令

  -c string
        is path for .sqlgen.yml (default ".sqlgen.yaml")
  -db string
        input mysql|postgres|sqlite|sqlserver|clickhouse. consult[https://gorm.io/docs/connecting_to_the_database.html]
  -dsn string
        consult[https://gorm.io/docs/connecting_to_the_database.html]
  -fieldCoverable
        generate with pointer when field has default value
  -fieldNullable
        generate with pointer when field is nullable
  -fieldSignable
        detect integer field's unsigned type, adjust generated data type
  -fieldWithTags string
        generate field with custom tag
  -ot string
        output template 指定模板输出目录
  -overwrite
        overwrite existing file
  -tableRegexp string
        table regexp
  -tableRegexpStyle string
        table regexp style: lower/camel/snake
  -tables string
        enter the required data table or leave it blank
  -templateDir string
        generated with template directory

配置文件

version: "0.1"
database:
  # 数据库连接 consult[https://gorm.io/docs/connecting_to_the_database.html]"
  dsn: "dbUser:dbPassword@tcp(host:port)/dbName?charset=utf8mb4&parseTime=true&loc=Asia%2FShanghai"
  # 数据库类型 consult[https://gorm.io/docs/connecting_to_the_database.html]
  db: "mysql"
  # 表类型 You can cli input : orders,users,goods
  tables: # 指定表 可以在命令行使用 tables
    - failed_job
    - server_user
    - admin_user
  defaultAllTable: false  # 未指定table 是否生成全部表
  # generate with pointer when field is nullable
  #  nullable 生成指针
  fieldNullable: false  
  fieldCoverable: false
  # Signable生成 无符号类型
  fieldSignable: true 

  # 是否覆盖文件
  overwrite: false
  # table 正则用于替换路径使用
  tableRegexp: ^([a-z]+)_([a-z_]+)$
  modelName: "{2}"
  #  table 正则风格
  tableRegexpStyle: lower  # lower/camel/snake
  # 自定义tag 按json 蛇形添加
  fieldWithTags:
    - json
  # 指定模板目录，默认系统模板
  templateDir:
  templates:
    - outPath: "./internal/domain/{1}/internal/model/{2}/model.go"
      name: "Model"
      isGo: true # 是否是go文件
      cmds:
       - name: "ls"
         argStr: "{f}"  # {f}标识文件路径
      params:   # 特殊参数 map[string]interface{}
       tableName: 1
       aaa:
         - "Id"
         - "Name"
    - outPath: "./internal/domain/{1}/bean/{2}/interface.go"
      name: "BeanInterface"
      isGo: true
    - outPath: "./internal/domain/{1}/bean/{2}/bean.go"
      name: "Bean"
      isGo: true
    - outPath: "./internal/domain/{1}/bean/{2}/po.go"
      name: "PO"
      isGo: true
    - outPath: "./internal/types/{2}/types.go"
      name: "Types"
      isGo: true
    - outPath: "./internal/domain/{1}/internal/mapper/{2}/interface.go"
      name: "MapperInterface"
      isGo: true
    - outPath: "./internal/domain/{1}/internal/mapper/{2}/mapper.go"
      name: "Mapper"
      isGo: true
    - outPath: "./internal/domain/{1}/service/{2}/interface.go"
      name: "ServiceInterface"
      isGo: true
    - outPath: "./internal/domain/{1}/service/{2}/service.go"
      name: "Service"
      isGo: true
    - outPath: "./internal/handle/adminapi/v1/{2}/interface.go"
      name: "AdminHandlerInterface"
      isGo: true
    - outPath: "./internal/handle/adminapi/v1/{2}/handler.go"
      name: "AdminHandler"
      isGo: true
  # 动态常量
  dynamicConstSuffixes:
    - "Status"
    - "Type"
  # 哪些是自动生成值的字段
  autoValueFields:
    - "ID"
    - "UpdateTime"
    - "CreateTime"
  dynamicConstOutPath: "./internal/consts/{2}/consts.go"
  dynamicConstTemplate: "Consts"
  # 常量包是否自动导入 ImportPkgPaths 变量中
  dynamicConstImport: false
  # 常量包后缀
  dynamicAliasSuffix: "Consts"
  # 禁用表名复数
  singularTable: true
  # 缩略词 
  # "API", "ASCII", "CPU", "CSS", "DNS", "EOF", "GUID", "HTML", "HTTP", "HTTPS", "ID", "IP", 
  # "JSON", "LHS", "QPS", "RAM", "RHS", "RPC", "SLA", "SMTP", "SSH", "TLS", "TTL", "UID", "UI", 
  # "UUID", "URI", "URL", "UTF8", "VM", "XML", "XSRF", "XSS"
  initialisms: true 
  # 自定义类型转换
  convTypeMap:
    timestamp: "timex.Time"
    datetime: "timex.Time"
  # 自定义类型包
  convTypePkgMap:
    "timex.Time": "gofastddd/internal/utils/timex"

template 值说明

CREATE TABLE `admin_user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mobile` varchar(11) DEFAULT NULL COMMENT '手机',
  `email` varchar(255) DEFAULT NULL COMMENT '邮箱',
  `nice_name` varchar(50) NOT NULL DEFAULT '' COMMENT '昵称',
  `password` varchar(32) NOT NULL DEFAULT '' COMMENT '密码',
  `salt` char(5) NOT NULL COMMENT '加盐',
  `avatar` varchar(255) NOT NULL DEFAULT '' COMMENT '头像',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态',
  `reason` varchar(255) DEFAULT '' COMMENT '原因',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_mobile` (`mobile`),
  UNIQUE KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';

{
    "Generated": true,
    "FileName": "admin_user",
    "S": "u",
    "QueryStructName": "user",
    "ModelStructName": "User",
    "TableName": "admin_user",
    "TableComment": "管理员表",
    "Fields": [
        {
            "Name": "ID",
            "ColumnName": "id",
            "TypeName": "uint32",
            "OriginalTypeName": "uint32",
            "DatabaseTypeName": "int",
            "ColumnType": "int(10) unsigned",
            "PrimaryKey": true,
            "AutoIncrement": true,
            "Length": 0,
            "DecimalSizeOK": true,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 10,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "",
            "DefaultValueOK": false,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "Mobile",
            "ColumnName": "mobile",
            "TypeName": "string",
            "OriginalTypeName": "string",
            "DatabaseTypeName": "varchar",
            "ColumnType": "varchar(11)",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 11,
            "DecimalSizeOK": false,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": true,
            "Unique": true,
            "ScanType": {},
            "Comment": "手机",
            "DefaultValueOK": false,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "Email",
            "ColumnName": "email",
            "TypeName": "string",
            "OriginalTypeName": "string",
            "DatabaseTypeName": "varchar",
            "ColumnType": "varchar(255)",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 255,
            "DecimalSizeOK": false,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": true,
            "Unique": true,
            "ScanType": {},
            "Comment": "邮箱",
            "DefaultValueOK": false,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "NiceName",
            "ColumnName": "nice_name",
            "TypeName": "string",
            "OriginalTypeName": "string",
            "DatabaseTypeName": "varchar",
            "ColumnType": "varchar(50)",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 50,
            "DecimalSizeOK": false,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "昵称",
            "DefaultValueOK": true,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "Password",
            "ColumnName": "password",
            "TypeName": "string",
            "OriginalTypeName": "string",
            "DatabaseTypeName": "varchar",
            "ColumnType": "varchar(32)",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 32,
            "DecimalSizeOK": false,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "密码",
            "DefaultValueOK": true,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "Salt",
            "ColumnName": "salt",
            "TypeName": "string",
            "OriginalTypeName": "string",
            "DatabaseTypeName": "char",
            "ColumnType": "char(5)",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 5,
            "DecimalSizeOK": false,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "加盐",
            "DefaultValueOK": false,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "Avatar",
            "ColumnName": "avatar",
            "TypeName": "string",
            "OriginalTypeName": "string",
            "DatabaseTypeName": "varchar",
            "ColumnType": "varchar(255)",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 255,
            "DecimalSizeOK": false,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "头像",
            "DefaultValueOK": true,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "Status",
            "ColumnName": "status",
            "TypeName": "userConsts.Status",
            "OriginalTypeName": "uint32",
            "DatabaseTypeName": "tinyint",
            "ColumnType": "tinyint(3) unsigned",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 0,
            "DecimalSizeOK": true,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 3,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "状态",
            "DefaultValueOK": true,
            "DefaultValue": "1",
            "CustomTypeMap": {}
        },
        {
            "Name": "Reason",
            "ColumnName": "reason",
            "TypeName": "string",
            "OriginalTypeName": "string",
            "DatabaseTypeName": "varchar",
            "ColumnType": "varchar(255)",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 255,
            "DecimalSizeOK": false,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": true,
            "Unique": false,
            "ScanType": {},
            "Comment": "原因",
            "DefaultValueOK": true,
            "DefaultValue": "",
            "CustomTypeMap": {}
        },
        {
            "Name": "UpdateTime",
            "ColumnName": "update_time",
            "TypeName": "timex.Time",
            "OriginalTypeName": "timex.Time",
            "DatabaseTypeName": "timestamp",
            "ColumnType": "timestamp",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 0,
            "DecimalSizeOK": true,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "",
            "DefaultValueOK": true,
            "DefaultValue": "CURRENT_TIMESTAMP",
            "CustomTypeMap": {}
        },
        {
            "Name": "CreateTime",
            "ColumnName": "create_time",
            "TypeName": "timex.Time",
            "OriginalTypeName": "timex.Time",
            "DatabaseTypeName": "timestamp",
            "ColumnType": "timestamp",
            "PrimaryKey": false,
            "AutoIncrement": false,
            "Length": 0,
            "DecimalSizeOK": true,
            "DecimalSizeScale": 0,
            "DecimalSizePrecision": 0,
            "Nullable": false,
            "Unique": false,
            "ScanType": {},
            "Comment": "",
            "DefaultValueOK": true,
            "DefaultValue": "CURRENT_TIMESTAMP",
            "CustomTypeMap": {}
        }
    ],
    "ImportPkgPaths": [
        "gofastddd/internal/utils/timex"
    ],
    "TemplatePkgPath": {
        "Consts": "github.com/githubzhaoqian/sqlgen/tools/gentool/internal/consts/user"
    },
    "PkgPath": "",
    "Package": "user",
    "Type": "",
    "ConvTypeMap": {
        "datetime": "timex.Time",
        "timestamp": "timex.Time"
    },
    "ConvTypePkgMap": {
        "timex.Time": "gofastddd/internal/utils/timex"
    },
    "DynamicConstSuffixes": [
        "Status",
        "Type"
    ],
    "AutoValueFields": {},
    "FieldWithTags": [
        "json"
    ],
    "SubMatch": ["admin_user", "admin", "user"],
    "Params": {}
}

增加方法

var FuncMap = template.FuncMap{
    "suffixes":  Suffixes,
    "suffix":    suffix,
    "pathBase":  pathBase,
    "inMap":     inMap,
    "lcFirst":   LcFirst,
    "contains":  Contains,
    "incr":      incr,
    "toLower":   ToLower,
    "snake":     Snake,
    "kebabCase": KebabCase,
}

// incr 累加
func incr(key int, increment int) int {
    return key + increment
}

// Suffixes 后缀
func Suffixes(name string, suffixList []string) bool {
    for _, suffix := range suffixList {
        if strings.HasSuffix(name, suffix) {
            return true
        }
	}
    return false
}

// Contains 包含
func Contains(name string, contains ...string) bool {
    for _, item := range contains {
        if strings.Contains(name, item) {
            return true
        }
    }
    return false
}

// suffix 后缀
func suffix(name, fix string) bool {
    return strings.HasSuffix(name, fix)
}

// pathBase 目录名
func pathBase(path string) string {
    return filepath.Base(path)
}

// inMap 判断key是否在map中
func inMap(key string, data map[string]struct{}) bool {
    _, ok := data[key]
    return ok
}

func ToLower(src string) string {
    str := stringy.New(src)
    return str.CamelCase().ToLower()
}

func Camel(src string) string {
    str := stringy.New(src)
    return str.CamelCase().Get()
}

func Snake(src string) string {
    str := stringy.New(src)
    return str.SnakeCase().ToLower()
}

func LcFirst(src string) string {
    str := stringy.New(src)
    return str.LcFirst()
}

func KebabCase(src string) string {
    str := stringy.New(src)
    return str.KebabCase().ToLower()
}

License

Released under the MIT License

Documentation

Index

• Constants
• Variables
• func OutputTemplate(dir string) error
• type Cmd
• type Config
  • func (cfg *Config) Revise() (err error)
  • func (cfg *Config) WithJSONTagNameStrategy(ns func(columnName string) (tagContent string))
• type GenerateMode
• type Generator
  • func NewGenerator(cfg Config) *Generator
  • func (g *Generator) ApplyBasic(models ...interface{})
  • func (g *Generator) ApplyInterface(fc interface{}, models ...interface{})
  • func (g *Generator) Execute()
  • func (g *Generator) GenerateAllTable() (tableModels []interface{})
  • func (g *Generator) GenerateModel(tableName string) *generate.StructMeta
  • func (g *Generator) GenerateModelAs(tableName string, modelName string) *generate.StructMeta
  • func (g *Generator) UseDB(db *gorm.DB)
• type M
• type RowsAffected
• type SQLResult
• type SQLRow
• type SQLRows
• type T
• type Template

Constants

const CmdFileID = "{f}"

const TemplateName = "template"

const TemplatePrefix = "*tpl"

Variables

var (
	// ErrEmptyCondition empty condition
	ErrEmptyCondition = errors.New("empty condition")
)

var (
	ErrFileExist = errors.New("file exist skip overwrite")
)

var TemplateFs embed.FS

Functions

func OutputTemplate

func OutputTemplate(dir string) error

Types

type Cmd

type Cmd struct {
	Name   string
	ArgStr string
}

type Config

type Config struct {

	// generate model global configuration
	FieldNullable  bool // generate pointer when field is nullable
	FieldCoverable bool // generate pointer when field has default value, to fix problem zero value cannot be assign: https://gorm.io/docs/create.html#Default-Values
	FieldSignable  bool // detect integer field's unsigned type, adjust generated data type

	Mode GenerateMode // generate mode

	QueryPkgName string // generated query code's package name

	TemplateDir      string   // generated with template directory
	FieldWithTags    []string // generate field with custom tag
	Overwrite        bool     // overwrite existing file
	DefaultAllTable  bool     // 默认全部表
	TableRegexp      string   // table regexp
	TableRegexpStyle string   // table regexp style
	ModelName        string   // model 名称

	Templates            []Template // dynamic const suffix
	DynamicConstSuffixes []string   // dynamic const suffix
	DynamicConstOutPath  string     // dynamic const out path
	DynamicConstTemplate string     // 动态常量模板
	DynamicAliasSuffix   string     // 动态常量包别名后缀
	DynamicConstImport   bool       // 动态常量自动导入
	AutoValueFields      []string   // dynamic const suffix

	ConvTypeMap    map[string]string `yaml:"convType"`    // conv type
	ConvTypePkgMap map[string]string `yaml:"convTypePkg"` // conv type pkg
	// contains filtered or unexported fields
}

Config generator's basic configuration

func (*Config) Revise

func (cfg *Config) Revise() (err error)

Revise format path and db

func (*Config) WithJSONTagNameStrategy

func (cfg *Config) WithJSONTagNameStrategy(ns func(columnName string) (tagContent string))

WithJSONTagNameStrategy specify json tag naming strategy

type GenerateMode

type GenerateMode uint

GenerateMode generate mode

const (
	// WithDefaultQuery create default query in generated code
	WithDefaultQuery GenerateMode = 1 << iota

	// WithoutContext generate code without context constrain
	WithoutContext

	// WithQueryInterface generate code with exported interface object
	WithQueryInterface
)

type Generator

type Generator struct {
	Config

	Data map[string]*genInfo //gen query data
	// contains filtered or unexported fields
}

Generator code generator

func NewGenerator

func NewGenerator(cfg Config) *Generator

NewGenerator create a new generator

func (*Generator) ApplyBasic

func (g *Generator) ApplyBasic(models ...interface{})

ApplyBasic specify models which will implement basic .diy_method

func (*Generator) ApplyInterface

func (g *Generator) ApplyInterface(fc interface{}, models ...interface{})

ApplyInterface specifies .diy_method interfaces on structures, implment codes will be generated after calling g.Execute() eg: g.ApplyInterface(func(model.Method){}, model.User{}, model.Company{})

func (*Generator) Execute

func (g *Generator) Execute()

Execute generate code to output path

func (*Generator) GenerateAllTable

func (g *Generator) GenerateAllTable() (tableModels []interface{})

GenerateAllTable generate all tables in db

func (*Generator) GenerateModel

func (g *Generator) GenerateModel(tableName string) *generate.StructMeta

GenerateModel catch table info from db, return a BaseStruct

func (*Generator) GenerateModelAs

func (g *Generator) GenerateModelAs(tableName string, modelName string) *generate.StructMeta

GenerateModelAs catch table info from db, return a BaseStruct

func (*Generator) UseDB

func (g *Generator) UseDB(db *gorm.DB)

UseDB set db connection

type M

type M map[string]interface{}

M map[string]interface{}

type RowsAffected

type RowsAffected int64

RowsAffected execute affected raws

type SQLResult

type SQLResult sql.Result

SQLResult sql.result

type SQLRow

type SQLRow sql.Row

SQLRow sql.Row

type SQLRows

type SQLRows sql.Rows

SQLRows sql.Rows

type T

type T interface{}

T generic type

type Template

type Template struct {
	OutPath string // specify a directory for output
	Name    string // template name
	IsGo    bool
	Cmds    []Cmd                  // 要执行的命令 {f} 表示模板文件名
	Params  map[string]interface{} // 参数
}