Skip to main content

SQL (SQLAlchemy)

Structured Query Language (SQL) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.

SQLAlchemy is an open-source SQL toolkit and object-relational mapper (ORM) for the Python programming language released under the MIT License.

This notebook goes over a SQLChatMessageHistory class that allows to store chat history in any database supported by SQLAlchemy.

Please note that to use it with databases other than SQLite, you will need to install the corresponding database driver.

Setup​

The integration lives in the langchain-community package, so we need to install that. We also need to install the SQLAlchemy package.

pip install -U langchain-community SQLAlchemy langchain-openai

It's also helpful (but not needed) to set up LangSmith for best-in-class observability

# os.environ["LANGCHAIN_TRACING_V2"] = "true"
# os.environ["LANGCHAIN_API_KEY"] = getpass.getpass()

Usage​

To use the storage you need to provide only 2 things:

  1. Session Id - a unique identifier of the session, like user name, email, chat id etc.
  2. Connection string - a string that specifies the database connection. It will be passed to SQLAlchemy create_engine function.
from langchain_community.chat_message_histories import SQLChatMessageHistory

chat_message_history = SQLChatMessageHistory(
    session_id="test_session", connection_string="sqlite:///sqlite.db"
)

chat_message_history.add_user_message("Hello")
chat_message_history.add_ai_message("Hi")
API Reference:SQLChatMessageHistory
chat_message_history.messages
[HumanMessage(content='Hello'), AIMessage(content='Hi')]

Chaining​

We can easily combine this message history class with LCEL Runnables

To do this we will want to use OpenAI, so we need to install that

from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
from langchain_core.runnables.history import RunnableWithMessageHistory
from langchain_openai import ChatOpenAI
prompt = ChatPromptTemplate.from_messages(
    [
        ("system", "You are a helpful assistant."),
        MessagesPlaceholder(variable_name="history"),
        ("human", "{question}"),
    ]
)

chain = prompt | ChatOpenAI()
chain_with_history = RunnableWithMessageHistory(
    chain,
    lambda session_id: SQLChatMessageHistory(
        session_id=session_id, connection_string="sqlite:///sqlite.db"
    ),
    input_messages_key="question",
    history_messages_key="history",
)
# This is where we configure the session id
config = {"configurable": {"session_id": "<SESSION_ID>"}}
chain_with_history.invoke({"question": "Hi! I'm bob"}, config=config)
AIMessage(content='Hello Bob! How can I assist you today?')
chain_with_history.invoke({"question": "Whats my name"}, config=config)
AIMessage(content='Your name is Bob! Is there anything specific you would like assistance with, Bob?')

Was this page helpful?