# Database Toolset General

> Use this when you need to work with MiChat’s profile-scoped SQLite database tools (create/inspect/migrate/query/update/backup).

- Skill: `filmicgaze/database-toolset-general` (Agent Skill)
- Install (CLI): `npx skillmds@latest add filmicgaze/database-toolset-general`
- Raw SKILL.md: https://api.skillmd.com/api/skills/filmicgaze/database-toolset-general/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: filmicgaze (https://skillmd.com/u/filmicgaze)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/filmicgaze/database-toolset-general

---


## When to use this skill
Use this skill whenever you’re about to do real work with MiChat’s **database toolset** (create/inspect/migrate/query/update/backup).

Default stance: **one DB per profile** (multiple tables). Create a second DB only for scratch/import/staging workflows or a clear separation reason.

## Practical workflow (the safe default)
1) **Discover / pick DB**
- If unsure what exists, list DBs.
- Otherwise default to a single main DB name (e.g. `personal`).

2) **Ensure it exists**
- If a DB might not exist yet, ensure it with create enabled.

3) **Inspect before changing**
- Use schema inspection to understand the current tables/columns.

4) **Back up before risky changes**
- Before DDL or destructive edits, take a backup with a short label.

5) **Migrate/seed in a batch**
- Prefer batched execution in a transaction for migrations and small seed data.
- Keep batches short and readable.

6) **Verify**
- Re-check schema and run small verification SELECTs.

## Tips and gotchas
### 1) SELECT vs non-SELECT routing
- SELECT/CTE belongs in the query tool; using the execute tool for SELECT will be rejected.

### 2) Paging and hard caps
- Query results are paged with an `offset:<n>` cursor.
- Even if you request a huge `max_rows`, results are hard-capped (practically: 200 rows/page). Use `next_cursor` to continue.
- For stable paging, include an **ORDER BY**.

### 3) `db_execute_many` failure semantics (choose intentionally)
- **Atomic default**: `transaction=true, stop_on_error=true`.
  - If any statement fails, you get an **error envelope only** (no partial results list).
  - Error details include `failed_index`, `sql_preview`, and sqlite error codes/names.
- If you need partial progress / diagnostics:
  - use `transaction=false`.
  - `stop_on_error=true` stops at first error and returns results up to the failure.
  - `stop_on_error=false` continues and returns per-statement errors; overall `ok=false` if any failed.
- Rule: when `transaction=true`, `stop_on_error` must be true.

### 4) RETURNING is for small results
- Statements with `RETURNING` cap returned rows (e.g. 50 per statement in batched execution).
- If you need lots of rows back, prefer:
  1) write changes without relying on a large RETURNING payload, then
  2) fetch what you need with a paged SELECT.

### 5) Debugging a failed batch quickly
- Use the batch error’s `failed_index` + `sql_preview` to pinpoint the failing statement.
- If the failure looks like a schema mismatch, re-run schema inspection (full detail if available).

## Recommended conventions (lightweight)
- Use params (positional/named) instead of interpolating values into SQL.
- Prefer idempotent DDL where possible (e.g., `IF NOT EXISTS`).
- Use consistent timestamps (e.g., `datetime('now')`).
- Start with minimal indexes; add them when queries become slow or frequent.

## Optional: scratch/import staging DB (second DB)
Use a second DB only when it makes your workflow simpler:
- messy imports
- dedup/normalization staging
- experimental schema work
Then copy cleaned data into the main DB and discard/ignore the scratch DB.

## Troubleshooting checklist
- not found: ensure DB exists (create enabled)
- paging confusion: add ORDER BY; follow `next_cursor`
- batch failure: check `failed_index` and the sqlite error; consider rerun with `transaction=false` for more visibility