# 09. Save chat to SQLite

## SQL (SQLAlchemy) <a href="#sql-sqlalchemy" id="sql-sqlalchemy"></a>

```
Structured Query Language (SQL) Domain-specific language used for silver programming, designed for managing data in relational database management systems (RDBMS) or for stream processing in relational data stream management systems (RDSMS). It is especially useful for dealing with structured data, including the relationship between entities and variables.

SQLAlchemy Is an open source for Python programming languages distributed according to MIT licenses SQL Toolkit and object relationship mapper (ORM).
```

In this laptop `SQLAlchemy` You can store chat records in any database supported by `SQLChatMessageHistory` Describe the class.

`SQLite` To use with a database other than, you need to install that database driver.

```
# API KEY A configuration file for managing environment variables
from dotenv import load_dotenv

# API KEY Load information
load_dotenv()
```

```
True
```

### Usage <a href="#id-1" id="id-1"></a>

To use storage, only the following 2 are provided:

1. `session_id` -A unique identifier for the session, such as username, email, chat ID, etc.
2. `connection` -A string that specifies a database connection. This string is passed to SQLAlchemy's create\_engine function.

```
from langchain_community.chat_message_histories import SQLChatMessageHistory

# SQLChatMessageHistory Create an object and set the session ID and database connection file.
chat_message_history = SQLChatMessageHistory(
    session_id="sql_history", connection="sqlite:///sqlite.db"
)
```

```
# Add a user message.
chat_message_history.add_user_message(
    "Hello? Nice to meet you. My name is Teddy. I am a Langchain developer. Please take care of me in the future!"
)
# AI add a message.
chat_message_history.add_ai_message("Hi Teddy, nice to meet you. Please take care of me too!")
```

* Check for saved conversation. `chat_message_history.messages`

```
# 채팅 메시지 기록의 메시지들
chat_message_history.messages
```

```
[HumanMessage (content='Hi? Nice to meet you. My name is Teddy. I am a Langstein developer. Please do it in the future!'), AIMessage (content=' Hi Teddy, nice to meet you. Please do me too!')] 
```

We have this message recording class [LCEL Runnables ](https://wikidocs.net/235884)It can be easily combined with.

### Apply to Chain <a href="#chain" id="chain"></a>

```
from langchain_core.prompts import (
    ChatPromptTemplate,
    MessagesPlaceholder,
)
from langchain_core.runnables.history import RunnableWithMessageHistory
from langchain_openai import ChatOpenAI
from langchain_core.output_parsers import StrOutputParser
```

```
prompt = ChatPromptTemplate.from_messages(
    [
        # system messages
        ("system", "You are a helpful assistant."),
        # For conversation records Placeholder
        MessagesPlaceholder(variable_name="chat_history"),
        ("human", "{question}"),  question
    ]
)

# chain System Messages.
chain = prompt | ChatOpenAI(model_name="gpt-4o") | StrOutputParser()
```

`sqlite.db` Create a function that brings in-conversation.

```
def get_chat_history(user_id, conversation_id):
    return SQLChatMessageHistory(
        table_name=user_id,
        session_id=conversation_id,
        connection="sqlite:///sqlite.db",
    )
```

`config_fields` Set. This is used as a reference when viewing conversational information.

* `user_id` : User ID
* `conversation_id` : Conversation ID

```
from langchain_core.runnables.utils import ConfigurableFieldSpec

config_fields = [
    ConfigurableFieldSpec(
        id="user_id",
        annotation=str,
        name="User ID",
        description="Unique identifier for a user.",
        default="",
        is_shared=True,
    ),
    ConfigurableFieldSpec(
        id="conversation_id",
        annotation=str,
        name="Conversation ID",
        description="Unique identifier for a conversation.",
        default="",
        is_shared=True,
    ),
]
```

```
chain_with_history = RunnableWithMessageHistory(
    chain,
    get_chat_history,  # Sets a function to retrieve conversation history.
    input_messages_key="question",  # Set the key of the input message to "question"
    history_messages_key="chat_history",  # Set the key for the conversation history message to "history"
    history_factory_config=config_fields,  # Set parameters to be referenced when viewing conversation records.
)
```

* `"configurable"` Under the key `"user_id"` , `"conversation_id"` Set the key-value pair.

```
# config setting
config = {"configurable": {"user_id": "user1", "conversation_id": "conversation1"}}
```

Let's ask a question that asks for a name. If you have a conversation you saved earlier, you will answer it correctly.

* `chain_with_history` Object `invoke` Call the method to generate an answer to the question.
* `invoke` The method has a question dictionary `config` Settings are passed.

```
# questions and config Pass it and execute it.
chain_with_history.invoke({"question": "Hello, nice to meet you, my name is Teddy."}, config)
```

```
 'You said your name was Teddy. Is that correct?' 
```

Same this time `user_id` I have `conversion_id` Set to have different values.

```
# config settings
config = {"configurable": {"user_id": "user1", "conversation_id": "conversation2"}}

# questions and config Execute by passing.
chain_with_history.invoke({"question": "what is my name?"}, config)
```

```
'Sorry, but I can't know your name. How can I help you?' 
```