> ## Documentation Index
> Fetch the complete documentation index at: https://genai.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# 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

```python theme={null}
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:**

```python theme={null}
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:**

```python theme={null}
# 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:**

```python theme={null}
# 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:**

```python theme={null}
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:

```python theme={null}
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:

```python theme={null}
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

```python theme={null}
check_logs("user_session_sqlite")
```

#### Sample Output

```text theme={null}
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.

<Accordion title="Solution">
  ```python theme={null}
  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")
  ```
</Accordion>

## Practice & Exercises

To practice, open the interactive notebook:

<CardGroup cols={1}>
  <Card title="Practice & Exercises" icon="laptop-code">
    Practice configuring SQL databases locally for persistent conversation sessions.

    [💻 VS Code](vscode://file/Users/sivaprasad/Downloads/GenAI%20With%20Python/public/notebooks/langchain/5_chat_model_save_message_history.ipynb) | [🚀 Colab](https://colab.research.google.com/github/prasad230776/genai-course/blob/master/public/notebooks/langchain/5_chat_model_save_message_history.ipynb) | <a href="/public/notebooks/langchain/5_chat_model_save_message_history.ipynb" download>📥 Download</a>
  </Card>
</CardGroup>