> ## Documentation Index
> Fetch the complete documentation index at: https://docs.mixpeek.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Lookup

> Enrich documents with data from SQL databases using parameterized queries

![SQL Lookup stage showing database enrichment with parameterized queries](https://mintcdn.com/mixpeek/TmiAqiYj-LwmWL2a/assets/retrievers/sql-lookup.svg?fit=max\&auto=format\&n=TmiAqiYj-LwmWL2a\&q=85\&s=d2f0f6d36e4fa48114bd6ab9741e9fc6)

The SQL Lookup stage enriches documents by querying external SQL databases. It executes parameterized queries using document fields as inputs, joining external structured data with your search results.

> [!NOTE]
>
> **Stage Category**: APPLY (Enriches documents)
>
> **Transformation**: N documents → N documents (with SQL data added)

## When to Use

| Use Case | Description |
| - | - |
| **Customer data enrichment** | Join customer details from CRM database |
| **Inventory lookups** | Add real-time stock levels to product results |
| **Pricing data** | Enrich with current pricing from ERP |
| **Cross-system joins** | Combine vector search with relational data |

## When NOT to Use

| Scenario | Recommended Alternative |
| - | - |
| Simple key-value lookups | `document_enrich` (collection join) |
| Read-heavy, cacheable data | Consider pre-indexing in Mixpeek |
| Real-time transactional queries | Direct database access |

## Parameters

| Parameter | Type | Default | Description |
| - | - | - | - |
| `connection_id` | string | *Required* | Database connection identifier |
| `query` | string | *Required* | SQL query with parameter placeholders |
| `parameters` | object | `{}` | Mapping of placeholder names to document fields |
| `output_field` | string | `sql_result` | Dot-path where query results are stored |
| `result_handling` | string | `first` | How to handle multiple rows (`first`, `all`, `error_if_empty`, `error_if_multiple`) |
| `on_no_results` | string | `null` | Behavior when no rows returned (`skip`, `null`, `error`) |
| `timeout` | integer | `30` | Query timeout in seconds (1-300) |
| `on_error` | string | `skip` | Error handling strategy (`skip`, `remove`, `raise`) |

## Supported Databases

| Database | Connection Type | Notes |
| - | - | - |
| PostgreSQL | `postgresql` | Create the connection as shown in [PostgreSQL](https://docs.mixpeek.com/docs/integrations/databases/postgresql.md) |

A connection to any other provider fails with `Connection does not support SQL queries`. For Snowflake or another warehouse, use the [API Call](https://docs.mixpeek.com/docs/retrieval/stages/api-call.md) stage against an endpoint that runs the query. See [Join Warehouse Metrics onto Results](#join-warehouse-metrics-onto-results).

## Configuration Examples

```json Basic Customer Lookup theme={null}
{
  "stage_name": "sql_lookup",
  "stage_type": "apply",
  "config": {
    "stage_id": "sql_lookup",
    "parameters": {
      "connection_id": "crm_postgres",
      "query": "SELECT name, email, tier FROM customers WHERE id = $1",
      "parameters": {
        "customer_id": "{{DOC.metadata.customer_id}}"
      },
      "output_field": "customer_data"
    }
  }
}
```

```json Inventory Enrichment theme={null}
{
  "stage_name": "sql_lookup",
  "stage_type": "apply",
  "config": {
    "stage_id": "sql_lookup",
    "parameters": {
      "connection_id": "inventory_db",
      "query": "SELECT stock_level, warehouse_location, last_updated FROM inventory WHERE sku = $1",
      "parameters": {
        "sku": "{{DOC.metadata.sku}}"
      },
      "output_field": "inventory",
      "timeout": 5
    }
  }
}
```

```json Multiple Row Results theme={null}
{
  "stage_name": "sql_lookup",
  "stage_type": "apply",
  "config": {
    "stage_id": "sql_lookup",
    "parameters": {
      "connection_id": "orders_db",
      "query": "SELECT order_id, status, total FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 5",
      "parameters": {
        "cid": "{{DOC.metadata.customer_id}}"
      },
      "output_field": "recent_orders",
      "result_handling": "all"
    }
  }
}
```

```json Join with Multiple Fields theme={null}
{
  "stage_name": "sql_lookup",
  "stage_type": "apply",
  "config": {
    "stage_id": "sql_lookup",
    "parameters": {
      "connection_id": "product_db",
      "query": "SELECT price, currency, discount_pct FROM pricing WHERE product_id = $1 AND region = $2",
      "parameters": {
        "pid": "{{DOC.document_id}}",
        "region": "{{INPUT.user_region}}"
      },
      "output_field": "pricing"
    }
  }
}
```

## Query Syntax

### Parameter Placeholders

Use `$1`, `$2`, and so on for placeholders. The stage sorts the keys of `parameters` in alphabetical order and binds their values to the placeholders in that order. `$1` takes the first key alphabetically.

```sql theme={null}
SELECT * FROM users WHERE status = $1 AND id = $2
```

With `parameters` set to `{"user_id": "...", "status": "active"}`, `$1` is `status` and `$2` is `user_id`. The database binds the values, so they are not concatenated into the query text.

### Template Variables

| Variable | Description |
| - | - |
| `{{DOC.*}}` | Any document field |
| `{{INPUT.*}}` | Input parameters from retriever call |
| `{{CONTEXT.*}}` | Pipeline context variables |

## Output Schema

### Single Row (default)

```json theme={null}
{
  "document_id": "doc_123",
  "content": "...",
  "sql_result": {
    "name": "John Doe",
    "email": "john@example.com",
    "tier": "premium"
  }
}
```

### Multiple Rows

```json theme={null}
{
  "document_id": "doc_123",
  "content": "...",
  "recent_orders": [
    {"order_id": "ord_1", "status": "delivered", "total": 99.99},
    {"order_id": "ord_2", "status": "shipped", "total": 149.99}
  ]
}
```

### No Results

```json theme={null}
{
  "document_id": "doc_123",
  "content": "...",
  "sql_result": null
}
```

## Security

> [!WARNING]
>
> SQL queries are parameterized to prevent injection attacks. Never concatenate user input directly into query strings.

| Security Feature | Description |
| - | - |
| **Parameterized queries** | All parameters are escaped |
| **Connection isolation** | Each connection uses dedicated credentials |
| **Read-only option** | Configure connections as read-only |
| **Query timeout** | Prevent long-running queries |

## Performance

| Metric | Value |
| - | - |
| **Latency** | 10-100ms (depends on query complexity) |
| **Connection pooling** | Automatic |
| **Execution** | One query per document, run one after another |
| **Timeout handling** | Graceful with null result |

> [!TIP]
>
> For high-volume lookups, ensure your database has appropriate indexes on the queried columns. Consider caching frequently accessed data.

## Common Pipeline Patterns

### Search + SQL Enrichment

```json theme={null}
[
  {
    "stage_name": "semantic_search",
    "stage_type": "filter",
    "config": {
      "stage_id": "feature_search",
      "parameters": {
        "searches": [
          { "feature_uri": "mixpeek://text_extractor@v1/multilingual_e5_large_instruct_v1", "query": { "input_mode": "text", "value": "{{INPUT.query}}" }, "top_k": 20 }
        ],
        "final_top_k": 20
      }
    }
  },
  {
    "stage_name": "sql_lookup",
    "stage_type": "apply",
    "config": {
      "stage_id": "sql_lookup",
      "parameters": {
        "connection_id": "product_db",
        "query": "SELECT price, stock FROM products WHERE id = $1",
        "parameters": {
          "id": "{{DOC.metadata.product_id}}"
        },
        "output_field": "product_data"
      }
    }
  },
  {
    "stage_name": "structured_filter",
    "stage_type": "filter",
    "config": {
      "stage_id": "attribute_filter",
      "parameters": {
        "field": "product_data.stock",
        "operator": "gt",
        "value": 0
      }
    }
  }
]
```

## Join Warehouse Metrics onto Results

A creative library often carries an ID such as `creative_id` that keys a metrics table. The table holds spend, ROAS, and thumb-stop rate. Two stages join that table onto search results. Where the table lives picks the stage.

| Table location | Stage |
| - | - |
| PostgreSQL | `sql_lookup` with a PostgreSQL connection |
| Snowflake or another warehouse | `api_call` against an HTTP endpoint that runs the query |

Both stages run after the search stage and add fields to each returned document. They do not change which documents were retrieved or how they scored. A search cannot rank by a joined value. To rank by ROAS, ingest it as document data.

### From PostgreSQL

Read `creative_id` from the document metadata and bind it to `$1`.

```json theme={null}
{
  "stage_name": "sql_lookup",
  "stage_type": "apply",
  "config": {
    "stage_id": "sql_lookup",
    "parameters": {
      "connection_id": "ad_metrics_postgres",
      "query": "SELECT spend, roas, thumbstop_rate FROM creative_performance WHERE creative_id = $1",
      "parameters": {
        "creative_id": "{{DOC.metadata.creative_id}}"
      },
      "output_field": "warehouse_metrics"
    }
  }
}
```

Each result gains a `warehouse_metrics` object. A creative with no row gets `null`, because `on_no_results` defaults to `null`.

```json theme={null}
{
  "document_id": "doc_123",
  "score": 0.91,
  "warehouse_metrics": {
    "spend": 1840.5,
    "roas": 3.2,
    "thumbstop_rate": 0.31
  }
}
```

### From Another Warehouse

Put an HTTP endpoint in front of the warehouse. The endpoint takes the ID in its path and returns one JSON object. The stage reads the token from an organization secret. See [API Call](https://docs.mixpeek.com/docs/retrieval/stages/api-call.md#storing-secrets).

```json theme={null}
{
  "stage_name": "api_call",
  "stage_type": "apply",
  "config": {
    "stage_id": "api_call",
    "parameters": {
      "url": "https://metrics.example.com/v1/creatives/{{DOC.metadata.creative_id}}/performance",
      "method": "GET",
      "allowed_domains": ["metrics.example.com"],
      "auth": {
        "type": "bearer",
        "secret_ref": "metrics_api_token"
      },
      "output_field": "warehouse_metrics",
      "timeout": 10,
      "on_error": "skip"
    }
  }
}
```

The endpoint needs a public address and must return JSON. The stage sends one request per document, one after another. A retriever that returns 20 documents sends 20 requests.

## Error Handling

| Error | Behavior |
| - | - |
| Query timeout | Handled per `on_error` (default: document passes through unchanged) |
| Connection failure | Handled per `on_error` (default: `skip`) |
| No rows returned | `output_field` set to `null` (default `on_no_results`) |
| Invalid SQL | Stage fails with error message |

## Related

* [Document Enrich](https://docs.mixpeek.com/docs/retrieval/stages/document-enrich.md) - Collection-based joins
* [API Call](https://docs.mixpeek.com/docs/retrieval/stages/api-call.md) - REST API enrichment
* [JSON Transform](https://docs.mixpeek.com/docs/retrieval/stages/json-transform.md) - Transform enriched data


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.