# 前言
写多了业务，数据库操作的 curd 占大部分的业务查询，也要占业务开发的近1/3的时间，在开发一个业务时，我们从建表，写 sql 查询语句到 Golang 代码，要花费不少时间，如果你是用ORM ，那么效率可能会稍微高一些，但试想，如果有一个工具能根据 sql 语句生成我们想要的代码，这份想要的代码你可以用 golang 模板去指定，是不是需要分钟级别甚至小时级别的业务查询就可以控制在秒级别来了。
在此前，我也调研了 sqlc，也体验了一下 sqlc，他的生成功能挺丰富，但是学习成本如果再降低些其实就完美了，了解其中原理后，我自己试着写了一个 sqlgen 工具，功能和 sqlc 相似，但学习成本更低，目前支持了 gorm,xorm,sqlx,sql,bun 的代码生成，如果想要生成其他orm 的代码，可以通过提供模板来支持。
# 介绍
sqlgen 是一个支持从 sql 文件,数据库链接两种方式来做代码生成的脚手架工具，目前支持了 gorm,xorm,sqlx,sql,bun 的代码生成，学习和使用非常简单。
# GitHub
[https://github.com/anqiansong/sqlgen](https://github.com/anqiansong/sqlgen)
# 安装
```
go install github.com/anqiansong/sqlgen@latest
```
# 使用示例
先有 sql 文件及查询语句如下
```sql
CREATE TABLE `user`
(
`id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key,
`name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT 'The username',
`password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'The \n user password',
`mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'The mobile phone number',
`gender` char(10) COLLATE utf8mb4_general_ci NOT NULL COMMENT 'gender,male|female|unknown',
`nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT 'The nickname',
`type` tinyint(1) COLLATE utf8mb4_general_ci 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 KEY `name_index` (`name`),
UNIQUE KEY `mobile_index` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'user table' COLLATE=utf8mb4_general_ci;
-- operation: create
-- note: sqlgen will generate only one create function named Create, so the next insert sql statements will be ignored.
-- test case: insert one.
-- fn: CreateOne
insert into `user` (`name`, `password`, `mobile`, `gender`, `nickname`, `type`, `create_at`, `update_at`) values (?, ?, ?, ?, ?, ?, ?, ?);
-- test case: insert partial columns.
-- fn: CreatePart
insert into `user` (`name`, `password`, `mobile`) values (?, ?, ?);
-- operation: update
-- note: sqlgen will generate update function whose name via the value of `fn:` in the comment.
-- update statement please see https://dev.mysql.com/doc/refman/8.0/en/update.html.
-- test case: update one.
-- fn: Update
update `user` set `name` = ?, `password` = ?, `mobile` = ?, `gender` = ?, `nickname` = ?, `type` = ?, `create_at` = ?, `update_at` = ? where `id` = ?;
-- case: update one with order by desc clause.
-- fn: UpdateOrderByIdDesc
update `user` set `name` = ?, `password` = ?, `mobile` = ?, `gender` = ?, `nickname` = ?, `type` = ?, `create_at` = ?, `update_at` = ? where `id` = ? order by id desc;
-- test case: update one with order by desc, limit count clause.
-- fn: UpdateOrderByIdDescLimitCount
update `user` set `name` = ?, `password` = ?, `mobile` = ?, `gender` = ?, `nickname` = ?, `type` = ?, `create_at` = ?, `update_at` = ? where `id` = ? order by id desc limit ?;
-- operation: read
-- note: sqlgen will generate update function whose name via the value of `fn:` in the comment.
-- select statement please see https://dev.mysql.com/doc/refman/8.0/en/select.html.
-- test case: find one by primary key.
-- note: the expression `limit 1` is necessary in order to get the first record, otherwise it will return multiple records.
-- fn: FindOne
select * from `user` where `id` = ? limit 1;
-- test case: find one by unique key.
-- note: the expression `limit 1` is necessary in order to get the first record, otherwise it will return multiple records.
-- fn: FindOneByName
select * from `user` where `name` = ? limit 1;
-- test case: find one with group by clause.
-- note: the expression `limit 1` is necessary in order to get the first record, otherwise it will return multiple records.
-- fn: FindOneGroupByName
select * from `user` where `name` = ? group by name limit 1;
-- test case: find one with group by desc, having clause.
-- note: the expression `limit 1` is necessary in order to get the first record, otherwise it will return multiple records.
-- fn: FindOneGroupByNameHavingName
select * from `user` where `name` = ? group by name having name = ? limit 1;
-- test case: find all
-- fn: FindAll
select * from `user`;
-- test case: find limit count, offset 0.
-- note: the expression both `limit ?`(unsupported marker likes `$1`) and `limit 10`(count must be gather than 1) can return multiple records. do not use `limit 1` if you want to read multiple records.
-- fn: FindLimit
select * from `user` where id > ? limit ?;
-- test case: find records, with limit count, offset clause.
-- note: the expression both `limit ?, ?`(unsupported marker likes `$1`) and `limit 10, 10`(count must gather than 1, offset must gather than 0) can return multiple records. do not use `limit ?,1` or `limit 10,1` if you want to read multiple records.
-- fn: FindLimitOffset
select * from `user` limit ?, ?;
-- test case: find records, with group by, limit, offset clause.
-- note: the expression both `limit ?, ?`(unsupported marker likes `$1`) and `limit 10, 10`(count must gather than 1, offset must gather than 0) can return multiple records. do not use `limit ?,1` or `limit 10,1` if you want to read multiple records.
-- fn: FindGroupLimitOffset
select * from `user` where id > ? group by name limit ?, ?;
-- test case: find records, with group by, having, limit, offset clause.
-- note: the expression both `limit ?, ?`(unsupported marker likes `$1`) and `limit 10, 10`(count must gather than 1, offset must gather than 0) can return multiple records. do not use `limit ?,1` or `limit 10,1` if you want to read multiple records.
-- fn: FindGroupHavingLimitOffset
select * from `user` where id > ? group by name having id > ? limit ?, ?;
-- test case: find records, with group by, having, order by asc, limit, offset clause.
-- note: the expression both `limit ?, ?`(unsupported marker likes `$1`) and `limit 10, 10`(count must gather than 1, offset must gather than 0) can return multiple records. do not use `limit ?,1` or `limit 10,1` if you want to read multiple records.
-- fn: FindGroupHavingOrderAscLimitOffset
select * from `user` where id > ? group by name having id > ? order by id limit ?, ?;
-- test case: find records, with group by, having, order by desc, limit, offset clause.
-- note: the expression both `limit ?, ?`(unsupported marker likes `$1`) and `limit 10, 10`(count must gather than 1, offset must gather than 0) can return multiple records. do not use `limit ?,1` or `limit 10,1` if you want to read multiple records.
-- fn: FindGroupHavingOrderDescLimitOffset
select * from `user` where id > ? group by name having id > ? order by id desc limit ?, ?;
-- test case: find partial columns.
-- fn: FindOnePart
select `name`, `password`, `mobile` from `user` where id > ? limit 1;
-- test case: built-in function: count.
-- note: AS expression is necessary if you are using built-in function.
-- fn: FindAllCount
select count(id) AS countID from `user`;
-- test case: built-in function: count.
-- note: AS expression is necessary if you are using built-in function.
-- fn: FindAllCountWhere
select count(id) AS countID from `user` where id > ?;
-- test case: built-in function: max
-- note: AS expression is necessary if you are using built-in function.
-- fn: FindMaxID
select max(id) AS maxID from `user`;
-- test case: built-in function: min
-- note: AS expression is necessary if you are using built-in function.
-- fn: FindMinID
select min(id) AS minID from `user`;
-- test case: built-in function: avg
-- note: AS expression is necessary if you are using built-in function.
-- fn: FindAvgID
select avg(id) AS avgID from `user`;
-- operation: delete
-- note: sqlgen will generate update function whose name via the value of `fn:` in the comment.
-- select statement please see https://dev.mysql.com/doc/refman/8.0/en/delete.html.
-- test case: delete one by primary key.
-- fn: DeleteOne
delete from `user` where `id` = ?;
-- test case: delete one by unique key.
-- fn: DeleteOneByName
delete from `user` where `name` = ?;
-- test case: delete one with order by asc clause.
-- fn: DeleteOneOrderByIDAsc
delete from `user` where `name` = ? order by id;
-- test case: delete one with order by desc clause.
-- fn: DeleteOneOrderByIDDesc
delete from `user` where `name` = ? order by id desc;
-- test case: delete one with order by desc clause, limit clause.
-- fn: DeleteOneOrderByIDDescLimitCount
delete from `user` where `name` = ? order by id desc limit ?;
```
这里以生成xorm 为例子来看一下其输出
```go
// Code generated by sqlgen. DO NOT EDIT!
package model
import (
"context"
"database/sql"
"fmt"
"time"
"xorm.io/xorm"
"github.com/shopspring/decimal"
)
// UserModel represents a user model.
type UserModel struct {
engine xorm.EngineInterface
}
// User represents a user struct data.
type User struct {
Id uint64 `xorm:"pk autoincr 'id'" json:"id"`
Name string `xorm:"'name'" json:"name"`
Password string `xorm:"'password'" json:"password"`
Mobile string `xorm:"'mobile'" json:"mobile"`
Gender string `xorm:"'gender'" json:"gender"`
Nickname string `xorm:"'nickname'" json:"nickname"`
Type int8 `xorm:"'type'" json:"type"`
CreateAt time.Time `xorm:"'create_at'" json:"createAt"`
UpdateAt time.Time `xorm:"'update_at'" json:"updateAt"`
}
// FindOneWhereParameter is a where parameter structure.
type FindOneWhereParameter struct {
IdEqual uint64
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindOneByNameWhereParameter is a where parameter structure.
type FindOneByNameWhereParameter struct {
NameEqual string
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindOneGroupByNameWhereParameter is a where parameter structure.
type FindOneGroupByNameWhereParameter struct {
NameEqual string
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindOneGroupByNameHavingNameWhereParameter is a where parameter structure.
type FindOneGroupByNameHavingNameWhereParameter struct {
NameEqual string
}
// FindOneGroupByNameHavingNameHavingParameter is a having parameter structure.
type FindOneGroupByNameHavingNameHavingParameter struct {
NameEqual string
}
// TableName returns the table name. it implemented by gorm.Tabler.
// TableName returns the table name. it implemented by gorm.Tabler.
// FindLimitWhereParameter is a where parameter structure.
type FindLimitWhereParameter struct {
IdGT uint64
}
// FindLimitLimitParameter is a limit parameter structure.
type FindLimitLimitParameter struct {
Count int
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindLimitOffsetLimitParameter is a limit parameter structure.
type FindLimitOffsetLimitParameter struct {
Count int
Offset int
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindGroupLimitOffsetWhereParameter is a where parameter structure.
type FindGroupLimitOffsetWhereParameter struct {
IdGT uint64
}
// FindGroupLimitOffsetLimitParameter is a limit parameter structure.
type FindGroupLimitOffsetLimitParameter struct {
Count int
Offset int
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindGroupHavingLimitOffsetWhereParameter is a where parameter structure.
type FindGroupHavingLimitOffsetWhereParameter struct {
IdGT uint64
}
// FindGroupHavingLimitOffsetHavingParameter is a having parameter structure.
type FindGroupHavingLimitOffsetHavingParameter struct {
IdGT uint64
}
// FindGroupHavingLimitOffsetLimitParameter is a limit parameter structure.
type FindGroupHavingLimitOffsetLimitParameter struct {
Count int
Offset int
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindGroupHavingOrderAscLimitOffsetWhereParameter is a where parameter structure.
type FindGroupHavingOrderAscLimitOffsetWhereParameter struct {
IdGT uint64
}
// FindGroupHavingOrderAscLimitOffsetHavingParameter is a having parameter structure.
type FindGroupHavingOrderAscLimitOffsetHavingParameter struct {
IdGT uint64
}
// FindGroupHavingOrderAscLimitOffsetLimitParameter is a limit parameter structure.
type FindGroupHavingOrderAscLimitOffsetLimitParameter struct {
Count int
Offset int
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindGroupHavingOrderDescLimitOffsetWhereParameter is a where parameter structure.
type FindGroupHavingOrderDescLimitOffsetWhereParameter struct {
IdGT uint64
}
// FindGroupHavingOrderDescLimitOffsetHavingParameter is a having parameter structure.
type FindGroupHavingOrderDescLimitOffsetHavingParameter struct {
IdGT uint64
}
// FindGroupHavingOrderDescLimitOffsetLimitParameter is a limit parameter structure.
type FindGroupHavingOrderDescLimitOffsetLimitParameter struct {
Count int
Offset int
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindOnePartWhereParameter is a where parameter structure.
type FindOnePartWhereParameter struct {
IdGT uint64
}
// TableName returns the table name. it implemented by gorm.Tabler.
// FindAllCountResult is a find all count result.
type FindAllCountResult struct {
CountID sql.NullInt64 `xorm:"'countID'" json:"countID"`
}
// TableName returns the table name. it implemented by gorm.Tabler.
func (FindAllCountResult) TableName() string {
return "user"
}
// FindAllCountWhereWhereParameter is a where parameter structure.
type FindAllCountWhereWhereParameter struct {
IdGT uint64
}
// FindAllCountWhereResult is a find all count where result.
type FindAllCountWhereResult struct {
CountID sql.NullInt64 `xorm:"'countID'" json:"countID"`
}
// TableName returns the table name. it implemented by gorm.Tabler.
func (FindAllCountWhereResult) TableName() string {
return "user"
}
// FindMaxIDResult is a find max id result.
type FindMaxIDResult struct {
MaxID sql.NullInt64 `xorm:"'maxID'" json:"maxID"`
}
// TableName returns the table name. it implemented by gorm.Tabler.
func (FindMaxIDResult) TableName() string {
return "user"
}
// FindMinIDResult is a find min id result.
type FindMinIDResult struct {
MinID sql.NullInt64 `xorm:"'minID'" json:"minID"`
}
// TableName returns the table name. it implemented by gorm.Tabler.
func (FindMinIDResult) TableName() string {
return "user"
}
// FindAvgIDResult is a find avg id result.
type FindAvgIDResult struct {
AvgID decimal.NullDecimal `xorm:"'avgID'" json:"avgID"`
}
// TableName returns the table name. it implemented by gorm.Tabler.
func (FindAvgIDResult) TableName() string {
return "user"
}
// UpdateWhereParameter is a where parameter structure.
type UpdateWhereParameter struct {
IdEqual uint64
}
// UpdateOrderByIdDescWhereParameter is a where parameter structure.
type UpdateOrderByIdDescWhereParameter struct {
IdEqual uint64
}
// UpdateOrderByIdDescLimitCountWhereParameter is a where parameter structure.
type UpdateOrderByIdDescLimitCountWhereParameter struct {
IdEqual uint64
}
// UpdateOrderByIdDescLimitCountLimitParameter is a limit parameter structure.
type UpdateOrderByIdDescLimitCountLimitParameter struct {
Count int
}
// DeleteOneWhereParameter is a where parameter structure.
type DeleteOneWhereParameter struct {
IdEqual uint64
}
// DeleteOneByNameWhereParameter is a where parameter structure.
type DeleteOneByNameWhereParameter struct {
NameEqual string
}
// DeleteOneOrderByIDAscWhereParameter is a where parameter structure.
type DeleteOneOrderByIDAscWhereParameter struct {
NameEqual string
}
// DeleteOneOrderByIDDescWhereParameter is a where parameter structure.
type DeleteOneOrderByIDDescWhereParameter struct {
NameEqual string
}
// DeleteOneOrderByIDDescLimitCountWhereParameter is a where parameter structure.
type DeleteOneOrderByIDDescLimitCountWhereParameter struct {
NameEqual string
}
// DeleteOneOrderByIDDescLimitCountLimitParameter is a limit parameter structure.
type DeleteOneOrderByIDDescLimitCountLimitParameter struct {
Count int
}
func (User) TableName() string {
return "user"
}
// NewUserModel returns a new user model.
func NewUserModel(engine xorm.EngineInterface) *UserModel {
return &UserModel{engine: engine}
}
// Create creates user data.
func (m *UserModel) Create(ctx context.Context, data ...*User) error {
if len(data) == 0 {
return fmt.Errorf("data is empty")
}
var session = m.engine.Context(ctx)
var list []interface{}
for _, v := range data {
list = append(list, v)
}
_, err := session.Insert(list...)
return err
}
// FindOne is generated from sql:
// select * from `user` where `id` = ? limit 1;
func (m *UserModel) FindOne(ctx context.Context, where FindOneWhereParameter) (*User, error) {
var result = new(User)
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`id = ?`, where.IdEqual)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindOneByName is generated from sql:
// select * from `user` where `name` = ? limit 1;
func (m *UserModel) FindOneByName(ctx context.Context, where FindOneByNameWhereParameter) (*User, error) {
var result = new(User)
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`name = ?`, where.NameEqual)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindOneGroupByName is generated from sql:
// select * from `user` where `name` = ? group by name limit 1;
func (m *UserModel) FindOneGroupByName(ctx context.Context, where FindOneGroupByNameWhereParameter) (*User, error) {
var result = new(User)
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`name = ?`, where.NameEqual)
session.GroupBy(`name`)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindOneGroupByNameHavingName is generated from sql:
// select * from `user` where `name` = ? group by name having name = ? limit 1;
func (m *UserModel) FindOneGroupByNameHavingName(ctx context.Context, where FindOneGroupByNameHavingNameWhereParameter, having FindOneGroupByNameHavingNameHavingParameter) (*User, error) {
var result = new(User)
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`name = ?`, where.NameEqual)
session.GroupBy(`name`)
session.Having(fmt.Sprintf(`name = '%v'`, having.NameEqual))
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindAll is generated from sql:
// select * from `user`;
func (m *UserModel) FindAll(ctx context.Context) ([]*User, error) {
var result []*User
var session = m.engine.Context(ctx)
session.Select(`*`)
err := session.Find(&result)
return result, err
}
// FindLimit is generated from sql:
// select * from `user` where id > ? limit ?;
func (m *UserModel) FindLimit(ctx context.Context, where FindLimitWhereParameter, limit FindLimitLimitParameter) ([]*User, error) {
var result []*User
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`id > ?`, where.IdGT)
session.Limit(limit.Count)
err := session.Find(&result)
return result, err
}
// FindLimitOffset is generated from sql:
// select * from `user` limit ?, ?;
func (m *UserModel) FindLimitOffset(ctx context.Context, limit FindLimitOffsetLimitParameter) ([]*User, error) {
var result []*User
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Limit(limit.Count, limit.Offset)
err := session.Find(&result)
return result, err
}
// FindGroupLimitOffset is generated from sql:
// select * from `user` where id > ? group by name limit ?, ?;
func (m *UserModel) FindGroupLimitOffset(ctx context.Context, where FindGroupLimitOffsetWhereParameter, limit FindGroupLimitOffsetLimitParameter) ([]*User, error) {
var result []*User
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`id > ?`, where.IdGT)
session.GroupBy(`name`)
session.Limit(limit.Count, limit.Offset)
err := session.Find(&result)
return result, err
}
// FindGroupHavingLimitOffset is generated from sql:
// select * from `user` where id > ? group by name having id > ? limit ?, ?;
func (m *UserModel) FindGroupHavingLimitOffset(ctx context.Context, where FindGroupHavingLimitOffsetWhereParameter, having FindGroupHavingLimitOffsetHavingParameter, limit FindGroupHavingLimitOffsetLimitParameter) ([]*User, error) {
var result []*User
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`id > ?`, where.IdGT)
session.GroupBy(`name`)
session.Having(fmt.Sprintf(`id > '%v'`, having.IdGT))
session.Limit(limit.Count, limit.Offset)
err := session.Find(&result)
return result, err
}
// FindGroupHavingOrderAscLimitOffset is generated from sql:
// select * from `user` where id > ? group by name having id > ? order by id limit ?, ?;
func (m *UserModel) FindGroupHavingOrderAscLimitOffset(ctx context.Context, where FindGroupHavingOrderAscLimitOffsetWhereParameter, having FindGroupHavingOrderAscLimitOffsetHavingParameter, limit FindGroupHavingOrderAscLimitOffsetLimitParameter) ([]*User, error) {
var result []*User
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`id > ?`, where.IdGT)
session.GroupBy(`name`)
session.Having(fmt.Sprintf(`id > '%v'`, having.IdGT))
session.OrderBy(`id`)
session.Limit(limit.Count, limit.Offset)
err := session.Find(&result)
return result, err
}
// FindGroupHavingOrderDescLimitOffset is generated from sql:
// select * from `user` where id > ? group by name having id > ? order by id desc limit ?, ?;
func (m *UserModel) FindGroupHavingOrderDescLimitOffset(ctx context.Context, where FindGroupHavingOrderDescLimitOffsetWhereParameter, having FindGroupHavingOrderDescLimitOffsetHavingParameter, limit FindGroupHavingOrderDescLimitOffsetLimitParameter) ([]*User, error) {
var result []*User
var session = m.engine.Context(ctx)
session.Select(`*`)
session.Where(`id > ?`, where.IdGT)
session.GroupBy(`name`)
session.Having(fmt.Sprintf(`id > '%v'`, having.IdGT))
session.OrderBy(`id desc`)
session.Limit(limit.Count, limit.Offset)
err := session.Find(&result)
return result, err
}
// FindOnePart is generated from sql:
// select `name`, `password`, `mobile` from `user` where id > ? limit 1;
func (m *UserModel) FindOnePart(ctx context.Context, where FindOnePartWhereParameter) (*User, error) {
var result = new(User)
var session = m.engine.Context(ctx)
session.Select(`name, password, mobile`)
session.Where(`id > ?`, where.IdGT)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindAllCount is generated from sql:
// select count(id) AS countID from `user`;
func (m *UserModel) FindAllCount(ctx context.Context) (*FindAllCountResult, error) {
var result = new(FindAllCountResult)
var session = m.engine.Context(ctx)
session.Select(`count(id) AS countID`)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindAllCountWhere is generated from sql:
// select count(id) AS countID from `user` where id > ?;
func (m *UserModel) FindAllCountWhere(ctx context.Context, where FindAllCountWhereWhereParameter) (*FindAllCountWhereResult, error) {
var result = new(FindAllCountWhereResult)
var session = m.engine.Context(ctx)
session.Select(`count(id) AS countID`)
session.Where(`id > ?`, where.IdGT)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindMaxID is generated from sql:
// select max(id) AS maxID from `user`;
func (m *UserModel) FindMaxID(ctx context.Context) (*FindMaxIDResult, error) {
var result = new(FindMaxIDResult)
var session = m.engine.Context(ctx)
session.Select(`max(id) AS maxID`)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindMinID is generated from sql:
// select min(id) AS minID from `user`;
func (m *UserModel) FindMinID(ctx context.Context) (*FindMinIDResult, error) {
var result = new(FindMinIDResult)
var session = m.engine.Context(ctx)
session.Select(`min(id) AS minID`)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// FindAvgID is generated from sql:
// select avg(id) AS avgID from `user`;
func (m *UserModel) FindAvgID(ctx context.Context) (*FindAvgIDResult, error) {
var result = new(FindAvgIDResult)
var session = m.engine.Context(ctx)
session.Select(`avg(id) AS avgID`)
session.Limit(1)
has, err := session.Get(result)
if !has {
return nil, sql.ErrNoRows
}
return result, err
}
// Update is generated from sql:
// update `user` set `name` = ?, `password` = ?, `mobile` = ?, `gender` = ?, `nickname` = ?, `type` = ?, `create_at` = ?, `update_at` = ? where `id` = ?;
func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWhereParameter) error {
var session = m.engine.Context(ctx)
session.Table(&User{})
session.Where(`id = ?`, where.IdEqual)
_, err := session.Update(map[string]interface{}{
"name": data.Name,
"password": data.Password,
"mobile": data.Mobile,
"gender": data.Gender,
"nickname": data.Nickname,
"type": data.Type,
"create_at": data.CreateAt,
"update_at": data.UpdateAt,
})
return err
}
// UpdateOrderByIdDesc is generated from sql:
// update `user` set `name` = ?, `password` = ?, `mobile` = ?, `gender` = ?, `nickname` = ?, `type` = ?, `create_at` = ?, `update_at` = ? where `id` = ? order by id desc;
func (m *UserModel) UpdateOrderByIdDesc(ctx context.Context, data *User, where UpdateOrderByIdDescWhereParameter) error {
var session = m.engine.Context(ctx)
session.Table(&User{})
session.Where(`id = ?`, where.IdEqual)
session.OrderBy(`id desc`)
_, err := session.Update(map[string]interface{}{
"name": data.Name,
"password": data.Password,
"mobile": data.Mobile,
"gender": data.Gender,
"nickname": data.Nickname,
"type": data.Type,
"create_at": data.CreateAt,
"update_at": data.UpdateAt,
})
return err
}
// UpdateOrderByIdDescLimitCount is generated from sql:
// update `user` set `name` = ?, `password` = ?, `mobile` = ?, `gender` = ?, `nickname` = ?, `type` = ?, `create_at` = ?, `update_at` = ? where `id` = ? order by id desc limit ?;
func (m *UserModel) UpdateOrderByIdDescLimitCount(ctx context.Context, data *User, where UpdateOrderByIdDescLimitCountWhereParameter, limit UpdateOrderByIdDescLimitCountLimitParameter) error {
var session = m.engine.Context(ctx)
session.Table(&User{})
session.Where(`id = ?`, where.IdEqual)
session.OrderBy(`id desc`)
session.Limit(limit.Count)
_, err := session.Update(map[string]interface{}{
"name": data.Name,
"password": data.Password,
"mobile": data.Mobile,
"gender": data.Gender,
"nickname": data.Nickname,
"type": data.Type,
"create_at": data.CreateAt,
"update_at": data.UpdateAt,
})
return err
}
// DeleteOne is generated from sql:
// delete from `user` where `id` = ?;
func (m *UserModel) DeleteOne(ctx context.Context, where DeleteOneWhereParameter) error {
var session = m.engine.Context(ctx)
session.Where(`id = ?`, where.IdEqual)
_, err := session.Delete(&User{})
return err
}
// DeleteOneByName is generated from sql:
// delete from `user` where `name` = ?;
func (m *UserModel) DeleteOneByName(ctx context.Context, where DeleteOneByNameWhereParameter) error {
var session = m.engine.Context(ctx)
session.Where(`name = ?`, where.NameEqual)
_, err := session.Delete(&User{})
return err
}
// DeleteOneOrderByIDAsc is generated from sql:
// delete from `user` where `name` = ? order by id;
func (m *UserModel) DeleteOneOrderByIDAsc(ctx context.Context, where DeleteOneOrderByIDAscWhereParameter) error {
var session = m.engine.Context(ctx)
session.Where(`name = ?`, where.NameEqual)
session.OrderBy(`id`)
_, err := session.Delete(&User{})
return err
}
// DeleteOneOrderByIDDesc is generated from sql:
// delete from `user` where `name` = ? order by id desc;
func (m *UserModel) DeleteOneOrderByIDDesc(ctx context.Context, where DeleteOneOrderByIDDescWhereParameter) error {
var session = m.engine.Context(ctx)
session.Where(`name = ?`, where.NameEqual)
session.OrderBy(`id desc`)
_, err := session.Delete(&User{})
return err
}
// DeleteOneOrderByIDDescLimitCount is generated from sql:
// delete from `user` where `name` = ? order by id desc limit ?;
func (m *UserModel) DeleteOneOrderByIDDescLimitCount(ctx context.Context, where DeleteOneOrderByIDDescLimitCountWhereParameter, limit DeleteOneOrderByIDDescLimitCountLimitParameter) error {
var session = m.engine.Context(ctx)
session.Where(`name = ?`, where.NameEqual)
session.OrderBy(`id desc`)
session.Limit(limit.Count)
_, err := session.Delete(&User{})
return err
}
```
除此外，还支持其他 orm
```
.
├── build.sh
├── bun
│ ├── user_model.gen.go
│ └── user_model.go
├── example.sql
├── go.mod
├── go.sum
├── gorm
│ ├── user_model.gen.go
│ └── user_model.go
├── sql
│ ├── scanner.go
│ ├── user_model.gen.go
│ └── user_model.go
├── sqlx
│ ├── user_model.gen.go
│ └── user_model.go
└── xorm
├── user_model.gen.go
└── user_model.go
```
可点击 [example](https://github.com/anqiansong/sqlgen/tree/main/example) 查看
分享到这里供大家一起学习，当然，对你有帮助也可以点个 star，不喜请轻喷！
有疑问加站长微信联系（非本文作者））
