LangChain With SQL Databases: Natural Language to SQL Queries

Building an SQL interface that is easy to use for NON-SQL users and provides immediate and accurate answers to business queries.

Varun Joshi

Jul. 06, 26 · Tutorial

Likes (0)

Comment

Save

132 Views

Every business runs on a database, but not everyone who needs an answer from the database speaks SQL. Data Analysts wait on engineers, and stakeholders wait on analysts, and by the time the query runs, the decision window has passed.

LangChain's SQL integration fixes this, translating plain English questions like "Which product category had the highest revenue last year' into valid SQL, executing it, and returning a human-readable answer.

In this tutorial, we will build a full natural-language SQL interface that covers setup, complex queries, and safety guardrails.

How It Works

LangChain's SQL integration follows a three-step pattern:

Question -> SQL generation -> execution -> Natural Language Answer

The LLM only sees the schema and never the RAW data.

Step 1: Setup

    Shell
   
   pip install langchain langchain-openai langchain-community\
			sqlalchemy pymysql psycopg2-binary

    Shell
   
   import os
from dotenv import load_dotenv
from langchain_openai import ChatOpenAI

load_dotenv()

llm = ChatOpenAI(
	  model="gpt-4o"
      temperature = 0 #keep deterministic for SQL generation
      )

Step 2: Connecting to the Database

LangChains' SQLDatabase wrapper works with any SQLAlchemy-compatible database.

SQLite(Local/Dev):

    Python
   
 

   from langchain_community.utilities import SQLDatabase

#Connect to local SQLite database
db = SQLDatabase.from_uri("sqlite:///sales.db")

#Check what LangChain can see
print(db.get_usable_table_names())
#['customers','orders','products','order_items']

print(db.get_table_info())

# Create Table Customers (
# id INTEGER PK
# name varchar()
# email varchar()
# region varchar()
# created_time
  

For PostgreSQL:

    Python
   
   db = SQLDatabase.from_uri("postgresql+psycopg2:/user:password@localhost:5432/test_db")

For MySQL:

    Python
   
   db = SQLDatabase.from_uri("mysql+pymysql://user:password@localhost:3306/test_db")

Large databases:

Use include_tables to limit schema exposure and sample_rows_in_table_info to show LLM real data formats:

    Python
   
 

   db = SQLDatabase.from_uri(
  		"postgresql+psycopg2://user:password@localhost/test_db")
         include_tables=["customers","orders","products","order_items"],
         sample_rows_in_table_info=2
    )
  

Step 3: Sample Schema

We will use a simple e-commerce schema: customers, products, orders, and order_items:

    SQL
   
   CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT,email TEXT, region TEXT,created_at DATETIME);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT,category TEXT, price INTEGER,stock INTEGER);
CREATE TABLE orders (id INTEGER PRIMARY KEY, cust_id INTEGER,email TEXT, STATUS TEXT,created_at DATETIME);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, ORDER_ID INTEGER,email TEXT, PRODUCT TEXT,QUANTITY INTEGER, UNIT_PRICE INTEGER);

Step 4: Basic Natural Language Queries

LangChain provides create_SQL_query_chain for the SQL generation step:

    Python
   
 

   for LangChain.chains import sql_query_chain

#create a chain that coverts question to SQL 
sql_chain = create_sql_query_chain(llm,db)

#Generate SQL from natural language question
question = "How many customer do we have in each region"
sql_query = sql_chain.invoke({"question":question})
print(f"Generated SQL:\n{sql_query}")
#Select region,COUNT(*) as customer_count
#From customers
#Group By region
#order by customer_count desc
  

To get the results for this query, add the query executor:

    Python
   
   from langchain_community.tools import QuerySQLDataBaseTool

#Tool that executes SQL against the database
execute_query = QuerySQLDataBaseTool(db=db)

#Chain: question>SQL->execute->raw_result
result= execute_query.invoke(sql_query)
print(result)

#[('North',14),('South',13),('East',12),('West',11)

To get natural language rather than tuples, chain it with LCEL:

    Python
   
 

   from langchain_core.output_parsers import StrOuputParser
from langchain_core.prompts import PromptTemplate
from operator import itemgetter

#Prompt to convert SQL results into Human readable answers

answer_prompt = PromptTemplate.from_template(
  				"""Given the following user question,SQL query and SQL result
                   Write a clear and concise answer in natural language.
                   
                   Question:{question}
                   SQL Query:{query}
                   SQL Result:{result}
                   
                   Answer:"""
)

#Full pipeline: Question -> SQL-> execute -> Natural Language
full_chain = (
     {"question":itemgetter("question"), "query":sql_chain}
     |{"question": itemgetter("question"), "query": itemgetter("query"),
       "result": lambda x: execute_query.invoke(x["query"])}
    | answer_prompt | llm | StrOutputParser()
)

response = full_chain.invoke({"question":"How many customers do we have in each region"})
  

LCEL: LangChain Expression Language

It's LangChain's declarative syntax for chaining components together using the pipe(|) operator borrowed from Unix style. Instead of manually writing inputs and outputs between steps, it is composed from right to left.

Step 5: Handling Complex Multi-Table Queries

The real test of SQL chain is multi-table queries involving joins, aggregations, and filters.

    Python
   
 

   questions = [
         "What are the top 3 best selling categories by total revenue this month",
         "Which customers have placed more than 5 orders",
         "What is the average order value by region?",
]

for q in questions:
    answer = full_chain.invoke({"question":q})
    print(f"Q:{q}\nA:{answer}\n")
  

Sample output:

Q: What are the top 3 best-selling categories by total revenue this month?

A: Electronics leads at $8,432.50, followed by Sports ($2,109.75) and Clothing ($1,876.20).

Step 6: Handling Complex Multi-Table Queries

Giving an LLM access to your database requires careful guardrails. A poorly crafted question, or a malicious one, could result in DELETE,DROP, or UPDATE statements.

Approach 1: Read-Only Database Connection

The simplest safeguard: connect with a read-only user.

    Python
   
   #SQL Read Only User
db = SQLDatabase.from_uri("postgresql+psycopg2://readonly_user:password@localhost/test_db")

Approach 2: Query Validation Before Execution

    Python
   
 

   import re

FORBIDDEN_KEYWORDS = ["DROP","DELETE","INSERT","UPDATE","ALTER","GRANT","REVOKE"]

def validate_sql(query: str)-> tuple[bool,str]:
  """Check SQL Query for destructive operations."""
  query_upper = query.upper().strip()
  for keyword in FORBIDDEN_KEYWORDS:
    #MATCH WHOLE WORDS ONLY AVOID FALSE POSITIVES LIKE UPDATES IN COLUMN NAME
    if re.search(rf"\b{keyword}\b", query_upper):
      return False, f"Blocked, query contains forbidden keyword '{keyword}'"
  
  if not query_upper.startswith("SELECT"):
    return False, "Blocked:Only select queries are permitted"
  
  return True, "OK"

def safe_execute(query:str) -> str:
  """Validate and then Execute a query"""
  is_safe,reason = validate_sql(query)
  if not is_safe:
    return f"query_rejected:{reason}"
  return execute_query.invoke(query)
  

Approach 3: Custom System Prompt

    Python
   
 

   from langchain_core.prompts import ChatPromptTemplate

safe_sql_prompt = ChatPromptTemplate.from_messages([
  				  ("system","""You are an SQL expert generate only SELECT queries
                  
                  CRITICAL RULES:
                  - NEVER Generate INSERT,DELETE,UPDATE,ALTER,DROP OR TRUNCATE QUERIES
                  - NEVER USE SEMI COLON TO CHAIN STATEMENTS
                  - Only query table listed in the Schema
                  - Limit result to 1000 unless specified
                  
                  Database dialect: {dialect}
                  Available tables and schema:
                  {table_info}""",
                  ("human","{input}")
                   ])
  

Step 7: Building Conversational SQL Agent

In production, you will need an agent that can handle follow-up questions, correct its own mistakes, and retry failed queries.

LangChain's agent framework handles all of this.

    Python
   
 

   from langchain_community.agents_toolkits import create_sql_agent
from langchain_community.agent_toolkits.sql.toolkit import SQLDatabaseToolkit

#The toolkit bundles all SQL related tools
#- sql_db_query: Execute SQL
#- sql_db_schema: Get Table Name
#- sql_db_list_table: List available Tables
#- sql_db_query_checker: Validate SQL before running

toolkit = SQLDatabaseToolkit(db=db,llm=llm)

agent = create_sql_agent(
  		llm=llm,
        toolkit = toolkit,
        verbose = True,             #show reasoning step
        agent_type = "openai-tools",
        max_iterations =10,
        handle_parsing_errors=True  #prevent infinite loop
)

#Agent can handle multi-step reasoning
response = agent.invoke({
  			"input":"Find the customer who spent the most money in last 30 days,"
  					"and tell me the products they bought."

print(response["output"])
  

Step 8: Add Memory and CLI Interface

Add ConversationBufferWindowMemory so that the user can ask follow-up questions, then wrap everything in the interactive loop.

    Python
   
 

   from langChain.memory import ConversationBufferWindowMemory
memory = ConversationBufferWindowMemory(
  		 memory_key="chat_history", return_messages=True, k=5
)

agent_with_memory = create_sql_agent(
  					llm=llm, toolkit = toolkit,
  					agent_type="openai-tools",
  					memory=memory, handle_parsing_errors= TRUE
					)

#follow up question resolve context automatically
agent_with_memory.invoke({"input":"what was your total revenue last month"})
agent_with_memory.invoke({"input":"which region contributed the most"})
agent_with_memory.invoke({"input":"who were the top customers there"})

#CLI loop

while True:
  	q=input("You: ").strip()
    if q.lower() =="quit":break
    print("Agent:",agent_with_memory.invoke({"input": q})["output"])

  

Common Pitfalls

Ambiguous column names across tables: Tell the LLM which particular table to refer to or use include_ tables parameter to limit scope.
Hallucinated table or column name: Always pass sample_rows_in_table=2 so the model sees real data format and is less likely to invent column names.
Slow queries on large tables: Add a LIMIT instruction to your prompt: " Always add limit 100 unless the user asks for more."
Date/Time dialect difference: SQLite uses date('now','-30 days') while PostgreSQL uses NOW() -interval '30 days'. LangChain passes the dialect to the LLM; hence, clearly specify it in the prompt.
Token limit on wide schemas: For a database with 50+ tables, use include_tables to pass only a relevant subset per query, so specify it clearly in your prompt

Conclusion

LangChain's SQL integration removes the translation layer between human and data. Business users get instant answers, analysts focus on interpretation rather than query writing, and the underlying database remains unchanged.

The post covers: natural_language->SQL->answer pipeline, multi-table joins, three layers of safety guardrails, self-correcting agent, and conversation memory. You can use this with BigQuery or Snowflake as well.

Database MySQL sql large language model

Opinions expressed by DZone contributors are their own.

Related

Trending