cool-mysql

name	cool-mysql
description	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 MySQL Helper Library

Overview

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:

• Keep database/sql interfaces intact
• Provide conveniences without hiding MySQL behavior
• Focus on productivity without sacrificing control
• Type-safe operations with flexible result mapping

When to Use This Skill

Use this skill when:

• Writing MySQL database operations in Go
• Setting up database connections with read/write separation
• Implementing caching strategies for queries
• Working with struct mappings and MySQL columns
• Migrating from database/sql to cool-mysql
• Optimizing query performance
• Handling transactions with proper context management
• Debugging query issues or understanding error handling
• Implementing CRUD operations, upserts, or batch inserts

Core Concepts

1. Dual Connection Pools

cool-mysql maintains separate connection pools for reads and writes to optimize for read-heavy workloads.

Default Behavior:

• Select(), SelectJSON(), Count(), Exists() → Read pool
• Insert(), Upsert(), Exec() → Write pool
• SelectWrites(), 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)

2. Named Parameters

cool-mysql uses @@paramName syntax instead of positional ? placeholders.

Key Points:

• Parameters are case-insensitive when merged
• Structs can be used directly as parameters (field names → parameter names)
• Use mysql.Params{"key": value} for explicit parameters
• Use mysql.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"))

3. Template Syntax

cool-mysql supports Go template syntax for conditional query logic.

Important Distinctions:

• Template variables use field names (.Name), not column names from tags
• Template processing happens before parameter interpolation
• Access parameters directly as fields: .ParamName

CRITICAL: 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"})

4. Caching

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.

5. Struct Tag Mapping

Control column mapping and behavior with mysql struct tags.

Tag Options:

• mysql:"column_name" - Map to database column
• mysql:"column_name,defaultzero" - Write DEFAULT(column_name) for zero values
• mysql:"column_name,omitempty" - Same as defaultzero
• mysql:"column_name,insertDefault" - Same as defaultzero
• mysql:"column_name,noinsert" - Skip this field for inserts; still works in selects and params
• mysql:"-" - Deprecated, use noinsert instead. Only skips inserts despite appearances
• mysql:"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:"password,noinsert"` // Skipped for inserts, still works in selects
}

Quick Start Guide

Creating a Database Connection

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)

Basic Query Patterns

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, "[e-mail]")

Insert data:

// Single insert
user := User{Name: "Alice", Email: "[e-mail]"}
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)

Migration Guide from database/sql

Key Differences

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

Migration Pattern

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)

Best Practices

Parameter Handling

DO:

• Use @@paramName syntax consistently
• Use mysql.Params{} for clarity
• Use structs as parameters when appropriate
• Use mysql.Raw() for literal SQL that shouldn't be escaped

DON'T:

• Mix ? and @@ syntax (use @@ exclusively)
• Assume parameters are case-sensitive (they're normalized)
• Inject user input with mysql.Raw() (SQL injection risk)

Template Usage

DO:

• Use templates for conditional query logic
• Use @@param for values (preferred - automatically marshaled)
• Use {{.Field | marshal}} when injecting values directly in templates
• Validate/whitelist identifiers (column names) before template injection
• Reference parameters by field name: .ParamName
• Add custom template functions with db.AddTemplateFuncs()

DON'T:

• Inject values without marshal: {{.Name}} causes syntax errors
• Use column names in templates (use field names)
• Forget that templates process before parameter interpolation
• Use templates when named parameters suffice
• Inject user-controlled identifiers without validation

Caching Strategy

DO:

• Use 0 TTL for frequently-changing data
• Use longer TTLs (5-60 minutes) for stable reference data
• Use SelectWrites() immediately after writes for consistency
• Consider MultiCache for high-traffic applications
• Enable Redis distributed locking to prevent cache stampedes

DON'T:

• Cache writes (they're automatically skipped)
• Use same TTL for all queries (tune based on data volatility)
• Forget that cache keys include query + parameters

Struct Tags

DO:

• Use defaultzero for timestamp columns with DB defaults
• Use noinsert option to exclude fields from inserts
• Use hex encoding for column names with special characters
• Implement Zeroer interface for custom zero-value detection

DON'T:

• Forget that tag column names override field names
• Mix json tags with mysql tags without testing

Error Handling

DO:

• Check for sql.ErrNoRows when selecting single values
• Rely on automatic retries for transient errors (deadlocks, timeouts)
• Use ExecResult() when you need LastInsertId() or RowsAffected()

DON'T:

• Expect sql.ErrNoRows when selecting into slices (returns empty slice)
• Implement manual retry logic (already built-in)

Performance Optimization

DO:

• Use channels for memory-efficient streaming of large datasets
• Use SelectWrites() sparingly (only when consistency required)
• Enable caching for expensive or frequent queries
• Use batch operations (slices/channels) for large inserts

DON'T:

• Load entire large result sets into memory when streaming is possible
• Use SelectWrites() as default (defeats read pool optimization)
• Cache everything (tune TTL based on access patterns)

Advanced Patterns

Streaming with Channels

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)

Function Receivers

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)

Transaction Management

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
}

Transaction Hooks:

PostCommitHooks run after a successful commit and can return errors. PostRollbackHooks run after a real rollback (not when cancel() is called on an already-committed transaction) and do not return errors since the transaction has already failed.

// Run cleanup after commit
tx.PostCommitHooks = append(tx.PostCommitHooks, func() error {
    defer collectors.Delete(tx)
    return flush()
})

// Run cleanup after rollback (e.g., clean up sync.Map entries)
tx.PostRollbackHooks = append(tx.PostRollbackHooks, func() {
    collectors.Delete(tx)
})

Custom Interfaces

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}
}

Environment Variables

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)

Bundled Resources

This skill includes comprehensive reference documentation and working examples:

Reference Documentation (references/)

• api-reference.md - Complete API documentation for all methods
• query-patterns.md - Query pattern examples and best practices
• caching-guide.md - Detailed caching strategies and configuration
• struct-tags.md - Comprehensive struct tag reference
• testing-patterns.md - Testing approaches with sqlmock

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

Working Examples (examples/)

• basic-crud.go - Simple CRUD operations
• advanced-queries.go - Templates, channels, function receivers
• caching-setup.go - Cache configuration examples
• transaction-patterns.go - Transaction handling patterns
• upsert-examples.go - Upsert use cases

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

Common Gotchas

1. Empty Result Handling: Selecting into slice returns empty slice (not sql.ErrNoRows); selecting into single value returns sql.ErrNoRows

2. Template vs Column Names: Templates use field names (.Name), not column names from tags

3. Cache Keys: Include both query and parameters, so identical queries with different params cache separately

4. Read/Write Consistency: Use SelectWrites() immediately after writes, not Select()

5. Struct Tag Priority: mysql tag overrides field name for column mapping

6. Parameter Case: Parameters are case-insensitive when merged (normalized to lowercase)

7. Automatic Chunking: Large inserts automatically chunk based on max_allowed_packet

8. Retry Behavior: Automatic retries for error codes 1213 (deadlock), 1205 (lock timeout), 2006 (server gone), 2013 (connection lost)

Next Steps

• Read references/api-reference.md for complete API documentation
• Check examples/basic-crud.go to see common patterns in action
• Review references/caching-guide.md for caching best practices
• Study references/struct-tags.md for advanced struct mapping
• Explore examples/advanced-queries.go for complex query patterns