NL2SQL with LangChain and Azure SQL Database

Brian Spendolini

LangChain is an open-source framework for creating applications that use and are powered by language models (LLM/MLM/SML). In this post, basic LangChain components (toolkits, chains, agents) will be used to create a natural language to SQL prompt that will allow interactions with an Azure SQL Database; just ask the database what you want as if speaking to another person.

Getting Started

Looking at the python notebook used for this sample, you may notice in the repository there are 2. One is using OpenAI and the other is using Azure OpenAI. This post will be using the OpenAI notebook. The differences are very minor with only change being a reference to where the Azure OpenAI endpoints are located.

The first section in the notebook sets up our python environment by installing the necessary libraries via a requirements.txt file.

pip install -r requirements.txt

Next, install the SQL ODBC driver and referenced in the notebook are links for Windows, MacOS, and Linux. Included in the repository is the script for Ubuntu because that’s the OS that is used with GitHub codespace; the easiest place to run this sample.

Remember to fill out the values in the .env file. For this example, all that needs to be filled out is the OPENAI_API_KEY and connection information for the py-connectionString variable.

AZURE_OPENAI_API_KEY=""
AZURE_OPENAI_ENDPOINT=""
OPENAI_API_KEY=""
py-connectionString="mssql+pyodbc://USERNAME:PASSWORD@SERVER_NAME.database.windows.net/DATABASE_NAME?driver=ODBC+Driver+18+for+SQL+Server"

The last setup section for the LangChain with Azure SQL Database example is the table creation script.

create table [dbo].[langtable] (id int Identity, username nvarchar(100))
GO

insert into [dbo].[langtable] (username) values('sammy')
insert into [dbo].[langtable] (username) values('mary')
insert into [dbo].[langtable] (username) values('jane')
insert into [dbo].[langtable] (username) values('fred')
insert into [dbo].[langtable] (username) values('billy')
insert into [dbo].[langtable] (username) values('jonny')
insert into [dbo].[langtable] (username) values('kenny')
insert into [dbo].[langtable] (username) values('dan')
insert into [dbo].[langtable] (username) values('frank')
insert into [dbo].[langtable] (username) values('jenny')
GO

select * from [dbo].[langtable]
GO

import pyodbc
import os
from dotenv import load_dotenv
from langchain.agents import create_sql_agent
from langchain.agents.agent_types import AgentType
from langchain.sql_database import SQLDatabase
from langchain_community.agent_toolkits import SQLDatabaseToolkit
from langchain_openai import OpenAI
load_dotenv()

from sqlalchemy import create_engine

connectionString=os.environ["py-connectionString"]

db_engine = create_engine(connectionString)

db = SQLDatabase(db_engine, view_support=True, schema="dbo")

# test the connection
print(db.dialect)
print(db.get_usable_table_names())
db.run("select convert(varchar(25), getdate(), 120)")

The next section utilizes the LangChain SQL Database Toolkit to create a LangChain SQL agent that uses OpenAI as the LLM and the database connection.

toolkit = SQLDatabaseToolkit(db=db, llm=OpenAI(temperature=0))

agent_executor = create_sql_agent(
llm=OpenAI(temperature=0),
toolkit=toolkit,
verbose=True,
agent_type=AgentType.ZERO_SHOT_REACT_DESCRIPTION,
)

agent_executor.invoke("count the rows in the langtable table.")

> Entering new AgentExecutor chain... 
I need to use a SQL query to count the rows in the langtable table. 
Action: sql_db_query 
Action Input: SELECT COUNT(*) FROM langtable[(10,)]10 rows were counted in the langtable table. 
Final Answer: 10 
> Finished chain.
{'input': 'count the rows in the langtable table.', 'output': '10'}

Summary

Brian Spendolini Senior Product Manager, Azure SQL Database

Follow