---
title: "Database (Live Tables) in the CLI"
canonical: https://trickest.com/docs/developer-tools/cli/database
description: "List Live Tables, inspect schemas, and query data with TQL from the trickest CLI."
---

# Database (Live Tables) in the CLI

The **`database`** family manages **Live Tables**, structured datasets populated
by workflow runs and solutions. From the CLI you list tables, inspect schemas,
preview rows, and run **TQL** (Trickest Query Language) filters.

<Info>
**TQL in the UI:** The Query tab in the app uses the same filter language. See
[Querying](/docs/using-the-app/database-management/querying) for UI-focused
examples and operator tables.
**Concepts:** [Solutions & Database](/docs/key-concepts/solutions-database).
</Info>

## Command summary

| Subcommand | Description |
|---|---|
| `database ls` | List tables |
| `database get <table>` | Table detail view |
| `database schema <table>` | Column types and constraints |
| `database preview <table>` | First N rows |
| `database query <expr>` | Run a TQL filter |
| `database live <table>` | Create a live table connection |
| `database detect` | Scan latest run outputs for JSONL tables |
| `database rm <table>` | Delete a table |

## Listing and inspecting

### `database ls`

The listing is workflow-scoped. Select a workflow first, or pass
`--filter-workflow <id>`. With neither an explicit workflow nor active context,
the command fails instead of returning every table in the vault.

```bash
trickest database ls
```
```json
[{"id":"1a66b80e-…","name":"merge-all_merge-all","status":"live","schema":{"fields":[{"name":"id","type":"int64","is_key":true}, …]}}]
```

### `database schema` / `preview` / `get`

```bash
trickest database schema <table-id>
trickest database preview <table-id> --limit 10
trickest database get <table-id>
```

## Querying with TQL

`database query` runs a **filter-only** expression, not SQL `SELECT`.

### Syntax

- **Operators:** `=`, `!=`, `>`, `<`, `~` (contains), `!~` (not contains)
- **Combine with:** `AND`, `OR`
- **No** `SELECT`, `JOIN`, `GROUP BY`, aggregates, or wildcards

Examples:

```text
severity = "critical"
port > 443 AND service = "http"
hostname ~ "staging"
```

### Running a query

```bash
trickest database query "id > 0" --table <table-id> --limit 10
trickest database query "severity = critical" --table <table-id> --limit 1 --select id,name,severity
```

| Flag | Description |
|---|---|
| `--table <id>` | Table ID (required) |
| `--limit <n>` | Max rows returned |
| `--offset <n>` | Pagination offset |
| `--select <cols>` | Comma-separated columns |
| `--order-by <expr>` | e.g. `"severity DESC"` |

### Row shape

Each row includes system columns prefixed with `_` (`_id`, `_status`,
`_is_active`, `_first_seen`, `_last_seen`, `_timestamp`, `_execution_id`,
`_user`) plus your dataset fields. Filter on **user** fields; `_` columns are
platform bookkeeping.

### Counting rows

JSON query output is a bare array of rows, with no `total_count`.
Use `database get <table>` to read the table's `row_count`; this is not a count
of an arbitrary filtered query. `--limit` controls returned rows, not a count operation.

<Warning>
`--limit 0` is rejected by the platform API. Do not use it for counts.
</Warning>

## Create a live table

For an existing output, run `database detect --workflow <workflow-id>`, inspect the
candidate and its schema, then promote it with `database live <table>`.
Use `--pk` for primary-key columns and `--columns` to set column types before creation.

To declare a table before its node has run:

```bash
trickest database live findings --workflow <workflow-id> \
  --node collect --file findings.jsonl \
  --columns host:text,port:int64 --pk host,port
```

Replace the node and filename with the output your workflow will write. Declaration
creates the connection; rows arrive when a run produces matching data. In declaration
mode, `--node` requires both `--file` and `--columns`.

Every database subcommand accepts `--workflow <id>` for per-call context.
To filter the table listing itself, use `database ls --filter-workflow <id>`.

## Export and edit

Use CSV export for an entire table rather than requesting an unbounded query:

```bash
trickest database export <table-id> --out ./findings.csv \
  --where 'severity = "critical"' --select host,severity
```

`--force` allows replacing an existing destination file. `--timeout <seconds>`
limits how long the CLI waits for the export; it is not a cancellation command.

`database rows ls|get|add|update|rm` manages user rows. `database fields add|rm`
changes columns. Inspect each subcommand's help before writing: row edits require
row IDs, field additions require `--type`, and a field default may not backfill
existing rows.

## Related

<CardGroup cols={2}>
  <Card title="Solutions" icon="gem" href="/docs/developer-tools/cli/solutions">
    Inspect solution metadata and current dataset API limits.
  </Card>
  <Card title="Querying (UI)" icon="database" href="/docs/using-the-app/database-management/querying">
    Write and run TQL from the Live Table Query tab.
  </Card>
</CardGroup>

---
_Markdown source of https://trickest.com/docs/developer-tools/cli/database._