LogiUpSkill

Menu

+91 996 011 2296

Full-Stack User Management App

In modern web applications, relational databases are essential for managing structured and transactional data. PostgreSQL is a powerful, open-source relational database known for its reliability, performance, and ease of use. In this comprehensive tutorial, we’ll build a complete User Management App from scratch using PostgreSQL, Node.js, Express, and React.
 

Table of Contents

1. Introduction and Project Overview

Project Title: User Management App

The User Management App is a full-stack application that demonstrates the complete CRUD (Create, Read, Update, Delete) operations cycle using a modern technology stack. This project serves as an excellent starting point for understanding how frontend, backend, and database layers communicate in a real-world application.

Key Features

➕
Add New User Create new user records with name and email validation
📋
Fetch All Users Retrieve and display all users from the database
🎨
Modern UI Clean, responsive interface built with Tailwind CSS
⚡
Fast Development Hot module replacement with Vite for instant updates

2. Technology Stack

⚛️

React

Frontend Framework Component-based UI library for building interactive user interfaces with state management and hooks
🟢

Node.js & Express

Backend Server JavaScript runtime with Express framework for building RESTful APIs and handling HTTP requests
🐘

PostgreSQL

Database Open-source relational database with ACID compliance, excellent performance, and reliability

Additional Technologies

Technology Purpose Why We Use It
Vite Build Tool Lightning-fast development server with HMR
Axios HTTP Client Promise-based HTTP library for API requests
Tailwind CSS CSS Framework Utility-first CSS for rapid UI development
pg (node-postgres) PostgreSQL Client Node.js library for PostgreSQL database connections
Express Web Framework Minimal and flexible Node.js web application framework

3. Application Architecture

Three-Tier Architecture

Presentation Layer React Frontend (Port 5173)
Application Layer Node.js + Express (Port 5000)
Data Layer PostgreSQL Database (Port 5432)
Data Flow:
  1. User interacts with React frontend (submits form or requests data)
  2. React sends HTTP request to Express backend via Axios
  3. Express receives request and queries PostgreSQL database
  4. Database processes query and returns results
  5. Express sends response back to React
  6. React updates UI with new data

4. Database Setup with PostgreSQL

Open pgAdmin

pgAdmin is a powerful open-source administration and development platform for PostgreSQL. Launch pgAdmin from your applications menu or start menu.

Create Database

Create a new database named userdb which will store all our user management data.
SQL Command
CREATE DATABASE userdb;
Alternative Method: You can also right-click on “Databases” in the left sidebar of pgAdmin and select “Create > Database” to create the database through the GUI.

Create Users Table

Now we’ll create a table to store user information. This table will have an auto-incrementing ID, name, and email fields.
SQL Table Definition
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

Table Schema Breakdown

Column Data Type Description
id SERIAL PRIMARY KEY Auto-incrementing integer, unique identifier for each user
name VARCHAR(100) User’s full name, maximum 100 characters
email VARCHAR(100) User’s email address, maximum 100 characters
Understanding SERIAL: The SERIAL data type in PostgreSQL automatically creates a sequence and sets the column default to the next value from that sequence. This ensures each new user gets a unique, auto-incremented ID.

5. Project Structure Setup

Step-by-Step Project Initialization

Create Root Folder

Create a main folder for the entire project called User Management App and open it with Visual Studio Code.
mkdir “User Management App” && cd “User Management App”
 

Setup Frontend with Vite

Step 1: Initialize Vite Project

npm create vite@latest

Step 2: Configure Project

  • Project name: frontend
  • Select framework: React
  • Select variant: JavaScript
  • Use rolldown-vite (Experimental)? No
  • Install with npm and start now? Yes
Success: Frontend React application has been created successfully with Vite!
 

Setup Backend

Step 1: Create Backend Folder

In the root project directory, create a folder named backend.
mkdir backend

Step 2: Initialize Node.js Project

cd backend
npm init -y
What is npm init -y? This command creates a package.json file with default values. The -y flag accepts all defaults automatically.

Final Project Structure

User Management App/ ├── frontend/ │ ├── node_modules/ │ ├── public/ │ ├── src/ │ │ ├── App.jsx │ │ ├── App.css │ │ ├── index.css │ │ ├── main.jsx │ │ ├── UserForm.jsx │ │ └── UserList.jsx │ ├── index.html │ ├── package.json │ └── vite.config.js │ └── backend/ ├── node_modules/ ├── index.js ├── config.js └── package.json

6. Backend Setup with Node.js & Express

Create Entry File

Create a file named index.js in the backend folder. This will be our main server file.
backend/index.js (Initial Test)
console.log('Hello');

Test the Backend

cd backend
node index.js
Expected Output: You should see “Hello” printed in the terminal.

Install Required Packages

Install the necessary npm packages for our backend server.
npm install pg express

Package Descriptions

Package Purpose
pg PostgreSQL client for Node.js – handles database connections and queries
express Web application framework – simplifies routing and middleware management

Create Database Configuration

Create a file named config.js to store database connection settings.
backend/config.js
const { Pool } = require('pg');

const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'userdb',
  password: 'your_password_here',
  port: 5432,
});

module.exports = pool;
Important: Replace 'your_password_here' with your actual PostgreSQL password. Never commit sensitive credentials to version control. Consider using environment variables in production.

Configuration Breakdown

  • user: PostgreSQL username (default is ‘postgres’)
  • host: Database server location (localhost for local development)
  • database: Name of the database we created (‘userdb’)
  • password: PostgreSQL password you set during installation
  • port: PostgreSQL default port (5432)

Create Express Server with API Endpoints

backend/index.js
const express = require('express');
const pool = require('./config');

const app = express();
const PORT = 5000;

// Middleware
app.use(express.json());
app.use(require('cors')());

// GET: Fetch all users
app.get('/users', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM users');
    res.json(result.rows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Database error' });
  }
});

// POST: Create new user
app.post('/users', async (req, res) => {
  const { name, email } = req.body;

  try {
    const result = await pool.query(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      [name, email]
    );
    res.status(201).json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Database error' });
  }
});

// Start server
app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

Code Explanation

Section Purpose
express.json() Middleware to parse JSON request bodies
cors() Enables Cross-Origin Resource Sharing for frontend communication
GET /users Endpoint to retrieve all users from database
POST /users Endpoint to create new user in database
RETURNING * PostgreSQL feature that returns the inserted row
Security Note: The $1, $2 syntax in the query is using parameterized queries, which protects against SQL injection attacks.

Install CORS Package

Since we’re using CORS in the code above, we need to install it:
npm install cors

Start the Backend Server

node index.js
Expected Output: Server running on http://localhost:5000 Your backend is now ready and listening for requests!

7. Frontend Setup with React & Vite

Install Frontend Dependencies

Navigate to the frontend folder and install required packages.
cd frontend
npm install axios tailwindcss @tailwindcss/vite

Package Descriptions

Package Purpose
axios Promise-based HTTP client for making API requests to backend
tailwindcss Utility-first CSS framework for styling
@tailwindcss/vite Tailwind CSS plugin for Vite

Configure Tailwind CSS with Vite

frontend/vite.config.js
import { defineConfig } from 'vite';
import react from '@vitejs/plugin-react';
import tailwindcss from '@tailwindcss/vite';

export default defineConfig({
  plugins: [react(), tailwindcss()],
});

Setup Tailwind CSS

Remove all existing CSS from App.css and index.css, then add Tailwind directives.
frontend/src/index.css
@import "tailwindcss";
frontend/src/App.css
@import "tailwindcss";

Update App.jsx

frontend/src/App.jsx
import React from "react";
import UserForm from "./UserForm";
import UserList from "./UserList";
import "./App.css";

function App() {
  return (
    <div className="min-h-screen bg-gradient-to-br from-blue-50 to-indigo-100">
      <div className="container mx-auto px-4 py-8">
        <h1 className="text-4xl font-bold text-center mb-8 text-gray-800">
          User Management System
        </h1>

        <div className="grid md:grid-cols-2 gap-8">
          <UserForm />
          <UserList />
        </div>
      </div>
    </div>
  );
}

export default App;

8. Building React Components

Create UserForm Component

This component handles user creation with form validation and API integration.
frontend/src/UserForm.jsx
import React, { useState } from "react";
import axios from "axios";

const UserForm = () => {
  const [name, setName] = useState("");
  const [email, setEmail] = useState("");

  const handleSubmit = async (e) => {
    e.preventDefault();

    if (!name || !email) {
      alert("Please fill all fields");
      return;
    }

    try {
      await axios.post("http://localhost:5000/users", {
        name,
        email,
      });

      alert("User created successfully");
      setName("");
      setEmail("");
    } catch (error) {
      console.error(error);
      alert("Error creating user");
    }
  };

  return (
    <div className="min-h-screen flex items-center justify-center">
      <div className="bg-white p-6 rounded-lg shadow-md w-full max-w-md">
        <h1 className="text-2xl font-bold text-center mb-6">
          Create New User
        </h1>

        <form onSubmit={handleSubmit} className="space-y-4">
          <div>
            <label htmlFor="name" className="block text-sm font-medium mb-1">
              Enter Name
            </label>
            <input
              id="name"
              type="text"
              placeholder="Enter Name"
              value={name}
              onChange={(e) => setName(e.target.value)}
              className="w-full px-3 py-2 border rounded-md focus:outline-none focus:ring-2 focus:ring-blue-500"
            />
          </div>

          <div>
            <label htmlFor="email" className="block text-sm font-medium mb-1">
              Enter Email
            </label>
            <input
              id="email"
              type="email"
              placeholder="Enter Email"
              value={email}
              onChange={(e) => setEmail(e.target.value)}
              className="w-full px-3 py-2 border rounded-md focus:outline-none focus:ring-2 focus:ring-blue-500"
            />
          </div>

          <button
            type="submit"
            className="w-full bg-blue-600 text-white py-2 rounded-md hover:bg-blue-700 transition"
          >
            Submit
          </button>
        </form>
      </div>
    </div>
  );
};

export default UserForm;

Component Features

  • State Management: Uses React hooks (useState) for form fields
  • Form Validation: Checks if fields are filled before submission
  • API Integration: Posts data to backend using Axios
  • User Feedback: Shows success/error alerts
  • Form Reset: Clears fields after successful submission

Create UserList Component

This component fetches and displays all users from the database.
frontend/src/UserList.jsx
import React, { useEffect, useState } from "react";
import axios from "axios";

const UserList = () => {
  const [users, setUsers] = useState([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    fetchUsers();
  }, []);

  const fetchUsers = async () => {
    try {
      const res = await axios.get("http://localhost:5000/users");
      setUsers(res.data);
    } catch (error) {
      console.error(error);
      alert("Failed to fetch users");
    } finally {
      setLoading(false);
    }
  };

  if (loading) {
    return (
      <p className="text-center mt-10 text-gray-600">
        Loading users...
      </p>
    );
  }

  return (
    <div className="min-h-screen flex items-center justify-center">
      <div className="max-w-4xl mx-auto bg-white p-6 rounded-lg shadow">
        <h2 className="text-2xl font-bold mb-4 text-center">
          User List
        </h2>

        {users.length === 0 ? (
          <p className="text-center text-gray-500">
            No users found
          </p>
        ) : (
          <table className="w-full border-collapse">
            <thead>
              <tr className="bg-gray-200">
                <th className="border p-2 text-left">ID</th>
                <th className="border p-2 text-left">Name</th>
                <th className="border p-2 text-left">Email</th>
              </tr>
            </thead>
            <tbody>
              {users.map((user) => (
                <tr key={user.id} className="hover:bg-gray-100">
                  <td className="border p-2">{user.id}</td>
                  <td className="border p-2">{user.name}</td>
                  <td className="border p-2">{user.email}</td>
                </tr>
              ))}
            </tbody>
          </table>
        )}
      </div>
    </div>
  );
};

export default UserList;

Component Features

  • Data Fetching: Uses useEffect to fetch users on component mount
  • Loading State: Shows loading message while data is being fetched
  • Empty State: Displays appropriate message when no users exist
  • Data Display: Renders users in a clean, responsive table
  • Error Handling: Catches and displays fetch errors

9. Testing and Final Output

Start Backend Server

Open a terminal in the backend folder and run:
node index.js
Backend running at: http://localhost:5000

Start Frontend Development Server

Open another terminal in the frontend folder and run:
npm run dev
Frontend running at: http://localhost:5173

Final Application Features

  • Users can be added via React form with real-time validation
  • Data is securely stored in PostgreSQL database
  • User list is fetched from backend and displayed in a responsive table
  • Clean, modern UI built with Tailwind CSS
  • Real-time updates between form submission and user list
  • Error handling for all API requests
  • Cross-Origin Resource Sharing (CORS) enabled for secure communication

Testing the Application

Test 1: Add a User

  1. Open http://localhost:5173 in your browser
  2. Fill in the “Create New User” form with name and email
  3. Click “Submit”
  4. You should see a success alert
  5. Refresh the page to see the new user in the User List

Test 2: Verify Database

Open pgAdmin and run this query to verify data storage:
SQL Query
SELECT * FROM users;
You should see all users you created through the React form.
 

10. Best Practices and Optimization

Security Enhancements

Production Considerations:
  • Use environment variables for database credentials
  • Implement input validation on both frontend and backend
  • Add email uniqueness constraint in database
  • Implement rate limiting to prevent abuse
  • Use HTTPS in production
  • Sanitize user inputs to prevent XSS attacks

Performance Optimization

Recommended Improvements:
  • Connection Pooling: Already implemented with pg Pool
  • Add Indexes: Create indexes on frequently queried columns (email)
  • Pagination: Implement pagination for large user lists
  • Caching: Use Redis for frequently accessed data
  • Debouncing: Add debouncing to form inputs for better UX

Code Quality Improvements

Improvement Implementation Benefit
Environment Variables Use dotenv package for config Secure credential management
Error Boundaries Add React error boundaries Graceful error handling in UI
TypeScript Migrate to TypeScript Type safety and better IDE support
Testing Add Jest and React Testing Library Ensure code reliability
API Versioning Use /api/v1/ prefix Future-proof API changes

Database Schema Enhancement

Enhanced Schema with Constraints
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add index for faster email lookups
CREATE INDEX idx_users_email ON users(email);

-- Add trigger for updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.updated_at = NOW();
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
Congratulations! You’ve successfully built a full-stack User Management Application with PostgreSQL, Node.js, Express, and React. This project demonstrates fundamental concepts of modern web development including database design, RESTful API creation, and reactive frontend development.

Next Steps

  • Add update and delete functionality (PUT and DELETE endpoints)
  • Implement user authentication with JWT
  • Add search and filtering capabilities
  • Deploy to production (consider platforms like Heroku, Vercel, or AWS)
  • Add unit and integration tests
  • Implement real-time updates with WebSockets
Full-Stack User Management App