MySQL

MySQL

go-zero uses goctl to generate type-safe, zero-reflection data access code from a DDL schema. The generated models handle connection pooling, caching, metrics, and tracing transparently.

1. Define Schema

user.sql

CREATE TABLE `user` (
  `id`         bigint NOT NULL AUTO_INCREMENT,
  `username`   varchar(255) NOT NULL DEFAULT '',
  `password`   varchar(255) NOT NULL DEFAULT '',
  `mobile`     varchar(20)  NOT NULL DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`),
  UNIQUE KEY `idx_mobile`   (`mobile`)
) ENGINE=InnoDB;

2. Generate Model

# Without cache
goctl model mysql ddl -src user.sql -dir ./internal/model

# With Redis cache (recommended for production)
goctl model mysql ddl -src user.sql -dir ./internal/model -cache

This generates:

internal/model/
├── usermodel.go        # interface + implementation
├── usermodel_gen.go    # CRUD methods (regenerated by goctl)
└── vars.go             # ErrNotFound sentinel

3. Configuration

etc/app.yaml

DataSource: "root:password@tcp(127.0.0.1:3306)/dbname?parseTime=true&loc=UTC"
CacheRedis:
  - Host: 127.0.0.1:6379
    Type: node
    Pass: ""

Connection pool

go-zero’s sqlx reuses Go’s database/sql connection pool. Tune it via the standard *sql.DB methods after obtaining the underlying connection:

conn := sqlx.NewMysql(c.DataSource)
conn.DB().SetMaxOpenConns(100)
conn.DB().SetMaxIdleConns(10)
conn.DB().SetConnMaxLifetime(time.Hour)

4. Service Context Wiring

internal/svc/servicecontext.go

func NewServiceContext(c config.Config) *ServiceContext {
    conn := sqlx.NewMysql(c.DataSource)
    return &ServiceContext{
        Config:    c,
        UserModel: model.NewUserModel(conn, c.CacheRedis),
    }
}

5. CRUD Examples

Insert

result, err := l.svcCtx.UserModel.Insert(l.ctx, &model.User{
    Username: req.Username,
    Password: hashPassword(req.Password),
    Mobile:   req.Mobile,
})
if err != nil {
    return nil, err
}
userId, _ := result.LastInsertId()

FindOne

// By primary key (uses cache if generated with -cache)
user, err := l.svcCtx.UserModel.FindOne(l.ctx, userId)
if errors.Is(err, model.ErrNotFound) {
    return nil, errorx.NewCodeError(404, "user not found")
}

FindOne by Unique Index

// goctl generates FindOneBy<FieldName> for every UNIQUE KEY
user, err := l.svcCtx.UserModel.FindOneByUsername(l.ctx, req.Username)

Update

err = l.svcCtx.UserModel.Update(l.ctx, &model.User{
    Id:       userId,
    Username: req.Username,
    Password: newHash,
    Mobile:   user.Mobile,
})

Delete

err = l.svcCtx.UserModel.Delete(l.ctx, userId)

6. Transactions

Wrap multiple operations in a single DB transaction using TransactCtx:

err = l.svcCtx.UserModel.TransactCtx(l.ctx, func(ctx context.Context, session sqlx.Session) error {
    // Insert user
    result, err := insertUser(ctx, session, user)
    if err != nil {
        return err   // auto-rollback
    }
    userId, _ := result.LastInsertId()

    // Insert associated profile
    if err := insertProfile(ctx, session, userId, profile); err != nil {
        return err   // auto-rollback
    }

    return nil       // commit
})

7. Custom Queries

Add custom methods to usermodel.go (not usermodel_gen.go, which is overwritten by goctl):

internal/model/usermodel.go

type UserModel interface {
    userModelInterface                    // generated interface
    FindByMobileAndStatus(ctx context.Context, mobile string, status int64) (*User, error)
    CountActiveUsers(ctx context.Context) (int64, error)
}

func (m *defaultUserModel) FindByMobileAndStatus(ctx context.Context, mobile string, status int64) (*User, error) {
    var user User
    query := fmt.Sprintf("SELECT %s FROM %s WHERE `mobile` = ? AND `status` = ? LIMIT 1",
        userRows, m.table)
    err := m.conn.QueryRowCtx(ctx, &user, query, mobile, status)
    switch {
    case err == nil:
        return &user, nil
    case errors.Is(err, sqlx.ErrNotFound):
        return nil, ErrNotFound
    default:
        return nil, err
    }
}

func (m *defaultUserModel) CountActiveUsers(ctx context.Context) (int64, error) {
    var count int64
    query := fmt.Sprintf("SELECT COUNT(*) FROM %s WHERE `status` = 1", m.table)
    err := m.conn.QueryRowCtx(ctx, &count, query)
    return count, err
}

8. Bulk Insert

goctl doesn’t generate bulk insert. Use sqlx.BulkInserter for efficient batch writes:

inserter, err := sqlx.NewBulkInserter(conn,
    fmt.Sprintf("INSERT INTO %s (%s) VALUES (?, ?, ?)", tableName, userRowsExpectAutoSet))
if err != nil {
    return err
}
defer inserter.Flush()

for _, user := range users {
    if err := inserter.Insert(user.Username, user.Password, user.Mobile); err != nil {
        return err
    }
}