# Sqlite Crud > Use when the user wants to configure saved SQLite database file profiles, inspect a local SQLite file, list tables or columns, query rows, insert records, update records, delete records, or run safe SQLite SQL with a Bash script, sqlite3, dry-run protections, readonly profiles, and local profile storage for reusable local file paths. - Skill: `finpeakinc/sqlite-crud` (Agent Skill, multi-file: 3 files) - Install (CLI): `npx skillmds@latest add finpeakinc/sqlite-crud` - Raw SKILL.md: https://api.skillmd.com/api/skills/finpeakinc/sqlite-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/sqlite-crud --- # SQLite CRUD Safely inspect, query, and change local SQLite database files through saved profiles. The skill uses a Bash script and the `sqlite3` CLI; it does not depend on Python or `jq`. ## Purpose Use this skill for: - saving reusable local SQLite database file profiles - inspecting tables and columns - `select`, `insert`, `update`, `delete`, and raw SQL - local SQLite file paths only Output: - JSON printed to stdout - the same JSON saved to `./out/sqlite-crud--.json` by default ## Safety Rules - Never save SQLite database files or generated database copies in this repository unless the user explicitly asks. - Use `~/.config/sqlite-crud/profiles/.conf` for saved local file profiles. - If no profile/default profile is configured, ask the user for an explicit local database file path and use `--path`; never scan the filesystem to discover SQLite files. - When the user provides `--path`, save that path to the default profile for later commands. - Prefer read-only queries unless the user clearly asks to mutate data. - Do not add `--readonly` by default; only use it when the user explicitly asks for a read-only profile. - 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. - Raw write SQL requires both `--execute` and `--allow-raw-write`. - Do not invent table or column names. Run `schema` first when unsure. - Prefer structured commands over `raw-sql`. ## What This Skill Needs - `bash` - local `sqlite3` CLI with JSON output support - a local SQLite database file path No SSH, remote `.env`, host, user, or password options are supported. ## Profile Storage Profiles are saved under: ```text ~/.config/sqlite-crud/profiles/ ``` The default profile name is saved at: ```text ~/.config/sqlite-crud/default_profile ``` Each profile is a `0600` shell-style config file. Example: ```bash DB_PATH=/Users/me/app/data.sqlite READONLY=false ``` Do not edit this file by hand unless needed; prefer `configure`. ## Configure Profiles Configure a local SQLite file: ```bash bash /scripts/sqlite_crud.sh configure \ --profile local \ --path /Users/me/app/data.sqlite \ --default ``` Add `--test-connection` to verify the file can be opened before the profile is saved: ```bash bash /scripts/sqlite_crud.sh configure \ --profile local \ --path /Users/me/app/data.sqlite \ --test-connection ``` The script requires the file to exist by default so SQLite does not silently create an empty database at a mistyped path. ## Explicit Local Path If the user has not configured a profile, require an explicit local file path: ```bash bash /scripts/sqlite_crud.sh schema \ --path /Users/me/app/data.sqlite ``` The script saves the provided path to the default profile. If no default exists, it creates `local`; if a default exists, it updates that default profile. Do not search common directories, repository folders, home folders, temp folders, or the whole filesystem for SQLite files. Ask the user for the exact path instead. ### List Profiles ```bash bash /scripts/sqlite_crud.sh list-profiles ``` ### Remove Profile ```bash bash /scripts/sqlite_crud.sh remove-profile --profile old-local ``` ## Inspect Schema List tables: ```bash bash /scripts/sqlite_crud.sh schema --profile local ``` List columns for one table: ```bash bash /scripts/sqlite_crud.sh schema \ --profile local \ --table users ``` ## Query Rows ```bash bash /scripts/sqlite_crud.sh select \ --profile local \ --table users \ --where "email = :email" \ --param email=test@example.com \ --limit 20 ``` Optional fields: - `--columns "id,email,created_at"` - `--order-by created_at` - `--desc` - `--limit 50` If no default profile exists, pass the user-provided file path with `--path`; the script will save it for later use: ```bash bash /scripts/sqlite_crud.sh select \ --path /Users/me/app/data.sqlite \ --table users \ --where "email = :email" \ --param email=test@example.com ``` ## Insert Rows Dry-run first: ```bash bash /scripts/sqlite_crud.sh insert \ --profile local \ --table users \ --value email=test@example.com \ --value name=Test ``` Execute only after explicit confirmation: ```bash bash /scripts/sqlite_crud.sh insert \ --profile local \ --table users \ --value email=test@example.com \ --value name=Test \ --execute ``` ## Update Rows Dry-run first: ```bash bash /scripts/sqlite_crud.sh update \ --profile local \ --table users \ --value name="New Name" \ --where "email = :email" \ --param email=test@example.com ``` Execute only after explicit confirmation: ```bash bash /scripts/sqlite_crud.sh update \ --profile local \ --table users \ --value name="New Name" \ --where "email = :email" \ --param email=test@example.com \ --execute ``` ## Delete Rows Dry-run first: ```bash bash /scripts/sqlite_crud.sh delete \ --profile local \ --table users \ --where "email = :email" \ --param email=test@example.com ``` Execute only after explicit confirmation: ```bash bash /scripts/sqlite_crud.sh delete \ --profile local \ --table users \ --where "email = :email" \ --param email=test@example.com \ --execute ``` ## Raw SQL Use raw SQL for read-only queries when structured commands are too limited: ```bash bash /scripts/sqlite_crud.sh raw-sql \ --profile local \ --sql "SELECT name FROM sqlite_master WHERE type = 'table'" ``` Raw write SQL requires both `--execute` and `--allow-raw-write`: ```bash bash /scripts/sqlite_crud.sh raw-sql \ --profile local \ --sql "UPDATE users SET active = 0 WHERE email = 'test@example.com'" \ --execute \ --allow-raw-write ``` ## Response Shape Success responses include: - `profile` - `operation` - `db_path` - operation-specific fields such as `table`, `where`, or `limit` - `dry_run` for write previews - `rows` for query results - `result` for command metadata or raw SQL output ## Notes - This skill only supports local SQLite files. Use another database skill for MySQL, PostgreSQL, Redis, or MongoDB. - If the user has not configured a profile/default profile, require the user to provide `--path`; do not scan the system for SQLite files. The script saves the provided path as the default profile for later commands. - The script validates table and column identifiers in structured commands and quotes them. - The script substitutes `--param name=value` placeholders into structured `--where` clauses before execution. - SQLite may lock files that are actively used by another process; if a write fails with a lock error, do not retry broad writes without user approval. ## Example Prompts ### Chinese - "配置一个 SQLite profile，名字叫 local，路径是 /Users/me/app/data.sqlite。" - "查看 local 里有哪些表。" - "查询 users 表里 email 是 test@example.com 的记录。" - "把 users 里某个 email 的 name 改掉，先 dry-run。" ### English - "Configure a SQLite profile for a local database file." - "List tables in the default SQLite profile." - "Find a user by email in a local SQLite file." - "Preview updating one SQLite row before executing it."