@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 |

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 (required for rest-api adapter)
--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

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)

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

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 duplicates (INSERT OR IGNORE)
  --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

# Ignore duplicates
dbcli create users --file ./users.json --batch --ignore

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.

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

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"

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 JSON:

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

// Success - create
{
  "success": true,
  "action": "create",
  "table": "users",
  "data": {"id": 1, "name": "John"},
  "id": 1
}

// Success - batch create
{
  "success": true,
  "action": "createMany",
  "table": "users",
  "created": 5,
  "ids": [1, 2, 3, 4, 5]
}

// Error
{
  "success": false,
  "action": "list",
  "table": "users",
  "error": "Table 'users' not found"
}

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