> ## Documentation Index
> Fetch the complete documentation index at: https://docs.anysite.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Operations

> Insert, upsert, query, and sync data with SQLite, PostgreSQL, and ClickHouse

## Insert Data

### From Stdin

Pipe API results directly into a database:

```bash theme={null}
# Single profile → database
anysite api /api/linkedin/user user=satyanadella -q --format jsonl | \
  anysite db insert mydb --table profiles --stdin --auto-create

# Batch results → database
anysite api /api/linkedin/user --from-file users.txt --input-key user \
  --parallel 5 -q --format jsonl | \
  anysite db insert mydb --table profiles --stdin --auto-create
```

### From File

Load a previously saved file:

```bash theme={null}
anysite db insert mydb --table profiles --file results.jsonl --auto-create
```

### Auto-Create Tables

With `--auto-create`, the CLI automatically infers the schema from the JSON data and creates the table if it doesn't exist. Column types are determined from the data values.

<Tip>
  The `--auto-create` flag is safe to use repeatedly — it only creates the table on the first run, then inserts into the existing table on subsequent runs.
</Tip>

## Upsert (Insert or Update)

Update existing records or insert new ones based on a unique key:

```bash theme={null}
# Upsert with conflict handling
cat updates.jsonl | \
  anysite db upsert mydb --table profiles --conflict-columns id --stdin

# Or use the insert command with --upsert flag
cat updated_profiles.jsonl | \
  anysite db insert mydb --table profiles --stdin --auto-create --upsert --key urn_value
```

The upsert performs an INSERT on new records and UPDATE on existing ones based on the specified key/conflict columns.

## Inspect Schema

View the table schema in your database:

```bash theme={null}
anysite db schema mydb --table profiles
```

## Query Data

Run SQL queries against your database:

```bash theme={null}
# Query with table output
anysite db query mydb --sql "SELECT name, headline FROM profiles LIMIT 10" --format table

# Export query results to CSV
anysite db query mydb --sql "SELECT * FROM profiles WHERE industry = 'Technology'" \
  --format csv --output tech_profiles.csv

# Count records
anysite db query mydb --sql "SELECT COUNT(*) FROM profiles"
```

## Dataset Loading

Load data from a collected dataset pipeline directly into a database:

```bash theme={null}
# Load all sources into database (creates tables per source)
anysite dataset load-db dataset.yaml -c mydb

# Drop existing tables and reload
anysite dataset load-db dataset.yaml -c mydb --drop-existing

# Load a specific snapshot date
anysite dataset load-db dataset.yaml -c mydb --snapshot 2026-01-15

# Auto-load during collection
anysite dataset collect dataset.yaml --load-db mydb
```

Each source in the pipeline becomes a separate table in the database, named after the source ID.

## Diff-Based Sync

Compare collected data with what's already in the database and apply incremental updates:

```bash theme={null}
anysite dataset diff dataset.yaml --source profiles --key urn.value

# Diff with specific fields
anysite dataset diff dataset.yaml --source profiles --key urn.value --fields "name,headline"
```

This shows:

* **New records** — present in dataset but not in database
* **Updated records** — present in both but with different values
* **Deleted records** — present in database but not in latest dataset

<Note>
  ClickHouse uses ALTER TABLE mutations for diff-sync updates. Transactions are not supported — each batch insert is applied directly.
</Note>

## Auto-Schema and Foreign Keys

The CLI automatically:

* **Infers column types** from JSON data (string, integer, float, boolean, timestamp)
* **Flattens nested objects** using underscore notation (e.g., `urn.value` → `urn_value`)
* **Tracks provenance** — links between parent and dependent source tables using foreign key references

## Complete Pipeline-to-Database Example

```bash theme={null}
# Step 1: Collect data
anysite dataset collect dataset.yaml

# Step 2: Load into PostgreSQL
anysite dataset load-db dataset.yaml -c pg

# Step 3: Query the results
anysite db query pg --sql "
  SELECT p.name, p.headline, e.company_name
  FROM profiles p
  JOIN employees e ON p.urn_value = e.urn_value
  LIMIT 20
" --format table

# Or do it all in one command:
anysite dataset collect dataset.yaml --load-db pg
```

## Operations Reference

| Command                                                            | Description                      |
| ------------------------------------------------------------------ | -------------------------------- |
| `anysite db insert <conn> --table <name> --stdin`                  | Insert data from stdin           |
| `anysite db insert <conn> --table <name> --file <path>`            | Insert data from file            |
| `anysite db insert <conn> ... --auto-create`                       | Auto-create table from data      |
| `anysite db insert <conn> ... --upsert --key <col>`                | Upsert on unique key             |
| `anysite db upsert <conn> --table <name> --conflict-columns <col>` | Upsert with conflict handling    |
| `anysite db schema <conn> --table <name>`                          | Inspect table schema             |
| `anysite db query <conn> --sql "..."`                              | Run SQL query                    |
| `anysite db query <conn> --sql "..." --format csv`                 | Export query to CSV              |
| `anysite dataset load-db <yaml> -c <conn>`                         | Load dataset into database       |
| `anysite dataset load-db <yaml> -c <conn> --drop-existing`         | Reload with fresh tables         |
| `anysite dataset load-db <yaml> -c <conn> --snapshot <date>`       | Load a specific snapshot         |
| `anysite dataset diff <yaml> --source <id> --key <field>`          | Show diff between dataset and DB |
| `anysite dataset diff <yaml> ... --fields "name,headline"`         | Diff with specific fields        |

## Next Steps

<CardGroup cols={2}>
  <Card title="LLM Analysis" icon="brain" href="/cli/llm-analysis">
    Enrich your data with AI-powered classification and summarization
  </Card>

  <Card title="SQL Querying" icon="chart-simple" href="/cli/querying">
    Query collected datasets with DuckDB SQL
  </Card>
</CardGroup>