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.
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_idstring Required Database connection identifier querystring Required SQL query with parameter placeholders parametersobject Required Mapping of placeholder names to document fields result_fieldstring sql_resultField name for query results multiple_rowsboolean falseReturn all rows or just first timeout_msinteger 5000Query timeout in milliseconds
Supported Databases Database Connection Type Notes PostgreSQL postgresFull support MySQL mysqlFull support SQL Server mssqlFull support SQLite sqliteRead-only
Configuration Examples Basic Customer Lookup
Inventory Enrichment
Multiple Row Results
Join with Multiple Fields
{ "stage_type" : "apply" , "stage_id" : "sql_lookup" , "parameters" : { "connection_id" : "crm_postgres" , "query" : "SELECT name, email, tier FROM customers WHERE id = :customer_id" , "parameters" : { "customer_id" : "{{DOC.metadata.customer_id}}" }, "result_field" : "customer_data" } }
Query Syntax Parameter Placeholders Use :name syntax for parameter placeholders:
SELECT * FROM users WHERE id = :user_id AND status = : status
Parameters are properly escaped to prevent SQL injection.
Template Variables Variable Description {{DOC.*}}Any document field {{INPUT.*}}Input parameters from retriever call {{CONTEXT.*}}Pipeline context variables
Output Schema Single Row (default) { "document_id" : "doc_123" , "content" : "..." , "sql_result" : { "name" : "John Doe" , "email" : "[email protected] " , "tier" : "premium" } }
Multiple Rows { "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 { "document_id" : "doc_123" , "content" : "..." , "sql_result" : null }
Security 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
Metric Value Latency 10-100ms (depends on query complexity) Connection pooling Automatic Parallel execution Up to 10 concurrent queries Timeout handling Graceful with null result
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 [ { "stage_type" : "filter" , "stage_id" : "semantic_search" , "parameters" : { "query" : "{{INPUT.query}}" , "vector_index" : "text_extractor_v1_embedding" , "top_k" : 20 } }, { "stage_type" : "apply" , "stage_id" : "sql_lookup" , "parameters" : { "connection_id" : "product_db" , "query" : "SELECT price, stock FROM products WHERE id = :id" , "parameters" : { "id" : "{{DOC.metadata.product_id}}" }, "result_field" : "product_data" } }, { "stage_type" : "filter" , "stage_id" : "structured_filter" , "parameters" : { "conditions" : { "field" : "product_data.stock" , "operator" : "gt" , "value" : 0 } } } ]
Error Handling Error Behavior Query timeout result_field set to nullConnection failure Stage fails, pipeline stops No rows returned result_field set to nullInvalid SQL Stage fails with error message
