Database Connections

Configure SQLite, PostgreSQL, and ClickHouse connections for data storage

Overview

Anysite CLI can load extracted data directly into a named database connection. It supports SQLite (built-in), PostgreSQL, and ClickHouse (via optional extras). Connections are stored in ~/.anysite/connections.yaml and referenced by name across all CLI commands.

Supported Databases

Database	Extra Required	Use Case
SQLite	None (built-in)	Local development, single-user workflows, prototyping
PostgreSQL	pip install "anysite-cli[postgres]"	Production, multi-user, large datasets
ClickHouse	pip install "anysite-cli[clickhouse]"	Analytics, columnar storage, large-scale aggregations

anysite db add also accepts --type mysql and --type duckdb — mysql matches an installable anysite-cli[mysql] extra, and duckdb matches the format used internally by dataset pipelines. In this CLI version neither has a working database adapter: any command beyond db add (db test, db insert, db query, db discover, ...) fails with Unsupported database type. Stick to sqlite, postgres, and clickhouse for now.

Add a Connection

SQLite

SQLite connections take a file **path**, not `--database`:
```bash
anysite db add mydb --type sqlite --path ./data/mydata.db
```
The file is created automatically if it doesn't exist.

PostgreSQL

```bash
pip install "anysite-cli[postgres]"
```
```bash
anysite db add pg --type postgres \
  --host localhost \
  --port 5432 \
  --database mydb \
  --user app \
  --password-env PGPASS
```
Or use a connection URL from an environment variable:
```bash
anysite db add pg --type postgres --url-env DATABASE_URL
```

ClickHouse

```bash
pip install "anysite-cli[clickhouse]"
```
```bash
anysite db add ch --type clickhouse \
  --host ch.example.com \
  --port 8443 \
  --database analytics \
  --user app \
  --password-env CH_PASS \
  --ssl
```
Port defaults to `8123` (HTTP) if omitted; SSL is not auto-enabled by port number — pass
`--ssl` explicitly.

Read-Only

```bash
anysite db add replica --type postgres \
  --host replica.example.com \
  --database mydb \
  --user readonly_user \
  --password-env REPLICA_PASS \
  --read-only
```
`--read-only` forces the connection into read-only mode, which blocks write operations
(`db insert`, `db upsert`, `db create-table`, `dataset load-db`). Discovery (`db discover`)
also auto-detects read-only status independently.

Use --password-env to reference an environment variable rather than passing --password directly on the command line. --password and --password-env are mutually exclusive.

Manage Connections

# List all connections
anysite db list

# Show details for one connection
anysite db info mydb

# Test a connection
anysite db test pg

# Remove a connection (asks for confirmation unless --force)
anysite db remove pg --force

Connection Reference

Option	Applies to	Description
--type, -t	all	sqlite, postgres, or clickhouse (see note above for other accepted-but-unimplemented values)
--path	sqlite	Database file path
--host, -h	postgres, clickhouse	Database host
--port, -p	postgres, clickhouse	Database port (no CLI-level default except ClickHouse, which falls back to 8123)
--database, -d	postgres, clickhouse	Database/schema name
--user, -u	postgres, clickhouse	Username
--password	postgres, clickhouse	Password, stored in plain text in connections.yaml (prefer --password-env)
--password-env	postgres, clickhouse	Environment variable name containing the password
--url-env	postgres, clickhouse	Environment variable containing a full connection URL
--ssl	clickhouse	Enable SSL
--read-only	all	Force the connection read-only, blocking write operations

ClickHouse uses the HTTP protocol via clickhouse-connect.

Next Steps

Database Discovery

Auto-discover database structure and browse saved catalogs

Database Operations

Insert, query, and sync data with your databases