Mysql Crud

MySQL CRUD

Safely inspect, query, and change MySQL data through saved local profiles. The skill uses a Bash script and the mysql CLI; it does not depend on Python, pymysql, or jq.

Purpose

Use this skill for:

• saving reusable MySQL connection profiles
• direct MySQL CLI connections
• SSH tunnel connections to private MySQL hosts
• SSH remote execution where the database URL exists on a server, such as a remote .env file
• schema inspection
• select, insert, update, delete, and raw SQL

Output:

• JSON printed to stdout
• the same JSON saved to ./out/mysql-crud-<UTC timestamp>-<pid>.json by default

Safety Rules

• Never save database credentials in this repository.
• Use ~/.config/mysql-crud/profiles/<profile>.conf for saved profiles.
• Do not print passwords or full database URLs; use list-profiles for redacted output.
• Prefer read-only queries unless the user clearly asks to mutate data.
• For insert, update, and delete, run dry-run first.
• Run writes with --execute only after explicit user confirmation.
• readonly profiles must not be used for insert, update, delete, or raw write SQL.
• update and delete require --where unless the user explicitly confirms a full-table operation and --allow-full-table is passed.
• Use parameter placeholders in filters, such as --where "email = :email" --param email=a@example.com.
• Do not invent table or column names. Run schema first when unsure.

What This Skill Needs

• bash
• local mysql CLI for direct and ssh-tunnel profiles
• ssh for ssh-tunnel and ssh-remote profiles
• remote bash and mysql CLI for ssh-remote profiles

Profile Storage

Profiles are saved under:

~/.config/mysql-crud/profiles/

The default profile name is saved at:

~/.config/mysql-crud/default_profile

Each profile is a 0600 shell-style config file. Example:

MODE=ssh-remote
READONLY=true
SSH_ALIAS=fr
REMOTE_CWD=/server/frevana-server-prod
ENV_FILE=.env
ENV_KEY=DATABASE_URL

Do not edit this file by hand unless needed; prefer configure.

Configure Profiles

Direct MySQL

Prefer explicit fields:

bash <skill-path>/scripts/mysql_crud.sh configure \
  --profile local \
  --mode direct \
  --mysql-host 127.0.0.1 \
  --mysql-port 3306 \
  --mysql-database app_db \
  --mysql-user app_user \
  --prompt-mysql-password \
  --default

A simple MySQL URL is also supported:

bash <skill-path>/scripts/mysql_crud.sh configure \
  --profile local \
  --mode direct \
  --url "mysql://user:password@127.0.0.1:3306/app_db" \
  --default

SSH Remote

Use this when the agent should SSH to a server and run the remote mysql client there. This is best when the server has access to a private database and the database URL is already present in a remote .env file.

bash <skill-path>/scripts/mysql_crud.sh configure \
  --profile frevana-prod \
  --mode ssh-remote \
  --ssh-alias fr \
  --remote-cwd /server/frevana-server-prod \
  --env-file .env \
  --env-key DATABASE_URL \
  --readonly \
  --default

The script SSHes to the server, optionally runs cd <remote_cwd>, reads DATABASE_URL from the remote .env, parses it on the remote host with Bash, runs mysql --batch --raw, and converts the tabular output to JSON locally. It must not display the full DATABASE_URL.

Use --remote-cwd when the .env file exists only inside an application directory after SSH login. --env-file may be either relative to --remote-cwd, such as .env, or an absolute path, such as /server/frevana-server-prod/.env.

SSH Tunnel

Use this when the local script should open an SSH tunnel to a private MySQL host, then connect locally with the mysql CLI.

bash <skill-path>/scripts/mysql_crud.sh configure \
  --profile staging \
  --mode ssh-tunnel \
  --ssh-host staging.example.com \
  --ssh-user ubuntu \
  --ssh-key ~/.ssh/staging.pem \
  --mysql-host 10.0.1.20 \
  --mysql-port 3306 \
  --mysql-database app_staging \
  --mysql-user app_user \
  --prompt-mysql-password

List Profiles

bash <skill-path>/scripts/mysql_crud.sh list-profiles

The output is redacted.

Remove Profile

bash <skill-path>/scripts/mysql_crud.sh remove-profile --profile staging

Inspect Schema

List tables for the selected profile:

bash <skill-path>/scripts/mysql_crud.sh schema --profile frevana-prod

List columns for one table:

bash <skill-path>/scripts/mysql_crud.sh schema \
  --profile frevana-prod \
  --table users

Query Rows

bash <skill-path>/scripts/mysql_crud.sh select \
  --profile frevana-prod \
  --table users \
  --where "email = :email" \
  --param email=test@example.com \
  --limit 20

If --profile is omitted, the script uses the saved default profile.

Optional fields:

• --columns "id,email,created_at"
• --order-by created_at
• --desc
• --limit 50

Insert Rows

Dry-run first:

bash <skill-path>/scripts/mysql_crud.sh insert \
  --profile staging \
  --table users \
  --value email=test@example.com \
  --value name=Test

Execute only after explicit confirmation:

bash <skill-path>/scripts/mysql_crud.sh insert \
  --profile staging \
  --table users \
  --value email=test@example.com \
  --value name=Test \
  --execute

Update Rows

Dry-run first:

bash <skill-path>/scripts/mysql_crud.sh update \
  --profile staging \
  --table orders \
  --set status=paid \
  --where "id = :id" \
  --param id=123

The dry-run previews matching rows and returns the SQL that would run.

Execute only after explicit confirmation:

bash <skill-path>/scripts/mysql_crud.sh update \
  --profile staging \
  --table orders \
  --set status=paid \
  --where "id = :id" \
  --param id=123 \
  --execute

Delete Rows

Dry-run first:

bash <skill-path>/scripts/mysql_crud.sh delete \
  --profile staging \
  --table sessions \
  --where "expires_at < :cutoff" \
  --param cutoff=2026-01-01

Execute only after explicit confirmation:

bash <skill-path>/scripts/mysql_crud.sh delete \
  --profile staging \
  --table sessions \
  --where "expires_at < :cutoff" \
  --param cutoff=2026-01-01 \
  --execute

Raw SQL

Use raw SQL for read-only statements when the structured commands are too limited:

bash <skill-path>/scripts/mysql_crud.sh raw-sql \
  --profile frevana-prod \
  --sql "SELECT COUNT(*) AS count FROM users"

Raw write SQL is blocked unless --execute is passed and the profile is not readonly:

bash <skill-path>/scripts/mysql_crud.sh raw-sql \
  --profile staging \
  --sql "UPDATE orders SET status = 'paid' WHERE id = 123" \
  --execute

Prefer structured insert, update, and delete over raw write SQL.

Response Shape

Success responses include:

• profile
• mode
• operation
• rows and row_count for queries
• dry_run for write previews
• sql for transparency

Example dry-run update response:

{
  "profile": "staging",
  "mode": "ssh-tunnel",
  "operation": "update",
  "dry_run": true,
  "table": "orders",
  "would_set": {
    "status": "paid"
  },
  "preview": {
    "row_count": 1,
    "rows": [
      {
        "id": "123",
        "status": "pending"
      }
    ]
  },
  "message": "Pass --execute only after user confirmation."
}

Notes

• Use ssh-remote for production-style access where the server already knows DATABASE_URL.
• For ssh-remote, set --remote-cwd when the .env lives inside a project directory on the remote server.
• Use ssh-tunnel when the database is private but CRUD should run through the local mysql CLI.
• Use direct for local or directly reachable MySQL.
• ssh-remote parses the remote .env with remote Bash; if remote bash, mysql, or remote_cwd is missing/invalid, stop and report that dependency or path issue.
• ssh-tunnel launches ssh -N -L and terminates the tunnel when the command finishes.
• Identifier names are restricted to simple MySQL identifiers and optional database.table form.
• The script is intentionally not a migration tool. Do not use it for schema changes unless the user explicitly asks for raw SQL and approves the risk.

Example Prompts

Chinese

• "配置一个 MySQL profile，名字叫 frevana-prod，通过 ssh alias fr 到服务器，进入 /server/frevana-server-prod 后读取 .env 的 DATABASE_URL，只读。"
• "查一下 frevana-prod 的 users 表结构。"
• "用默认数据库查 users 表里 email 是 test@example.com 的记录。"
• "把 staging 的 orders 表 id=123 的 status 改成 paid，先 dry-run。"

English

• "Configure a readonly MySQL profile through SSH using the remote DATABASE_URL."
• "Inspect the schema for the users table."
• "Query users by email using the default profile."
• "Preview an update to an order status before executing it."