Write SQL and analytics queries with AI
Skills used in this guide (3)
生产后端里的 MySQL 与 MariaDB 模式：schema、查询、索引、事务、主从复制与连接池。
npx skills add affaan-m/ECC -gPostgreSQL 模式实践：查询优化、schema 设计、索引与安全，基于 Supabase 最佳实践。适合设计表结构与索引、配置 RLS，或排查慢查询。
npx skills add affaan-m/ECC -g数据库迁移最佳实践：schema 变更、数据迁移、回滚与零停机发布，覆盖 PostgreSQL、MySQL 以及 Prisma、Drizzle、Kysely、Django、TypeORM、golang-migrate 等常见 ORM。
npx skills add affaan-m/ECC -g
AI SQL's scare is "runs but wrong口径" — looks right, aggregation dimension wrong. The point: give schema and clear口径 (by day / by user, dedup?, exclude refunds), have it explain then write, not just "count sales".
Who this is for / not for
For:
- Can't write complex SQL but need analysis
- Have schema, want AI to generate query
- Want AI help with create/alter (migrations)
Not for:
- Prod DB, AI runs dangerous stmt directly (review first)
- No口径 then blame result (you set口径)
- Realtime millisecond (that's index/architecture)
Before you start
- Schema: fields & types of relevant tables.
- 口径: dimension, dedup?, filters.
- (Optional) sample: align expectation.
Steps
1. Install the skill
mysql-patterns is a MySQL skill. Copy its folder into your client's skills directory (same as any Agent Skill):
| Client | Directory |
|---|---|
| Claude Code | ~/.claude/skills/ |
| Codex | ~/.codex/skills/ |
| Cursor | ~/.cursor/skills/ |
| Trae | ~/.trae/skills/ |
| CodeBuddy | ~/.codebuddy/skills/ |
| Tongyi Lingma | ~/.lingma/skills/ |
2. Schema, then口径
From orders(user_id, amount, status, created_at) and order_items,
write SQL for "paid users last 30d (distinct) + total GMV (status='paid', exclude refund)".
State aggregation口径 in one line, then statement, annotate each field.3. PG use postgres-patterns
PostgreSQL has specifics (ILIKE, window fns); postgres-patterns avoids pitfalls.
4. Create/alter with database-migrations
Add/alter table → AI migration with rollback:
Add last_login_at to users, write migration SQL with rollback, comment the impact.Pitfalls
| Pit | Symptom | Fix |
|---|---|---|
| Wrong口径 | Mismatch | State口径 then write |
| No dedup | Users inflated | Explicit count(distinct) |
| Prod direct | Delete/lock | Review then run, confirm danger |
| PG/MySQL mix | Syntax err | Specify engine, use patterns |
| No rollback | Hard recover | Migration with rollback |
FAQ
No SQL, analyze?
Yes — give schema &口径; you verify result.
Define口径?
Dimension (day/user), dedup?, filters; state then run.
Prod direct?
No — review stmt, manual confirm for danger.
PG/MySQL same?
Not fully; specify engine, use patterns.
Migration rollback?
Yes — ask AI for rollback with migration.
Related guides
Continue across these scenes:
相关源码
- affaan-m/ECC · mysql-patterns source
- affaan-m/ECC · postgres-patterns source
- affaan-m/ECC · database-migrations source
- Agent Skill open format spec
一句话结论
结论：AI 写 SQL 先给表结构和口径再出语句，生产库先审再跑、迁移带回滚。
References
Related Scenes
Build dashboards and visualizations with AI
The pain of dashboards isn't "can't draw charts" — it's "metrics undefined, charts piled, nobody looks". The point: define the north-star metric and the question first, then let AI pick chart types and write queries, not pile fancy charts upfront.
Turn raw data into a report you can ship, with AI
The most common analytics failure isn't "can't compute" — it's "computed fast but wrong": units not unified, nulls treated as 0,口径 drifting from last time. The point: define口径 first, let AI compute, then spot-check 3 rows by hand.