# Mysql Crud

> Use when the user wants to configure saved MySQL database connections, connect directly or through SSH, inspect schemas, query rows, insert records, update records, delete records, or run safe MySQL SQL with a Bash script, dry-run protections, readonly profiles, local profile storage, SSH tunnel access, or remote-server MySQL access through a saved .env DATABASE_URL.

- Skill: `finpeakinc/mysql-crud` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add finpeakinc/mysql-crud`
- Raw SKILL.md: https://api.skillmd.com/api/skills/finpeakinc/mysql-crud/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: finpeakinc (https://skillmd.com/u/finpeakinc)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/finpeakinc/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:

```text
~/.config/mysql-crud/profiles/
```

The default profile name is saved at:

```text
~/.config/mysql-crud/default_profile
```

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

```bash
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
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
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
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
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
bash <skill-path>/scripts/mysql_crud.sh list-profiles
```

The output is redacted.

### Remove Profile

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

## Inspect Schema

List tables for the selected profile:

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

List columns for one table:

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

## Query Rows

```bash
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
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
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
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
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
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
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
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
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:

```json
{
  "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."