Building a LangChain.js SQL agent for Presto

// FILL THIS OUTconst catalog = '<CATALOG>';const schema = '<SCHEMA>';const host = '<HOST_IP>';const port = '<PORT>';
class InternalPrestoIntegration {  __client: presto;
  constructor() {    this.getClient();  }
  getClient() {    if (this.__client) {      return this.__client;    }    this.__client = new presto.Client({      host,      port,      catalog,      schema,    });    return this.__client;  }
  async describeTable(tableName: string): Promise<string[][] | string> {    if (!ALLOW_LISTED_TABLE_NAMES[tableName]) {      return `The ${tableName} table is not a valid table or is not allow-listed to query. Please run the get_visible_tables tool to see a list of valid tables and add an entry to this table in TABLE_DETAILS_BY_NAME if there isn't one.`;    }    return (await this.runQuery(`DESCRIBE ${tableName}`)) as string[][] | string;  }
  async showTables(): Promise<string[][] | string> {    return (await this.runQuery(`SHOW TABLES`)) as string[][] | string;  }
  async runQuery(query: string): Promise<unknown | string> {    try {      return await this.__executePrestoQuery(query);    } catch (error) {      return error?.message || 'error running sql query';    }  }
  async __executePrestoQuery(query: string): Promise<unknown> {    const client = this.getClient();    const queryResults: unknown[] = [];    return new Promise((resolve, reject) => {      client.execute({        query: query,        catalog,        schema,        data: (_, data) => queryResults.push(data),        success: error => {          if (error) {            reject(error);          } else {            resolve(queryResults.$a_flatten());          }        },        error: error => reject(error),      });    });  }}export { InternalPrestoIntegration };

import fetch, { Headers, Request, Response } from 'node-fetch';
function setupLangChainForNode16() {  globalThis.fetch = fetch;  globalThis.Headers = Headers;  globalThis.Request = Request;  globalThis.Response = Response;}

async function runSqlAgent({  input,  messages,}: {  input: string;  messages?: { type: 'human' | 'bot'; text: string };}): Promise<string> {  const __postUpdate = async (text: string) => {    // TODO: send update to user  };
  // remind the user how the internal tool works  __postUpdate(    `Running SQL queries...      * Prefix "follow-up" for follow-up questions (max 20 msg)      * Prefix "sql" for a new query (clear history)`,  );
  const prestoIntegration = new InternalPrestoIntegration();
  const yesterday = new Date();  yesterday.setDate(yesterday.getDate() - 1);
  const tools = [    new DynamicTool({      name: 'run_sql_query',      description: `call this to run a valid SELECT presto sql query on the DB...etc`,      func: async query => {        __postUpdate(`[run_sql_query]: ${query}`);        const result = JSON.stringify(await prestoIntegration.runQuery(query));        __postUpdate(result);        return result;      },    }),    new DynamicTool({      name: 'describe_table',      description: 'call this to run a DESCRIBE table presto sql query...etc',      func: async tableName => {        __postUpdate(`[describe_table]: DESCRIBE ${tableName}`);        const result = await prestoIntegration.describeTable(tableName);        const response = JSON.stringify(result);        __postUpdate(response);        return response;      },    }),    new DynamicTool({      name: 'get_visible_tables',      description: 'call this to get a list of all the presto sql tables you can see...etc',      func: async () => {        __postUpdate(`[get_visible_tables]: SHOW TABLES;`);        const result = await prestoIntegration.showTables();        const response = JSON.stringify(result);        __postUpdate(response);        return response;      },    }),  ];
  const model = new ChatOpenAI({    temperature: 0.9,    openAIApiKey: process.env.OPENAI_API_KEY,    modelName: 'gpt-4',  });
  const executor = await initializeAgentExecutor(    tools,    model,    'chat-conversational-react-description',  );
  const pastMessages =    messages?.map(x => {      if (x.type === 'human') {        return new HumanChatMessage(x.text);      }      return new AIChatMessage(x.text);    }) || [];
  const memory = new BufferMemory({    chatHistory: new ChatMessageHistory(pastMessages),    returnMessages: true,    memoryKey: 'chat_history',    inputKey: 'input',  });
  executor.memory = memory;
  try {    const result = await executor.call({      input,    });    return result?.output;  } catch (e) {    return e.message;  }}