Nathan Sebhastian A senior software developer with experience in building fullstack JavaScript app with React and Express. You can find me online at sebhastian.com.

Getting started with Postgres in your React app

7 min read 2177

Getting started with Postgres in your React app

Frontend developers typically don’t need to understand the process of receiving, recording, and removing information. That’s a job for backend developers.

That said, there are plenty of good reasons for a frontend developer to learn about backend programming and database interaction. For instance:

  • You’ll stand out from other frontend developers because you’ll know how your application work as a whole
  • You’ll be able to work on both the front and back side of your app
  • You can be promoted to a full-stack developer and take on a bigger role with a higher salary
  • Knowledge of both frontend and backend programming — as well as designing scalable systems and building solid application architecture — is a requirement to be a tech lead

In this tutorial, we’ll demonstrate how to create a small application using Express and Node.js that can record and remove information from a PostgreSQL database according to the HTTP requests it receives. We’ll then create a simple React app to test and see how the entire application flows from front to back.

I’ll assume that you understand how a React application works and are familiar with frontend JavaScript HTTP requests. We won’t cover how to validate data before interacting with the database. Instead, we’ll focus on showing how requests from the interface are recorded into a database.

I published a GitHub repo for this tutorial so you can compare your code if you get stuck. Now let’s get our database running.

Setting up PostgreSQL

PostgreSQL, or Postgres, is a relational database management system that claims to be the world’s most advanced open-source relational database. It has been maintained since 1996 and has a reputation for being reliable and robust.

Start by downloading and installing PosgreSQL. It supports all major operating systems, so choose the right one for your computer and follow the instructions to set up the database. The setup wizard will prompt you to enter a superuser password. Make sure you remember this password; you’ll need it to login later.

Once the installation is complete, you can access your database by using pgAdmin, a graphical interface tool database management that is installed automatically with PostgreSQL.

Once opened, pgAdmin will ask for your password to log in. Below is the overview of a newly installed PostgreSQL database.

PostgreSQL Database Overview in pgAdmin

Creating a Postgres database

To better understand SQL language, we need to create a database and table from the terminal.

To access PostgreSQL from the terminal, use the command psql with the option -d to select the database you want to access and -U to select the user. If the terminal replies that the psql command is not found, you’ll most likely need to add the Postgres bin/ and lib/ directories into your system path.

psql -d postgres -U postgres

You will be asked to input your password. Use the password you created earlier. Once you’re logged in, create a new user by adding a login permission with the password “root.”

CREATE ROLE my_user WITH LOGIN PASSWORD 'root';

A user is simply a role that has login permission. Now that you have one, give it permission to create databases by issuing the ALTER ROLE [role name] CREATEDB syntax.

ALTER ROLE my_user CREATEDB;

Log out from your postgres superuser and log in as my_user using the command \q.

\q
psql -d postgres -U my_user 

Now that you’re back inside, create a new database named my_database.



CREATE DATABASE my_database;

You might be wondering, why can’t we just use the default postgres user to create the database? That’s because the default user is a superuser, which means it has access to everything within the database. According to the Postgres documentation, “superuser status is dangerous and should be used only when really needed.”

An SQL-based database stores data inside a table. Now that you have a database, let’s create a simple table where you can record your data.

CREATE TABLE merchants( id SERIAL PRIMARY KEY, name VARCHAR(30), email VARCHAR(30) );

One database can have multiple tables, but we’ll be fine with one table for this tutorial. If you’d like to check the created database and table, you can use the command \list and \dt, respectively. You might see more rows or less, but as long as you have the database and the table you created previously, your table should look like this:

my_database=> \list
          List of databases
 Name        |  Owner   | Encoding
 my_database | my_user  | UTF8     
 postgres    | postgres | UTF8     
 template0   | postgres | UTF8     
 template1   | postgres | UTF8     


my_database=> \dt
          List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+---------
 public | merchants | table | my_user

Now have a table into which you can insert data. Let’s do that next.

Basic SQL queries

Postgres is an SQL-based system, which means you need to use SQL language to store and manipulate its data. Let’s explore four basic example of SQL queries you can use.

1. Select query

To retrieve data from a table, use the SELECT key, followed by the name of the columns you want to retrieve and the name of the table.

SELECT id, name, email from merchants;

To retrieve all columns in the table, you can simply use SELECT *.

SELECT * from merchants;

2. Insert query

To insert new data into a table, use the INSERT keyword followed by the table name, column name(s), and values.

INSERT INTO merchants (name, email) VALUES ('john', '[email protected]');

3. Delete query

You can delete a row from a table by using the DELETE keyword.


More great articles from LogRocket:


DELETE from merchants WHERE id = 1;

When you use the delete query, don’t forget to specify which row you want to delete with the WHERE keyword. Otherwise, you’ll delete all the rows in that table.

4. Update query

To update a certain row, you can use the UPDATE keyword.

UPDATE merchants SET name = 'jake', email = '[email protected]' WHERE id = 1;

Now that you know how to manipulate data inside your table, let’s examine how to connect your database to React.

Creating an API server with Node.js and Express

To connect your React app with a PostgreSQL database, you must first create an API server that can process HTTP requests. Let’s set up a simple one using NodeJS and Express.

Create a new directory and set a new npm package from your terminal with the following commands.

mkdir node-postgres && cd node-postgres
npm init

You can fill in your package information as you like, but here is an example of my package.json:

{
  "name": "node-postgres",
  "version": "1.0.0",
  "description": "Learn how NodeJS and Express can interact with PostgreSQL",
  "main": "index.js",
  "license": "ISC"
}

Next, install the required packages.

npm i express pg

Express is a minimalist web framework you can use to write web applications on top of Node.js technology, while node-postgres(pg) is a client library that enables Node.js apps to communicate with PostgreSQL.

Once both are installed, create an index.js file with the following content.

const express = require('express')
const app = express()
const port = 3001

app.get('/', (req, res) => {
  res.status(200).send('Hello World!');
})

app.listen(port, () => {
  console.log(`App running on port ${port}.`)
})

Open your terminal in the same directory and run node index.js. Your Node application will run on port 3001, so open your browser and navigate to http://localhost:3001. You’ll see “Hello World!” text displayed in your browser.

You now have everything you need to write your API.

Making NodeJS talk with Postgres

The pg library allows your Node application to talk with Postgres, so you’ll want to import it first. Create a new file named merchant_model.js and input the following code.

const Pool = require('pg').Pool
const pool = new Pool({
  user: 'my_user',
  host: 'localhost',
  database: 'my_database',
  password: 'root',
  port: 5432,
});

Please note that putting credentials such as user, host, database, password, and port like in the example above is not recommended in a production environment. We’ll keep it in this file to simplify the tutorial.

The pool object you created above will allow you to query into the database that it’s connected to. Let’s create three queries to make use of this pool. These queries will be placed inside a function, which you can call from your index.js.

const getMerchants = () => {
  return new Promise(function(resolve, reject) {
    pool.query('SELECT * FROM merchants ORDER BY id ASC', (error, results) => {
      if (error) {
        reject(error)
      }
      resolve(results.rows);
    })
  }) 
}
const createMerchant = (body) => {
  return new Promise(function(resolve, reject) {
    const { name, email } = body
    pool.query('INSERT INTO merchants (name, email) VALUES ($1, $2) RETURNING *', [name, email], (error, results) => {
      if (error) {
        reject(error)
      }
      resolve(`A new merchant has been added added: ${results.rows[0]}`)
    })
  })
}
const deleteMerchant = () => {
  return new Promise(function(resolve, reject) {
    const id = parseInt(request.params.id)
    pool.query('DELETE FROM merchants WHERE id = $1', [id], (error, results) => {
      if (error) {
        reject(error)
      }
      resolve(`Merchant deleted with ID: ${id}`)
    })
  })
}

module.exports = {
  getMerchants,
  createMerchant,
  deleteMerchant,
}

The code above will process and export the getMerchants, createMerchant, and deleteMerchant functions. Now it’s time to update your index.js file and make use of these functions.

const express = require('express')
const app = express()
const port = 3001

const merchant_model = require('./merchant_model')

app.use(express.json())
app.use(function (req, res, next) {
  res.setHeader('Access-Control-Allow-Origin', 'http://localhost:3000');
  res.setHeader('Access-Control-Allow-Methods', 'GET,POST,PUT,DELETE,OPTIONS');
  res.setHeader('Access-Control-Allow-Headers', 'Content-Type, Access-Control-Allow-Headers');
  next();
});

app.get('/', (req, res) => {
  merchant_model.getMerchants()
  .then(response => {
    res.status(200).send(response);
  })
  .catch(error => {
    res.status(500).send(error);
  })
})

app.post('/merchants', (req, res) => {
  merchant_model.createMerchant(req.body)
  .then(response => {
    res.status(200).send(response);
  })
  .catch(error => {
    res.status(500).send(error);
  })
})

app.delete('/merchants/:id', (req, res) => {
  merchant_model.deleteMerchant(req.params.id)
  .then(response => {
    res.status(200).send(response);
  })
  .catch(error => {
    res.status(500).send(error);
  })
})
app.listen(port, () => {
  console.log(`App running on port ${port}.`)
})

Now your app has three HTTP routes that can accept requests. The code from line 7 is written so that Express can accept incoming requests with JSON payloads. To allow requests to this app from React, I also added headers for Access-Control-Allow-Origin, Access-Control-Allow-Methods, and Access-Control-Allow-Headers.

Creating your React application

Your API is ready to serve and process requests. Now it’s time to create a React application to send requests into it.

Let’s bootstrap your React app with the create-react-app command.

npx create-react-app react-postgres

In your React app directory, you can remove everything inside the src/ directory.

Now let’s write a simple React app from scratch.

First, create an App.js file with the following content.

import React, {useState, useEffect} from 'react';
function App() {
  const [merchants, setMerchants] = useState(false);
  useEffect(() => {
    getMerchant();
  }, []);
  function getMerchant() {
    fetch('http://localhost:3001')
      .then(response => {
        return response.text();
      })
      .then(data => {
        setMerchants(data);
      });
  }
  function createMerchant() {
    let name = prompt('Enter merchant name');
    let email = prompt('Enter merchant email');
    fetch('http://localhost:3001/merchants', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({name, email}),
    })
      .then(response => {
        return response.text();
      })
      .then(data => {
        alert(data);
        getMerchant();
      });
  }
  function deleteMerchant() {
    let id = prompt('Enter merchant id');
    fetch(`http://localhost:3001/merchants/${id}`, {
      method: 'DELETE',
    })
      .then(response => {
        return response.text();
      })
      .then(data => {
        alert(data);
        getMerchant();
      });
  }
  return (
    <div>
      {merchants ? merchants : 'There is no merchant data available'}
      <br />
      <button onClick={createMerchant}>Add merchant</button>
      <br />
      <button onClick={deleteMerchant}>Delete merchant</button>
    </div>
  );
}
export default App;

This React app will send requests to the Express server you created. It has two buttons to add and delete a merchant. The function getMerchant will fetch merchant data from the server and set the result to the merchant state.

createMerchant and deleteMerchant will start the process to add and remove merchants, respectively, when you click on the buttons.

Finally, create an index.js file and render the App component.

import React from 'react';
import ReactDOM from 'react-dom';
import App from './App';

ReactDOM.render(<App />, document.getElementById('root'));

Now run your React app with npm start. You can test and see how the data collected from your React application is recorded into PostgreSQL. I’ll leave you the implementation of the UPDATE query as an exercise.

Conclusion

Now you know how to install PostgreSQL database, create a database and table, and build a minimal API to function as a bridge between your React app and your database. We created an end-to-end example of how to use Postgres with React and demonstrated exactly what happens when you send those HTTP requests from your React app.

This tutorial is far from a complete guide to backend programming, but it’s enough to help you get started in understanding how the backend side works.

Should you find any error or can’t insert data from the application, you can clone the complete source code from this repo and compare the code from the repo with your example.

Get setup with LogRocket's modern React error tracking in minutes:

  1. Visit https://logrocket.com/signup/ to get an app ID.
  2. Install LogRocket via NPM or script tag. LogRocket.init() must be called client-side, not server-side.
  3. $ npm i --save logrocket 

    // Code:

    import LogRocket from 'logrocket';
    LogRocket.init('app/id');
    Add to your HTML:

    <script src="https://cdn.lr-ingest.com/LogRocket.min.js"></script>
    <script>window.LogRocket && window.LogRocket.init('app/id');</script>
  4. (Optional) Install plugins for deeper integrations with your stack:
    • Redux middleware
    • ngrx middleware
    • Vuex plugin
Get started now
Nathan Sebhastian A senior software developer with experience in building fullstack JavaScript app with React and Express. You can find me online at sebhastian.com.

30 Replies to “Getting started with Postgres in your React app”

  1. As a DBA I have to say that mentioning the wild card * in a select without mentioning the problems it can cause is a rookie mistake.

    Never select more than you need, especially if the given table contains indexes that might be used if you select only the required columns (which are also present in the index) and thus relying on an index that might make a difference from having your query running at 20 seconds to less than 200ms when an unnecessary key lookup occures.

    Never use * in production, especially when defining views, they tend to parse the * into the actual columns in that time the view got created, meaning – if you add another column to the underlying table the view won’t return it, and if you remove a column some RDBMS will not validate dependency and cause an error when the view is queried.

  2. I am getting a warning when I render the react page: functions are not valid as a React child.

  3. I would like to suggest that you add a step to your tutorial. After `my_database` is created, you will want to change into the database before you create the table with the following command:
    `\c my_database`

    1. Thank you. This is the step i missed out cause me to not find the table in my database

  4. You have a mistake in:

    const deleteMerchant = () => {
    return new Promise(function(resolve, reject) {
    const id = parseInt(request.params.id)
    pool.query(‘DELETE FROM merchants WHERE id = $1’, [id], (error, results) => {
    if (error) {
    reject(error)
    }
    resolve(`Merchant deleted with ID: ${id}`)
    })
    })
    }

    it should be:

    const deleteMerchant = (id) => {
    return new Promise(function(resolve, reject) {
    pool.query(‘DELETE FROM merchants WHERE id = $1’, [id], (error, results) => {
    if (error) {
    reject(error)
    }
    resolve(`Merchant deleted with ID: ${id}`)
    })
    })
    }

    Thanks for the rest of the tutorial!

    Cheers,

    Nathan

  5. Excellent tutorial!! I learned a lot.
    I just had one small query.
    I think the delete method is not working. I tried as Nathan pointed out but that doesn’t seem to work either

  6. When I try to install react-postgres, I receibe this error:

    npm ERR! code E404
    npm ERR! 404 Not Found – GET https://registry.npmjs.org/react-postgres – Not found
    npm ERR! 404
    npm ERR! 404 ‘[email protected]’ is not in the npm registry.
    npm ERR! 404 You should bug the author to publish it (or use the name yourself!)
    npm ERR! 404
    npm ERR! 404 Note that you can also install from a
    npm ERR! 404 tarball, folder, http url, or git url.

    npm ERR! A complete log of this run can be found in:
    npm ERR! /home/daniel/.npm/_logs/2020-11-09T23_53_15_619Z-debug.log
    Install for [ ‘[email protected]’ ] failed with code 1

    Perphaps the code it’s bad written?

    1. Hey there, `react-postgres` isn’t a library. It’s the name the author gave to the CRA project he created with the `npx create-react-app` command.

  7. Hi there,

    I’ve been trying to follow along with this tutorial in a project I’m working on, but
    when I try to use the pg library, I receive the following error:

    ./node_modules/pg/lib/native/client.js
    Module not found: Can’t resolve ‘pg-native’

    I checked and it’s in the folder listed, but when I try to run “index.js” in that folder, it returns a syntax error. I haven’t made any changes to the library…so I’m not sure why it’s doing it.

    Here’s a link to my code:
    https://github.com/medemak/can-dash

    Any help would be greatly appreciated!

  8. hi – i am getting a ‘Cross-Origin Request Blocked: The Same Origin Policy disallows reading the remote resource at http://localhost:3001/. (Reason: CORS request did not succeed).’ error. Please let me know how I can overcome the CORS issue?
    I am on a Ubuntu machine.

  9. Hi.. I followed the exact steps. but still could not access the pgsql. pgsql server is running and is able to insert data into it directly via console. could not insert data from application. there is no error! hence couldn’t find a way to make it working

  10. Hi I followed the same tutorial. But when I’m trying to insert the data or delete the data. I am not able to do that operations. Checked for errors didn’t find anything. Can you please help me out?

      1. Hi Nathan, still I am not able to insert the data. When I inspect on the app screen it shows me an error on console that App.js:18 POST http://localhost:3001/merchants net::ERR_CONNECTION_REFUSED and when I try to do on postman for post method it shows title error with code 404 Cannot POST /

        1. Did you run the Node server? Head to the node-postgres folder and execute `node index.js` command from the terminal to start the server on localhost:3001

          1. Thank you Nathan. It worked when I run the node server. But When I’m inserting the data which is post request it’s inserting the null values even though I gave the data.

              1. Hi Nathan, I figured it out. I was not running the node server. I’m just running the react app so, whatever values I’m inserting will be null. Then, I have installed the Nodemon which, keeps running the node server automatically even though we make any changes on the server-side.

                1. Hi Nik, glad to hear that!

                  Yeah, Nodemon is definitely nice to have when you’re developing a Node-based application because `node server` command doesn’t apply the code changes when you hit the save button.

  11. Hi Nathan, I have question, when I’m inserting the data for the first time it’s inserting the null values because I was not running the node server. But if my server is not running when it getting into the database?

  12. Can you help me I got this message “Unhandled Rejection (TypeError): Failed to fetch”

    10 | function getMerchant() {
    > 11 | fetch(“http://localhost:3001”)
    | ^ 12 | .then((response) => {
    13 | return response.text();
    14 | })

  13. Question, is it possible to upload a picture of what the file structure looks like for this project? We created two index.js files and I am unsure of what file is suppose to be where when resolving this project. Any information on this matter helps! Thank you.

  14. Hi there, when i wrote this codes on the pgAdmin 4 it’s works but how can i use it in the backend server

    INSERT INTO products (product_name,main_category, img_url, description, price)
    SELECT ‘Margarita Pizza’, categories.cat_name, ‘https://cdn.yemek.com/mnresize/1250/833/uploads/2022/03/pizza-margherita-tarifi-yemekcom.jpg’, ‘Monza Sos, Mozarella, Fesleğen’, ‘119.90’
    FROM categories WHERE cat_name = ‘pizza’

Leave a Reply