LLM — Connecting LangChain with PostgreSQL for Text-to-SQL Integration

LangChain is a powerful framework designed for building applications with language models. By integrating LangChain with PostgreSQL, you can leverage natural language processing to interact with your database using simple text inputs.

Photo by Luke Turner on Unsplash

This text-to-SQL integration simplifies querying databases, making data access more intuitive and accessible for users without deep SQL knowledge.

In this article, we’ll walk through the process of connecting LangChain with PostgreSQL and provide sample Python code and prompts to get you started.

Let’s get down into the code 🚀

Step-by-Step Guide

Step 1: Set Up Your PostgreSQL Database

First, ensure your PostgreSQL server is running and you have created a database. You can create a new database using the following command in the psql shell:

CREATE DATABASE mydatabase;

Step 2: Create a Database Table

For this example, let’s create a simple table to store employee information:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(50),
    salary NUMERIC
);

Step 3: Install Required Python Libraries

Install LangChain and psycopg2 using pip:

pip install langchain psycopg2

Step 4: Write Python Code to Connect LangChain with PostgreSQL

Here’s a sample Python script that integrates LangChain with PostgreSQL:

import psycopg2
from langchain import LangChain

# Connect to your PostgreSQL database
conn = psycopg2.connect(
    dbname="mydatabase",
    user="myuser",
    password="mypassword",
    host="localhost",
    port="5432"
)
cursor = conn.cursor()

# Initialize LangChain
langchain = LangChain()

# Define a function to convert text to SQL and execute the query
def text_to_sql(text):
    # Use LangChain to generate SQL from text
    sql_query = langchain.text_to_sql(text)
    
    try:
        # Execute the SQL query
        cursor.execute(sql_query)
        # Fetch and return the results
        results = cursor.fetchall()
        return results
    except Exception as e:
        return str(e)

# Example prompt to convert text to SQL
prompt = "Show all employees with a salary greater than 50000"

# Convert the prompt to SQL and execute
results = text_to_sql(prompt)
print(results)

# Close the database connection
cursor.close()
conn.close()

Step 5: Testing the Integration

In order to test the integration, ensure your PostgreSQL database has some data. Insert some dummy data into the employees table with:

INSERT INTO employees (name, position, salary) VALUES
('John Doe', 'Software Engineer', 60000),
('Jane Smith', 'Project Manager', 75000),
('Emily Johnson', 'Designer', 50000);

Now, when you run the Python script, it should convert the natural language prompt into a SQL query and fetch the results from the database.

Sample Prompts and Expected Outputs

Here are some sample prompts you can try with the text_to_sql function:

Prompt: “List all project managers”

• Generated SQL: SELECT * FROM employees WHERE position = 'Project Manager';
• Expected Output: [('Jane Smith', 'Project Manager', 75000)]

Prompt: “Show employees with salaries less than 60000”

• Generated SQL: SELECT * FROM employees WHERE salary < 60000;
• Expected Output: [('Emily Johnson', 'Designer', 50000)]

Prompt: “What is the average salary of all employees?”

• Generated SQL: SELECT AVG(salary) FROM employees;
• Expected Output: [(61666.67)]

Conclusions

Integrating LangChain with PostgreSQL for text-to-SQL functionality can greatly enhance the usability of your database systems. This setup allows users to interact with the database using natural language, abstracting away the complexity of SQL.

With the provided sample code and prompts, you should be able to set up and start experimenting with this powerful integration. 🚀

By combining the capabilities of LangChain and PostgreSQL, you can create more intuitive and user-friendly data applications.