5. Chat Model Save Message History

Persist chat history database sessions locally using SQLite

In this section, you will learn how to connect your LangChain chat assistant to a local SQLite database to store and retrieve conversation history.

Objectives

1. Understand SQL-backed persistent history tracking.
2. Store message loops dynamically in a local SQLite file using SQLChatMessageHistory.
3. Retrieve and output logs for a specific session ID from a completely separate script.

SQLite Persistence

Unlike cloud databases, SQLite requires zero credentials or network configurations. It saves all logs locally inside a standard .db database file.

Goal

Implement a live terminal chat loop that saves queries to a local SQLite database, and create a second script to retrieve and print those saved conversation logs.

Sample Input

SESSION_ID = "user_session_sqlite"
DB_CONNECTION = "sqlite:///chat_history.db"

Sample Output

Automatic storage of messages inside the local SQLite database.

Plan

• Saver Script:
  1. Import SQLChatMessageHistory from langchain_community.chat_message_histories.
  2. Instantiate the history object using the local database connection.
  3. Start a console chat loop, adding inputs and replies to the history.
• Retriever Script:
  1. Connect to the same SQLite database.
  2. Iterate through and print the history messages.

Code Implementation

1. Conversation Loop & Saver (5a_chat_model_save_message_history_sqlite.py)

Let’s build the SQLite saver application incrementally step-by-step:

Step 1: Imports and Setup

Plan:

1. Import environment variable loader load_dotenv from dotenv.
2. Import SQLChatMessageHistory from langchain_community.chat_message_histories.
3. Import the unified model initializer init_chat_model from langchain.chat_models.

Code Implementation:

from dotenv import load_dotenv
from langchain_community.chat_message_histories import SQLChatMessageHistory
from langchain.chat_models import init_chat_model

load_dotenv()

Step 2: Initialize Database Connection

Plan:

1. Set up a unique SESSION_ID string to identify this chat session.
2. Define the local connection URI sqlite:///chat_history.db.
3. Instantiate the SQLChatMessageHistory object.

Code Implementation:

# Setup SQLite Connection and Session
SESSION_ID = "user_session_sqlite"
DB_CONNECTION = "sqlite:///chat_history.db"

# Initialize SQL-backed Message History
chat_history = SQLChatMessageHistory(
    session_id=SESSION_ID,
    connection_string=DB_CONNECTION
)

Step 3: Setup Chat Model

Plan:

1. Initialize a Groq-provided Llama model (llama-3.3-70b-versatile) using the core abstraction helper init_chat_model.

Code Implementation:

# Initialize Chat Model using core abstractions (Groq Llama model)
model = init_chat_model("llama-3.3-70b-versatile", model_provider="groq")

Step 4: Execute Interactive Chat Loop

Plan:

1. Prompt user queries from the console in a while True loop.
2. Call .add_user_message() to persist the input in the SQL database.
3. Call model.invoke() passing the current full message array.
4. Call .add_ai_message() to persist the model’s answer.

Code Implementation:

print("Start chatting with the AI. Type 'exit' to quit.")

while True:
    human_input = input("User: ")
    if human_input.lower() == "exit":
        break

    # 1. Add user query to SQL database
    chat_history.add_user_message(human_input)

    # 2. Invoke model on complete session history
    ai_response = model.invoke(chat_history.messages)

    # 3. Add AI reply to SQL database
    chat_history.add_ai_message(ai_response.content)

    print(f"AI: {ai_response.content}")

Combined Saver Code

Combining all the steps above gives the final completed script:

from dotenv import load_dotenv
from langchain_community.chat_message_histories import SQLChatMessageHistory
from langchain.chat_models import init_chat_model

load_dotenv()

# Setup SQLite Connection and Session
SESSION_ID = "user_session_sqlite"
DB_CONNECTION = "sqlite:///chat_history.db"

# Initialize SQL-backed Message History
chat_history = SQLChatMessageHistory(
    session_id=SESSION_ID,
    connection_string=DB_CONNECTION
)

# Initialize Chat Model using core abstractions (Groq Llama model)
model = init_chat_model("llama-3.3-70b-versatile", model_provider="groq")

print("Start chatting with the AI. Type 'exit' to quit.")

while True:
    human_input = input("User: ")
    if human_input.lower() == "exit":
        break

    # 1. Add user query to SQL database
    chat_history.add_user_message(human_input)

    # 2. Invoke model on complete session history
    ai_response = model.invoke(chat_history.messages)

    # 3. Add AI reply to SQL database
    chat_history.add_ai_message(ai_response.content)

    print(f"AI: {ai_response.content}")

2. Session Retriever Script (5b_chat_model_retrieve_message_history_sqlite.py)

Below is the retriever script that loads the SQL log independently and outputs the session chat logs:

from langchain_community.chat_message_histories import SQLChatMessageHistory

# Setup SQLite Connection and Session (must match the saver script)
SESSION_ID = "user_session_sqlite"
DB_CONNECTION = "sqlite:///chat_history.db"

# Load the SQL-backed Message History
chat_history = SQLChatMessageHistory(
    session_id=SESSION_ID,
    connection_string=DB_CONNECTION
)

print(f"--- Stored Logs for Session: {SESSION_ID} ---")
for message in chat_history.messages:
    role = "User" if message.type == "human" else "AI"
    print(f"{role}: {message.content}")

Exercise: Session Config Checker 🔍

Goal

Write a python utility function check_logs(session_id: str) that connects to our local SQLite database and prints how many human vs AI messages are currently stored for the given session.

Sample Input

check_logs("user_session_sqlite")

Sample Output

Total Messages: 4 (User: 2, AI: 2)

Plan

1. Instantiate the SQLChatMessageHistory inside the function.
2. Iterate through .messages and count human and ai instances.
3. Print the formatted totals.

Solution

from langchain_community.chat_message_histories import SQLChatMessageHistory

def check_logs(session_id: str):
    chat_history = SQLChatMessageHistory(
        session_id=session_id,
        connection_string="sqlite:///chat_history.db"
    )
    
    msgs = chat_history.messages
    user_count = sum(1 for m in msgs if m.type == "human")
    ai_count = sum(1 for m in msgs if m.type == "ai")
    
    print(f"Total Messages: {len(msgs)} (User: {user_count}, AI: {ai_count})")

check_logs("user_session_sqlite")

Practice & Exercises

To practice, open the interactive notebook:

Practice & Exercises

Practice configuring SQL databases locally for persistent conversation sessions.

💻 VS Code | 🚀 Colab | 📥 Download