gitgoodordietrying

SQL Toolkit

開発v1.0.0
作者:gitgoodordietrying更新日 2026-05-11sql-toolkit@clawhub_gitgoodordietrying/sql-toolkit
コード生成バックエンド開発#SQL#Backup#MySQL#Sqlite#Debugging

AlphaMoatによると、2026-10時点でSQL Toolkitのダウンロード数は35,345、スター数は56で、累計ダウンロード数で今回のランキング対象AIスキル10,914件中755位、開発では1,069件中47位です。

一覧の初期表示は累計ダウンロード数順で、スター数順にも並べ替えできます。詳細ページの順位は累計ダウンロード数で計算しています。

ダウンロード数
3.5万
スター数
56
累計ダウンロード順位
#755
全体の上位 7% · 開発の上位 5%
プラットフォームダウンロード数スター数コメント
ClawHub ↗2.1万41-
SkillHub ↗3.5万56—

プラットフォームごとに集計方法と集計時点が異なります。

このスキルでできること

SQLite、PostgreSQL、MySQL を対象に、スキーマ設計、結合や集計、ウィンドウ関数、CTE を使う複雑な問い合わせ、移行、索引、バックアップと復元まで扱います。実行環境には sqlite3、psql、mysql のいずれかがあれば足ります。 移行や復元の前に対象データベースを確認してバックアップを取り、移行ファイルを検証してください。可能ならトランザクション対応またはツール管理の移行を使います。

資料公開日 2026-10-27

できること

  • 既存の表に索引を設計し遅い問い合わせを改善する
  • 複数表の集計やウィンドウ関数を使う問い合わせを書く

使う場面

  • 表構造や移行スクリプトを変えるとき
  • 遅い問い合わせで実行計画を見たいとき

プロンプト例

スキルのドキュメントをもとに生成した例であり、実際の実行結果ではありません。

この問い合わせが遅いので、適切な索引を追加して理由を説明してください
今回のスキーマ変更を移行スクリプトにしてください

月次の推移

ダウンロード数3.5万
1.4万2.5万3.7万2026-03 · ダウンロード数 1.5万2026-05 · ダウンロード数 1.9万2026-06 · ダウンロード数 2万2026-07 · ダウンロード数 2.1万2026-08 · ダウンロード数 2.2万2026-09 · ダウンロード数 2.4万2026-10 · ダウンロード数 3.5万26-0326-0926-10
スター数56
3648592026-03 · スター数 382026-05 · スター数 452026-06 · スター数 482026-07 · スター数 562026-08 · スター数 572026-09 · スター数 572026-10 · スター数 5626-0326-0926-10

2026-09 から集計方法を変更し、同名スキルを作者ごとに分けて集計し、全スキルを対象にしました。破線は変更前の月で、変更後のデータとは直接比較せず、変更をまたぐ増減も計算していません。

月ダウンロード数前月比スター数
2026-103.5万—56
2026-092.4万—57
2026-082.2万+4.8%57
2026-072.1万+6.4%56
2026-062万+5.4%48
2026-051.9万—45
2026-031.5万—38

SKILL.md 原文

作者が公開した元のファイルをテキストのまま表示しています。AlphaMoat による編集や審査はしていません。リンクはクリックできません。コマンドは内容を確認してから実行してください。取得日 2026-10-23。

12 KB・ClawHub v1.0.0
---
name: sql-toolkit
description: Query, design, migrate, and optimize SQL databases. Use when working with SQLite, PostgreSQL, or MySQL — schema design, writing queries, creating migrations, indexing, backup/restore, and debugging slow queries. No ORMs required.
metadata: {"clawdbot":{"emoji":"🗄️","requires":{"anyBins":["sqlite3","psql","mysql"]},"os":["linux","darwin","win32"]}}
---

# SQL Toolkit

Work with relational databases directly from the command line. Covers SQLite, PostgreSQL, and MySQL with patterns for schema design, querying, migrations, indexing, and operations.
すべて表示(全 435 行)
## When to Use

- Creating or modifying database schemas
- Writing complex queries (joins, aggregations, window functions, CTEs)
- Building migration scripts
- Optimizing slow queries with indexes and EXPLAIN
- Backing up and restoring databases
- Quick data exploration with SQLite (zero setup)

## SQLite (Zero Setup)

SQLite is included with Python and available on every system. Use it for local data, prototyping, and single-file databases.

### Quick Start

```bash
# Create/open a database
sqlite3 mydb.sqlite

# Import CSV directly
sqlite3 mydb.sqlite ".mode csv" ".import data.csv mytable" "SELECT COUNT(*) FROM mytable;"

# One-liner queries
sqlite3 mydb.sqlite "SELECT * FROM users WHERE created_at > '2026-01-01' LIMIT 10;"

# Export to CSV
sqlite3 -header -csv mydb.sqlite "SELECT * FROM orders;" > orders.csv

# Interactive mode with headers and columns
sqlite3 -header -column mydb.sqlite
```

### Schema Operations

```sql
-- Create table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Create with foreign key
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total REAL NOT NULL CHECK(total >= 0),
    status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','paid','shipped','cancelled')),
    created_at TEXT DEFAULT (datetime('now'))
);

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Create index
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- View schema
.schema users
.tables
```

## PostgreSQL

### Connection

```bash
# Connect
psql -h localhost -U myuser -d mydb

# Connection string
psql "postgresql://user:pass@localhost:5432/mydb?sslmode=require"

# Run single query
psql -h localhost -U myuser -d mydb -c "SELECT NOW();"

# Run SQL file
psql -h localhost -U myuser -d mydb -f migration.sql

# List databases
psql -l
```

### Schema Design Patterns

```sql
-- Use UUIDs for distributed-friendly primary keys
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('user','admin','moderator')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT users_email_unique UNIQUE(email)
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_modtime
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- Enum type (PostgreSQL-specific)
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status order_status NOT NULL DEFAULT 'pending',
    total NUMERIC(10,2) NOT NULL CHECK(total >= 0),
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Partial index (only index active orders — smaller, faster)
CREATE INDEX idx_orders_active ON orders(user_id, created_at)
    WHERE status NOT IN ('delivered', 'cancelled');

-- GIN index for JSONB queries
CREATE INDEX idx_orders_metadata ON orders USING GIN(metadata);
```

### JSONB Queries (PostgreSQL)

```sql
-- Store JSON
INSERT INTO orders (user_id, total, metadata)
VALUES ('...', 99.99, '{"source": "web", "coupon": "SAVE10", "items": [{"sku": "A1", "qty": 2}]}');

-- Query JSON fields
SELECT * FROM orders WHERE metadata->>'source' = 'web';
SELECT * FROM orders WHERE metadata->'items' @> '[{"sku": "A1"}]';
SELECT metadata->>'coupon' AS coupon, COUNT(*) FROM orders GROUP BY 1;

-- Update JSON field
UPDATE orders SET metadata = jsonb_set(metadata, '{source}', '"mobile"') WHERE id = '...';
```

## MySQL

### Connection

```bash
mysql -h localhost -u root -p mydb
mysql -h localhost -u root -p -e "SELECT NOW();" mydb
```

### Key Differences from PostgreSQL

```sql
-- Auto-increment (not SERIAL)
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- JSON type (MySQL 5.7+)
CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    metadata JSON,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Query JSON
SELECT * FROM orders WHERE JSON_EXTRACT(metadata, '$.source') = 'web';
-- Or shorthand:
SELECT * FROM orders WHERE metadata->>'$.source' = 'web';
```

## Query Patterns

### Joins

```sql
-- Inner join (only matching rows)
SELECT u.name, o.total, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.created_at > '2026-01-01';

-- Left join (all users, even without orders)
SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Self-join (find users with same email domain)
SELECT a.name, b.name, SPLIT_PART(a.email, '@', 2) AS domain
FROM users a
JOIN users b ON SPLIT_PART(a.email, '@', 2) = SPLIT_PART(b.email, '@', 2)
WHERE a.id < b.id;
```

### Aggregations

```sql
-- Group by with having
SELECT status, COUNT(*) AS cnt, SUM(total) AS revenue
FROM orders
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY revenue DESC;

-- Running total (window function)
SELECT date, revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_sales;

-- Rank within groups
SELECT user_id, total,
    RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank
FROM orders;

-- Moving average (last 7 entries)
SELECT date, revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_sales;
```

### Common Table Expressions (CTEs)

```sql
-- Readable multi-step queries
WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', created_at) AS month,
           SUM(total) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1
),
growth AS (
    SELECT month, revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
           ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
                 NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1) AS growth_pct
    FROM monthly_revenue
)
SELECT * FROM growth ORDER BY month;

-- Recursive CTE (org chart / tree traversal)
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT REPEAT('  ', depth) || name AS org_chart FROM org_tree ORDER BY depth, name;
```

## Migrations

### Manual Migration Script Pattern

```bash
#!/bin/bash
# migrate.sh - Run numbered SQL migration files
DB_URL="${1:?Usage: migrate.sh <db-url>}"
MIGRATIONS_DIR="./migrations"

# Create tracking table
psql "$DB_URL" -c "CREATE TABLE IF NOT EXISTS schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ DEFAULT NOW()
);"

# Run pending migrations in order
for file in $(ls "$MIGRATIONS_DIR"/*.sql | sort); do
    version=$(basename "$file" .sql)
    already=$(psql "$DB_URL" -tAc "SELECT 1 FROM schema_migrations WHERE version='$version';")
    if [ "$already" = "1" ]; then
        echo "SKIP: $version (already applied)"
        continue
    fi
    echo "APPLY: $version"
    psql "$DB_URL" -f "$file" && \
    psql "$DB_URL" -c "INSERT INTO schema_migrations (version) VALUES ('$version');" || {
        echo "FAILED: $version"
        exit 1
    }
done
echo "All migrations applied."
```

### Migration File Convention

```
migrations/
  001_create_users.sql
  002_create_orders.sql
  003_add_users_phone.sql
  004_add_orders_metadata_index.sql
```

Each file:
```sql
-- 003_add_users_phone.sql
-- Up
ALTER TABLE users ADD COLUMN phone TEXT;

-- To reverse: ALTER TABLE users DROP COLUMN phone;
```

## Query Optimization

### EXPLAIN (PostgreSQL)

```sql
-- Show query plan
EXPLAIN SELECT * FROM orders WHERE user_id = '...' AND status = 'paid';

-- Show actual execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = '...' AND status = 'paid';
```

**What to look for:**
- `Seq Scan` on large tables → needs an index
- `Nested Loop` with large row counts → consider `Hash Join` (may need more `work_mem`)
- `Rows Removed by Filter` being high → index doesn't cover the filter
- Actual rows far from estimated → run `ANALYZE tablename;` to update statistics

### Index Strategy

```sql
-- Single column (most common)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite (for queries filtering on both columns)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Column ORDER matters: put equality filters first, range filters last

-- Covering index (includes data columns to avoid table lookup)
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);

-- Partial index (smaller, faster — only index what you query)
CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';

-- Check unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;
```

### SQLite EXPLAIN

```sql
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;
-- Look for: SCAN (bad) vs SEARCH USING INDEX (good)
```

## Backup & Restore

### PostgreSQL

```bash
# Full dump (custom format, compressed)
pg_dump -Fc -h localhost -U myuser mydb > backup.dump

# Restore
pg_restore -h localhost -U myuser -d mydb --clean --if-exists backup.dump

# SQL dump (portable, readable)
pg_dump -h localhost -U myuser mydb > backup.sql

# Dump specific tables
pg_dump -h localhost -U myuser -t users -t orders mydb > partial.sql

# Copy table to CSV
psql -c "\copy (SELECT * FROM users) TO 'users.csv' CSV HEADER"
```

### SQLite

```bash
# Backup (just copy the file, but use .backup for consistency)
sqlite3 mydb.sqlite ".backup backup.sqlite"

# Dump to SQL
sqlite3 mydb.sqlite .dump > backup.sql

# Restore from SQL
sqlite3 newdb.sqlite < backup.sql
```

### MySQL

```bash
# Dump
mysqldump -h localhost -u root -p mydb > backup.sql

# Restore
mysql -h localhost -u root -p mydb < backup.sql
```

## Tips

- Always use parameterized queries in application code — never concatenate user input into SQL
- Use `TIMESTAMPTZ` (not `TIMESTAMP`) in PostgreSQL for timezone-aware dates
- Set `PRAGMA journal_mode=WAL;` in SQLite for concurrent read performance
- Use `EXPLAIN` before deploying any query that runs on large tables
- PostgreSQL: `\d+ tablename` shows columns, indexes, and size. `\di+` lists all indexes with sizes
- For quick data exploration, import any CSV into SQLite: `sqlite3 :memory: ".mode csv" ".import file.csv t" "SELECT ..."`

開発の人気スキル

编程大神.Skill開発

【全栈能力】编程大神.Skill:面向复杂真实项目的高阶全栈编程助手。不止生成代码,而是从一句目标接手陌生代码库,拆解需求、补齐实现、追到根因、完成重构、联调前后端,并把结果推进到能运行、能验证、能交付。覆盖 Web、后端、脚本、iOS、安卓、鸿蒙 ArkUI、微信小程序及其他技术栈,支持中英文工程、疑难 Bug、跨模块任务和长任务续做;用构建、日志、真实入口、设备或接口证据说明结果。仅问概念时直接回答,要求落地时持续推进到验收或明确阻塞。

↓ 1125.9万★ 82u_42bbb65f
编程专家Pro.Skill開発

编程专家 Pro:融合编程专家 2.0.3 工程专题与编程大神的鸿蒙 ArkTS/ArkUI、微信小程序和小游戏开发资料。用于项目实现、根因调试、审查、重构、测试、架构性能分析及多端开发验证。支持自然语言和 @专题标识,按任务读取包内知识;只问概念时直接回答。

↓ 626.9万★ 32user_47d655f0
编程专家.Skill開発

编程专家.Skill P8级编程助手,25年实战经验技能Skill,全栈:Java/Php网站/软件项目、API设计、Bug诊断、代码生成、代码审查、重构、测试用例、性能基准、技术选型、文档生成、任务拆解、Spec驱动、Karpathy规范、CMS二次开发、前端设计、MySQL/Mariadb、项目知识图谱,以及工程纪律层(根因调试硬闭环、阶段导航、事故复盘/SLO、威胁建模/供应链安全、生产就绪/渐进式发布、AI编码治理、面向Agent代码可读性、LLM应用安全)。支持 @标识 显式调用跳过路由。

↓ 600.7万★ 378user_741dc82b
dev-expert開発

编程专家.Skill P8级编程助手,25年实战经验技能Skill,全栈:Java/Php网站/软件项目、API设计、Bug诊断、代码生成、代码审查、重构、测试用例、性能基准、技术选型、文档生成、任务拆解、Spec驱动、Karpathy规范、CMS二次开发、前端设计、MySQL/Mariadb、项目知识图谱,以及工程纪律层(根因调试硬闭环、阶段导航、事故复盘/SLO、威胁建模/供应链安全、生产就绪/渐进式发布、AI编码治理、面向Agent代码可读性、LLM应用安全)。支持 @标识 显式调用跳过路由。

↓ 528.1万★ 32user_814dbe54
dev-expert-pro開発

工程专题、鸿蒙与微信小程序开发的集成派生版

↓ 104.7万★ -user_814dbe54
编程专家 Pro開発

编程专家 Pro:融合编程专家 2.0.3 工程专题与编程大神的鸿蒙 ArkTS/ArkUI、微信小程序和小游戏开发资料。用于项目实现、根因调试、审查、重构、测试、架构性能分析及多端开发验证。支持自然语言和 @专题标识,按任务读取包内知识;只问概念时直接回答。

↓ 98.2万★ 1user_28275a78
编程专家.Skill開発

适用场景:全栈编程助手。接到需求先读现有代码和约束,再动手:Java/PHP 开发、接口设计、缺陷定位、代码评审、重构、测试用例、性能排查、技术选型与文档产出,也做 CMS 二次开发、前端页面和 MySQL 调优。每次改动先给方案和影响范围,改完必须跑验证,交付附交接说明。适合要把代码真正跑通、而不是只要片段的场景。写完代码常要配套的造价、法律、工程文档,站内已有现成技能,用「查找技能」按需求找即可。

↓ 70.1万★ 6user_613b33ea
Github開発

gh コマンドラインで GitHub の PR チェック、ワークフロー実行、API 照会を扱うスキルです。

↓ 48万★ 714Peter Steinberger

gitgoodordietryingのその他のスキル

データ月:2026-10 · ダウンロード数とスター数は公開スキルレジストリ(ClawHub、SkillHub)から月単位で集計しています。詳しくはデータの方法論をご覧ください。