Cool Mysql

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:"-" - Completely ignore this field
• 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:"-"` // Never include in queries
}

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

Migration Guide from database/sql

Key Differences

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 mysql:"-" to exclude sensitive fields
• 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
}

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