peach-db-query

DB Data Query Skill

Persona

Development DB data verification expert. Helps development progress by immediately checking actual DB data during backend/frontend development and testing.

⚠️ Safety Rules (Strictly Follow)

Tier 1 — Direct Execution (SELECT)

The AI directly executes only SELECT queries and returns the results
LIMIT 100 applied by default — automatically added if unspecified
For advanced patterns (Window Functions, CTE, JSONB, etc.), see query-patterns.md reference

Tier 2 — Present SQL Only (INSERT / UPDATE / DELETE)

AI는 Only generate and show the SQL — do not execute it directly
Guide the user to execute it directly in the CLI
Presentation format:

⚠️ DML — 직접 실행이 필요합니다
─────────────────────
SQL:   UPDATE [테이블] SET status = 'A' WHERE [테이블]_seq = 42;
대상:  [테이블] 테이블
조건:  PK 단건 (WHERE [테이블]_seq = 42)

확인용 SELECT:
SELECT * FROM [테이블] WHERE [테이블]_seq = 42;

실행 방법:
PGPASSWORD='[pw]' psql -h [host] -p [port] -U [user] -d [db] -c "위 SQL"
─────────────────────

Tier 3 — Absolutely Prohibited (DDL)

DROP / ALTER / TRUNCATE / CREATE — Do not even generate the SQL
Response when requested: "DDL must be handled through migrations. Use the /peach-db-migrate skill."

Environment Restrictions

Only the development environment is allowed — env.local.yml or env.dev.yml use only connection info
Production connections prohibited — env.prod.yml Using DATABASE_URL is absolutely prohibited

CLI Installation Strategy (by OS)

PostgreSQL (psql)

OS	Strategy	Command
macOS	Direct brew install (Docker unnecessary)	`brew install libpq` + register PATH
Linux	Package Manager	`apt-get install -y postgresql-client`
Windows	Docker fallback	`docker run --rm postgres:16 psql ...`

MySQL (mysql)

OS	Strategy	Command
macOS	Direct brew install (Docker unnecessary)	`brew install mysql-client` + register PATH
Linux	Package Manager	`apt-get install -y mysql-client`
Windows	Docker fallback	`docker run --rm mysql:8 mysql ...`

Do not use Docker on macOS — brew is much faster and reusable afterward. The initial Docker image download is postgres:16 ~300MB and mysql:8 ~500MB, causing unnecessary waiting. Detailed installation procedure and PATH setup → cli-installation-guide.md

Preflight: CLI check + automatic installation

Step P-1: Detect OS

uname -s   # Darwin=macOS / Linux / MINGW64_NT=Windows

Step P-2: Check and install CLI

macOS — Direct CLI install (strongly recommended, do not use Docker)

# psql 확인
which psql && psql --version || echo "psql 없음"

# mysql 확인
which mysql && mysql --version || echo "mysql 없음"

psql not found → automatically execute after requesting installation approval from the user:

brew install libpq
echo 'export PATH="/opt/homebrew/opt/libpq/bin:$PATH"' >> ~/.zshrc && source ~/.zshrc
# 현재 세션 즉시 적용
export PATH="/opt/homebrew/opt/libpq/bin:$PATH"
psql --version   # 설치 확인

libpq keg-only caution: brew installs libpq as keg-only, so it must be added to PATH. Without adding to PATH, running psql results in "command not found". ~/.zshrc Apply permanently by registering to export Apply immediately to the current session with the command.

mysql not found → automatically execute after requesting installation approval from the user:

brew install mysql-client
echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc && source ~/.zshrc
export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"
mysql --version   # 설치 확인

Linux (Ubuntu/Debian) — Install via package manager

sudo apt-get install -y postgresql-client   # PostgreSQL
sudo apt-get install -y mysql-client        # MySQL

Windows (Git Bash) — Docker fallback (for environments where CLI installation is not possible)

Docker fallback은 Windows only. macOS/Linux has a simple CLI installation, so do not use Docker.

# Docker 설치 확인 (선행 필수)
docker --version && echo "Docker 사용 가능" || echo "Docker 없음 — CLI 직접 설치 필요"

PostgreSQL (Docker):

# URL의 특수문자는 인코딩 유지 (env.local.yml의 URL 그대로 사용)
docker run --rm postgres:16 psql \
  "postgresql://[user]:[encoded_password]@[host]:[port]/[dbname]" \
  -c "[SQL]" 2>/dev/null

MySQL (Docker):

# -p'password' — 특수문자 포함 시 반드시 따옴표로 감싸기
docker run --rm mysql:8 mysql \
  -h [host] -P [port] -u [user] -p'[password]' [dbname] \
  -e "[SQL]" 2>/dev/null

Note: 2>/dev/nullhides the Docker pull progress messages. On first run, image downloads (postgres:16 ~300MB, mysql:8 500MB) may occur. Subsequent runs start within 12 seconds thanks to caching.

Workflow

Step 1: Determine DB type

Node/Java project — Detect DATABASE_URL in env.yml:

grep "DATABASE_URL" api/src/environments/env.local.yml 2>/dev/null || \
grep "DATABASE_URL" api/env.local.yml 2>/dev/null

postgresql:// → PostgreSQL mode
mysql:// → MySQL mode

PHP project — Detect db_ in conf.php variable:*

# 우선 탐색 경로
grep "db_host\|db_port\|db_dbname\|db_user\|db_password\|db_division" src/cfg/conf.php 2>/dev/null || \
grep "db_host\|db_port\|db_dbname\|db_user\|db_password\|db_division" cfg/conf.php 2>/dev/null || \
# fallback: 전체 탐색
find . -name "conf.php" | xargs grep -l "db_host" 2>/dev/null | head -1 | \
  xargs grep "db_host\|db_port\|db_dbname\|db_user\|db_password\|db_division" 2>/dev/null

db_division = 'pdo' or 'mysqli' → MySQL mode
db_division = 'postgresql' → PostgreSQL mode
For detailed patterns, see php-project-db-connection.md refer to

Step 2: Parse connection info

Extract host, port, user, password, and dbname from DATABASE_URL.

Example URL parsing:

postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}
→ host={DB_HOST}, port={DB_PORT}, user={DB_USER}, password={DB_PASSWORD} (URL 디코딩), db={DB_NAME}

URL encoding decoding: %21 → !, %25 → %, %23 → #

Step 3: Execute SQL

PostgreSQL:

PGPASSWORD='[password]' psql -h [host] -p [port] -U [user] -d [db] -c "[SQL]"

MySQL:

mysql -h [host] -P [port] -u [user] -p'[password]' [db] -e "[SQL]"

Step 4: Interpret results

Analyze the results and convey information meaningful in the development context.

Frequently used query patterns

For detailed patterns, see query-patterns.md .

Output format

Use the psql default table format (best readability):

 column1 | column2 | column3
---------+---------+---------
 value1  | value2  | value3
(N rows)

When there are many columns and the output is horizontally long, use -x expanded mode:

PGPASSWORD='...' psql ... -x -c "[SQL]"