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:

go
1db.Insert("users", user)
2// Need immediate consistency - use write pool
3db.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:

go
1// Named parameters
2db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `age` > @@minAge AND `status` = @@status", 0,
3    mysql.Params{"minAge": 18, "status": "active"})
4
5// Struct as parameters
6user := User{ID: 1, Name: "Alice"}
7db.Exec("UPDATE `users` SET `name` = @@Name WHERE `id` = @@ID", user)
8
9// Raw SQL injection
10db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE @@condition", 0,
11    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:

go
1// ✅ CORRECT - Use @@param for values (automatically marshaled)
2query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE {{ if .MinAge }}`age` > @@minAge{{ end }}"
3
4// ✅ CORRECT - Use | marshal when injecting value directly in template
5query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `name` = {{ .Name | marshal }}"
6
7// ❌ WRONG - Direct injection without marshal causes syntax errors
8query := "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `name` = {{ .Name }}"  // BROKEN!
9
10// ✅ CORRECT - Identifiers (column names) validated, then injected
11if !allowedColumns[sortBy] { return errors.New("invalid column") }
12query := "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:

go
1db.Select(&users,
2    "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE 1=1"+
3    " {{ if .MinAge }}AND `age` > @@minAge{{ end }}"+
4    " {{ if .Status }}AND `status` = @@status{{ end }}",
5    0,
6    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:

go
1// Redis (with distributed locking)
2db.EnableRedis(redisClient)
3
4// Memcached
5db.EnableMemcache(memcacheClient)
6
7// In-memory (weak pointers, GC-managed)
8db.UseCache(mysql.NewWeakCache())
9
10// Layered caching (fast local + shared distributed)
11db.UseCache(mysql.NewMultiCache(
12    mysql.NewWeakCache(),      // L1: Fast local cache
13    mysql.NewRedisCache(redis), // L2: Shared distributed cache
14))

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:

go
1type User struct {
2    ID        int       `mysql:"id"`
3    Name      string    `mysql:"name"`
4    Email     string    `mysql:"email"`
5    CreatedAt time.Time `mysql:"created_at,defaultzero"` // Use DB default on zero value
6    UpdatedAt time.Time `mysql:"updated_at,defaultzero"`
7    Password  string    `mysql:"-"` // Never include in queries
8}

Quick Start Guide

Creating a Database Connection

From connection parameters:

go
1db, err := mysql.New(
2    wUser, wPass, wSchema, wHost, wPort,  // Write connection
3    rUser, rPass, rSchema, rHost, rPort,  // Read connection
4    collation,                             // e.g., "utf8mb4_unicode_ci"
5    timeZone,                              // e.g., "America/New_York"
6)

From DSN strings:

go
1db, err := mysql.NewFromDSN(writesDSN, readsDSN)

From existing connections:

go
1db, err := mysql.NewFromConn(writesConn, readsConn)

Basic Query Patterns

Select into struct slice:

go
1var users []User
2err := db.Select(&users, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users` WHERE `age` > @@minAge", 0, 18)

Select single value:

go
1var name string
2err := db.Select(&name, "SELECT `name` FROM `users` WHERE `id` = @@id", 0, 1)
3// Returns sql.ErrNoRows if not found

Count records:

go
1count, err := db.Count("SELECT COUNT(*) FROM `users` WHERE `active` = @@active", 0, 1)

Check existence:

go
1exists, err := db.Exists("SELECT 1 FROM `users` WHERE `email` = @@email", 0, "user@example.com")

Insert data:

go
1// Single insert
2user := User{Name: "Alice", Email: "alice@example.com"}
3err := db.Insert("users", user)
4
5// Batch insert (automatically chunked)
6users := []User{{Name: "Bob"}, {Name: "Charlie"}}
7err := db.Insert("users", users)

Upsert (INSERT ... ON DUPLICATE KEY UPDATE):

go
1err := db.Upsert(
2    "users",                      // table
3    []string{"email"},           // unique columns
4    []string{"name", "updated_at"}, // columns to update on conflict
5    "",                          // optional WHERE clause
6    user,                        // data
7)

Execute query:

go
1err := db.Exec("UPDATE `users` SET `active` = 1 WHERE `id` = @@id", 1)

Migration Guide from database/sql

Key Differences

Migration Pattern

Before (database/sql):

go
1rows, err := db.Query("SELECT `id`, `name`, `email` FROM `users` WHERE `age` > ?", 18)
2if err != nil {
3    return err
4}
5defer rows.Close()
6
7var users []User
8for rows.Next() {
9    var u User
10    if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
11        return err
12    }
13    users = append(users, u)
14}
15return rows.Err()

After (cool-mysql):

go
1var users []User
2return 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:

go
1userCh := make(chan User)
2go func() {
3    defer close(userCh)
4    db.Select(userCh, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`", 0)
5}()
6
7for user := range userCh {
8    // Process user
9}

Insert from channel:

go
1userCh := make(chan User)
2go func() {
3    for _, u := range users {
4        userCh <- u
5    }
6    close(userCh)
7}()
8
9err := db.Insert("users", userCh)

Function Receivers

go
1err := db.Select(func(u User) {
2    log.Printf("Processing user: %s", u.Name)
3}, "SELECT `id`, `name`, `email`, `age`, `active`, `created_at`, `updated_at` FROM `users`", 0)

Transaction Management

go
1tx, commit, cancel, err := mysql.GetOrCreateTxFromContext(ctx)
2defer cancel()
3if err != nil {
4    return err
5}
6
7// Store transaction in context
8ctx = mysql.NewContextWithTx(ctx, tx)
9
10// Do database operations...
11
12if err := commit(); err != nil {
13    return err
14}

Custom Interfaces

Custom zero detection:

go
1type CustomTime struct {
2    time.Time
3}
4
5func (ct CustomTime) IsZero() bool {
6    return ct.Time.IsZero() || ct.Time.Unix() == 0
7}

Custom value conversion:

go
1type Point struct {
2    X, Y float64
3}
4
5func (p Point) Values() []any {
6    return []any{p.X, p.Y}
7}

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