How to Turn go-zero’s DB Automation into Production‑Ready CRUD

Why Database Automation Is an Engineering Standard

In medium‑to‑large back‑end systems the data‑access layer faces two opposing pressures: rapid business iteration that forces schema changes, and production stability that demands flawless SQL, transactions, and caching under high concurrency. Manual CRUD code is costly, inconsistent, and hard to evolve, while go-zero’s automation standardizes the Model layer, embeds cache, primary‑key and unique‑index queries, and lets developers focus on business rules, transaction boundaries, performance, and system evolution.

Suitable Scenarios

The approach fits typical middle‑platform, transaction, order, or marketing services that use MySQL, have many micro‑services, large teams, and need both development speed and scalability. The article uses an e‑commerce order service as a running example with QPS 2,000 (10,000+ during spikes) and a table of tens of millions of rows.

What go-zero Automation Actually Generates

go-zero does not produce a full ORM. It uses the goctl tool to read DDL or connect to a database, parses table names, columns, primary keys, unique and ordinary indexes, maps MySQL types to Go types, and renders templates that create two files per table: xxxmodel_gen.go – auto‑generated, re‑generatable, contains basic CRUD, cache keys, and default implementations. xxxmodel.go – custom extension file that developers edit and is never overwritten.

This separation solves the classic "generated code vs. maintainable code" problem.

Type Mapping (MySQL → Go)

bigint

→ int64 /

uint64

int

→ int64 /

int32

tinyint

→ int64 /

int8

varchar

→

string

decimal

→ float64 (or string strategy) datetime → time.Time Production notes: avoid using float64 for monetary fields, prefer int64 representing cents; use sql.NullString, sql.NullTime or pointers for nullable columns; enforce a unified time zone and serialization format.

Generation Pipeline Overview

DDL / table schema
   │
   ▼
goctl model mysql ddl / datasource
   │
   ▼
Parse fields, indexes, type mapping
   │
   ▼
Template rendering → xxxmodel_gen.go, xxxmodel.go, vars.go
   │
   ▼
Business layer calls Model interface
   │
   ▼
sqlx + sqlc + cache + mysql

Cache Integration

When the cache‑enabled model is used, a primary‑key or unique‑index query first checks Redis; on a miss it reads the DB and back‑fills the cache; updates or deletes automatically invalidate related keys. This unifies cache‑consistency handling across the code base.

Recommended Layered Architecture

API / RPC Handler

Application / Service – validation, business state machine, idempotency, transaction orchestration, logging, error mapping.

Repository / Domain Access – wraps go-zero Model, adds complex queries, shields storage details, aggregates cache/DB/message‑table access.

Model (goctl generated)

MySQL / Redis

Project Structure Example

order-service/
├── cmd/api
├── etc
├── internal/
│   ├── config
│   ├── handler
│   ├── logic
│   ├── svc
│   ├── model
│   ├── repository
│   └── types
├── sql/order.sql
└── scripts

Order Table Design (Key Points)

Use order_no as a business‑unique identifier.

Store amount in cents to avoid floating‑point errors.

Add version for optimistic locking.

Indexes on high‑frequency query paths (e.g., (user_id, status, created_at)).

Command to Generate Model

goctl model mysql ddl \
  -src ./sql/order.sql \
  -dir ./internal/model \
  -c

The -c flag creates a cache‑enabled model. The same can be done directly from a live datasource.

Typical Generated Interface

type Orders struct {
    Id        uint64    `db:"id"`
    OrderNo   string    `db:"order_no"`
    UserId    uint64    `db:"user_id"`
    ProductId uint64    `db:"product_id"`
    Quantity  uint64    `db:"quantity"`
    AmountCent uint64   `db:"amount_cent"`
    Status    uint8    `db:"status"`
    Version   uint64    `db:"version"`
    PaidAt    *time.Time `db:"paid_at"`
    CreatedAt time.Time   `db:"created_at"`
    UpdatedAt time.Time   `db:"updated_at"`
}

type OrdersModel interface {
    Insert(*Orders) (sql.Result, error)
    FindOne(id uint64) (*Orders, error)
    FindOneByOrderNo(orderNo string) (*Orders, error)
    Update(*Orders) error
    Delete(id uint64) error
    // custom methods go here
}

The generated implementation includes cache look‑up logic, unified error handling, and a clean skeleton for custom extensions.

Production‑Grade Enhancements

Custom Queries – place them in ordersmodel.go (not the _gen.go file). Example: paginated user‑order list with keyset pagination.

High‑Concurrency Controls – configure DB connection pool, use keyset pagination instead of OFFSET, implement batch inserts.

Optimistic Locking – add a version column and an UpdateStatusWithVersion method that checks the version before updating.

Transaction Management – wrap multiple writes (order, inventory, outbox) in a local transaction; for distributed scenarios use the Outbox pattern instead of heavyweight distributed‑transaction frameworks.

Observability – add tracing, slow‑query alerts, structured logs, and metrics in the Service layer.

Example Transaction (Local)

func (l *CreateOrderLogic) CreateWithTx(ctx context.Context, req *CreateOrderReq) error {
    return l.svcCtx.DB.TransactCtx(ctx, func(ctx context.Context, session sqlx.Session) error {
        // build order, insert, decrease stock, write outbox
        // any error aborts the whole transaction
        return nil
    })
}

Outbox Pattern Sketch

INSERT INTO orders ...;
INSERT INTO order_outbox ...; // event row
-- transaction commits, then an async worker reads outbox and publishes to MQ

High‑Concurrency Optimizations

Connection Pool – set MaxOpenConns, MaxIdleConns, and reasonable lifetimes.

Keyset Pagination – avoid large OFFSET scans.

Batch Insert – build a single INSERT with multiple value tuples.

Cache Strategy – cache hot primary/unique look‑ups, avoid caching full list queries, set proper TTL, delete on update.

Testing Strategy

Unit Tests – validate Service‑layer parameter checks, state‑machine logic, idempotency, optimistic‑lock handling.

Integration Tests – real MySQL/Redis interactions, cache hit/miss, transaction commit/rollback.

Load Tests – measure TP99 latency, DB connections, slow‑SQL ratio, Redis hit rate, error rate.

Common Pitfalls & Mitigations

Treat generated code as a foundation, never edit _gen.go directly.

Never store monetary values as float64; use integer cents.

Avoid caching whole list queries; focus on point look‑ups.

Do not perform RPC or MQ calls inside a DB transaction; use Outbox.

Always design indexes based on query patterns before adding cache.

Production Checklist

Generate standard Models for all core tables with goctl.

Separate _gen.go and custom files; keep custom logic in the latter.

Enable cache version for primary/unique queries.

Implement complex list/aggregation queries as custom methods.

Store money as integer cents.

Add optimistic‑lock fields and methods for high‑contention updates.

Use local transactions or Outbox for multi‑table writes.

Service layer handles validation, idempotency, logging, and error mapping.

Repository layer isolates business from storage, ready for future sharding.

Run real‑SQL EXPLAIN and performance tests before each release.

Conclusion

go-zero’s database automation delivers production‑grade benefits not by eliminating code but by standardizing the data‑access foundation, unifying caching, and providing clear extension points. Teams gain faster delivery, consistent quality, better performance tuning, and a solid base for future scaling such as sharding, read/write splitting, and distributed transactions.