golib/skills/use-sql-migrate-mysql/SKILL.md

name, description, depends

name	description	depends
use-sql-migrate-mysql	MySQL and MariaDB migrations with sql-migrate and mymigrate. Use when setting up MySQL/MariaDB migrations, configuring multiStatements, or MY_CNF credentials.	use-sqlmigrate

CLI setup

# MariaDB
sql-migrate -d ./sql/migrations/ init --sql-command mariadb

# MySQL
sql-migrate -d ./sql/migrations/ init --sql-command mysql

Environment

# .env
MY_URL='user:pass@tcp(localhost:3306)/mydb?multiStatements=true&parseTime=true'
MY_CNF='./my.cnf'

MUST: Include multiStatements=true in the DSN. mymigrate validates this on first exec and returns an error if missing.

Credentials file (for CLI)

The CLI uses --defaults-extra-file to avoid passwords in command args:

# my.cnf
[client]
host=localhost
port=3306
database=mydb
user=appuser
password=secret

Go library

import (
    "database/sql"

    _ "github.com/go-sql-driver/mysql"
    "github.com/therootcompany/golib/database/sqlmigrate"
    "github.com/therootcompany/golib/database/sqlmigrate/mymigrate"
)

db, err := sql.Open("mysql", myURL)
conn, err := db.Conn(ctx)
defer func() { _ = conn.Close() }()

runner := mymigrate.New(conn)
applied, err := sqlmigrate.Latest(ctx, runner, scripts)

SQL dialect notes

• DDL statements (CREATE/ALTER/DROP) auto-commit in MySQL — partial failures possible on multi-statement down migrations
• INSERT IGNORE for idempotent seeds (not ON CONFLICT)
• NOW() for current timestamp
• String concatenation: CONCAT(id, CHAR(9), name) (used by sync query)
• ON UPDATE CURRENT_TIMESTAMP for auto-updated timestamps
• Error 1146 = table doesn't exist (handled automatically by mymigrate)