@promakeai/dbcli

Database CLI for AI agents. Simple, idempotent commands with JSON output.

Installation

npm install -g @promakeai/dbcli

Commands

| Command | Description | |---------|-------------| | generate | Sync database schema + generate TypeScript types | | list | Query records with filters, pagination, ordering | | get | Get single record by ID | | create | Create record(s) from JSON or file | | update | Update record(s) | | delete | Delete record(s) with confirmation | | count | Count records with optional filters | | tables | List all tables in database | | schema | Show table schema | | exec | Execute raw SQL (data only — DDL is rejected) | | usage | Print the self-describing command catalog (--json for agents) |

Global Options

-d, --database <path>   # Database file (resolution: --database > ./database.db if exists > ./public/data/database.db if exists > create/use ./public/data/database.db)
-a, --adapter <type>    # Adapter: sqlite (default), rest-api
-f, --format <format>   # Output: json, table, csv (default: json)
-s, --schema <json|path> # Schema as inline JSON string or .json file
--lang <code>           # Language for translations (e.g., tr, en)
--api-url <url>         # REST API base URL (auto-built from .env if omitted: DB_API_URL, else VITE_TENANT_UUID + VITE_PROMAKE_ENV)
--api-key <token>       # API auth token (auto-reads from .env if omitted)

REST API Adapter

Use --adapter rest-api to connect to a remote REST API instead of a local SQLite file:

dbcli list products --adapter rest-api --api-url https://api.example.com

Base URL resolution order:

1. --api-url <url> flag (explicit)
2. DB_API_URL from .env or .env.local
3. https://{VITE_TENANT_UUID}.{suffix} — the suffix comes from the plane in VITE_PROMAKE_ENV: testb -> backend.promake.ai, prodb -> backend-test.promake.ai, devx / devb -> backend-dev.promake.ai

Each variable is resolved across both env files independently, .env winning over .env.local per key. The plane has one more fallback: if neither env file sets VITE_PROMAKE_ENV, an exported VITE_PROMAKE_ENV from the environment is used, so a project's own config always outranks the ambient one but an ambient plane still works where the files are silent.

A tenant UUID whose plane cannot be resolved from either source is an error rather than a guess at production — pass --api-url or set DB_API_URL for a plane this table does not know.

Token resolution order:

1. --api-key <token> flag (explicit)
2. VITE_DB_ADMIN_TOKEN from .env or .env.local
3. DB_ADMIN_TOKEN from .env or .env.local
4. No token (unauthenticated / anon)

generate

Sync database schema and generate TypeScript types.

dbcli generate [options]

Options:
  -s, --schema <json|path>  Schema as inline JSON string or .json file path
  -o, --output <dir>     Output directory (default: ./src/db)
  --allow-drop           Forward allowDrop=true to the REST backend (destructive
                         table-drop opt-in; does not authorize column drops).
  --allow-column-drop    Forward allowColumnDrop=true to the REST backend
                         (destructive column-drop opt-in; does not authorize
                         table drops). Both are no-ops on SQLite; default sync
                         is additive-only.

Note: Use global `-d, --database <path>` to select the database file.

The schema is validated before anything is synced; a structural problem returns a VALIDATION error (code) with the failing fields listed in message. With --format json (default) the full machine envelope is emitted, including a changed ledger (one entry per synced table plus every file written) and a next hint. --format table / csv print the human-friendly summary instead.

Examples

# From JSON schema file
dbcli generate --schema ./schema.json --database ./app.db

# Inline JSON schema
dbcli generate --schema '{"name":"myapp","languages":["en"],"tables":{...}}'

# Custom output directory
dbcli generate --schema ./schema.json --output ./src/db/generated

Generated files

dbcli generate writes:

• schema.json - Runtime JSON schema
• types.ts - Generated TypeScript interfaces

Schema File (JSON)

{
  "name": "myapp",
  "languages": ["en", "tr"],
  "defaultLanguage": "en",
  "tables": {
    "users": {
      "$permissions": {
        "anon": ["read"],
        "user": ["read"],
        "admin": ["read", "create", "update", "delete"]
      },
      "id": { "type": "id" },
      "email": { "type": "string", "required": true, "unique": true },
      "name": { "type": "string", "required": true }
    },
    "products": {
      "$permissions": {
        "anon": ["read"],
        "user": ["read"],
        "admin": ["read", "create", "update", "delete"]
      },
      "id": { "type": "id" },
      "sku": { "type": "string", "required": true, "unique": true },
      "price": { "type": "decimal", "required": true },
      "stock": { "type": "int", "default": 0 },
      "name": { "type": "string", "translatable": true, "required": true },
      "description": { "type": "text", "translatable": true },
      "categoryId": { "type": "int", "ref": "categories" },
      "tags": { "type": ["string"] },
      "metadata": { "type": { "color": "string", "weight": "number" } },
      "variants": { "type": [{ "sku": "string", "price": "number" }] }
    }
  }
}

$permissions defines role-based access control as table metadata (not a DB column). Roles: anon, user, admin. Actions: create, read, update, delete.

list

Query multiple records.

dbcli list <table> [options]

Options:
  -w, --where <json>     Filter conditions (MongoDB-style)
  -l, --limit <n>        Max records to return
  -o, --offset <n>       Skip records
  --order <field:dir>    Sort by field (e.g., "name:ASC", "price:DESC")
  -s, --select <fields>  Select specific fields (comma-separated)
  -c, --count            Return count only
  -p, --populate <refs>  Resolve foreign key references
  --lang <code>          Language for translations

Examples

# List all users
dbcli list users

# With pagination
dbcli list users --limit 10 --offset 20

# Filter with MongoDB-style operators
dbcli list products --where '{"price":{"$gt":100}}'
dbcli list products --where '{"stock":{"$between":[10,100]}}'
dbcli list users --where '{"role":{"$in":["admin","moderator"]}}'
dbcli list products --where '{"$or":[{"stock":0},{"price":{"$lt":10}}]}'
dbcli list products --where '{"tags":{"$contains":"sale"}}'
dbcli list products --where '{"tags":{"$containsAny":["sale","new"]}}'

# Sort and select fields
dbcli list products --order "price:DESC" --select "id,name,price"

# Resolve references
dbcli list posts --populate "userId,categoryId"

# Multi-language
dbcli list products --lang tr

# Count only
dbcli list users --where '{"active":true}' --count

# Output formats
dbcli list users --format table
dbcli list users --format csv

get

Get single record by ID.

dbcli get <table> <id> [options]

Options:
  -p, --populate <refs>  Resolve foreign key references
  --lang <code>          Language for translations

Examples

dbcli get users 1
dbcli get posts 1 --populate "userId"
dbcli get products 1 --lang tr

create

Create new record(s).

dbcli create <table> [options]

Options:
  -D, --data <json>          Record data as JSON
  --file <path>              Load from JSON or CSV file
  --input-format <format>    File format: json, csv (auto-detected)
  --batch                    Batch mode for multiple records
  --ignore                   Skip rows that violate an existing UNIQUE/PK constraint (single or batch)
  --translations <json>      Translation data

Examples

# Single record
dbcli create users --data '{"email":"[email protected]","name":"John"}'

# With translations
dbcli create products --data '{
  "sku": "SHIRT-001",
  "price": 29.99
}' --translations '{
  "en": {"name": "Blue Shirt", "description": "Cotton shirt"},
  "tr": {"name": "Mavi Gomlek", "description": "Pamuklu gomlek"}
}'

# Batch from JSON file
dbcli create users --file ./users.json --batch

# Batch from CSV file
dbcli create users --file ./users.csv --batch

# Skip rows that hit an existing UNIQUE/PK constraint (batch)
dbcli create users --file ./users.json --batch --ignore

# Same idempotent skip for a single record
dbcli create users --data '{"email":"[email protected]","name":"John"}' --ignore

# Disable the transaction wrapper for batch
dbcli create users --file ./users.json --batch --no-atomic

Note: translatable fields in --translations are always persisted to {table}_translations. Main-table cache writes happen only for columns that actually exist on the main table.

Idempotent seeding (--ignore)

--ignore is constraint-based, not content-based dedup. It skips a row only when that row would violate an existing constraint, either the primary key or a column declared unique: true in the schema. If your natural key is not unique, nothing gets skipped, so declare it unique first:

# 1. Make the natural key unique in your schema (e.g. email: { unique: true }),
#    then dbcli generate to apply it.
# 2. Re-running the same seed is now a no-op for rows that already exist:
dbcli create users --file ./users.json --batch --ignore

Rollout note (REST tenants): enforcing unique: true on an EXISTING, already seeded table requires a backend with PromakeAi/customer-backend#50. On older backends the declaration is a silent no-op and --ignore dedupes nothing. The duplicates-only guarantee below is #50-gated too: pre-#50 backends still run INSERT OR IGNORE server-side, so a NOT NULL / CHECK violation under --ignore can come back as ignored for a row that never landed. Treat ignored as authoritative only on #50+ backends (SQLite is always exact). dbcli generate also does not yet surface the backend's unique_skipped / warnings fields (the REST adapter discards the generate response body), so gate on the raw POST /database/generate response if you need the dedup signal.

--ignore works for a single record too (routed through the same skip) and then returns the batch shape with created: 0|1: { success: true, created, ids, ... }, plus ignored when the row was skipped (in batch mode created ranges 0..N). The skip applies ONLY to UNIQUE/PK conflicts: a row that violates any other constraint (NOT NULL, CHECK) is a hard error (code: CONSTRAINT), never a skip (on REST tenants this requires a #50+ backend — see the rollout note above). Note that with --ignore, records containing translatable fields do NOT get _translations rows written (the same behavior as --batch today), so --ignore and --translations cannot be combined (in any mode). Passing multiple records without --batch is an error rather than a silent first-row-only insert, with or without --ignore.

Batch partial-failure (--batch --no-atomic): a batch is transaction-wrapped by default, so any failure rolls the whole batch back and returns a hard error (nothing landed). With --no-atomic the rows are not wrapped, so a mid-batch failure leaves an unknown number of leading rows already committed. In that case the command does not throw: it returns success: true with partial: true, a warnings note (with created: 0 as a sentinel — not the real count), and a next hint pointing at the verify command. Re-run the verify command to see the true state before retrying.

File Formats

JSON file (array of objects):

[
  {"email": "[email protected]", "name": "John"},
  {"email": "[email protected]", "name": "Jane"}
]

CSV file:

email,name
[email protected],John
[email protected],Jane

update

Update existing record(s).

dbcli update <table> [id] [options]

Options:
  -D, --data <json>          Update data as JSON
  --file <path>              Load updates from JSON file
  --batch                    Batch mode for multiple records
  --lang <code>              Update translation for single language
  --translations <json>      Update translations for multiple languages

Examples

# Single record (main table fields)
dbcli update users 1 --data '{"name":"Jane Doe"}'

# Update single language translation
dbcli update products 1 --data '{"name":"Yeni Isim"}' --lang tr

# Update multiple languages at once
dbcli update products 1 --translations '{
  "en": {"name": "New Name", "description": "New desc"},
  "tr": {"name": "Yeni Isim", "description": "Yeni aciklama"}
}'

# Add new language translation
dbcli update products 1 --translations '{"de": {"name": "Deutsches Produkt"}}'

# Batch update
dbcli update users --file ./updates.json --batch

Batch file format:

[
  {"id": 1, "data": {"name": "Updated Name 1"}},
  {"id": 2, "data": {"name": "Updated Name 2"}}
]

delete

Delete record(s).

dbcli delete <table> <id> [options]

Options:
  -y, --yes              Skip confirmation prompt
  --batch                Batch delete mode
  --ids <ids>            Comma-separated IDs for batch delete
  --file <path>          JSON file with array of IDs

Examples

# Single record (with confirmation)
dbcli delete users 1

# Skip confirmation
dbcli delete users 1 --yes

# Batch delete by IDs
dbcli delete users --batch --ids "1,2,3,4,5"

# Batch delete from file
dbcli delete users --batch --file ./delete-ids.json --yes

Non-interactive safety: delete asks for confirmation only on a real TTY. When stdin is not a TTY (an agent or subprocess with piped stdio), it does not prompt — that would hang forever. Instead it fails fast with a MISSING_PREREQ error and a next hint to re-run with --yes. Always pass --yes from scripts and agents.

count

Count records in a table.

dbcli count <table> [options]

Options:
  -w, --where <json>     Filter conditions

Examples

dbcli count users
dbcli count products --where '{"stock":{"$gt":0}}'
dbcli count orders --where '{"status":"pending"}'

tables

List all tables in database.

dbcli tables

Examples

# List all tables
dbcli tables

schema

Show table schema.

dbcli schema <table>

Examples

dbcli schema users
dbcli schema products --format table

exec

Execute raw SQL.

dbcli exec [sql] [options]

Options:
  -p, --params <json>    Query parameters as JSON array
  --file <path>          Load SQL from file

Examples

# Inline query
dbcli exec "SELECT * FROM users WHERE id = ?" --params '[1]'

# From file
dbcli exec --file ./query.sql

# Multiple statements
dbcli exec "UPDATE users SET active = 1; SELECT COUNT(*) FROM users"

DDL guard: exec is for data only (SELECT / INSERT / UPDATE / DELETE). Schema-changing DDL (CREATE / ALTER / DROP / TRUNCATE, including forms like CREATE UNIQUE INDEX or CREATE OR REPLACE VIEW, and DDL hidden behind leading SQL comments) is rejected before execution with a VALIDATION error and a next hint. All schema changes must go through dbcli generate so src/db/schema.json stays in sync with the database.

usage

Print the self-describing command catalog. The catalog is built from the live command tree, so it never goes stale.

dbcli usage         # human-readable: happy path, every command + flags, notes
dbcli usage --json  # JSON catalog for agents: { happyPath, notes, commands }

Each command entry carries its name, aliases, arguments (with required-ness), and options. Use --json to introspect the full surface programmatically.

Query Operators

MongoDB-style operators for --where:

| Operator | Example | SQL | |----------|---------|-----| | $eq | {"status":"active"} | = 'active' | | $ne | {"status":{"$ne":"deleted"}} | != 'deleted' | | $gt | {"price":{"$gt":100}} | > 100 | | $gte | {"price":{"$gte":100}} | >= 100 | | $lt | {"stock":{"$lt":10}} | < 10 | | $lte | {"stock":{"$lte":10}} | <= 10 | | $in | {"status":{"$in":["a","b"]}} | IN ('a','b') | | $nin | {"role":{"$nin":["admin"]}} | NOT IN ('admin') | | $like | {"name":{"$like":"%john%"}} | LIKE '%john%' | | $notLike | {"name":{"$notLike":"%test%"}} | NOT LIKE '%test%' | | $between | {"price":{"$between":[10,100]}} | BETWEEN 10 AND 100 | | $isNull | {"deleted":{"$isNull":true}} | IS NULL | | $contains | {"tags":{"$contains":"sale"}} | json_each(...) | | $containsAny | {"tags":{"$containsAny":["sale","new"]}} | json_each(...) | | $and | {"$and":[{...},{...}]} | (...) AND (...) | | $or | {"$or":[{...},{...}]} | (...) OR (...) | | $nor | {"$nor":[{...},{...}]} | NOT ((...) OR (...)) | | $not | {"age":{"$not":{"$lt":18}}} | NOT (age < 18) |

Multi-Language

For translatable fields:

# Query with language (with fallback)
dbcli list products --lang tr

# Create with translations
dbcli create products --data '{
  "sku": "SHIRT-001",
  "price": 29.99
}' --translations '{
  "en": {"name": "Blue Shirt", "description": "Cotton shirt"},
  "tr": {"name": "Mavi Gomlek", "description": "Pamuklu gomlek"}
}'

# Update single language (--lang)
dbcli update products 1 --data '{"name":"Yeni Isim"}' --lang tr

# Update multiple languages at once (--translations)
dbcli update products 1 --translations '{
  "en": {"name": "New Name"},
  "tr": {"name": "Yeni Isim"}
}'

# Get all translations
dbcli exec "SELECT * FROM products_translations WHERE product_id = 1"

Response Format

All commands return a JSON envelope. success is the primary contract; on top of it, every response can carry optional agent-experience metadata:

| Field | Type | Meaning | |-------|------|---------| | next | string[] | Recovery / follow-up commands the agent should consider next. | | warnings | string[] | Non-fatal problems or partial-success notes. | | changed | Change[] | Side-effect ledger: { action: "created" \| "updated" \| "deleted", target, kind?, count? } per affected table/file/rows. | | partial | boolean | Primary goal met but a secondary step failed. Still a success (exit 0). |

// Success - list
{
  "success": true,
  "table": "users",
  "count": 10,
  "data": [{ "id": 1, "name": "John" }]
}

// Success - create (with side-effect ledger + next hint)
{
  "success": true,
  "table": "users",
  "id": 1,
  "data": { "id": 1, "name": "John" },
  "changed": [{ "action": "created", "target": "users", "kind": "rows", "count": 1 }],
  "next": ["dbcli list users --limit 5   # verify the inserted rows"]
}

// Error (classified code + recovery hint)
{
  "success": false,
  "error": "no such table: users",
  "code": "NOT_FOUND",
  "table": "users",
  "action": "list",
  "next": ["dbcli list users   # verify the table/record exists"]
}

Error codes & exit codes

Errors carry a stable code (DbErrorCode) an agent can branch on instead of parsing the message. The process exit code matches the classified code, so shell callers get the same signal:

| code | Exit | Raised when | |--------|------|-------------| | OK | 0 | Success (including a partial: true success). | | GENERIC | 1 | Unclassified failure / user-cancelled. | | BAD_ARGS | 2 | Bad arguments, invalid JSON in --data / --params, or a REST record id the URL parser rewrites (see the orm README, "Record ids the adapter refuses"). | | MISSING_PREREQ | 3 | A required precondition is unmet (e.g. delete without --yes on a non-TTY). | | NOT_FOUND | 4 | Table / column / record / file does not exist. | | VALIDATION | 5 | Schema validation failed, or DDL was attempted via exec. | | CONSTRAINT | 6 | Unique / foreign-key / not-null / duplicate violation. | | BACKEND_REJECTED | 7 | REST backend rejected the request (HTTP 4xx, forbidden, unauthorized). | | NETWORK | 8 | Connectivity failure (connection refused, timeout, fetch failed). | | IO | 9 | I/O failure. |

A successful command (even a partial success) always exits 0; only a hard failure exits non-zero with its classified code. In json mode (default) the full envelope — code, next, warnings, changed, partial — is printed to stdout; in table / csv mode the machine output stays on stdout and the human warnings / next notes are written to stderr.

SQLite Features

• WAL Mode - Enabled by default for better concurrency
• Transactions - Batch operations use transactions
• Foreign Keys - Enabled with PRAGMA foreign_keys = ON
• JSON Support - JSON fields stored as TEXT, parsed on read

Development

# Install dependencies
bun install

# Run in development mode
bun run dev list users -d ./test.db

# Run tests
bun test

# Build for current platform
bun run build

# Build for all platforms
bun run build:all

Related Packages

• @promakeai/orm - Core ORM with query builder
• @promakeai/dbreact - React hooks and providers

License

MIT