Use this skill when working with the cool-mysql library for Go. This skill provides comprehensive guidance on using cool-mysql's MySQL helper functions, including dual connection pools, named parameters, template syntax, caching strategies, and advanced query patterns. Apply when writing database code, optimizing queries, setting up caching, or migrating from database/sql.
cool-mysql is a MySQL helper library for Go that wraps database/sql with MySQL-specific conveniences while keeping the underlying interfaces intact. The library reduces boilerplate code for common database operations while providing advanced features like caching, automatic retries, and dual read/write connection pools.
Core Philosophy:
database/sql interfaces intactUse this skill when:
database/sql to cool-mysqlcool-mysql maintains separate connection pools for reads and writes to optimize for read-heavy workloads.
Default Behavior:
Select(), SelectJSON(), Count(), Exists() → Read poolInsert(), Upsert(), Exec() → Write poolSelectWrites(), ExistsWrites() → Write pool (for read-after-write consistency)When to use SelectWrites(): Use immediately after writing data when you need consistency:
db.Insert("users", user)
// Need immediate consistency - use write pool
db.SelectWrites(&user, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `id` = @@id", 0, user.ID)
cool-mysql uses @@paramName syntax instead of positional ? placeholders.
Key Points:
mysql.Params{"key": value} for explicit parametersmysql.Raw() to inject literal SQL (not escaped)Example:
// Named parameters
db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `age` > @@minAge AND `status` = @@status", 0,
mysql.Params{"minAge": 18, "status": "active"})
// Struct as parameters
user := User{ID: 1, Name: "Alice"}
db.Exec("UPDATE `users` SET `name` = @@Name WHERE `id` = @@ID", user)
// Raw SQL injection
db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE @@condition", 0,
mysql.Raw("created_at > NOW() - INTERVAL 1 DAY"))
cool-mysql supports Go template syntax for conditional query logic.
Important Distinctions:
.Name), not column names from tags.ParamNameCRITICAL: Marshaling Template Values
When injecting VALUES (not identifiers) via templates, you MUST use the marshal pipe:
// ✅ CORRECT - Use @@param for values (automatically marshaled)
query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE {{ if .MinAge }}`age` > @@minAge{{ end }}"
// ✅ CORRECT - Use | marshal when injecting value directly in template
query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `name` = {{ .Name | marshal }}"
// ❌ WRONG - Direct injection without marshal causes syntax errors
query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `name` = {{ .Name }}" // BROKEN!
// ✅ CORRECT - Identifiers (column names) validated, then injected
if !allowedColumns[sortBy] { return errors.New("invalid column") }
query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` ORDER BY {{ .SortBy }}" // OK - validated identifier
Best Practice: Use @@param syntax for values. Only use template injection with | marshal when you need conditional value logic.
Example:
db.Select(&users,
"SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE 1=1"+
" {{ if .MinAge }}AND `age` > @@minAge{{ end }}"+
" {{ if .Status }}AND `status` = @@status{{ end }}",
0,
mysql.Params{"minAge": 18, "status": "active"})
cool-mysql provides pluggable caching with support for Redis, Memcached, or in-memory storage.
Cache TTL:
0 = No caching (always query database)> 0 = Cache for specified duration (e.g., 5*time.Minute)Cache Setup:
// Redis (with distributed locking)
db.EnableRedis(redisClient)
// Memcached
db.EnableMemcache(memcacheClient)
// In-memory (weak pointers, GC-managed)
db.UseCache(mysql.NewWeakCache())
// Layered caching (fast local + shared distributed)
db.UseCache(mysql.NewMultiCache(
mysql.NewWeakCache(), // L1: Fast local cache
mysql.NewRedisCache(redis), // L2: Shared distributed cache
))
Only SELECT operations are cached - writes always hit the database.
Control column mapping and behavior with mysql struct tags.
Tag Options:
mysql:"column_name" - Map to database columnmysql:"column_name,defaultzero" - Write DEFAULT(column_name) for zero valuesmysql:"column_name,omitempty" - Same as defaultzeromysql:"column_name,insertDefault" - Same as defaultzeromysql:"-" - Completely ignore this fieldmysql:"column0x2cname" - Hex encoding for special characters (becomes column,name)Example:
type User struct {
ID int `mysql:"id"`
Name string `mysql:"name"`
Email string `mysql:"email"`
CreatedAt time.Time `mysql:"created_at,defaultzero"` // Use DB default on zero value
UpdatedAt time.Time `mysql:"updated_at,defaultzero"`
Password string `mysql:"-"` // Never include in queries
}
From connection parameters:
db, err := mysql.New(
wUser, wPass, wSchema, wHost, wPort, // Write connection
rUser, rPass, rSchema, rHost, rPort, // Read connection
collation, // e.g., "utf8mb4_unicode_ci"
timeZone, // e.g., "America/New_York"
)
From DSN strings:
db, err := mysql.NewFromDSN(writesDSN, readsDSN)
From existing connections:
db, err := mysql.NewFromConn(writesConn, readsConn)
Select into struct slice:
var users []User
err := db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `age` > @@minAge", 0, 18)
Select single value:
var name string
err := db.Select(&name, "SELECT `name` FROM `users` WHERE `id` = @@id", 0, 1)
// Returns sql.ErrNoRows if not found
Count records:
count, err := db.Count("SELECT COUNT(*) FROM `users` WHERE `active` = @@active", 0, 1)
Check existence:
exists, err := db.Exists("SELECT 1 FROM `users` WHERE `email` = @@email", 0, "[email protected]")
Insert data:
// Single insert
user := User{Name: "Alice", Email: "[email protected]"}
err := db.Insert("users", user)
// Batch insert (automatically chunked)
users := []User{{Name: "Bob"}, {Name: "Charlie"}}
err := db.Insert("users", users)
Upsert (INSERT ... ON DUPLICATE KEY UPDATE):
err := db.Upsert(
"users", // table
[]string{"email"}, // unique columns
[]string{"name", "updated_at"}, // columns to update on conflict
"", // optional WHERE clause
user, // data
)
Execute query:
err := db.Exec("UPDATE `users` SET `active` = 1 WHERE `id` = @@id", 1)
| database/sql | cool-mysql | Notes |
|---|---|---|
? placeholders | @@paramName | Named parameters are case-insensitive |
db.Query() + rows.Scan() | db.Select(&result, query, cacheTTL, params) | Automatic scanning into structs |
| Manual connection pools | Dual pools (read/write) | Automatic routing based on operation |
| No caching | Built-in caching | Pass TTL as second parameter |
sql.ErrNoRows always | sql.ErrNoRows for single values only | Slices return empty, not error |
| Manual chunking | Automatic chunking | Insert operations respect max_allowed_packet |
| No retry logic | Automatic retries | Handles deadlocks, timeouts, connection losses |
Before (database/sql):
rows, err := db.Query("SELECT `id`, `name`, `email` FROM `users` WHERE `age` > ?", 18)
if err != nil {
return err
}
defer rows.Close()
var users []User
for rows.Next() {
var u User
if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
return err
}
users = append(users, u)
}
return rows.Err()
After (cool-mysql):
var users []User
return db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `age` > @@minAge", 0, 18)
DO:
@@paramName syntax consistentlymysql.Params{} for claritymysql.Raw() for literal SQL that shouldn't be escapedDON'T:
? and @@ syntax (use @@ exclusively)mysql.Raw() (SQL injection risk)DO:
@@param for values (preferred - automatically marshaled){{.Field | marshal}} when injecting values directly in templates.ParamNamedb.AddTemplateFuncs()DON'T:
{{.Name}} causes syntax errorsDO:
0 TTL for frequently-changing dataSelectWrites() immediately after writes for consistencyMultiCache for high-traffic applicationsDON'T:
DO:
defaultzero for timestamp columns with DB defaultsmysql:"-" to exclude sensitive fieldsZeroer interface for custom zero-value detectionDON'T:
json tags with mysql tags without testingDO:
sql.ErrNoRows when selecting single valuesExecResult() when you need LastInsertId() or RowsAffected()DON'T:
sql.ErrNoRows when selecting into slices (returns empty slice)DO:
SelectWrites() sparingly (only when consistency required)DON'T:
SelectWrites() as default (defeats read pool optimization)Select into channel:
userCh := make(chan User)
go func() {
defer close(userCh)
db.Select(userCh, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`", 0)
}()
for user := range userCh {
// Process user
}
Insert from channel:
userCh := make(chan User)
go func() {
for _, u := range users {
userCh <- u
}
close(userCh)
}()
err := db.Insert("users", userCh)
err := db.Select(func(u User) {
log.Printf("Processing user: %s", u.Name)
}, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`", 0)
tx, commit, cancel, err := mysql.GetOrCreateTxFromContext(ctx)
defer cancel()
if err != nil {
return err
}
// Store transaction in context
ctx = mysql.NewContextWithTx(ctx, tx)
// Do database operations...
if err := commit(); err != nil {
return err
}
Custom zero detection:
type CustomTime struct {
time.Time
}
func (ct CustomTime) IsZero() bool {
return ct.Time.IsZero() || ct.Time.Unix() == 0
}
Custom value conversion:
type Point struct {
X, Y float64
}
func (p Point) Values() []any {
return []any{p.X, p.Y}
}
Configure behavior via environment variables:
COOL_MAX_EXECUTION_TIME_TIME - Max query execution time (default: 27s)COOL_MAX_ATTEMPTS - Max retry attempts (default: unlimited)COOL_REDIS_LOCK_RETRY_DELAY - Lock retry delay (default: 0.020s)COOL_MYSQL_MAX_QUERY_LOG_LENGTH - Max query length in logs (default: 4096 bytes)This skill includes comprehensive reference documentation and working examples:
references/)To access reference documentation:
Read references/api-reference.md for complete API documentation
Read references/query-patterns.md for query examples
Read references/caching-guide.md for caching strategies
Read references/struct-tags.md for struct tag details
Read references/testing-patterns.md for testing patterns
examples/)To access examples:
Read examples/basic-crud.go for basic patterns
Read examples/advanced-queries.go for advanced usage
Read examples/caching-setup.go for cache setup
Read examples/transaction-patterns.go for transactions
Read examples/upsert-examples.go for upsert patterns
Empty Result Handling: Selecting into slice returns empty slice (not sql.ErrNoRows); selecting into single value returns sql.ErrNoRows
Template vs Column Names: Templates use field names (.Name), not column names from tags
Cache Keys: Include both query and parameters, so identical queries with different params cache separately
Read/Write Consistency: Use SelectWrites() immediately after writes, not Select()
Struct Tag Priority: mysql tag overrides field name for column mapping
Parameter Case: Parameters are case-insensitive when merged (normalized to lowercase)
Automatic Chunking: Large inserts automatically chunk based on max_allowed_packet
Retry Behavior: Automatic retries for error codes 1213 (deadlock), 1205 (lock timeout), 2006 (server gone), 2013 (connection lost)
references/api-reference.md for complete API documentationexamples/basic-crud.go to see common patterns in actionreferences/caching-guide.md for caching best practicesreferences/struct-tags.md for advanced struct mappingexamples/advanced-queries.go for complex query patterns