GET_STARTED.md

Getting Started

A comprehensive guide to use sqlgen.

1. Install sqlgen CLI.

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

2. Define your model struct.

   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
   }

   But I prefer UUID primary key, how can I do that? To do that, you may refer to here.

3. Generate the output files.

   sqlgen generate ./model/user.go

4. This will generate the output files.

   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(')')
       }
   }
   
   ...

5. We can now utilise the generated codes.

   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)
       }
   }

   But these seem too easy to handle my use case, I need to handle more complex statements. Don't worry, we do provide QueryStmt to support more complex statements such as dynamic SQL statement.

   For example :

   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.OrderByClause{
               db.Desc(user.GetCreated()),
           },
           Limit: 50,
       })
       if err != nil {
           panic(err)
       }
       log.Println("Users ->", users)
   }