Database Connections

Configure SQLite, PostgreSQL, and ClickHouse connections for data storage

Overview

Anysite CLI can load extracted data directly into databases. It supports SQLite (built-in), PostgreSQL, and ClickHouse (via optional extras). Connections are stored in ~/.anysite/connections.yaml and can be 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

Add a Connection

SQLite

```bash
anysite db add mydb --type sqlite --database ./data/mydata.db
```
SQLite databases are created automatically if the file doesn't exist.

PostgreSQL

First install the PostgreSQL extra:
```bash
pip install "anysite-cli[postgres]"
```

Then add the connection:
```bash
anysite db add pg --type postgres \
  --host localhost \
  --port 5432 \
  --database mydb \
  --user app \
  --password "$DB_PASSWORD"
```

Or reference an existing environment variable directly:
```bash
anysite db add pg --type postgres \
  --host localhost \
  --database mydb \
  --user app \
  --password-env PGPASS
```

Or use a connection URL from an environment variable:
```bash
anysite db add pg --url-env DATABASE_URL
```

ClickHouse

First install the ClickHouse extra:
```bash
pip install "anysite-cli[clickhouse]"
```

Then add the connection:
```bash
anysite db add ch --type clickhouse \
  --host localhost \
  --port 8123 \
  --database default \
  --user default \
  --password "$CH_PASSWORD"
```

Or with SSL (auto-enabled on port 8443):
```bash
anysite db add ch --type clickhouse \
  --host ch.example.com \
  --port 8443 \
  --database analytics \
  --user app \
  --password-env CH_PASS \
  --ssl
```

Read-Only

Mark a connection as read-only to prevent accidental writes (useful for production replicas):
```bash
anysite db add replica --type postgres \
  --host replica.example.com \
  --database mydb \
  --user readonly_user \
  --password-env REPLICA_PASS \
  --read-only
```
Read-only connections block INSERT/UPDATE/DELETE operations. Discovery auto-detects read-only status for PostgreSQL replicas, SQLite files with restricted permissions, and ClickHouse `readonly` system setting.

Use --password-env to reference an environment variable instead of passing passwords directly. The CLI stores connection details in ~/.anysite/connections.yaml with passwords stored as env var references.

Manage Connections

# List all connections
anysite db list

# Test a connection
anysite db test pg

# Remove a connection
anysite db remove pg

Connection Reference

SQLite Options

Option	Description	Default
--type	sqlite	Required
--database	Path to the SQLite database file	Required

PostgreSQL Options

Option	Description	Default
--type	postgres	Required
--host	Database host	localhost
--port	Database port	5432
--database	Database name	Required
--user	Username	Required
--password	Password (prefer --password-env)	—
--password-env	Environment variable name containing the password	—
--url-env	Environment variable containing the full connection URL	—
--read-only	Mark connection as read-only (blocks writes)	false

ClickHouse Options

Option	Description	Default
--type	clickhouse	Required
--host	Database host	localhost
--port	HTTP port	8123
--database	Database name	default
--user	Username	—
--password	Password (prefer --password-env)	—
--password-env	Environment variable name containing the password	—
--ssl	Enable SSL (auto-enabled on port 8443)	false
--read-only	Mark connection as read-only (blocks writes)	false

ClickHouse uses the HTTP protocol via clickhouse-connect. The default engine for auto-created tables is MergeTree().

Next Steps

Database Discovery

Auto-discover database structure and browse saved catalogs

Database Operations

Insert, query, and sync data with your databases