Design and Implementation of a Simple Go ORM for MySQL

This article explains why an ORM is useful, describes the drawbacks of existing Go ORMs (GORM, XORM), and introduces a lightweight, elegant custom ORM written in Go.

Native MySQL connection in Go

The basic steps are importing the driver, opening a connection, and performing CRUD operations with sql.Open, db.Exec, db.QueryRow, etc. Example code:

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

db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/ApiDB?charset=utf8")
if err != nil { panic(err) }

// Insert example
result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)", "lisi", "dev", "2020-08-04")

ORM engine design (SmallormEngine)

The engine stores the DB handle, table name, SQL fragments, and parameter slices. Core fields include Db, TableName, Prepare, AllExec, WhereParam, WhereExec, etc.

type SmallormEngine struct {
    Db           *sql.DB
    TableName    string
    Prepare      string
    AllExec      []interface{}
    Sql          string
    WhereParam   string
    LimitParam   string
    OrderParam   string
    OrWhereParam string
    WhereExec    []interface{}
    UpdateParam  string
    UpdateExec   []interface{}
    FieldParam   string
    TransStatus  int
    Tx           *sql.Tx
    GroupParam   string
    HavingParam  string
}

Connection helper

A function NewMysql creates a new SmallormEngine instance with a MySQL DSN.

func NewMysql(username, password, address, dbname string) (*SmallormEngine, error) {
    dsn := username + ":" + password + "@tcp(" + address + ")/" + dbname + "?charset=utf8&timeout=5s&readTimeout=6s"
    db, err := sql.Open("mysql", dsn)
    if err != nil { return nil, err }
    return &SmallormEngine{Db: db, FieldParam: "*"}, nil
}

Fluent API

The ORM uses chainable methods: Table, Where, OrWhere, Limit, Order, Select, Insert, Update, Delete. Each method builds the corresponding SQL fragment and stores parameters for later execution.

Insert

Insert can accept a single struct or a slice of structs. Reflection extracts field names (using the sql tag) and values, builds a prepared statement with placeholders, and executes it.

type User struct {
    Username   string `sql:"username"`
    Departname string `sql:"departname"`
    Status     int64  `sql:"status"`
}

user := User{Username: "EE", Departname: "22", Status: 1}
id, err := e.Table("userinfo").Insert(user)

BatchInsert

For a slice of structs, the engine iterates over each element, collects field names once, creates a placeholder group for each row, and concatenates them into a single INSERT ... VALUES (...), (...) statement.

Where and OrWhere

Both methods accept either a struct (all fields combined with AND) or explicit parameters ( field, operator, value). They generate the WHERE clause and store the values in WhereExec. Multiple calls are combined with AND for Where and OR for OrWhere.

// Example
e.Table("userinfo").Where("uid", ">=", 123).OrWhere("uid", "<=", 454).Delete()

Delete

Builds DELETE FROM table WHERE ... using the previously collected WhereParam / OrWhereParam and executes the statement, returning the affected rows.

Update

Supports updating with a struct or a field, value pair. The generated SQL is UPDATE table SET ... WHERE .... Parameters from both the SET part and the WHERE part are merged before execution. e.Table("userinfo").Where("uid", 123).Update("status", 1) Other features

Chainable Limit, Order, Group, Having methods (not fully shown).

Error handling includes file and line information.

The article concludes with a brief author bio and links to related technical articles.