PostgresChatMessageHistory: error: column "message" does not exist

[chrismichaelps]

Checked other resources

• [X] I added a very descriptive title to this issue.
• [X] I searched the LangChain.js documentation with the integrated search.
• [X] I used the GitHub search to find a similar question and didn't find it.
• [X] I am sure that this is a bug in LangChain.js rather than my code.
• [X] The bug is not resolved by updating to the latest stable version of LangChain (or the specific integration package).

Example Code

Example Code:

import pg, { PoolConfig } from "pg";

import { PostgresChatMessageHistory } from "@langchain/community/stores/message/postgres";
import { ChatOpenAI } from "@langchain/openai";
import { RunnableWithMessageHistory } from "@langchain/core/runnables";
import {
  ChatPromptTemplate,
  MessagesPlaceholder,
} from "@langchain/core/prompts";
import { StringOutputParser } from "@langchain/core/output_parsers";

const poolConfig: PoolConfig = {
  host: 'localhost',
  port: 5432,
  user: '....',
  password: '....',
  database: '.....',
}

const pool = new pg.Pool(poolConfig)

const apiKey = '...';

const model = new ChatOpenAI({ apiKey: apiKey, model: 'gpt-3.5-turbo', temperature: 0, n: 3 })

const prompt = ChatPromptTemplate.fromMessages([
  [
    "system",
    "You are a helpful assistant. Answer all questions to the best of your ability.",
  ],
  new MessagesPlaceholder("chat_history"),
  ["human", "{input}"],
]);

const chain = prompt.pipe(model).pipe(new StringOutputParser());

const chainWithHistory = new RunnableWithMessageHistory({
  runnable: chain,
  inputMessagesKey: "input",
  historyMessagesKey: "chat_history",
  getMessageHistory: async (sessionId) => {
    const chatHistory = new PostgresChatMessageHistory({
      tableName: 'directories',
      sessionId,
      pool,
    });
    return chatHistory;
  },
});

const res1 = await chainWithHistory.invoke(
  {
    input: "Hi! I'm Chris.",
  },
  { configurable: { sessionId: "langchain-test-session" } }
);
console.log(res1);


const res2 = await chainWithHistory.invoke(
  { input: "Provide me with adventure recommendations in the municipality of San Juan." },
  { configurable: { sessionId: "langchain-test-session" } }
);
console.log(res2);

await pool.end();

Error Message and Stack Trace (if applicable)

Error

chrismperez@Chriss-MBP vt-ai % yarn start
yarn run v1.22.10
$ esno src/index.ts
/Users/chrismperez/Desktop/chris-projects/vt-ai/node_modules/pg-pool/index.js:45
    Error.captureStackTrace(err)
          ^

error: column "message" does not exist
    at /Users/chrismperez/Desktop/chris-projects/vt-ai/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresChatMessageHistory.getMessages (file:///Users/chrismperez/Desktop/chris-projects/vt-ai/node_modules/@langchain/community/dist/stores/message/postgres.js:115:21)
    at async RunnableWithMessageHistory._enterHistory (file:///Users/chrismperez/Desktop/chris-projects/vt-ai/node_modules/@langchain/core/dist/runnables/history.js:212:26)
    at async file:///Users/chrismperez/Desktop/chris-projects/vt-ai/node_modules/@langchain/core/dist/runnables/base.js:1484:34 {
  length: 106,
  severity: 'ERROR',
  code: '42703',
  detail: undefined,
  hint: undefined,
  position: '8',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '3513',
  routine: 'errorMissingColumn'
}

Node.js v18.18.0
error Command failed with exit code 1.

Description

Description:

The error is occurring when I try to specify the table I want to use in the tableName property of the PostgresChatMessageHistory class. When executing the langchain code the langchain_chat_histories table does not exist.

But if I don't use the tableName property the code responds correctly.

System Info

System Info:

• [x] plataform: Mac M1
• [x] node: v18.18.0
• [x] yarn: v1.22.10
• [x] package.json

  "dependencies": {
    "@langchain/community": "^0.2.13",
    "@langchain/openai": "^0.0.33",
    "@types/pg": "^8.11.6",
    "pg": "^8.12.0",
    "typeorm": "^0.3.20"
  },

[dosubot[bot]]

Hey there, @ChrisMichaelPerezSantiago! 👋 I'm here to help you out with any bugs, questions, or contributions you might have. Let's squash some bugs together!

To resolve the error "column 'message' does not exist" when using the PostgresChatMessageHistory class with a specified tableName, ensure that the table specified by tableName has been created with the correct schema. The PostgresChatMessageHistory class expects the table to have at least the following columns:

• id (SERIAL PRIMARY KEY)
• session_id (VARCHAR(255) NOT NULL)
• message (JSONB NOT NULL)

You can ensure the table is created correctly by calling the ensureTable method, which is designed to create the table if it does not already exist. Here is the relevant part of the code that creates the table:

private async ensureTable(): Promise<void> {
    if (this.initialized) return;

    const query = `
        CREATE TABLE IF NOT EXISTS ${this.tableName} (
            id SERIAL PRIMARY KEY,
            session_id VARCHAR(255) NOT NULL,
            message JSONB NOT NULL
        );`;

    try {
        await this.pool.query(query);
    } catch (e: any) {
        if (!("code" in e) || e.code !== "23505") {
            throw e;
        }
    }
    this.initialized = true;
}

Make sure this method is called before any operations that interact with the table, such as addMessage, getMessages, or clear. The ensureTable method is already called within these methods, so you should not need to call it explicitly.

If you still encounter the error, verify that the table exists and has the correct schema by running the following SQL query directly in your PostgreSQL database:

CREATE TABLE IF NOT EXISTS directories (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    message JSONB NOT NULL
);

Replace directories with your specified tableName if it is different. This will ensure that the table has the required columns ^[1].

To continue talking to Dosu, mention @dosu.

[chrismichaelps]

@dosue

The langchain_chat_histories table initially does not exist.

langchain_chat_histories is the table that should contain:

id SERIAL PRIMARY KEY,
session_id VARCHAR(255) NOT NULL,
message JSONB NOT NULL

[dosubot[bot]]

To ensure the langchain_chat_histories table is created correctly with the required schema, you can use the ensureTable method provided by the PostgresChatMessageHistory class. This method will create the table if it does not already exist. Here is how you can do it:

1. Instantiate the PostgresChatMessageHistory class:

   • Provide the tableName if you want to use a custom table name.
   • Provide the sessionId for storing and retrieving chat message history.
   • Provide either a pg.Pool instance or a pg.PoolConfig object for the Postgres connection.

2. Ensure the table is created:

   • Call the ensureTable method to create the table if it does not exist.

Here is an example:

import pg from 'pg';
import { PostgresChatMessageHistory } from '@langchain/community/stores/message/postgres';

const pool = new pg.Pool({
  host: 'localhost',
  port: 5432,
  user: 'myuser',
  password: 'ChangeMe',
  database: 'api',
});

const chatHistory = new PostgresChatMessageHistory({
  tableName: 'langchain_chat_histories',
  sessionId: 'lc-example',
  pool,
});

// Ensure the table is created
await chatHistory.ensureTable();

The ensureTable method will execute the following SQL query to create the table if it does not exist:

CREATE TABLE IF NOT EXISTS langchain_chat_histories (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    message JSONB NOT NULL
);

This ensures that the table is created correctly and that the PostgresChatMessageHistory class can interact with it without errors ^[1].

To continue talking to Dosu, mention @dosu.

[chrismichaelps]

@dosue Your solution is not correct.I want to consume the data (ask questions) from the directories table. But at the same time the langchain_chat_histories table doesnot exist.

[dosubot[bot]]

To resolve the issue where the PostgresChatMessageHistory class is throwing an error that the column "message" does not exist when trying to consume data from the directories table, you need to ensure that the directories table has the correct schema. The PostgresChatMessageHistory class expects the table to have the following columns:

• id (SERIAL PRIMARY KEY)
• session_id (VARCHAR(255) NOT NULL)
• message (JSONB NOT NULL)

You can modify the directories table to include the message column with the following SQL command:

ALTER TABLE directories ADD COLUMN message JSONB NOT NULL;

Alternatively, if you need to create the table from scratch, use:

CREATE TABLE directories (
    id SERIAL PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    message JSONB NOT NULL
);

Ensure that the directories table has the correct schema, and the error should be resolved ^[1].

To continue talking to Dosu, mention @dosu.

[chrismichaelps]

@dosue

Your solution is not correct.

The tableName property is used to specify the table that I want to use to consume data. But the langchain_chat_histories table must be created to keep record of the conversation history.

The error I am having is the following:

/Users/chrismperez/Desktop/chris-projects/vt-ai/node_modules/pg-pool/index.js:45
 Error.captureStackTrace(err)
 ^
error: column "message" does not exist

[dosubot[bot]]

To resolve the error "column 'message' does not exist" and ensure both the directories table and the langchain_chat_histories table are correctly utilized for consuming data and recording conversation history, you need to ensure that the langchain_chat_histories table is created with the correct schema and that your queries are correctly referencing the columns.

From the provided code, the langchain_chat_histories table is created with the following columns: id, session_id, type, content, role, name, and additional_kwargs. The message column does not exist in this schema.

Here is the relevant part of the code that ensures the table is created correctly:

private async ensureTable(): Promise<void> {
  if (this.tableInitialized) {
    return;
  }

  const query = `CREATE TABLE IF NOT EXISTS ${this.tableName} (id BINARY(16) PRIMARY KEY, session_id VARCHAR(255), type VARCHAR(255), content VARCHAR(255), role VARCHAR(255), name VARCHAR(255), additional_kwargs VARCHAR(255));`;

  await this.connection.execute(query);

  const indexQuery = `ALTER TABLE ${this.tableName} MODIFY id BINARY(16) DEFAULT (UUID_TO_BIN(UUID()));`;

  await this.connection.execute(indexQuery);

  this.tableInitialized = true;
}

To ensure that the directories table is also correctly utilized, you need to make sure that any operations involving this table are correctly defined and executed. However, the provided context does not include any information about the directories table.

If you are encountering the error "column 'message' does not exist," it is likely that there is a query somewhere in your code that incorrectly references a non-existent column. Ensure that all your queries reference the correct columns as defined in the langchain_chat_histories table schema. For example, when adding a message:

const query = `INSERT INTO ${this.tableName} (session_id, type, content, role, name, additional_kwargs) VALUES (:session_id, :type, :content, :role, :name, :additional_kwargs)`;
const params = {
  session_id: this.sessionId,
  type: messageToAdd[0].type,
  content: messageToAdd[0].data.content,
  role: messageToAdd[0].data.role,
  name: messageToAdd[0].data.name,
  additional_kwargs: JSON.stringify(messageToAdd[0].data.additional_kwargs),
};
await this.connection.execute(query, params);

And when retrieving messages:

const query = `SELECT * FROM ${this.tableName} WHERE session_id = :session_id`;
const params = {
  session_id: this.sessionId,
};
const rawStoredMessages = await this.connection.execute(query, params);
const storedMessagesObject = rawStoredMessages.rows as unknown as selectStoredMessagesDTO[];

const orderedMessages: StoredMessage[] = storedMessagesObject.map(
  (message) => {
    const data = {
      content: message.content,
      additional_kwargs: JSON.parse(message.additional_kwargs),
    } as StoredMessageData;

    if (message.role) {
      data.role = message.role;
    }

    if (message.name) {
      data.name = message.name;
    }

    return {
      type: message.type,
      data,
    };
  }
);
return mapStoredMessagesToChatMessages(orderedMessages);

Ensure that your table schemas and queries are correctly defined and referenced to avoid the "column 'message' does not exist" error ^[1][2][3][4].

To continue talking to Dosu, mention @dosu.

[chrismichaelps]

@dosue

The directory table contains these columns: id, title, description, phone, created_at, updated_at, published_at, created_by_id, updated_by_id

[dosubot[bot]]

To resolve the error and correctly configure your setup to use the directories table for data consumption while also ensuring that the langchain_chat_histories table is created to keep a record of the conversation history, you need to instantiate the PostgresChatMessageHistory class with the appropriate parameters and ensure the langchain_chat_histories table is created.

Here is an example of how you can do this:

1. Ensure the langchain_chat_histories table is created:

import pg from 'pg';
import { PostgresChatMessageHistory } from '@langchain/community/stores/message/postgres';

const pool = new pg.Pool({
  host: 'localhost',
  port: 5432,
  user: 'myuser',
  password: 'ChangeMe',
  database: 'api',
});

const chatHistory = new PostgresChatMessageHistory({
  tableName: 'langchain_chat_histories', // This ensures the table for chat history is created
  sessionId: 'lc-example',
  pool,
});

// Ensure the table is created
await chatHistory.ensureTable();

2. Use the directories table for data consumption:

Since the directories table has a different schema, you should handle data consumption separately from the chat history. You can query the directories table directly using the pg library.

Here is an example of how you can query the directories table:

const query = 'SELECT * FROM directories WHERE id = $1';
const values = [1]; // Example value

try {
  const res = await pool.query(query, values);
  console.log(res.rows);
} catch (err) {
  console.error('Error executing query', err.stack);
}

3. Integrate both functionalities:

You can integrate both functionalities by ensuring the langchain_chat_histories table is used for chat history and the directories table is used for data consumption.

Here is an example of how you can integrate both:

import pg from 'pg';
import { PostgresChatMessageHistory } from '@langchain/community/stores/message/postgres';
import { ChatOpenAI } from '@langchain/openai';
import { RunnableWithMessageHistory } from '@langchain/core/runnables';
import { ChatPromptTemplate, MessagesPlaceholder } from '@langchain/core/prompts';
import { StringOutputParser } from '@langchain/core/output_parsers';

const pool = new pg.Pool({
  host: 'localhost',
  port: 5432,
  user: 'myuser',
  password: 'ChangeMe',
  database: 'api',
});

const chatHistory = new PostgresChatMessageHistory({
  tableName: 'langchain_chat_histories',
  sessionId: 'lc-example',
  pool,
});

// Ensure the table is created
await chatHistory.ensureTable();

const model = new ChatOpenAI({ apiKey: 'YOUR_API_KEY', model: 'gpt-3.5-turbo', temperature: 0, n: 3 });

const prompt = ChatPromptTemplate.fromMessages([
  [
    'system',
    'You are a helpful assistant. Answer all questions to the best of your ability.',
  ],
  new MessagesPlaceholder('chat_history'),
  ['human', '{input}'],
]);

const chain = prompt.pipe(model).pipe(new StringOutputParser());

const chainWithHistory = new RunnableWithMessageHistory({
  runnable: chain,
  inputMessagesKey: 'input',
  historyMessagesKey: 'chat_history',
  getMessageHistory: async (sessionId) => {
    const chatHistory = new PostgresChatMessageHistory({
      tableName: 'langchain_chat_histories',
      sessionId,
      pool,
    });
    return chatHistory;
  },
});

const res1 = await chainWithHistory.invoke(
  {
    input: "Hi! I'm Chris.",
  },
  { configurable: { sessionId: 'langchain-test-session' } }
);
console.log(res1);

const res2 = await chainWithHistory.invoke(
  { input: 'Provide me with adventure recommendations in the municipality of San Juan.' },
  { configurable: { sessionId: 'langchain-test-session' } }
);
console.log(res2);

// Query the directories table
const query = 'SELECT * FROM directories WHERE id = $1';
const values = [1]; // Example value

try {
  const res = await pool.query(query, values);
  console.log(res.rows);
} catch (err) {
  console.error('Error executing query', err.stack);
}

await pool.end();

This setup ensures that the langchain_chat_histories table is created and used for storing chat message history, while the directories table is used for data consumption as needed ^[1].

To continue talking to Dosu, mention @dosu.

[MJDeligan]

If a table with the name directories already exists, then the message history will not create a new table. The tableName you pass to the message history is the table you want the history to use to store messages. This defaults to langchain_chat_histories. It looks like you've created a directories table for some other purpose and are now passing that name to the message history. The history will assume that the existing table whose name you've passed is a table that follows the chat message table schema, which it does not since

The directory table contains these columns: id, title, description, phone, created_at, updated_at, published_at, created_by_id, updated_by_id

I'm not sure why you are passing the name of that table to the message history. If you need that table, then you should use another table name. If you don't need it, then drop it and use the directories name.

[chrismichaelps]

@MJDeligan Thanks for the clarification.

And how can I interact with the data of a particular table?

[MJDeligan]

I'm guessing you cannot query the data based on the input. Retrieval will depend on whether the data can be reliably searched by exact or pattern matching. If for example you have a table with people and those people are associated with a unique username and you want to answer queries such as "How old is lisab561?" then tool calling would be the right idea, since the llm can infer the username and issue a tool call, which you would implement as a function that performs the sql query.

If however you want to retrieve data which can't be matched exactly or by some pattern and instead requires a semantic match then you need a vectorstore with embeddings for the data you want to match.

A case for this would be if your people data has descriptions for people and you want to handle queries such as "What's the name of a kind and loving person". User lisab561 might have a description like "Does sports and especially loves climbing. She's a lovely and nice person.". Obviously this is not an exact match but semantically her description fits the query and a vectorstore would be able to match the query to the data.