npm package: @promakeai/dbcli
Description: Database CLI tool
Author: Promake Inc.
Latest version: 2.2.2

# @promakeai/dbcli

Database CLI for AI agents. Simple, idempotent commands with JSON output.

## Installation

```bash
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

```bash
-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:

```bash
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.

```bash
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

```bash
# 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)

```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.

```bash
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

```bash
# 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.

```bash
dbcli get <table> <id> [options]

Options:
  -p, --populate <refs>  Resolve foreign key references
  --lang <code>          Language for translations
```

### Examples

```bash
dbcli get users 1
dbcli get posts 1 --populate "userId"
dbcli get products 1 --lang tr
```

---

## create

Create new record(s).

```bash
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

```bash
# Single record
dbcli create users --data '{"email":"john@example.com","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):
```json
[
  {"email": "john@example.com", "name": "John"},
  {"email": "jane@example.com", "name": "Jane"}
]
```

**CSV file**:
```csv
email,name
john@example.com,John
jane@example.com,Jane
```

---

## update

Update existing record(s).

```bash
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

```bash
# 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**:
```json
[
  {"id": 1, "data": {"name": "Updated Name 1"}},
  {"id": 2, "data": {"name": "Updated Name 2"}}
]
```

---

## delete

Delete record(s).

```bash
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

```bash
# 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.

```bash
dbcli count <table> [options]

Options:
  -w, --where <json>     Filter conditions
```

### Examples

```bash
dbcli count users
dbcli count products --where '{"stock":{"$gt":0}}'
dbcli count orders --where '{"status":"pending"}'
```

---

## tables

List all tables in database.

```bash
dbcli tables
```

### Examples

```bash
# List all tables
dbcli tables
```

---

## schema

Show table schema.

```bash
dbcli schema <table>
```

### Examples

```bash
dbcli schema users
dbcli schema products --format table
```

---

## exec

Execute raw SQL.

```bash
dbcli exec [sql] [options]

Options:
  -p, --params <json>    Query parameters as JSON array
  --file <path>          Load SQL from file
```

### Examples

```bash
# 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:

```bash
# 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:

```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

```bash
# 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](../orm) - Core ORM with query builder
- [@promakeai/dbreact](../dbreact) - React hooks and providers

## License

MIT