SQL query Generator using OpenAI API with NodeJs

Published in

Globant

6 min readJun 7, 2023

In this article, we will use the OpenAI API to generate SQL queries from natural language descriptions. In response to a text prompt, OpenAI uses the gpt-3.5-turbo models which can translate English prompts to SQL queries.

Definition

OpenAI’s SQL query generator is an AI-based system for generating SQL queries from natural language descriptions. The system is designed to take a user query in natural language and generate a valid SQL query.

What features are available?

SQL generation from English textual instructions. Supports SELECT, UPDATE, DELETE queries, CREATE and ALTER TABLE requests, constraints, window functions, and literally everything!
SQL query explanation to plain English
Your custom database schema (tables, fields, types) connection (with history)
SQL dialects for MySQL, PostgreSQL, Snowflake, BigQuery, and MS SQL Server.

What are the use cases? The app is ideal for beginners, who want to learn how to write SQL queries and also for professionals(for example, data analysts, data scientists, data engineers, and any software developers), who want to save time or improve skills in their work.

OpenAI Models

The OpenAI API is powered by a diverse set of models with different capabilities and price points. You can also make limited customizations to our original base models for your specific use case with fine-tuning. Below are some OpenAI models:

GPT-4: A set of models that improve on GPT-3.5 and can understand as well as generate natural language or code.
GPT-3.5: A set of models that improve on GPT-3 and can understand as well as generate natural language or code
DALL·E: A model that can generate and edit images given a natural language prompt
Whisper: A model that can convert audio into text
Moderation: A fine-tuned model that can detect whether text may be sensitive or unsafe

More details are available in the official OpenAI Models documentation.

Creating the needed API key

To create the API key we need, we must follow the following steps:

Access our user profile in OpenAI
Select the View API Keys option in the side menu, and once there click on the Create new secret key button.

NodeJs Server Configuration

Let’s create the NodeJs server with query Generator API endpoint. The steps are listed below.

Run npm init -y command to generate a package.json file.
Install the dependencies required to set up the server such as express, body-parser, dotenv, and cors.

npm i --save express body-parser dotenv cors

3. Install openai dependency to use OpenAI APIs.

npm i --save openai

4. Create a .env file and add the environment variable OPENAI_API_KEY.

OPENAI_API_KEY = "Added here openai API key"

5. NodeJs server configure with OpenAI.

const express = require("express")
const { Configuration, OpenAIApi } = require("openai")
const cors = require("cors");
const bodyParser = require("body-parser");
require("dotenv").config();

const app = express()
app.use(bodyParser.json());
app.use(cors());

const configuration = new Configuration({
  apiKey: process.env.OPENAI_API_KEY || ""
})
const openai = new OpenAIApi(configuration)

app.listen(8000, ()=>{
  console.log(`servcer running on port: 8000`)
})

6. Create POST request endpoint /completions to generate SQL query.

app.post('/completions',async (req, res) => {
  try{
  const completions = await openai.createChatCompletion({
    model: "gpt-3.5-turbo",
    messages: [{
      role: "user", 
      content: "Create a SQL request to " + req.body.message
    }]
  }) 
  res.send(completions.data.choices[0].message)
  } catch (error){
    res.status(500).send('Server Error')
  }  
})

Create a user view in React

To create a user interface with React, follow the below steps to set up User Interface:

Create a new React application using the below command:

npx create-react-app app_name --template typescript

2. Update the App.tsx file with the below code.

import {useState} from 'react'
interface ChatData  {
  role: string,
  content: string
}

const App = ()=> {
   
  const [value, setValue] = useState<string>("")
  const [chat, setChat] = useState<any>([])

const getQuery = async ()=>{
  try{
    const options = {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ message: value })
    };

    const response = await fetch("http://localhost:8000/completions", options )
    const data = await response.json()
    const useMessage = {
      role: "user",
      content: value
    }
    setChat((oldData:any) => [...oldData, data , useMessage])

  } catch(error){
  console.log(error)
  }
}

const clearChat = ()=>{
  setValue("");
  setChat([])
}

  const latestCode = chat.filter((message: ChatData) => message.role === "assistant").pop()
 
  return (
    <div className="app">
      <input value={value} onChange={e => setValue(e.target.value)}/>
      
      <div className="code-display">
      <div className="buttons">
        <div className="button first"></div>
        <div className="button middle"></div>
        <div className="button last"></div>
      </div>

      <div className="code-output">
        <p>{latestCode?.content|| ""}</p>
      </div>

      </div>

      <div className="button-container">
        <button id="get-query" onClick={getQuery}>Get Query!</button>
        <button id="clear-chat" onClick={clearChat}>Clear Chat</button>
      </div>
      
    </div>
  );
}

export default App;

3. Add CSS code to theindex.css file.

* {
  font-family: Verdana, Geneva, Tahoma, sans-serif;
  color: rgb(34, 34, 34);
}

body {
  margin: 0;
  padding: 0;
  display: flex;
  justify-content: center;
  align-items: center;
  height: 100vh;
  background-color: rgb(250, 250, 250)
}

p,
input {
  font-size: 16px;
}

.app {
  height: 100vh;
  width: 120vh;
  background-color: rgb(255, 255, 255);
  border-radius: 10px;
  box-shadow: rgba(0, 0, 0, 0.16), 0, 1px 4px;
  display: flex;
  justify-content: center;
  flex-direction: column;

}

.code-display {
  width: 96%;
  margin: 2%;
  height: 46%;
  border-radius: 8px;
  background-color: rgb(34, 34, 34);
  overflow: hidden;
}

.buttons {
  width: 100%;
  height: 35px;
  background-color: rgb(35, 35, 35);
  display: flex;
  align-items: center;
  padding-left: 5px;
}

.button {
  width: 15px;
  height: 15px;
  border-radius: 50%;
  margin: 4px;
  background-color: red;
}

.button.first {
  background-color: rgb(255, 96, 86);
}

.button.middle {
  background-color: rgb(255, 189, 45);
}

.button.last {
  background-color: rgb(38, 201, 64);
}

input {
  margin: 2%;
  padding: 16px;
  border: solid rgba(0, 0, 0, 0.16) 1px;
  box-sizing: border-box;
  border-radius: 5px;
}

.button-container {
  margin: 2%;
  display: flex;
  justify-content: flex-end;
}

.button-container button {
  text-transform: uppercase;
  padding: 7px;
  border: none;

}

button#get-query {
  border-radius: 10px 0 0 10px;
  background-color: rgb(255, 95, 86);
}

button#clear-chat {
  border-radius: 0 10px 10px 0;
  background-color: rgb(255, 189, 45);
}

.code-output {
  margin: 20px;
}

.code-output p {
  color: rgb(255, 189, 45);
}

The user interface will look like this after updating both files:

Test Snapshots

Let’s test our application with some examples and see the results. Here are some intriguing test snapshots.

Create a SQL request to find all users who visited from Chrome and Safari

This limited test results but we can also get a more specific response according to your input.

Summary

SQL generation using OpenAI APIs is a powerful technique that allows developers to easily convert natural language sentences into SQL queries. This can be especially useful for business analysts, data scientists, and other non-technical users who need to retrieve data from a database but are not familiar with SQL.

Thanks for reading, and happy coding!

Reference links

Overview of AI Models : https://platform.openai.com/docs/models/overview
POC Reference : https://www.youtube.com/watch?v=mb36Ny-VNgU
Chat Completions API : Ihttps://platform.openai.com/docs/guides/chat