前言

写多了业务，数据库操作的 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

github.com/anqiansong/sqlgen

安装

go install github.com/anqiansong/sqlgen@latest

使用示例

先有 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;

-- fn: FindOne
select * from user where id = ? limit 1;

-- fn: UpdateOne
update user set name = ? where id = ?;

-- fn: DeleteOne
delete from user where id = ? limit 1;

-- fn: FindLimit
select * from user where id > ? limit ?;

-- fn: Count
select count(id) AS count from user where id > ?;

这里以生成 gorm 为例子来看一下其输出

// Code generated by sqlgen. DO NOT EDIT!

package model

import (
    "context"
    "database/sql"
    "fmt"
    "time"

    "gorm.io/gorm"
)

// UserModel represents a user model.
type UserModel struct {
    db *gorm.DB
}

// User represents a user struct data.
type User struct {
    Id       uint64    `gorm:"column:id;primaryKey;autoIncrement" json:"id"`
    Name     string    `gorm:"column:name" json:"name"`         // The username
    Password string    `gorm:"column:password" json:"password"` // The  user password
    Mobile   string    `gorm:"column:mobile" json:"mobile"`     // The mobile phone number
    Gender   string    `gorm:"column:gender" json:"gender"`     // gender,male|female|unknown
    Nickname string    `gorm:"column:nickname" json:"nickname"` // The nickname
    Type     int8      `gorm:"column:type" json:"type"`         // The user type, 0:normal,1:vip, for test golang keyword
    CreateAt time.Time `gorm:"column:create_at" json:"createAt"`
    UpdateAt time.Time `gorm:"column:update_at" json:"updateAt"`
}

// FindOneWhereParameter is a where parameter structure.
type FindOneWhereParameter struct {
    IdEqual uint64
}

// FindLimitWhereParameter is a where parameter structure.
type FindLimitWhereParameter struct {
    IdGT uint64
}

// FindLimitLimitParameter is a limit parameter structure.
type FindLimitLimitParameter struct {
    Count int
}

// CountWhereParameter is a where parameter structure.
type CountWhereParameter struct {
    IdGT uint64
}

// CountResult is a count result.
type CountResult struct {
    Count sql.NullInt64 `gorm:"column:count" json:"count"`
}

// TableName returns the table name. it implemented by gorm.Tabler.
func (CountResult) TableName() string {
    return "user"
}

// UpdateOneWhereParameter is a where parameter structure.
type UpdateOneWhereParameter struct {
    IdEqual uint64
}

// DeleteOneWhereParameter is a where parameter structure.
type DeleteOneWhereParameter struct {
    IdEqual uint64
}

// TableName returns the table name. it implemented by gorm.Tabler.
func (User) TableName() string {
    return "user"
}

// NewUserModel returns a new user model.
func NewUserModel(db *gorm.DB) *UserModel {
    return &UserModel{db: db}
}

// Create creates  user data.
func (m *UserModel) Create(ctx context.Context, data ...*User) error {
    if len(data) == 0 {
        return fmt.Errorf("data is empty")
    }

    db := m.db.WithContext(ctx)
    list := data[:]
    return db.Create(&list).Error
}

// 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 db = m.db.WithContext(ctx)
    db = db.Select(`*`)
    db = db.Where(`id = ?`, where.IdEqual)
    db = db.Limit(1)
    db = db.Take(result)
    return result, db.Error
}

// 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 db = m.db.WithContext(ctx)
    db = db.Select(`*`)
    db = db.Where(`id > ?`, where.IdGT)
    db = db.Limit(limit.Count)
    db = db.Find(&result)
    return result, db.Error
}

// Count is generated from sql:
// select count(id) AS count from user where id > ?;
func (m *UserModel) Count(ctx context.Context, where CountWhereParameter) (*CountResult, error) {
    var result = new(CountResult)
    var db = m.db.WithContext(ctx)
    db = db.Select(`count(id) AS count`)
    db = db.Where(`id > ?`, where.IdGT)
    db = db.Limit(1)
    db = db.Take(result)
    return result, db.Error
}

// UpdateOne is generated from sql:
// update user set name = ? where id = ?;
func (m *UserModel) UpdateOne(ctx context.Context, data *User, where UpdateOneWhereParameter) error {
    var db = m.db.WithContext(ctx)
    db = db.Model(&User{})
    db = db.Where(`id = ?`, where.IdEqual)
    db = db.Updates(map[string]interface{}{
        "name": data.Name,
    })
    return db.Error
}

// DeleteOne is generated from sql:
// delete from user where id = ? limit 1;
func (m *UserModel) DeleteOne(ctx context.Context, where DeleteOneWhereParameter) error {
    var db = m.db.WithContext(ctx)
    db = db.Where(`id = ?`, where.IdEqual)
    db = db.Limit(1)
    db = db.Delete(&User{})
    return db.Error
}

除此外，还支持其他 orm

.
├── bun
│   ├── mock.go
│   └── user_model.gen_test.go
├── gorm
│   ├── mock.go
│   └── user_model.gen_test.go
├── readme.md
├── sql
│   ├── mock.go
│   ├── scanner.go
│   └── user_model.gen_test.go
├── sqlx
│   ├── mock.go
│   └── user_model.gen_test.go
└── xorm
    ├── mock.go
    └── user_model.gen_test.go

可点击 example 查看

分享到这里供大家一起学习，当然，对你有帮助也可以点个 star，不喜请轻喷！

本作品采用《CC 协议》，转载必须注明作者和本文链接