Setting up a web application using React for the front end and PostgreSQL for the database

Hello guys, how are you? Welcome back to my blog. Guys today in this bog post we are Setting up a web application using React.js for the front end and PostgreSQL for the database involves several steps, including setting up the environment, coding the backend to connect with the database, and developing the front end. Here, I’ll provide a step-by-step guide on how to set up each part of the stack using Node.js and Express for the backend.

For react js new comers, please check the below links:

1. Reactjs Tutorials
2. Bootstrap 5
3. React Free Ecommerce Templates
4. React Free Admins

Step 1: Setting up PostgreSQL

1. Install PostgreSQL: You can download it from the official PostgreSQL website.
2. Create a Database: Once installed, create a new database for your project.
3. Create Tables: Define the schema and create the necessary tables.

Guys Creating a table in PostgreSQL involves using the SQL CREATE TABLE statement. This statement allows you to define the structure of your table including column names, data types, and any constraints like primary keys or unique indexes.

Here’s a general step-by-step guide on how to create a table in PostgreSQL:

Step 1: Open the PostgreSQL Command Line

You can use the PostgreSQL command line tool (psql) to interact with your database. To access your PostgreSQL database via the command line:

1. Open your terminal or command prompt.
2. Connect to PostgreSQL with the psql command. If you’re connecting to a database on your local machine with the default PostgreSQL user (often postgres), you would run:

   psql -U postgres

Replace postgres with the username of your PostgreSQL installation if different.

Step 2: Connect to Your Database

Once in the psql interface, connect to the database where you want to create your table:

\c mydatabase

Replace mydatabase with the name of your database.

Step 3: Create the Table

To create a table, you use the CREATE TABLE SQL command. Here’s the syntax and an example:

Syntax

CREATE TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
);

Example

Suppose you want to create a table called employees with the following specifications:

• employee_id: A primary key, integer type.
• first_name: Variable character type, maximum 50 characters.
• last_name: Variable character type, maximum 50 characters.
• email: Variable character type, unique.
• date_of_birth: Date type.
• salary: Numeric type.

Here’s how you would write the SQL statement:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    date_of_birth DATE NOT NULL,
    salary NUMERIC(10, 2)
);

• SERIAL is a PostgreSQL auto-incrementing integer which is often used for primary keys.
• NOT NULL means the column cannot have a null value.
• UNIQUE ensures all values in the column are different.
• NUMERIC(10, 2) defines a number with up to 10 digits and 2 decimal places.

Step 4: Verify the Table Creation

After running the command, ensure your table has been created successfully by listing all tables:

\dt

Or you can describe your table structure using:

\d employees

This process will give you a basic yet functional table in PostgreSQL. If you need to create a more complex table or implement additional features like foreign keys or indexes, you might need to look into more specific SQL commands or table options.

Now add demo data inside this table

To insert demo data into a PostgreSQL table, you use the INSERT INTO SQL statement. This statement allows you to add one or more rows of data into your specified table. Assuming you have created the employees table as described earlier, I’ll show you how to add sample data to it.

Step 1: Open the PostgreSQL Command Line

Make sure you’re connected to your PostgreSQL database via the command line tool (psql). If you’ve disconnected, you can reconnect using:

psql -U postgres
\c mydatabase

Replace postgres with your PostgreSQL username if different, and mydatabase with the name of your database.

Step 2: Insert Data into the Table

Here’s how you would insert a single row into the employees table:

INSERT INTO employees (first_name, last_name, email, date_of_birth, salary) VALUES
('John', 'Doe', 'john.doe@example.com', '1985-01-15', 55000.00);

You can insert multiple rows in a single INSERT INTO command by adding more rows of values:

INSERT INTO employees (first_name, last_name, email, date_of_birth, salary) VALUES
('Jane', 'Doe', 'jane.doe@example.com', '1990-07-22', 62000.00),
('Jim', 'Beam', 'jim.beam@example.com', '1988-03-11', 46000.00),
('Jack', 'Daniels', 'jack.daniels@example.com', '1979-08-30', 50000.00);

Step 3: Confirm the Data Insertion

After inserting the data, you can confirm that the entries are correctly added by querying the table:

SELECT * FROM employees;

This command will display all the rows in the employees table, allowing you to see the data you just inserted.

Step 4: Additional Insert Options

• Specific Columns: You can specify which columns you want to insert data into, as shown in the examples above. This is useful when some columns have default values or are auto-incremented (like employee_id in your table).
• Bulk Insert from a File: If you have a large amount of data, you might consider using the COPY command to import data directly from a CSV file. This is much faster than using multiple INSERT statements.

Here’s an example of how you might use the COPY command for bulk data loading from a CSV file:

1. Prepare a CSV file employees.csv with the following format (assuming it does not include an employee_id as it’s auto-generated):

   first_name,last_name,email,date_of_birth,salary
   Alice,Smith,alice.smith@example.com,1992-05-21,73000.00
   Bob,Johnson,bob.johnson@example.com,1984-12-13,68000.00

2. Use the following SQL command to load the data:

   COPY employees(first_name, last_name, email, date_of_birth, salary)
   FROM '/path/to/your/employees.csv' DELIMITER ',' CSV HEADER;

• Replace /path/to/your/employees.csv with the actual path to your CSV file.
• The HEADER keyword indicates that the first line of the file is a header row.

Conclusion

Using these steps, you can easily add demo data to your PostgreSQL tables. Always ensure the data types and values match the column definitions to avoid errors during inserts. If you need more help with specific scenarios or advanced features like transactions or handling duplicates, feel free to ask!

Now show this table data inside reactjs application:

To display data from a PostgreSQL table in a React.js application, you’ll need to set up a way for your React frontend to communicate with your PostgreSQL database through a backend server. Assuming you already have a Node.js/Express backend and React setup as discussed earlier, let’s go through the steps to fetch and display data from the PostgreSQL employees table in your React application.

Step 1: Create a Backend Endpoint to Fetch Data

In your Node.js/Express application, you’ll need to create an API endpoint that retrieves data from your PostgreSQL database.

1. Modify your Express server (index.js) to include a new route that fetches data:

const express = require('express');
const bodyParser = require('body-parser');
const { Pool } = require('pg');
require('dotenv').config();

const app = express();
const port = process.env.PORT || 3000;

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

// New route to get employees
app.get('/employees', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT * FROM employees');
    res.status(200).json(rows);
  } catch (error) {
    res.status(500).json({error: error.message});
  }
});

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

1. This code creates an API endpoint /employees that fetches all rows from the employees table and returns them as JSON.

Step 2: Fetch Data in React

2. In your React application, modify or create a component to fetch and display this data.Here’s an example component:

import React, { useEffect, useState } from 'react';

function EmployeeList() {
  const [employees, setEmployees] = useState([]);
  const [loading, setLoading] = useState(true);
  const [error, setError] = useState(null);

  useEffect(() => {
    fetch('http://localhost:3000/employees')
      .then(response => {
        if (!response.ok) {
          throw new Error('Network response was not ok');
        }
        return response.json();
      })
      .then(data => {
        setEmployees(data);
        setLoading(false);
      })
      .catch(error => {
        setError(error.message);
        setLoading(false);
      });
  }, []);

  if (loading) return <div>Loading...</div>;
  if (error) return <div>Error: {error}</div>;

  return (
    <div>
      <h1>Employee List</h1>
      <ul>
        {employees.map(employee => (
          <li key={employee.employee_id}>
            {employee.first_name} {employee.last_name} - {employee.email}
          </li>
        ))}
      </ul>
    </div>
  );
}

export default EmployeeList;

• This component uses the useEffect hook to fetch data when the component mounts.
• The data is stored in the state using the useState hook.
• It handles loading and error states and displays a list of employees.

Step 3: Include the Component in Your App

3. Add the EmployeeList component to your main App component so it renders on your page

import React from 'react';
import EmployeeList from './EmployeeList';  // Adjust the path as necessary

function App() {
  return (
    <div className="App">
      <EmployeeList />
    </div>
  );
}

export default App;

Step 4: Run Your Application

4. Start both your backend server and your React development server (if they aren’t already running):
   • Run the backend server: node index.js (or however you start your backend).
   • Run the React server: npm start from your React project directory.

Once everything is up and running, your React application should be able to fetch and display the data from the PostgreSQL employees table through your Express API. This demonstrates a basic full-stack application flow from database to user interface. If you encounter any CORS issues or other problems, make sure your server is set up to handle cross-origin requests and that all endpoints are correctly configured.

This is it guys and if you will have any kind of query, suggestion or requirement then feel free to comment below.

Thanks