[Tutorial] Full-stack app with ChatGPT API, React and Node
Tags: javascript, chatgpt, react, node
Published: 2023-04-12 20:06:52

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.

{% youtube kjUSOa_KOts %}

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:

```sql
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](https://github.com/openai/openai-quickstart-node).

## 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.

```bash
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](https://vitejs.dev/) 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:

```bash
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.

```bash
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:

```bash
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](https://codesandbox.io/s/staging-leaf-lgheij?file=/src/styles.module.css) 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](https://www.flaticon.com/free-icon/sql-server_5815809?term=sql&page=1&position=6&origin=search&related_id=5815809). 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.

```jsx
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:

```jsx
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](https://platform.openai.com/account/api-keys) 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.

```bash
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](https://www.twilio.com/blog/2017/08/working-with-environment-variables-in-node-js.html).

### 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:

```bash
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:

```bash
.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:

```js
// 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](https://developer.mozilla.org/en-US/docs/Web/HTTP/CORS).

```bash
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:

```js
// 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:

```bash
node index.js
```

If everything went well, you should see the following message in your terminal:

```bash
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:

```bash
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:

```js
// 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:

```js
// 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:

```json
{
  "prompt": "Select all the users from the users table"
}
```

You should see the following message:

```json
{
  "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. 

```js
// 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:

```js
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:

```js
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](alejandro-ao.comhttps://alejandro-ao.com/posts/software-development/chat-gpt-api-tutorial/)