Function generateSqlQuery

server/modules/ai/generateSqlQuery.js:13–84

(schema, question, conversationHistory = [], currentQuery = "")

}

async function generateSqlQuery(schema, question, conversationHistory = [], currentQuery = "") {
  if (!openaiClient) {
    throw new Error("OpenAI client is not initialized. Please check your environment variables.");
  }

  const formattedSchema = JSON.stringify(schema).replace(/\\/g, "").replace(/"/g, "");

  try {
    const messages = [
      {
        role: "system",
        content: `
        You are an expert SQL query generator. Use the following database schema to generate an SQL query that matches the user's intent.
        The user might also provide a current query, which you should use to generate the final query but only if it's relevant.
        Database Schema:
        ${formattedSchema}

        Output the SQL query only.

        If the user asks for a query with variables, you should use the variables in the query.
        Example: SELECT * FROM movies WHERE status = {{status}} LIMIT 10;

        Don't add variables if not specified by the user.
      `,
      },
      ...conversationHistory,
    ];

    messages.push({
      role: "user",
      content: question,
    });

    if (currentQuery) {
      messages.push({
        role: "user",
        content: `Current Query: ${currentQuery}`,
      });
    }

    const response = await openaiClient.chat.completions.create({
      model: openAiModel || "gpt-4o-mini",
      messages,
    });

    const sqlQuery = response.choices[0].message.content;

    conversationHistory.push({
      role: "user",
      content: question,
    });

    if (currentQuery) {
      conversationHistory.push({
        role: "system",
        content: `Current Query: ${currentQuery}`,
      });
    }