[Question]: I want to store the chat_history in a postgresql that is installed locally on my machine.

[SiddharthMurjani]

Question Validation

• [X] I have searched both the documentation and discord for an answer.

Question

So, I am trying to create a chatbot that performs RAG over documents of different companies. I what to store chat_session in a postgres table using chat_session_id.

Also, when I retrive the data back from the table, I wany to convert it into

[List[ChatMessage]]

and pass it to my ContextChatEngine

chat_engine = ContextChatEngine.from_defaults(
        memory=memory,
        retriever=index.as_retriever(),
    )

[dosubot[bot]]

To store chat sessions in a PostgreSQL table with a chat_session_id, retrieve the data back from the table, convert it into a List[ChatMessage] in Python, and pass the retrieved data to your ContextChatEngine, you can follow these steps:

1. Define the PostgreSQL table schema:

   CREATE TABLE chat_sessions (
       chat_session_id SERIAL PRIMARY KEY,
       session_key VARCHAR(255) NOT NULL,
       message JSONB NOT NULL
   );

2. Implement the methods in Python:

   import psycopg2
   from typing import List
   from llama_index.legacy.llms import ChatMessage
   from llama_index.legacy.storage.chat_store.base import BaseChatStore
   
   class PostgresChatStore(BaseChatStore):
       def __init__(self, db_config):
           self.conn = psycopg2.connect(**db_config)
           self.cursor = self.conn.cursor()
   
       def set_messages(self, key: str, messages: List[ChatMessage]) -> None:
           self.cursor.execute("DELETE FROM chat_sessions WHERE session_key = %s", (key,))
           for message in messages:
               self.cursor.execute(
                   "INSERT INTO chat_sessions (session_key, message) VALUES (%s, %s)",
                   (key, message.to_json())
               )
           self.conn.commit()
   
       def get_messages(self, key: str) -> List[ChatMessage]:
           self.cursor.execute("SELECT message FROM chat_sessions WHERE session_key = %s", (key,))
           rows = self.cursor.fetchall()
           return [ChatMessage.from_json(row[0]) for row in rows]
   
       def add_message(self, key: str, message: ChatMessage) -> None:
           self.cursor.execute(
               "INSERT INTO chat_sessions (session_key, message) VALUES (%s, %s)",
               (key, message.to_json())
           )
           self.conn.commit()
   
       def delete_messages(self, key: str) -> Optional[List[ChatMessage]]:
           messages = self.get_messages(key)
           self.cursor.execute("DELETE FROM chat_sessions WHERE session_key = %s", (key,))
           self.conn.commit()
           return messages
   
       def delete_message(self, key: str, idx: int) -> Optional[ChatMessage]:
           messages = self.get_messages(key)
           if idx < len(messages):
               message = messages.pop(idx)
               self.set_messages(key, messages)
               return message
           return None
   
       def delete_last_message(self, key: str) -> Optional[ChatMessage]:
           messages = self.get_messages(key)
           if messages:
               message = messages.pop()
               self.set_messages(key, messages)
               return message
           return None
   
       def get_keys(self) -> List[str]:
           self.cursor.execute("SELECT DISTINCT session_key FROM chat_sessions")
           rows = self.cursor.fetchall()
           return [row[0] for row in rows]
   
   # Example usage
   db_config = {
       'dbname': 'your_db',
       'user': 'your_user',
       'password': 'your_password',
       'host': 'your_host',
       'port': 'your_port'
   }
   
   chat_store = PostgresChatStore(db_config)
   chat_engine = ContextChatEngine(chat_store)

3. Pass the retrieved data to ContextChatEngine:

   session_key = 'example_session_key'
   messages = chat_store.get_messages(session_key)
   chat_engine.process_messages(messages)

This code sets up a PostgreSQL-backed chat store, retrieves chat messages, converts them into List[ChatMessage], and passes them to the ContextChatEngine ^[1][2].

To continue talking to Dosu, mention @dosu.

[logan-markewich]

@SiddharthMurjani you can convert your chat history to json / string, then store it wherever you need

Something like

chat_history_dicts = [x.dict() for x in chat_engine.chat_history]
chat_history_blob = json.dumps(chat_history_dicts)

chat_history_dicts = json.loads(chat_history_blob)
chat_history = [ChatHistory.parse_obj(x) for x in chat_history_dicts]

chat_engine.chat("Hello", chat_history=chat_history)

[SiddharthMurjani]

Thankyou @logan-markewich !