sqlitex is a non-interactive, flag-based SQLite CLI:

• CRUD operations
• migrations + seeds
• backup/reset helpers
• CSV/JSON export
• DB info / health checks / optimization
• dry-run mode for safe mutations
• simple tuning, explaining and sampling
• optional timeout-based lock handling

It’s ideal for scripts and CI where you want explicit flags instead of interactive shells.

• sqlite3 (required)
• jq (optional, required only for JSON seed files)
• sqlite3 with -json support (optional, required for JSON exports & -json mode)

sudo curl -fsSL "https://raw.githubusercontent.com/infocyph/Toolset/main/Sqlite/sqlitex" \
  -o /usr/local/bin/sqlitex && sudo chmod +x /usr/local/bin/sqlitex

Global options must come before the command:

sqlitex [global options] <command> [command options]

ℹ️ --env is also used in the migration tracking table name: migrations_<ENV_SAFE>.

🔐 --use-lock makes all SQL use .timeout 5000 ms (configurable in the script) to avoid “database is locked” errors in concurrent environments.

🔒 Mutating commands (create-db, create-table, insert, insert-batch, update, delete, migrate, migrate-create, seed, reset, exec with DML/DDL, backup, dump --file, optimize, tune, export-all writing files) all honor --dry-run and will only print what they would do.

Create a new SQLite database file (and parent directory if needed).

sqlitex --db my.db create-db

With dry-run:

sqlitex --db my.db --dry-run create-db
# prints intent, does not create file

Create a table if it doesn’t already exist.

sqlitex --db my.db create-table \
  --table users \
  --columns "id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE"

Insert a single row using a raw VALUES (...) clause.

sqlitex --db my.db insert \
  --table users \
  --values "NULL,'Alice','[email protected]'"

Execute a SQL file containing multiple statements (inserts / DML / DDL).

sqlitex --db my.db insert-batch --file seeds/users.sql

With dry-run, it only prints the file that would be executed.

Query data and print as a table, CSV or JSON.

# Pretty-print with headers
sqlitex --db my.db select --table users --where "id > 5"

# Export result to CSV
sqlitex --db my.db select \
  --table users \
  --where "id > 5" \
  --export csv

# Export result to JSON (requires sqlite3 with -json support)
sqlitex --db my.db select \
  --table users \
  --where "email LIKE '%@example.com'" \
  --export json

CSV/JSON files are stored in --export-path (default: ./exports).

Update rows; a backup is taken before the change (unless --dry-run).

sqlitex --db my.db update \
  --table users \
  --set "name='Bob'" \
  --where "id=1"

With --dry-run, sqlitex prints the UPDATE query instead of executing it.

Delete rows; a backup is taken before the change (unless --dry-run).

sqlitex --db my.db delete \
  --table users \
  --where "last_login IS NULL"

Show CREATE TABLE definition.

sqlitex --db my.db schema --table users

Show the first N rows from a table.

# First 10 rows (default)
sqlitex --db my.db sample --table users

# First 3 rows
sqlitex --db my.db sample --table users --limit 3

Run arbitrary SQL or a SQL file.

# Single statement
sqlitex --db my.db exec --sql "VACUUM;"

# Multi-statement script
sqlitex --db my.db exec --file scripts/maintenance.sql

Note: With --dry-run, the SQL or file path is printed instead of executed.

Note: You must choose either --sql or --file (the CLI enforces this).

List all user tables (excluding internal sqlite_ tables):

sqlitex --db my.db tables

Migrations are simple .sql files in the migrations directory.

• Up migrations: *.sql
• Down migrations: matching *.down.sql

Applied migrations are tracked in a table named:

migrations_<ENV_SAFE>

Where <ENV_SAFE> is ENV with non-alphanumeric chars replaced by _.

Apply all pending migrations:

sqlitex --db my.db migrate

Rollback the last migration:

sqlitex --db my.db migrate --rollback

With --dry-run, sqlitex prints which files would be applied or rolled back (and the insert/delete into the migration table) without executing anything.

Generate a timestamped up/down migration pair:

# Name can be argument or flag
sqlitex --db my.db migrate-create add_users_table

# or
sqlitex --db my.db migrate-create --name add_users_table

Creates (in --migrations-path):

• YYYYMMDD_HHMMSS_add_users_table.sql
• YYYYMMDD_HHMMSS_add_users_table.down.sql

Both with minimal comment skeletons.

With --dry-run, only the intended file paths are printed.

Seed files live in --seeds-path and are handled by extension:

• *.sql → executed as SQL
• *.csv → imported as CSV into table name derived from filename (e.g. users_seed.csv → table users)
• *.json → imported via jq → CSV → sqlite3 .import, table derived similarly

JSON seeding requires jq to be installed.

sqlitex --db my.db seed

This iterates over all files in the seeds directory and applies them in filename order.

Full reset: backup, drop DB, recreate, migrate & seed.

sqlitex --db my.db reset

Steps:

1. Ensure DB exists
2. Backup DB file
3. Delete DB file
4. Create an empty DB file
5. Run migrate
6. Run seed

With --dry-run, nothing is touched — only the planned actions (including backup path) are printed.

Create a timestamped copy of the DB in --backup-path:

sqlitex --db my.db backup
# e.g. ./db_backups/my_20251203_120304.db

Full .dump with schema + data:

# Dump to stdout
sqlitex --db my.db dump

# Dump to file
sqlitex --db my.db dump --file backup.sql

With --dry-run, the file path is printed but not written.

Export all user tables to CSV or JSON in --export-path:

# All tables to CSV
sqlitex --db my.db export-all --format csv

# All tables to JSON (requires sqlite3 -json)
sqlitex --db my.db export-all --format json

Each table becomes EXPORT_DIR/<table>.csv or EXPORT_DIR/<table>.json.

With --dry-run, it just prints the paths it would write.

Quick DB metadata snapshot:

sqlitex --db my.db info

Shows:

• SQLite version
• DB size (bytes + human-readable)
• Page size & page count
• Journal mode
• Foreign key status
• User table count and up to 20 table names

Run health checks:

sqlitex --db my.db doctor

Performs:

• PRAGMA integrity_check;

• PRAGMA foreign_key_check;

• Checks for:

  • Migration table existing but no migration files
  • Missing seeds directory
  • Large DB (> 100MB) not in WAL mode (recommends tune --profile prod)

Returns non-zero exit code if integrity or FK checks fail.

Backup then run standard optimizations:

sqlitex --db my.db optimize

Steps:

1. Backup DB file
2. VACUUM;
3. ANALYZE;
4. PRAGMA optimize; (ignored if unsupported)

With --dry-run, only the planned operations are printed.

View query plan to help with indexing and performance:

sqlitex --db my.db explain --sql "SELECT * FROM users WHERE email = '[email protected]';"

Under the hood uses:

EXPLAIN QUERY PLAN <your_query>;

Apply simple pragma-based tuning profiles:

# Development profile
sqlitex --db my.db tune --profile dev

# Production-ish profile
sqlitex --db my.db tune --profile prod

# Safe minimal profile (default)
sqlitex --db my.db tune          # same as --profile safe

Profiles:

• dev:

  • PRAGMA journal_mode = WAL;
  • PRAGMA synchronous = NORMAL;
  • PRAGMA foreign_keys = ON;
  • PRAGMA temp_store = MEMORY;

• prod:

  • PRAGMA journal_mode = WAL;
  • PRAGMA synchronous = FULL;
  • PRAGMA foreign_keys = ON;

• safe (default / fallback):

  • PRAGMA foreign_keys = ON;

With --dry-run, the PRAGMAs are printed but not applied.

# Create DB and basic schema
sqlitex --db app.db create-db
sqlitex --db app.db create-table \
  --table users \
  --columns "id INTEGER PRIMARY KEY, name TEXT, email TEXT UNIQUE"

# Seed using CSV & JSON
sqlitex --db app.db seed

# See a quick data sample
sqlitex --db app.db sample --table users --limit 5

# Export everything for debugging
sqlitex --db app.db export-all --format csv

# Check DB health
sqlitex --db app.db doctor

# Optimize DB (VACUUM + ANALYZE)
sqlitex --db app.db optimize

# See how a query will run
sqlitex --db app.db explain --sql "SELECT * FROM users WHERE email = '[email protected]';"

# Tune for dev, with lock handling enabled
sqlitex --db app.db --use-lock tune --profile dev

# Dry-run a dangerous update to see the SQL only
sqlitex --db app.db --dry-run update \
  --table users \
  --set "is_active = 0" \
  --where "last_login IS NULL"

# Using env-specific migrations/seeds
sqlitex --db prod.db --env staging \
  --migrations-path ./db/migrations \
  --seeds-path ./db/seeds \
  migrate