# Database Connections

Connect external databases to your AI chatbot so it can query real data during conversations.

---

Database Connections lets your AI chatbot query external databases during conversations. Instead of hardcoding answers or uploading CSVs, your AI can run live SQL queries against your PostgreSQL, MySQL, or SQLite database.

## What It Does

Your AI gets two tools when a database connection is configured:

| Tool | Purpose |
|------|---------|
| **inspectDatabaseSchema** | Lists tables and columns so the AI knows what data is available |
| **queryDatabase** | Executes SQL queries and returns results |

The AI automatically calls `inspectDatabaseSchema` first to understand your data structure, then writes accurate queries based on the real schema. This eliminates hallucinated column names.

```
Consumer: "How many orders did we get last week?"
    ↓
AI calls inspectDatabaseSchema → sees orders table with columns
    ↓
AI calls queryDatabase → SELECT count(*) FROM orders WHERE created_at > now() - interval '7 days'
    ↓
AI: "You received 247 orders in the last 7 days."
```

## Requirements

- **Studio Pro plan** or higher
- An externally accessible database (PostgreSQL, MySQL, or SQLite)
- A connection string with credentials that have appropriate permissions

> **Note:** Database Connections run server-side. Your connection string is encrypted at rest and never sent to the AI model or exposed in API responses.

## Setting Up a Connection


  **1.** 
    <strong>Open Capabilities</strong>

    Go to your app's **Build** page, click the **Capabilities** tab, and scroll to **Database Connections**.
  


  **2.** 
    <strong>Add a Connection</strong>

    Click **Add connection** and fill in:

    - **Connection name**: A friendly label (e.g., "Production Analytics")
    - **Database type**: PostgreSQL, MySQL, or SQLite
    - **Connection string**: Your database URL (e.g., `postgres://user:password@host:5432/dbname`)
    - **Default access level**: Read-only (recommended) or Read/Write
  


  **3.** 
    <strong>Test the Connection</strong>

    After saving, click the wrench icon to test the connection. This verifies connectivity and discovers your database schema (tables and columns). The schema is saved so your AI knows the exact structure.
  


  **4.** 
    <strong>Preview Queries (Optional)</strong>

    Click the edit icon, then switch to the **Query Preview** tab. Write SQL queries and see results instantly. This helps you verify the connection works as expected before publishing.
  


## Security Features

Database Connections include multiple security layers to protect your data:

### Encrypted Credentials

Connection strings are encrypted with AES-256-CTR before storage. They are decrypted only at query execution time on the server and are never included in API responses, logs, or LLM prompts.

### Read-Only Mode

When set to **Read-only** (the default), the database connection enforces read-only mode at the database level:

- **PostgreSQL**: `SET default_transaction_read_only = on`
- **MySQL**: `SET SESSION TRANSACTION READ ONLY`
- **SQLite**: `PRAGMA query_only = true`

This means even if the AI generates an `INSERT` or `UPDATE`, the database itself rejects it.

### DDL Blocking

Dangerous statements are blocked before they reach your database:

- `DROP DATABASE`
- `DROP TABLE`
- `TRUNCATE`
- `ALTER TABLE`

These are blocked regardless of access level. You can customize the blocklist in the connection settings.

### Row Limits

Every query has a configurable maximum row count (default: 1,000). Results exceeding the limit are automatically truncated, preventing your AI from accidentally fetching millions of rows.

### Column Masking

Hide sensitive columns from the AI entirely. Masked columns are removed from:

1. The schema description (the AI doesn't know they exist)
2. Query results (values are stripped even if selected)

Use this for columns like `password_hash`, `ssn`, `api_key`, or any PII you don't want the AI to see.

### Table Allowlist

Restrict which tables the AI can query. When configured, queries referencing tables outside the allowlist are rejected.

> **Warning:** The table allowlist uses pattern matching and can be bypassed with advanced SQL constructs (CTEs, subqueries). For true data isolation, use database-level permissions on the connection credentials. The allowlist is a guardrail, not a security boundary.

### SSRF Protection

Connection strings are validated to prevent Server-Side Request Forgery:

- Private hostnames blocked (localhost, .local, .internal)
- DNS resolution checks both IPv4 and IPv6 records for private IP addresses

## Per-Consumer Access Control (RBAC)

Each connection has a **default access level** that applies to all consumers. You can override this per consumer:

| Access Level | What It Allows |
|-------------|----------------|
| **Read-only** | `SELECT` queries only |
| **Read/Write** | `SELECT`, `INSERT`, `UPDATE`, `DELETE` |
| **None** | No access (connection hidden from this consumer) |

To manage per-consumer overrides, use the API:

```bash
# Set a consumer to read/write
curl -X PUT /api/applications/{appId}/database-connections/{connectionId}/overrides/{consumerId} \
  -H "Content-Type: application/json" \
  -d '{"accessLevel": "readwrite"}'

# Remove an override (reverts to default)
curl -X DELETE /api/applications/{appId}/database-connections/{connectionId}/overrides/{consumerId}
```

## Multiple Connections

You can add up to 10 database connections per app. When multiple connections are configured:

- The AI sees all connection names and their schemas
- The AI must specify which connection to query
- Each connection has independent access controls, row limits, and column masking

This is useful when your data lives across multiple databases (e.g., a user database and an analytics database).

## Query Timeout

Queries have a configurable timeout (default: 10 seconds, max: 60 seconds). Long-running queries are killed automatically to prevent your chatbot from hanging.

## Best Practices

1. **Use read-only credentials**: Create a database user with `SELECT`-only permissions. This provides defense-in-depth on top of Chipp's read-only mode.

2. **Mask sensitive columns**: Add `password_hash`, `api_key`, `ssn`, and similar columns to the mask list. The AI won't know they exist.

3. **Set appropriate row limits**: For analytical queries, 1,000 rows is usually sufficient. For lookup queries, consider lower limits (50-100).

4. **Test before publishing**: Use the Query Preview tab to verify your connection and test common queries before making the app live.

5. **Use table allowlists for focus**: Even if the allowlist isn't a security boundary, it helps the AI focus on relevant tables and reduces confusion from unrelated schemas.

## Supported Database Types

| Database | Connection String Format | Notes |
|----------|------------------------|-------|
| **PostgreSQL** | `postgres://user:pass@host:5432/dbname` | Most popular. Full schema discovery via `information_schema`. |
| **MySQL** | `mysql://user:pass@host:3306/dbname` | Schema discovery via `information_schema`. |
| **SQLite** | Relative file path (e.g., `data/app.db`) | File-based. Must use relative paths for security. |

> **Tip:** If your password contains special characters (`#`, `*`, `@`), URL-encode them in the connection string. For example, `p@ss#word` becomes `p%40ss%23word`.

## Billing

Database queries are included in your LLM token billing. There is no separate per-query charge. You only pay for the AI tokens used to process and respond to the query results.

## Troubleshooting

### "Connection must not point to private hosts"

Your database must be publicly accessible. Chipp's servers cannot reach databases on private networks (localhost, 10.x.x.x, 192.168.x.x).

**Fix**: Ensure your database has a public IP or is accessible through a connection proxy.

### "Schema not yet discovered"

The connection was saved but hasn't been tested. Click the wrench icon to test the connection and discover the schema.

### "Table not in the allowed list"

Your table allowlist is blocking the query. Either add the table to the allowlist or remove the allowlist entirely.

### "Cannot execute INSERT in a read-only transaction"

The connection is set to read-only mode. Change the default access level to "Read/Write" if write operations are needed, or set a per-consumer override for specific users.

### Query timeout

Complex queries may exceed the timeout. Increase the timeout in connection settings (up to 60 seconds), or optimize the query with indexes and `LIMIT` clauses.