> ## 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 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

<Tabs>
  <Tab title="SQLite">
    ```bash theme={null}
    anysite db add mydb --type sqlite --database ./data/mydata.db
    ```

    SQLite databases are created automatically if the file doesn't exist.
  </Tab>

  <Tab title="PostgreSQL">
    First install the PostgreSQL extra:

    ```bash theme={null}
    pip install "anysite-cli[postgres]"
    ```

    Then add the connection:

    ```bash theme={null}
    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 theme={null}
    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 theme={null}
    anysite db add pg --url-env DATABASE_URL
    ```
  </Tab>

  <Tab title="ClickHouse">
    First install the ClickHouse extra:

    ```bash theme={null}
    pip install "anysite-cli[clickhouse]"
    ```

    Then add the connection:

    ```bash theme={null}
    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 theme={null}
    anysite db add ch --type clickhouse \
      --host ch.example.com \
      --port 8443 \
      --database analytics \
      --user app \
      --password-env CH_PASS \
      --ssl
    ```
  </Tab>

  <Tab title="Read-Only">
    Mark a connection as read-only to prevent accidental writes (useful for production replicas):

    ```bash theme={null}
    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.
  </Tab>
</Tabs>

<Warning>
  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.
</Warning>

## Manage Connections

```bash theme={null}
# 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`     |

<Note>
  ClickHouse uses the HTTP protocol via [clickhouse-connect](https://clickhouse.com/docs/en/integrations/python). The default engine for auto-created tables is `MergeTree()`.
</Note>

## Next Steps

<CardGroup cols={2}>
  <Card title="Database Discovery" icon="binoculars" href="/cli/database/discovery">
    Auto-discover database structure and browse saved catalogs
  </Card>

  <Card title="Database Operations" icon="arrows-rotate" href="/cli/database/operations">
    Insert, query, and sync data with your databases
  </Card>
</CardGroup>