Building a Custom Go ORM: SQL Builder and Reflection-based Scanner

In this article the author, a senior developer, discusses the need for a custom ORM in Go to improve development efficiency and quality, highlighting limitations of using database/sql directly.

He enumerates pain points such as hard‑coded SQL, repetitive scanning code, error‑prone resource handling, and the mental load of writing boilerplate.

To address these issues he proposes two core components: a chainable SQLBuilder that constructs SQL statements without hard‑coding, and a Scanner that maps query results to Go structs via reflection.

Example of a raw SQL query using database/sql and the corresponding User struct:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(100) NOT NULL COMMENT '名称',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `mtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

type User struct {
    Id    int64  `json:"id"`
    Name  string `json:"name"`
    Age   int64
    Ctime time.Time
    Mtime time.Time // 更新时间
}

He then shows a manual query function FindUsers that demonstrates the repetitive boilerplate.

func FindUsers(ctx context.Context) ([]*User, error) {
    rows, err := db.QueryContext(ctx, "SELECT `id`,`name`,`age`,`ctime`,`mtime` FROM user WHERE `age`

To eliminate such duplication he defines the SelectBuilder struct and its fluent API.

type SelectBuilder struct {
    builder    *strings.Builder
    column     []string
    tableName  string
    where      []func(s *SelectBuilder)
    args       []interface{}
    orderby    string
    offset     *int64
    limit      *int64
}
func (s *SelectBuilder) Select(field ...string) *SelectBuilder { s.column = append(s.column, field...); return s }
func (s *SelectBuilder) From(name string) *SelectBuilder { s.tableName = name; return s }
func (s *SelectBuilder) Where(f ...func(s *SelectBuilder)) *SelectBuilder { s.where = append(s.where, f...); return s }
func (s *SelectBuilder) OrderBy(field string) *SelectBuilder { s.orderby = field; return s }
func (s *SelectBuilder) Limit(offset, limit int64) *SelectBuilder { s.offset = &offset s.limit = &limit return s }
func GT(field string, arg interface{}) func(s *SelectBuilder) {
    return func(s *SelectBuilder) {
        s.builder.WriteString("`" + field + "` > ?")
        s.args = append(s.args, arg)
    }
}
func (s *SelectBuilder) Query() (string, []interface{}) {
    s.builder.WriteString("SELECT ")
    for k, v := range s.column {
        if k > 0 { s.builder.WriteString(",") }
        s.builder.WriteString("`" + v + "`")
    }
    s.builder.WriteString(" FROM `" + s.tableName + "` ")
    if len(s.where) > 0 {
        s.builder.WriteString("WHERE ")
        for k, f := range s.where {
            if k > 0 { s.builder.WriteString(" AND ") }
            f(s)
        }
    }
    if s.orderby != "" { s.builder.WriteString(" ORDER BY " + s.orderby) }
    if s.limit != nil { s.builder.WriteString(" LIMIT " + strconv.FormatInt(*s.limit, 10)) }
    if s.offset != nil { s.builder.WriteString(" OFFSET " + strconv.FormatInt(*s.offset, 10)) }
    return s.builder.String(), s.args
}

Usage example:

b := SelectBuilder{builder: &strings.Builder{}}
sql, args := b.
    Select("id", "name", "age", "ctime", "mtime").
    From("user").
    Where(GT("id", 0), GT("age", 0)).
    OrderBy("id").
    Limit(0, 20).
    Query()

The Scanner implementation uses reflection to map rows to a slice of structs, handling arbitrary column order via JSON tags.

func ScanSlice(rows *sql.Rows, dst interface{}) error {
    defer rows.Close()
    val := reflect.ValueOf(dst)
    if val.Kind() != reflect.Ptr { return errors.New("dst not a pointer") }
    val = reflect.Indirect(val)
    if val.Kind() != reflect.Slice { return errors.New("dst not a pointer to slice") }
    struPointer := val.Type().Elem()
    stru := struPointer.Elem()
    cols, err := rows.Columns()
    if err != nil { return err }
    if stru.NumField() < len(cols) { return errors.New("NumField and cols not match") }
    tagIdx := make(map[string]int)
    for i := 0; i < stru.NumField(); i++ {
        tagname := stru.Field(i).Tag.Get("json")
        if tagname != "" { tagIdx[tagname] = i }
    }
    // Build resultType and index slices, scan each row, set struct fields via reflection ...
    return rows.Err()
}

Combining the builder and scanner yields a concise query function:

func FindUserReflect() ([]*User, error) {
    b := SelectBuilder{builder: &strings.Builder{}}
    sql, args := b.Select("id","name","age","ctime","mtime").
        From("user").
        Where(GT("id",0), GT("age",0)).
        OrderBy("id").
        Limit(0,20).
        Query()
    rows, err := db.QueryContext(ctx, sql, args...)
    if err != nil { return nil, err }
    result := []*User{}
    err = ScanSlice(rows, &result)
    if err != nil { return nil, err }
    return result, nil
}

The article also shows how to generate Go structs and constants automatically from a table definition using a template and a Table model.

type Table struct {
    TableName   string
    GoTableName string
    PackageName string
    Fields      []*Column
}
type Column struct {
    ColumnName    string
    ColumnType    string
    ColumnComment string
}

Using the template, the user table yields the same User struct and a set of column constants, allowing calls like Select(Columns...) .

Finally, the author discusses when reflection can be avoided by comparing the selected columns with a predefined Columns slice and using direct rows.Scan for maximum performance.

In summary, the article demonstrates the motivation, design, and implementation of a lightweight, customizable ORM in Go, covering SQL building, reflection‑based scanning, code generation, and performance considerations.