> ## Agent Instructions
>
> Base URL: https://api.anysite.io
> Authentication: send the `access-token` header. Do NOT use `Authorization: Bearer`.
> Full endpoint catalog: https://app.anysite.io/docs
# 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

```bash
# 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](https://clickhouse.com/docs/en/integrations/python). 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