Database Operations

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

Insert Data

From Stdin

# 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

anysite db insert mydb --table profiles --file results.jsonl --auto-create

Auto-Create Tables

With --auto-create, the CLI infers a schema from the JSON data and creates the table if it doesn't exist — column types are inferred from the values. --pk <column> sets the primary key when creating the table.

--auto-create is safe to use repeatedly — it only creates the table on the first run, then inserts into the existing table on subsequent runs.

To create a table without inserting data, use db create-table directly (--stdin reads a sample of JSON to infer the schema, --dry-run prints the CREATE TABLE SQL without running it):

echo '{"id":1,"name":"test"}' | anysite db create-table mydb --table users --pk id --dry-run

Upsert (Insert or Update)

db insert and db upsert share the same underlying logic. db upsert is shorthand for db insert --on-conflict update:

# db upsert: conflict columns are required, always updates on conflict
cat updates.jsonl | \
  anysite db upsert mydb --table profiles --conflict-columns id --stdin

# Equivalent via db insert, with explicit --on-conflict
cat updates.jsonl | \
  anysite db insert mydb --table profiles --stdin --auto-create \
  --on-conflict update --conflict-columns id

--on-conflict on db insert accepts error (default), ignore, replace, or update. --batch-size (default 100) controls how many rows are sent per batch insert.

Inspect Schema

# List tables
anysite db schema mydb

# Show columns of one table
anysite db schema mydb --table profiles

Query Data

# Query with table output (default format for db query)
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"

# Query from a .sql file instead of --sql
anysite db query mydb --file queries/report.sql --format json

--format accepts json, jsonl, csv, table (default), or parquet (with --output).

Dataset Loading

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

# 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, named after the source ID. Per-source db_load.key and db_load.sync (full — default, deletes rows no longer present; or append — no deletes) control incremental behavior and foreign-key linking between parent and dependent sources — see Source Types.

Comparing Two Collection Snapshots

dataset diff compares two dated Parquet snapshots of the same source collected by the pipeline — it reports what changed between collection runs. It does not compare against or write to a database:

anysite dataset diff dataset.yaml --source profiles --key urn.value

# Compare specific dates instead of the two most recent snapshots
anysite dataset diff dataset.yaml --source profiles --key urn.value --from 2026-01-01 --to 2026-01-08

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

This shows:

Added — records present in the newer snapshot but not the older one
Removed — records present in the older snapshot but not the newer one
Changed — records present in both but with different field values
Unchanged — count of identical records

To actually sync changes into a live database, use dataset load-db above.

Complete Pipeline-to-Database Example

# 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 --pk <col>`	Auto-create table from data
`anysite db insert <conn> ... --on-conflict update --conflict-columns <cols>`	Upsert on conflict
`anysite db upsert <conn> --table <name> --conflict-columns <cols>`	Upsert, shorthand for the above
`anysite db create-table <conn> --table <name> --stdin`	Create a table from inferred schema, no insert
`anysite db schema <conn>` / `--table <name>`	List tables, or inspect one table's columns
`anysite db query <conn> --sql "..."`	Run SQL query
`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>`	Compare two collection snapshots of a source

Next Steps

LLM Analysis

Enrich your data with AI-powered classification and summarization

SQL Querying

Query collected datasets with DuckDB SQL