MidQL

Fast, safety-first PostgreSQL/MySQL CLI for developers — schema-aware autocomplete, natural-language shortcuts, and previews before anything destructive runs.

MidQL is a modern alternative to psql/mysql built around two ideas:

• Speed — raw SQL is first-class with schema-aware tab completion, and a rule-based natural-language layer turns show users where rank 20 into SQL faster than you can type the full SELECT. Cross-table filters like select users which has money > 20 discover the JOIN path from foreign keys automatically.
• Control — DELETE/UPDATE/DROP show the affected rows and ask for confirmation before running, production connections are visually distinct and can be locked read-only, and every statement is written to a local audit history.

Everything runs offline: the natural-language engine is rule-based and schema-driven. No API keys, no telemetry.

Install

npm install -g midql-cli

Requires Node 18+. The installed command is midql.

Quickstart

midql postgres://user:pass@localhost:5432/mydb

midql (mydb:mydb)> show users where rank > 10
→ SELECT * FROM "users" WHERE "rank" > $1   $1=10
┌────┬───────┬───────────────────┬──────┐
│ id │ name  │ email             │ rank │
...

midql (mydb:mydb)> SELECT count(*) FROM orders JOIN users ON users.id = orders.user_id;

Plain English and raw SQL work side by side — input that starts with a SQL keyword and looks like SQL runs as-is; everything else goes through the natural-language engine. The generated SQL is always previewed under the input line before you press Enter, and echoed with the results after.

Natural language cookbook

| You type | MidQL runs | |---|---| | show all users | SELECT * FROM users | | search users which has rank 20 | SELECT * FROM users WHERE rank = 20 | | show name and email of users | SELECT name, email FROM users | | select users which has money > 20 | SELECT users.* FROM users JOIN accounts ON … WHERE accounts.money > 20 (join inferred from foreign keys) | | show users whose email contains gmail | … WHERE email ILIKE '%gmail%' | | show orders where status in pending, paid | … WHERE status IN (…) | | show users where rank between 5 and 10 | … WHERE rank BETWEEN 5 AND 10 | | show users sorted by rank descending first 10 | … ORDER BY rank DESC LIMIT 10 | | show user john / show user 42 | name / primary-key lookup | | count users / how many users have rank 20 | SELECT COUNT(*) … | | average money of users | SELECT AVG(accounts.money) FROM users JOIN accounts … | | add user with name john and rank 5 | INSERT INTO users (name, rank) VALUES (…) | | update users set rank to 10 where name is john | UPDATE users SET rank = … WHERE … | | change rank of user john to 10 | same as above | | delete users which has rank 0 | DELETE FROM users WHERE … (with preview + confirmation) | | create table pets with name text, age number, owner references users | CREATE TABLE pets (id SERIAL PRIMARY KEY, …) | | drop table pets | DROP TABLE pets (typed-name confirmation) | | describe users / what is in users | table structure | | show tables | table list |

Operator words: is, is not, greater than / more than / over / above, less than / under / below, at least, at most, contains, starts with, ends with, between … and …, in a, b or c, is null, is not null. Combine with and/or (and binds tighter).

Table and column names are matched fuzzily against the live schema — singular/plural (user → users), underscores (firstname → first_name), and small typos (usres → users) all resolve. When a reference is genuinely ambiguous, MidQL lists the candidates instead of guessing; qualify with table.column to be explicit.

All values are sent as bound parameters — never interpolated into SQL — so injection through the natural-language layer is structurally impossible.

Safety model

• destructive (DELETE/UPDATE with WHERE): MidQL first runs a count and a 10-row sample of the affected rows, shows them, and asks Proceed? [y/N] (default No).
• catastrophic (DROP TABLE, TRUNCATE, DELETE/UPDATE without WHERE): confirmation requires typing the table name. Statements without a WHERE clause are called out explicitly.
• \safe off reduces checks to a one-line warning — except on connections tagged prod, where safety is always enforced and the prompt turns red (midql (prod:mydb)!>).
• \readonly on (or readonly: true in the profile) blocks every write before it reaches the driver.
• One-shot mode (midql query) refuses destructive statements without --yes and prints the row count it would have affected.
• Every input is appended to ~/.midql/history.jsonl with timestamp, connection, generated SQL, and outcome — a local audit trail.

REPL reference

• Results adapt to your terminal: columns shrink to fit the window, and when a row is too wide to fit at all, MidQL switches to a vertical record view (one column value pair per line) automatically.
• Input starting with a SQL keyword runs as raw SQL on a pinned session connection, so BEGIN / COMMIT / ROLLBACK work naturally; an open transaction shows a [tx] badge in the prompt.
• Tab accepts the top completion (verbs → tables → columns → operators, context-aware in both NL and SQL modes). Columns reachable through a foreign key are annotated (accounts, joined).
• ↑/↓ history, Ctrl+R history search, Ctrl+C clear line (twice to exit), Ctrl+A/E home/end, Esc dismiss.

| Command | Description | |---|---| | \c <profile\|url> [alias] | connect (multiple connections at once) | | \profiles | list saved profiles | | \profile add [name] | create a profile with a guided step-by-step wizard (masked password input) | | \profile remove <name> | delete a saved profile | | \use <alias> | switch the active connection | | \connections | list open connections | | \disconnect <alias> | close a connection | | \tables, \d <table> | list tables / describe a table | | \refresh | reload the schema cache | | \sql | lock raw SQL mode (skip NL parsing) | | \safe on\|off, \readonly on\|off | toggle safety / read-only | | \history [search] | show recent inputs | | \export csv\|json <file> | export the last result | | \backup [file] [sql] | pg_dump / mysqldump the active database | | \restore <file> | restore from a backup (typed confirmation) | | \explain | re-run the last SELECT with EXPLAIN, summarized in plain English | | \help, \q | help / quit |

Profiles and one-shot commands

midql profiles add main --dialect postgres --host db.example.com --database app \
  --user deploy --password-env PGPASSWORD --env prod --readonly

midql profiles list
midql main                                  # open the REPL on a saved profile

midql query "count users" --db main
midql query "delete users which has rank 0" --db dev --yes
midql query "show users" --db main --format json > users.json

midql backup --db main                      # pg_dump -Fc → midql-app-<timestamp>.dump
midql restore backup.dump --db dev --yes

Environment tags (--env dev|staging|prod) color the prompt and harden confirmations on prod. Backup/restore shells out to pg_dump/pg_restore/psql/mysqldump/mysql; if a tool is missing MidQL tells you how to install it or set an explicit path in the config.

Configuration

~/.midql/config.json:

{
  "profiles": [],
  "safety": "on",
  "maxJoinDepth": 3,
  "tools": { "pgDumpPath": null, "pgRestorePath": null, "psqlPath": null, "mysqldumpPath": null, "mysqlPath": null }
}

Set MIDQL_HOME to relocate the config directory.

Credential storage

Saved passwords are encrypted with AES-256-GCM using a random key generated at first run into ~/.midql/.key (file mode 600). This protects against casual disclosure of the config file — it is not a defense against an attacker with full access to your OS account. Prefer --password-env to reference an environment variable, or omit the password entirely for URL-based ad-hoc connections.

Development

npm install
npm test                                     # unit tests (no database needed)
docker compose -f docker/docker-compose.yml up -d --wait
npm run test:int                             # integration tests against seeded PG 16 + MySQL 8
npm run build

The natural-language engine is pure functions over a schema snapshot — see test/fixtures/ for the schemas the unit tests run against.

License

MIT © Eser Sariyar