LangChain Tutorial (Python): connecting to PostgreSQL for beginners

This tutorial shows you how to connect a LangChain Python app to PostgreSQL, store conversation or application data, and query it with real SQL. You’d use this when you want your agent to read from a database instead of relying only on prompts or in-memory state.

What You'll Need

• •Python 3.10+
• •A running PostgreSQL instance
• •A PostgreSQL database name, user, password, host, and port
• •An OpenAI API key if you want to use an LLM with LangChain
• •These packages:
  • •langchain
  • •langchain-openai
  • •langchain-community
  • •sqlalchemy
  • •psycopg2-binary

Install them with:

pip install langchain langchain-openai langchain-community sqlalchemy psycopg2-binary

Step-by-Step

1. •First, create a PostgreSQL connection string and verify that SQLAlchemy can reach the database. This is the foundation for every LangChain-to-Postgres setup.

from sqlalchemy import create_engine, text

DB_USER = "postgres"
DB_PASSWORD = "postgres"
DB_HOST = "localhost"
DB_PORT = "5432"
DB_NAME = "demo_db"

connection_string = (
    f"postgresql+psycopg2://{DB_USER}:{DB_PASSWORD}"
    f"@{DB_HOST}:{DB_PORT}/{DB_NAME}"
)

engine = create_engine(connection_string)

with engine.connect() as conn:
    result = conn.execute(text("SELECT version();"))
    print(result.fetchone()[0])

2. •Next, create a simple table and insert some sample rows. LangChain works best when the database has predictable schema and clean test data.

from sqlalchemy import create_engine, text

engine = create_engine(connection_string)

setup_sql = """
CREATE TABLE IF NOT EXISTS customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    tier TEXT NOT NULL
);

TRUNCATE TABLE customers;

INSERT INTO customers (name, email, tier) VALUES
('Amina Yusuf', 'amina@example.com', 'gold'),
('Brian Okafor', 'brian@example.com', 'silver'),
('Chipo Moyo', 'chipo@example.com', 'platinum');
"""

with engine.begin() as conn:
    conn.execute(text(setup_sql))

print("Table created and sample data inserted.")

3. •Now wire PostgreSQL into LangChain using SQLDatabase. This gives LangChain a structured view of your schema so it can generate valid SQL against your database.

from langchain_community.utilities import SQLDatabase

db = SQLDatabase.from_uri(connection_string)

print(db.get_usable_table_names())
print(db.run("SELECT * FROM customers LIMIT 2;"))

4. •Add an LLM and build a SQL query chain. This is the part that lets you ask natural-language questions and have LangChain translate them into SQL.

import os
from langchain_openai import ChatOpenAI
from langchain.chains import create_sql_query_chain

os.environ["OPENAI_API_KEY"] = "your-openai-api-key"

llm = ChatOpenAI(model="gpt-4o-mini", temperature=0)
chain = create_sql_query_chain(llm, db)

question = "Which customers are in the gold tier?"
sql_query = chain.invoke({"question": question})

print(sql_query)

5. •Finally, execute the generated query safely through the database object and inspect the result. In production, you should validate or restrict generated SQL before running it against sensitive data.

from langchain_core.prompts import PromptTemplate

question = "List all customer names and tiers."
sql_query = chain.invoke({"question": question})

print("Generated SQL:")
print(sql_query)

result = db.run(sql_query)
print("\nQuery Result:")
print(result)

Testing It

Run the first script to confirm PostgreSQL connectivity before touching LangChain. If that fails, fix your connection string, credentials, or local database service first.

Then run the table setup script and confirm that customers exists with three rows. After that, run the LangChain query scripts and check whether the generated SQL matches your schema.

If the model produces invalid SQL, reduce ambiguity in your prompt and make sure db.get_usable_table_names() returns the table you expect. For real applications, keep temperature at 0 for deterministic SQL generation.

Next Steps

• •Add a custom prompt that constrains SQL generation to read-only queries only.
• •Learn how to use SQLDatabaseToolkit for agentic database workflows.
• •Extend this pattern to Postgres-backed chat history or retrieval pipelines for production agents.

Keep learning

• •The complete AI Agents Roadmap — my full 8-step breakdown
• •Free: The AI Agent Starter Kit — PDF checklist + starter code
• •Work with me — I build AI for banks and insurance companies

By Cyprian Aarons, AI Consultant at Topiax.