入门指南

使用 sqlgen 的全面指南。

安装 sqlgen 命令行工具。

 go install github.com/si3nloong/sqlgen/cmd/sqlgen@main

定义您的模型结构。

`model/user.go`

package model

import (
    "time"

    "cloud.google.com/go/civil"
)

type Gender int

const (
    Female Gender = iota
    Male
)

type User struct {
    ID        int64 `sql:",auto_increment"`
    Name      string
    BirthDate civil.Date
    Gender    Gender
    Address   string
    Created   time.Time
}

但我想使用 UUID 作为主键，我该怎么做？要实现这一点，您可以参考这里。

输入目标文件或文件夹。
```
sqlgen generate ./model/user.go
```

这将生成以下的代码。

`model/generated.go`

// Code generated by sqlgen. DO NOT EDIT.

package model

import (
    "database/sql/driver"
    "time"

    "cloud.google.com/go/civil"
    "github.com/si3nloong/sqlgen/sequel"
    "github.com/si3nloong/sqlgen/sequel/types"
)

func (v User) CreateTableStmt() string {
    return "CREATE TABLE IF NOT EXISTS " + v.TableName() + " (`id` BIGINT NOT NULL AUTO_INCREMENT,`name` VARCHAR(255) NOT NULL,`birth_date` DATE NOT NULL,`gender` INTEGER NOT NULL,`address` VARCHAR(255) NOT NULL,`created` DATETIME NOT NULL,PRIMARY KEY (`id`));"
}
func (User) AlterTableStmt() string {
    return "ALTER TABLE `user` MODIFY `id` BIGINT NOT NULL AUTO_INCREMENT,MODIFY `name` VARCHAR(255) NOT NULL AFTER `id`,MODIFY `birth_date` DATE NOT NULL AFTER `name`,MODIFY `gender` INTEGER NOT NULL AFTER `birth_date`,MODIFY `address` VARCHAR(255) NOT NULL AFTER `gender`,MODIFY `created` DATETIME NOT NULL AFTER `address`;"
}
func (User) TableName() string {
    return "`user`"
}
func (User) InsertVarQuery() string {
    return "(?,?,?,?,?,?)"
}
func (User) Columns() []string {
    return []string{"`id`", "`name`", "`birth_date`", "`gender`", "`address`", "`created`"}
}
func (v User) IsAutoIncr() {}
func (v User) PK() (columnName string, pos int, value driver.Value) {
    return "`id`", 0, int64(v.ID)
}
func (v User) Values() []any {
    return []any{int64(v.ID), string(v.Name), types.TextMarshaler(v.BirthDate), int64(v.Gender), string(v.Address), time.Time(v.Created)}
}
func (v *User) Addrs() []any {
    return []any{types.Integer(&v.ID), types.String(&v.Name), types.Date(&v.BirthDate), types.Integer(&v.Gender), types.String(&v.Address), (*time.Time)(&v.Created)}
}
func (v User) GetID() sequel.ColumnValuer[int64] {
    return sequel.Column[int64]("`id`", v.ID, func(vi int64) driver.Value { return int64(vi) })
}
func (v User) GetName() sequel.ColumnValuer[string] {
    return sequel.Column[string]("`name`", v.Name, func(vi string) driver.Value { return string(vi) })
}
func (v User) GetBirthDate() sequel.ColumnValuer[civil.Date] {
    return sequel.Column[civil.Date]("`birth_date`", v.BirthDate, func(vi civil.Date) driver.Value { return types.TextMarshaler(vi) })
}
func (v User) GetGender() sequel.ColumnValuer[Gender] {
    return sequel.Column[Gender]("`gender`", v.Gender, func(vi Gender) driver.Value { return int64(vi) })
}
func (v User) GetAddress() sequel.ColumnValuer[string] {
    return sequel.Column[string]("`address`", v.Address, func(vi string) driver.Value { return string(vi) })
}
func (v User) GetCreated() sequel.ColumnValuer[time.Time] {
    return sequel.Column[time.Time]("`created`", v.Created, func(vi time.Time) driver.Value { return time.Time(vi) })
}

`db/db.go`

// Code generated by sqlgen. DO NOT EDIT.

 package db

 import (
     "context"
     "database/sql"
     "strconv"
     "strings"

     "github.com/si3nloong/sqlgen/sequel"
     "github.com/si3nloong/sqlgen/sequel/strpool"
 )

 func InsertOne[T sequel.TableColumnValuer[T], Ptr interface {
     sequel.TableColumnValuer[T]
     sequel.Scanner[T]
 }](ctx context.Context, db sequel.DB, v Ptr) (sql.Result, error) {
     args := v.Values()
     switch vi := any(v).(type) {
     case sequel.SingleInserter:
         switch vk := vi.(type) {
         case sequel.AutoIncrKeyer:
             _, idx, _ := vk.PK()
             args = append(args[:idx], args[idx+1:]...)
         }
         return db.ExecContext(ctx, vi.InsertOneStmt(), args...)
     }

     columns := v.Columns()
     switch vi := any(v).(type) {
     case sequel.AutoIncrKeyer:
         // If it's a auto increment primary key
         // We don't need to pass the value
         _, idx, _ := vi.PK()
         columns = append(columns[:idx], columns[idx+1:]...)
         args = append(args[:idx], args[idx+1:]...)
     }
     stmt := strpool.AcquireString()
     defer strpool.ReleaseString(stmt)
     stmt.WriteString("INSERT INTO " + v.TableName() + " (" + strings.Join(columns, ",") + ") VALUES ")
     stmt.WriteByte('(')
     for i := range args {
         if i > 0 {
             stmt.WriteByte(',')
         }
         stmt.WriteString("?")
     }
     stmt.WriteString(");")
     return db.ExecContext(ctx, stmt.String(), args...)
 }

 ...

`db/operator.go`

// Code generated by sqlgen. DO NOT EDIT.

 package db

 import (
     "github.com/si3nloong/sqlgen/sequel"
 )

 func And(stmts ...sequel.WhereClause) sequel.WhereClause {
     return func(stmt sequel.StmtBuilder) {
         stmt.WriteByte('(')
         for i := range stmts {
             if i > 0 {
                 stmt.WriteString(" AND ")
             }
             stmts[i](stmt)
         }
         stmt.WriteByte(')')
     }
 }

 ...

现在我们可以利用生成的代码。

`main.go`

package main

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

     "db"
     "model"

     _ "github.com/go-sql-driver/mysql"
 )

 func main() {
     ctx, cancel := context.WithCancel(context.Background())
     defer cancel()
     dbConn, err := sql.Open("mysql", "root:abcd1234@/sqlbench?parseTime=true")
     if err != nil {
         panic(err)
     }
     defer dbConn.Close()

     // table migration
     if err := db.Migrate[model.User](ctx, dbConn); err != nil {
         panic(err)
     }

     user := model.User{}
     user.Name = "Micheal"
     user.Age = 18
     user.Created = time.Now()
     // insert single record
     result, err := db.InsertOne(ctx, dbConn, &user)
     if err != nil {
         panic(err)
     }

     inputs := []model.User{
         {Name: "John", Created: time.Now()},
         {Name: "Michael", Created: time.Now()},
         {Name: "Jerry", Created: time.Now()},
     }
     // insert multiple record
     if _, err := db.InsertInto(ctx, dbConn, inputs); err != nil {
         panic(err)
     }

     newUser := model.User{}
     newUser.ID, _ = result.LastInsertId()
     // find record using primary key
     if err := db.FindByPK(ctx, dbConn, &newUser); err != nil {
         panic(err)
     }
     log.Println(newUser)

     newUser.Age = 27
     // update record using primary key
     if _, err := db.UpdateByPK(ctx, dbConn, newUser); err != nil {
         panic(err)
     }
     log.Println(newUser)

     // remove record using primary key
     if _, err := db.DeleteByPK(ctx, dbConn, newUser); err != nil {
         panic(err)
     }
 }

但这些用例太简单了，我需要处理更复杂的语句。别担心，我们提供了 QueryStmt 来支持更复杂的语句，比如动态 SQL 语句。

例如：

`main.go`

package main

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

    "cloud.google.com/go/civil"
    _ "github.com/go-sql-driver/mysql"
    "db"
    "model"
)

func main() {
    ctx, cancel := context.WithCancel(context.Background())
    defer cancel()
    dbConn, err := sql.Open("mysql", "root:abcd1234@/sqlbench?parseTime=true")
    if err != nil {
        panic(err)
    }
    defer dbConn.Close()

    if err := db.Migrate[model.User](ctx, dbConn); err != nil {
        panic(err)
    }

    birthDate, _ := civil.ParseDate("1995-01-28")

    if _, err := db.InsertInto(ctx, dbConn, []model.User{
        {Name: "John Doe", Gender: model.Male, BirthDate: birthDate, Created: time.Now()},
        {Name: "YY", Gender: model.Female, BirthDate: birthDate, Created: time.Now()},
        {Name: "Yoman", Gender: model.Male, BirthDate: birthDate, Created: time.Now()},
    }); err != nil {
        panic(err)
    }

     /*
     SELECT `id`, `name`, `birth_date`, `gender`, `address`, `created`
     FROM `user` WHERE `gender` = 0 AND `birth_date` >= "1995-01-28"
     ORDER BY `created` DESC LIMIT 50;
     */
    users, err := db.QueryStmt[model.User](ctx, dbConn, db.SelectStmt{
        Select:    user.Columns(),
        FromTable: user.TableName(),
        Where: db.And(
            db.Equal(user.GetGender(), model.Female),
            db.GreaterThanOrEqual(user.GetBirthDate(), birthDate),
        ),
        OrderBy: []sequel.ColumnOrder{
            db.Desc(user.GetCreated()),
        },
        Limit: 50,
    })
    if err != nil {
        panic(err)
    }
    log.Println("Users ->", users)
}

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

GET_STARTED-zh.md

GET_STARTED-zh.md

入门指南

`model/user.go`

`model/generated.go`

`db/db.go`

`db/operator.go`

`main.go`

`main.go`

Files

GET_STARTED-zh.md

Latest commit

History

GET_STARTED-zh.md

File metadata and controls

入门指南

model/user.go

model/generated.go

db/db.go

db/operator.go

main.go

main.go

`model/user.go`

`model/generated.go`

`db/db.go`

`db/operator.go`

`main.go`

`main.go`