Help using semantic caching like redis or something similar for langchain sql chain

[servaram]

I was trying to use redis semantic cache for my text to sql application, based on some examples from redis and langchain, i've impletemented this. i did not find enough documention on this topic

i do see that my complete chain including intermediate queiers, table structure etc and all that's being pushed to the redis store but on the subsequent runs its just returning an empty string with no sql (only when cache is enabled and cached data available)

1. How can i set this only to store the user_input, generated sql to the redis cache and use it when the same question is being asked
2. How can i update the sample code to effectively look for semantic matches with the ones that already exist in redis
3. Is there an alternative to redis semantic caching that works best with langchain's sql toolkit

import os
import time
from dotenv import load_dotenv
import redis
from langchain.chains.sql_database.query import create_sql_query_chain
from langchain_community.utilities import SQLDatabase
from langchain_community.agent_toolkits import create_sql_agent
from langchain_openai import OpenAI, OpenAIEmbeddings, ChatOpenAI
from langchain_redis import RedisCache, RedisSemanticCache


load_dotenv()


PG_URI = f'postgresql+psycopg2://{POSTGRES_USER}:{POSTGRES_PASSWORD}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB}'
PG_SCHEMA = 'myschema'


# ========== Redis as semantic cache ==========
REDIS_URL = os.getenv('REDIS_URL', "redis://localhost:6379")
redis_client = redis.from_url(REDIS_URL)
print(f"\tConnection to Redis : {redis_client.ping()}\n")
embeddings = OpenAIEmbeddings()
semantic_cache = RedisSemanticCache(
    redis_url=REDIS_URL, embeddings=embeddings, distance_threshold=0.2)


# ========== Database + LLM Setup ==========
db = SQLDatabase.from_uri(PG_URI, schema=PG_SCHEMA )
llm = OpenAI(model="gpt-4o-mini", temperature=0)
llm.cache = semantic_cache  
chain = create_sql_query_chain(db=db, llm=llm)

def execute_chain_with_timing(u_question):
    start_time = time.time()
    result = chain.invoke({'question': u_question})
    end_time = time.time()
    return result, end_time - start_time


user_question = "give me total customers from new york "
result1, time1 = execute_chain_with_timing (user_question)
print(f"{result1}\nTime: {time1:.2f} seconds\n")

result2, time2 = execute_chain_with_timing (user_question)
print(f"{result2}\nTime: {time2:.2f} seconds\n")

result3, time3 = execute_chain_with_timing (user_question)
print(f"{result3}\nTime: {time3:.2f} seconds\n")

[dosubot[bot]]

Hi @servaram! I'm Dosu and I’m helping the langchain team.

To store only the user input and generated SQL in RedisSemanticCache, you should avoid custom cache key logic (like using hashlib or custom prefixes/names) and let RedisSemanticCache handle semantic matching automatically. It uses embeddings and a distance_threshold to match similar queries, so just pass the user question as the prompt—no need to cache intermediate steps or table structures. If you’re seeing empty results, double-check that your chain returns the SQL string as the main output and that you’re not caching intermediate or irrelevant data by accident.

To improve semantic matching, make sure you’re not overriding the cache key logic. Remove any custom prefix or name parameters—these interfere with semantic matching. RedisSemanticCache will then embed each prompt and retrieve cached results for semantically similar queries above your distance_threshold. You can tune this threshold for stricter or looser matching. See this discussion for more details.

If you want alternatives, you might consider other vector-based caches like those built on top of Milvus, Qdrant, or Chroma, but RedisSemanticCache is the most integrated with LangChain’s SQL toolkit. Also, note that SQLDatabaseChain is deprecated; consider using VectorSQLDatabaseChain for newer projects, as it’s designed for semantic retrieval and caching in SQL workflows source.

For your sample code, just ensure you’re setting the cache on the LLM and not passing extra cache key logic. The cache will store and retrieve based on the user question and the generated SQL, using semantic similarity. If you want to cache only the SQL, make sure your chain’s output is just the SQL string. If you need more control, you may need to customize the chain’s return values.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}   

[servaram]

@dosu can you give me sample code for the one you mentioed