← All Cookbooks
LangChainIntermediate25 min
LangChain + HatiData: Hybrid SQL+Vector Search
Combine SQL metadata filters with vector similarity search in a LangChain retrieval chain for precise, contextual document retrieval.
What You'll Build
A LangChain retrieval chain that combines SQL filters with vector similarity for precise, contextual document retrieval.
Prerequisites
$pip install langchain langchain-openai hatidata-agent
$hati init
$OpenAI API key
Architecture
┌──────────────┐ ┌──────────────┐ ┌──────────────┐
│ LangChain │───▶│ HatiData │───▶│ Vector Index │
│ Retriever │ │ Hybrid API │ │ (vectors) │
└──────────────┘ │ │ └──────────────┘
│ │───▶┌──────────────┐
└──────────────┘ │ Engine │
SQL + Vector │ (metadata) │
combined └──────────────┘Key Concepts
- ●Hybrid search: combine SQL WHERE clauses (exact metadata filters) with semantic_match() (vector similarity) in a single query
- ●Metadata filtering: store JSON metadata alongside memories and filter with json_extract_string() for precise retrieval
- ●Vector-only pitfall: pure vector search can return semantically similar but contextually wrong results — SQL filters fix this
- ●Single query engine: HatiData runs both SQL and vector operations in one query — no need to orchestrate separate systems
Step-by-Step Implementation
1
Install Dependencies
Install LangChain and the HatiData agent SDK.
Bash
pip install langchain langchain-openai hatidata-agent
hati initExpected Output
HatiData initialized successfully.
Proxy running on localhost:5439Note: Hybrid search combines SQL filtering with vector similarity in a single query.
2
Store Documents with Metadata
Store documents with rich metadata that can be filtered with SQL alongside vector search.
Python
from hatidata_agent import HatiDataAgent
hati = HatiDataAgent(host="localhost", port=5439, agent_id="hybrid-search")
# Store documents with metadata
docs = [
("Kubernetes pod autoscaling uses HPA based on CPU and memory metrics", "devops", "infrastructure"),
("React Server Components reduce client-side JavaScript bundle size by 40%", "frontend", "performance"),
("PostgreSQL BRIN indexes are ideal for time-series append-only tables", "database", "performance"),
("Terraform state locking prevents concurrent modifications to infrastructure", "devops", "infrastructure"),
("Columnar storage achieves 10x compression on analytical workloads", "database", "analytics"),
]
for content, category, topic in docs:
hati.execute(f"""
SELECT store_memory(
'{content}',
'tech-docs',
'{{"category": "{category}", "topic": "{topic}"}}'
)
""")
print(f"Stored {len(docs)} documents with metadata")Expected Output
Stored 5 documents with metadata3
Run Vector-Only Search
First, try a pure vector similarity search to see what it returns.
Python
# Vector-only search: finds semantically similar content
vector_results = hati.query("""
SELECT content, namespace, metadata
FROM _hatidata_memory.memories
WHERE namespace = 'tech-docs'
AND semantic_match(embedding, 'how to improve database performance', 0.6)
ORDER BY semantic_rank(embedding, 'how to improve database performance') DESC
LIMIT 5
""")
print("=== Vector-Only Search ===")
print(f"Query: 'how to improve database performance'")
print(f"Results: {len(vector_results)}\n")
for r in vector_results:
print(f" [{r['metadata']}] {r['content'][:80]}...")Expected Output
=== Vector-Only Search ===
Query: 'how to improve database performance'
Results: 3
[{"category":"database","topic":"performance"}] PostgreSQL BRIN indexes are ideal for time-series append-only tables...
[{"category":"database","topic":"analytics"}] Columnar storage achieves 10x compression on analytical workloads...
[{"category":"frontend","topic":"performance"}] React Server Components reduce client-side JavaScript bundle size by 40%...Note: Vector search returned a frontend result because 'performance' is semantically similar. We need SQL filters to narrow down.
4
Run Hybrid SQL + Vector Search
Combine SQL metadata filters with vector similarity for precise results.
Python
# Hybrid search: SQL filter + vector similarity
hybrid_results = hati.query("""
SELECT content, metadata
FROM _hatidata_memory.memories
WHERE namespace = 'tech-docs'
AND json_extract_string(metadata, '$.category') = 'database'
AND semantic_match(embedding, 'how to improve database performance', 0.6)
ORDER BY semantic_rank(embedding, 'how to improve database performance') DESC
LIMIT 5
""")
print("=== Hybrid SQL + Vector Search ===")
print(f"Query: 'database performance' (category=database only)")
print(f"Results: {len(hybrid_results)}\n")
for r in hybrid_results:
print(f" {r['content']}")Expected Output
=== Hybrid SQL + Vector Search ===
Query: 'database performance' (category=database only)
Results: 2
PostgreSQL BRIN indexes are ideal for time-series append-only tables
Columnar storage achieves 10x compression on analytical workloadsNote: The frontend 'performance' result is now filtered out because we required category='database'.
5
Build a LangChain Retriever
Wire the hybrid search into a LangChain retrieval chain for natural language Q&A.
Python
from langchain_openai import ChatOpenAI
from hatidata_agent import HatiDataAgent
llm = ChatOpenAI(model="gpt-4o")
hati = HatiDataAgent(host="localhost", port=5439, agent_id="hybrid-search")
def hybrid_retrieve(query: str, category: str = None) -> str:
where_clause = "WHERE namespace = 'tech-docs'"
if category:
where_clause += f" AND json_extract_string(metadata, '$.category') = '{category}'"
where_clause += f" AND semantic_match(embedding, '{query}', 0.6)"
results = hati.query(f"""
SELECT content FROM _hatidata_memory.memories
{where_clause}
ORDER BY semantic_rank(embedding, '{query}') DESC
LIMIT 3
""")
return "\n".join(r["content"] for r in results)
# Ask a question with hybrid retrieval
query = "What indexing strategies work for time-series data?"
context = hybrid_retrieve(query, category="database")
response = llm.invoke(f"Context:\n{context}\n\nQuestion: {query}")
print(response.content)Expected Output
Based on the context, PostgreSQL BRIN (Block Range Index) indexes are particularly well-suited for time-series data in append-only tables. BRIN indexes store summary information about ranges of physical table blocks, making them extremely space-efficient for time-ordered data.