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-<UTC timestamp>-<pid>.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/<profile>.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:

~/.config/sqlite-crud/profiles/

The default profile name is saved at:

~/.config/sqlite-crud/default_profile

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

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

Remove Profile

bash <skill-path>/scripts/sqlite_crud.sh remove-profile --profile old-local

Inspect Schema

List tables:

bash <skill-path>/scripts/sqlite_crud.sh schema --profile local

List columns for one table:

bash <skill-path>/scripts/sqlite_crud.sh schema \
  --profile local \
  --table users

Query Rows

bash <skill-path>/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 <skill-path>/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 <skill-path>/scripts/sqlite_crud.sh insert \
  --profile local \
  --table users \
  --value email=test@example.com \
  --value name=Test

Execute only after explicit confirmation:

bash <skill-path>/scripts/sqlite_crud.sh insert \
  --profile local \
  --table users \
  --value email=test@example.com \
  --value name=Test \
  --execute

Update Rows

Dry-run first:

bash <skill-path>/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 <skill-path>/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 <skill-path>/scripts/sqlite_crud.sh delete \
  --profile local \
  --table users \
  --where "email = :email" \
  --param email=test@example.com

Execute only after explicit confirmation:

bash <skill-path>/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 <skill-path>/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 <skill-path>/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

• "Configure a SQLite profile named local, with the path /Users/me/app/data.sqlite."
• "Check which tables are in local."
• "Query the record in the users table where email is test@example.com ."
• "Change the name for a certain email in users, dry-run first."

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."