Hello everyone! In this article, I will show you how to make an app that uses the OpenAI API. This API is a very powerful tool that allows you to generate text based on a prompt. We will be using React for the front end and Node.js for the backend.
Both the front end and the backend are going to be very simple. In our configuration we will have them both in the same repository. This is not the best way to do it, but it is the easiest way to get started.
What we are going to build
By the end of this article, you will have your own app that uses the OpenAI API to generate SQL queries based on what the user wants it to do.
The idea of this app that we are going to build is to take an input value from the user and convert it to an SQL query. For example, if the user submits the following text:
show 10 elements from a table called users where the column name is equal to "John"
Our app should return this:
SELECT * FROM users WHERE name = "John" LIMIT 10
This is a spin-off of the example that OpenAI uses in their documentation. But we are going to build it from scratch. You can find their example here.
Step 1: Set up the project
Remember that we mentioned that we are going to have our front end and our back end in the same repository. To do this, let's create a new folder called openai-sql-generator. Inside of this folder, we are going to create our folder server and our folder client. But don't worry about creating the client folder just yet. We will do that in the next step.
mkdir openai-sql-generator
cd openai-sql-generator
mkdir server
Step 2: Set up the front end
Now, we are going to start a new React app. We will be using Vite to do this. If you don’t know what Vite is, it is a new tool that allows you to create React apps without having to use create-react-app. It is very fast and it is very easy to use. So we run:
npm create vite@latest
And then we name it client and select the react template. Now we can just install the dependencies and run the app.
cd client
npm install
npm run dev
Now it's a good time to make your first commit. Go to the root folder of your project (the one that contains the server and client folders) and run the following commands:
git init
git add .
git commit -m "Initial commit"
git remote add origin # your GitHub repo URL
git push -u origin master
Step 3: Create the main component
Main skeleton
The first thing that we are going to need is to create the skeleton of the React application. Our main component will look something like the code below. Since we are building this off of the example of the OpenAI documentation, here is the CSS code they used for this application. All you have to do is copy and paste it into a file called styles.module.css in your src folder.
We are also going to need to import the SQL server icon that we are going to use in our app. You can find it on Flaticon. You can download it and put it in a folder called assets in your src folder. Then you can import it in your main component.
import styles from "./styles.module.css";
import sqlServer from "./assets/sql-server.png";
export default function App() {
return (
<main className={styles.main}>
<img src={sqlServer} className={styles.icon} alt="SQL server" />
<h3>Generate SQL</h3>
<form>
<input
type="text"
name="query-description"
placeholder="Describe your query"
/>
<input type="submit" value="Generate query" />
</form>
</main>
);
}
Pretty good, huh? So far we just added the default styles that come with the demo app from the OpenAI documentation. Now let’s make that input field reactive.
Manage input and submit
As you may know, if you use React, we don’t want to forget to make the input field reactive and track its value within the state of the component. So let’s add a state to our component and make the input field reactive. We are going to use the useState hook to do this.
While we are at it, let’s also add a onSubmit function to our form. We will use this function to trigger the API call that we are going to make later on. But for now, let’s just log the user’s prompt in the console. Our component should look like this:
import styles from "./styles.module.css";
import sqlServer from "./assets/sql-server.png";
import { useState } from "react";
export default function App() {
const [userPrompt, setUserPrompt] = useState("");
const onSubmit = (e) => {
e.preventDefault();
console.log(userPrompt);
};
return (
<main className={styles.main}>
<img src={sqlServer} className={styles.icon} alt="SQL server" />
<h3>Generate SQL</h3>
<form onSubmit={onSubmit}>
<input
type="text"
name="query-description"
placeholder="Describe your query"
value={userPrompt}
onChange={(e) => setUserPrompt(e.target.value)}
/>
<input type="submit" value="Generate query" />
</form>
</main>
);
}
Great! Now we have a component that is reactive and that has an onSubmit function. But we still need to make the API call to OpenAI. Let's build our back end first. We will come back to this component later.
Step 4: Setup the back end
Create the API key
The first thing that we need to do is to create an API key for our app. To do this, we need to go to the OpenAI dashboard and create a new API key. You can name it whatever you want. I named mine openai-sql-generator. Then you can copy the key and save it somewhere safe. We will need it later on.
Initialize the Node.js project
We will use Node.js to create the API. So let's navigate to the server folder and initialize a new NPM project. We will also need to install the OpenAI SDK for Node.js and dotenv to be able to use environment variables.
cd server
npm init -y
npm install openai
npm install dotenv
In case you don't know what environment variables are, they are variables that are stored outside of your code. They are usually used to store sensitive information such as API keys. You can read more about them here.
Create the .env file
Now we are going to create a new file called .env in the root of our project. This is where we are going to store our API key. We will also need to create a new file called .env.example. This is where we are going to store the structure of our .env file. It is a good practice to do this so that other developers know what environment variables they need to set up in order to run your project. So let's add the following code to our .env.example file:
OPENAI_API_KEY=
Now we can add our API key to the .env file. We will also need to add this file to our .gitignore file so that we don't accidentally push it to GitHub (what would be the point of having a secret key in our .env file if we are going to push it to GitHub?). So let's add the following code to our .gitignore file:
.env
Step 5: Create the API
Create api.js and index.js
Now we can create the API. We will create a new file called api.js in the root of our project. This is where we are going to make the API call to OpenAI. We will also need to import the OpenAI SDK and the dotenv package. We will also need to import the OPENAI_API_KEY environment variable from our .env file. We will use this key to make the API call. Our api.js file should look like this:
// api.js
import { Configuration, OpenAIApi } from "openai";
import dotenv from "dotenv";
dotenv.config();
const openaiApiKey = process.env.OPENAI_API_KEY;
if (!openaiApiKey) {
console.error('OPENAI_API_KEY is not set.');
process.exit(1);
}
const configuration = new Configuration({
apiKey: process.env.OPENAI_API_KEY,
});
const openai = new OpenAIApi(configuration);
export default openai;
Now we will have to install the express package to create the API endpoint. We will also need to install the cors package to allow cross-origin requests. We will need this because we are going to make the API call from a different domain than the one where the API is hosted. You can read more about this here.
npm install express
npm install cors
Now we can create the API endpoint. Inside the server folder, create a new file called index.js. This is where we are going to create the API endpoint. We will also need to import the express and cors packages, and the api.js file that we created earlier. We will use this file to make the API call to OpenAI. Our index.js file should look like this:
// index.js
import express from "express";
import cors from "cors";
const app = express();
app.use(cors());
const port = process.env.PORT || 3002;
app.get("/", (req, res) => {
res.send("Hello World!");
});
app.listen(port, () => {
console.log(`Listening on port ${port}...`);
});
There we go! That's the configuration file for our API. Now we can start the API by running the following command:
node index.js
If everything went well, you should see the following message in your terminal:
Example app listening at http://localhost:3001
Now we can test our API by going to http://localhost:3001 in our browser. You should see the following message:
Hello World!
Great! Now we have a working API. But we still need to create an endpoint that will make the API call to OpenAI and generate our SQL query. Let's do that now.
Create the API endpoint
Now we can create the API endpoint that will make the API call to OpenAI. We will create a new file called generate.js in the server folder. This is where we are going to make the API call to OpenAI. We will also need to import the openaiClient object that we created in the api.js file. We will use this object to make the API call. Our generate.js file should look like this:
// generate.js
import openaiClient from "./api.js";
const generate = async (queryDescription) => {
const response = await openaiClient.createCompletion({
model: 'text-davinci-003',
prompt: `Convert the following natural language description into a SQL query:\n\n${queryDescription}.`,
max_tokens: 100,
temperature: 0,
});
return response.data.choices[0].text;
};
export default generate;
Period at the end of the prompt
Be careful when designing your prompt. If you don't design it correctly, the API will not be able to generate the response you want. For example, note that in the example above, there is a period at the end of the prompt. This is important because this is a text-completion API. If you don't add the period, it might try to complete your prompt instead of generating the response.
For example, if you try to generate a SQL query for the following prompt:
"Convert the following natural language description into a SQL query:\n\nSelect all the users from the users table"
You will get the following response:
"where the lastname equals 'smith' and the firstname equals 'john'\n\nSELECT * FROM users WHERE lastname = 'smith' AND firstname = 'john';"
Did you see that? It completed your own prompt before generating the response. So be sure to add the period at the end.
Now we can create our endpoint. We will create a new route called /generate that will take a queryDescription parameter. We will use this parameter to make the API call to OpenAI. Note that we are using a POST request instead of a GET request. This is because we are sending a body with the request. Our index.js file should look like this:
// index.js
// ...
app.post("/generate", async (req, res) => {
const { prompt } = req.query;
try {
const sqlQuery = await generate(prompt);
res.json({ sqlQuery });
} catch (error) {
console.error(error);
res.status(500).send("Internal Server Error");
}
});
Now we can test our API by using a tool like Postman or Thunder Client. Send a POST request to http://localhost:3001/generate with the following body:
{
"prompt": "Select all the users from the users table"
}
You should see the following message:
{
"sqlQuery": "SELECT * FROM users;"
}
Great! Now we have a working API endpoint that will make the API call to OpenAI and generate our SQL query. But we still need to make our frontend communicate with this API endpoint. Let's do that now.
Step 6: Call the API from the frontend
So our API is working and our frontend is ready. Let's just make the connection between them. Many people like to work with Axios, but I prefer to use the Fetch API. So let's use the Fetch API to make the API call to our API endpoint.
We will do this directly inside the component to keep things simple and since the frontend part is not the central part of this tutorial. So let's go to the App.js file and complete the function onSubmit that we previously created.
// app.jsx
// ...
export default function App() {
// ...
const onSubmit = async (e) => {
e.preventDefault();
const query = await generateQuery();
console.log(query)
};
const generateQuery = async () => {
const response = await fetch("http://localhost:3002/generate", {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ queryDescription: userPrompt }),
});
const data = await response.json();
return data.sqlQuery.trim();
};
// ...
}
Display the generated query
Finally, we can display the generated query. We will do this simply by adding a <pre> tag to our App.js file and setting a state variable to store the generated query. Our App.js file should look like this:
import styles from "./styles.module.css";
import sqlServer from "./assets/sql-server.png";
import { useState } from "react";
export default function App() {
const [userPrompt, setUserPrompt] = useState("");
const [sqlQuery, setSqlQuery] = useState("");
const onSubmit = async (e) => {
e.preventDefault();
const query = await generateQuery();
setSqlQuery(query);
};
const generateQuery = async () => {
const response = await fetch("http://localhost:3002/generate", {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ queryDescription: userPrompt }),
});
const data = await response.json();
return data.sqlQuery.trim();
};
return (
<main className={styles.main}>
<img src={sqlServer} className={styles.icon} alt="SQL server" />
<h3>Generate SQL</h3>
<form onSubmit={onSubmit}>
<input
type="text"
name="query-description"
placeholder="Describe your query"
value={userPrompt}
onChange={(e) => setUserPrompt(e.target.value)}
/>
<input type="submit" value="Generate query" />
</form>
<pre>{sqlQuery}</pre>
</main>
);
}
Use the new ChatGPT API instead
I recommend that you use the new ChatGPT API instead of the TextGPT API. It is cheaper and works better! Once you have understood how to use the TextGPT API, you can easily switch to the ChatGPT API. All you have to do is change the model parameter in the generate.js file and feed it a conversation instead of a prompt.
You will also need to update the method that makes the API call to OpenAI. You can do this by changing the createCompletion method to the createChatCompletion method.
Here is an example of how our current app would look like if we used the ChatGPT API instead of the TextGPT API:
import openaiClient from "./api.js";
const generate = async (queryDescription) => {
const daVinci = async (queryDescription) => {
const response = await openaiClient.createCompletion({
model: 'text-davinci-003',
prompt: `Convert the following natural language description into a SQL query:\n\n${queryDescription}`,
max_tokens: 100,
temperature: 0,
});
return response.data.choices[0].text;
};
const chatGPT = async (queryDescription) => {
const message = [
{ role: "system", content: `You are a translator from plain English to SQL.` },
{ role: "user", content: `Convert the following natural language description into a SQL query:\n\nShow all all the elements in the table users` },
{ role: "assistant", content: "SELECT * FROM users;" },
{ role: "user", content: `Convert the following natural language description into a SQL query:\n\n${queryDescription}` },
];
const response = await openaiClient.createChatCompletion({
model: "gpt-3.5-turbo",
messages: message,
});
return response.data.choices[0].message.content;
}
const sqlQuery = await chatGPT(queryDescription);
return sqlQuery;
};
export default generate;
Conclusion
And that's it! We have successfully created a web app that can generate SQL queries from natural language descriptions. We have used OpenAI's GPT-3 API to generate the SQL queries. We have also used React to create the frontend and Express to create the API. And we also learned how to use the TextGPT API and the ChatGPT API.
Note: This is a repost from my personal blog
Top comments (0)