Comprehensive Guide to Building a Product and Service Website with PostgreSQL, Node.js, and Vue 3
Step-by-Step Implementation for a Secure and Scalable E-commerce Platform
Key Takeaways
- Robust Database Structure: Efficiently organize products, services, categories, and user data using PostgreSQL to ensure data integrity and scalability.
- Secure Backend Development: Implement Node.js with ES6, incorporating best security practices like JWT authentication, bcrypt hashing, and environment variable management.
- Responsive Frontend with Vue 3: Utilize Vue 3's Composition API for a dynamic and user-friendly interface, integrated seamlessly with backend APIs for real-time data interaction.
1. Database Design with PostgreSQL
A well-structured database is the backbone of any e-commerce platform. PostgreSQL offers a robust and scalable solution for managing complex relationships between various entities such as users, products, services, and orders.
1.1. Database Schema Overview
The database will comprise several interrelated tables to handle users, categories, subcategories, products, services, project examples, comments, and orders. This structure ensures efficient data retrieval and maintains referential integrity.
1.1.1. Users Table
Stores user information for authentication and order management.
-- Users Table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
full_name VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1.1.2. Categories and Subcategories Tables
Organize products and services into hierarchical categories and subcategories for better navigation and management.
-- Categories Table
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
parent_id INTEGER REFERENCES categories(id),
type VARCHAR(50) NOT NULL -- 'product' or 'service'
);
-- Subcategories Table
CREATE TABLE subcategories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
category_id INT REFERENCES categories(id) ON DELETE CASCADE
);
1.1.3. Products Table
Holds details about the products offered on the website.
-- Products Table
CREATE TABLE products (
id SERIAL PRIMARY KEY,
category_id INTEGER REFERENCES categories(id),
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INTEGER NOT NULL,
photo_url TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1.1.4. Services Table
Contains information about the services provided, including project examples.
-- Services Table
CREATE TABLE services (
id SERIAL PRIMARY KEY,
category_id INTEGER REFERENCES categories(id),
name VARCHAR(255) NOT NULL,
description TEXT,
base_price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1.1.5. Projects and Comments Tables
Showcase project examples related to services, allowing user engagement through comments.
-- Projects Table
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
service_id INTEGER REFERENCES services(id),
title VARCHAR(255) NOT NULL,
description TEXT,
completion_date DATE NOT NULL
);
-- Project Images Table
CREATE TABLE project_images (
id SERIAL PRIMARY KEY,
project_id INTEGER REFERENCES projects(id),
image_url VARCHAR(255) NOT NULL,
is_primary BOOLEAN DEFAULT false
);
-- Project Comments Table
CREATE TABLE project_comments (
id SERIAL PRIMARY KEY,
project_id INTEGER REFERENCES projects(id),
user_id INTEGER REFERENCES users(id),
comment TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
1.1.6. Orders and Order Items Tables
Manage user orders for both products and services, detailing each order's status and contents.
-- Orders Table
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) NOT NULL DEFAULT 'Pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Order Items Table
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(id),
product_id INTEGER REFERENCES products(id),
service_id INTEGER REFERENCES services(id),
quantity INTEGER NOT NULL,
price DECIMAL(10,2) NOT NULL
);
1.2. Database Relationships
Understanding the relationships between tables is crucial for efficient data management:
- Users to Orders: One-to-Many – A user can place multiple orders.
- Categories to Subcategories: One-to-Many – Each category can have multiple subcategories.
- Products to Subcategories: Many-to-One – Each product belongs to a single subcategory.
- Services to Subcategories: Many-to-One – Each service belongs to a single subcategory.
- Services to Projects: One-to-Many – A service can have multiple project examples.
- Projects to Project Images: One-to-Many – Each project can have multiple images.
- Projects to Comments: One-to-Many – Each project can have multiple comments from users.
2. Backend Setup with Node.js and ES6
The backend serves as the core of the application, handling data operations, user authentication, and business logic. Utilizing Node.js with ES6 modules ensures modern and efficient code practices.
2.1. Dependencies Installation
Install the necessary packages to build a secure and functional backend:
npm install express pg bcrypt jsonwebtoken cors dotenv helmet rate-limit
2.2. Folder Structure
A modular folder structure promotes maintainability and scalability:
backend/
├── config/
│ └── db.js
├── controllers/
│ ├── authController.js
│ ├── productController.js
│ ├── serviceController.js
│ └── orderController.js
├── middlewares/
│ ├── authMiddleware.js
│ └── securityMiddleware.js
├── models/
│ ├── userModel.js
│ ├── productModel.js
│ ├── serviceModel.js
│ └── orderModel.js
├── routes/
│ ├── authRoutes.js
│ ├── productRoutes.js
│ ├── serviceRoutes.js
│ └── orderRoutes.js
├── utils/
│ └── jwtUtils.js
├── .env
└── server.js
2.3. Configuring the Database Connection
Establish a connection to the PostgreSQL database using environment variables for security:
// config/db.js
import { Pool } from 'pg';
import dotenv from 'dotenv';
dotenv.config();
const pool = new Pool({
user: process.env.DB_USER,
host: process.env.DB_HOST,
database: process.env.DB_NAME,
password: process.env.DB_PASSWORD,
port: process.env.DB_PORT,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
export default pool;
2.4. Implementing User Authentication
Secure user authentication is essential for protecting user data and managing orders. This involves hashing passwords with bcrypt and generating JWT tokens for session management.
2.4.1. Authentication Middleware
Verify JWT tokens to secure protected routes:
// middlewares/authMiddleware.js
import jwt from 'jsonwebtoken';
import dotenv from 'dotenv';
dotenv.config();
export const authenticateJWT = (req, res, next) => {
const authHeader = req.headers.authorization;
if (!authHeader) return res.status(401).send('Access denied.');
const token = authHeader.split(' ')[1];
if (!token) return res.status(401).send('Access denied.');
jwt.verify(token, process.env.JWT_SECRET, (err, user) => {
if (err) return res.status(403).send('Invalid token.');
req.user = user;
next();
});
};
2.4.2. Authentication Routes
Handle user registration and login:
// routes/authRoutes.js
import express from 'express';
import bcrypt from 'bcrypt';
import jwt from 'jsonwebtoken';
import pool from '../config/db.js';
import dotenv from 'dotenv';
dotenv.config();
const router = express.Router();
// User Registration
router.post('/register', async (req, res) => {
const { username, email, password, full_name } = req.body;
try {
const hashedPassword = await bcrypt.hash(password, 10);
const result = await pool.query(
'INSERT INTO users (username, email, password_hash, full_name) VALUES ($1, $2, $3, $4) RETURNING id, username, email, full_name',
[username, email, hashedPassword, full_name]
);
res.status(201).json(result.rows[0]);
} catch (err) {
res.status(400).json({ error: 'Registration failed.' });
}
});
// User Login
router.post('/login', async (req, res) => {
const { email, password } = req.body;
try {
const user = await pool.query('SELECT * FROM users WHERE email = $1', [email]);
if (user.rows.length === 0) return res.status(400).send('User not found.');
const validPassword = await bcrypt.compare(password, user.rows[0].password_hash);
if (!validPassword) return res.status(400).send('Invalid credentials.');
const token = jwt.sign(
{ id: user.rows[0].id, email: user.rows[0].email },
process.env.JWT_SECRET,
{ expiresIn: '1h' }
);
res.json({ token });
} catch (err) {
res.status(500).json({ error: 'Login failed.' });
}
});
export default router;
2.5. Security Middleware
Enhance security by setting HTTP headers and rate limiting to prevent attacks:
// middlewares/securityMiddleware.js
import helmet from 'helmet';
import rateLimit from 'express-rate-limit';
export const securityMiddleware = (app) => {
// Set security HTTP headers
app.use(helmet());
// Rate limiting
const limiter = rateLimit({
windowMs: 15 * 60 * 1000, // 15 minutes
max: 100, // limit each IP to 100 requests per windowMs
message: 'Too many requests from this IP, please try again later.'
});
app.use('/api/', limiter);
};
2.6. Server Initialization
Initialize the Express server and integrate all routes and middleware:
// server.js
import express from 'express';
import cors from 'cors';
import dotenv from 'dotenv';
import authRoutes from './routes/authRoutes.js';
import productRoutes from './routes/productRoutes.js';
import serviceRoutes from './routes/serviceRoutes.js';
import orderRoutes from './routes/orderRoutes.js';
import { authenticateJWT } from './middlewares/authMiddleware.js';
import { securityMiddleware } from './middlewares/securityMiddleware.js';
dotenv.config();
const app = express();
// Middleware
app.use(cors());
app.use(express.json());
securityMiddleware(app);
// Routes
app.use('/api/auth', authRoutes);
app.use('/api/products', authenticateJWT, productRoutes);
app.use('/api/services', authenticateJWT, serviceRoutes);
app.use('/api/orders', authenticateJWT, orderRoutes);
// Root Endpoint
app.get('/', (req, res) => {
res.send('Welcome to the E-commerce API');
});
// Start Server
const PORT = process.env.PORT || 5000;
app.listen(PORT, () => console.log(`Server running on port ${PORT}`));
3. Frontend Setup with Vue 3 Composition API
The frontend provides a user-friendly interface for interacting with the backend. Vue 3's Composition API offers a flexible and efficient way to manage component logic.
3.1. Project Initialization
Use Vue CLI to scaffold the project:
npm install -g @vue/cli
vue create frontend
Select the Vue 3 preset during setup.
3.2. Installing Required Libraries
Install Vue Router and Axios for routing and API communication:
cd frontend
npm install vue-router axios
3.3. Folder Structure
Organize the frontend project for scalability:
frontend/
├── src/
│ ├── assets/
│ ├── components/
│ │ ├── Navbar.vue
│ │ ├── ProductCard.vue
│ │ └── ServiceCard.vue
│ ├── router/
│ │ └── index.js
│ ├── views/
│ │ ├── HomeView.vue
│ │ ├── ProductsView.vue
│ │ ├── ServicesView.vue
│ │ ├── RegisterView.vue
│ │ ├── LoginView.vue
│ │ └── OrderView.vue
│ ├── store/
│ │ └── index.js
│ ├── App.vue
│ └── main.js
└── package.json
3.4. Configuring Vue Router
Set up routing for different views:
// src/router/index.js
import { createRouter, createWebHistory } from 'vue-router';
import HomeView from '../views/HomeView.vue';
import ProductsView from '../views/ProductsView.vue';
import ServicesView from '../views/ServicesView.vue';
import RegisterView from '../views/RegisterView.vue';
import LoginView from '../views/LoginView.vue';
import OrderView from '../views/OrderView.vue';
import store from '../store/index.js';
const routes = [
{ path: '/', name: 'Home', component: HomeView },
{ path: '/products', name: 'Products', component: ProductsView },
{ path: '/services', name: 'Services', component: ServicesView },
{ path: '/register', name: 'Register', component: RegisterView },
{ path: '/login', name: 'Login', component: LoginView },
{
path: '/order',
name: 'Order',
component: OrderView,
meta: { requiresAuth: true }
},
];
const router = createRouter({
history: createWebHistory(),
routes,
});
// Navigation Guard for Protected Routes
router.beforeEach((to, from, next) => {
if (to.meta.requiresAuth && !store.state.isAuthenticated) {
next('/login');
} else {
next();
}
});
export default router;
3.5. State Management with Vuex
Manage global state, including user authentication and cart management:
// src/store/index.js
import { createStore } from 'vuex';
import axios from 'axios';
const store = createStore({
state() {
return {
user: null,
token: localStorage.getItem('token') || '',
isAuthenticated: !!localStorage.getItem('token'),
products: [],
services: [],
cart: [],
};
},
mutations: {
SET_USER(state, user) {
state.user = user;
},
SET_TOKEN(state, token) {
state.token = token;
state.isAuthenticated = true;
},
LOGOUT(state) {
state.user = null;
state.token = '';
state.isAuthenticated = false;
},
SET_PRODUCTS(state, products) {
state.products = products;
},
SET_SERVICES(state, services) {
state.services = services;
},
ADD_TO_CART(state, item) {
state.cart.push(item);
},
REMOVE_FROM_CART(state, index) {
state.cart.splice(index, 1);
},
CLEAR_CART(state) {
state.cart = [];
},
},
actions: {
async register({ commit }, userData) {
const response = await axios.post('/api/auth/register', userData);
return response.data;
},
async login({ commit }, credentials) {
const response = await axios.post('/api/auth/login', credentials);
const token = response.data.token;
localStorage.setItem('token', token);
commit('SET_TOKEN', token);
// Optionally fetch user data here
},
logout({ commit }) {
localStorage.removeItem('token');
commit('LOGOUT');
},
async fetchProducts({ commit }) {
const response = await axios.get('/api/products', {
headers: { Authorization: `Bearer ${this.state.token}` }
});
commit('SET_PRODUCTS', response.data);
},
async fetchServices({ commit }) {
const response = await axios.get('/api/services', {
headers: { Authorization: `Bearer ${this.state.token}` }
});
commit('SET_SERVICES', response.data);
},
addToCart({ commit }, item) {
commit('ADD_TO_CART', item);
},
removeFromCart({ commit }, index) {
commit('REMOVE_FROM_CART', index);
},
clearCart({ commit }) {
commit('CLEAR_CART');
},
},
getters: {
cartItemCount: state => state.cart.length,
cartTotalPrice: state => state.cart.reduce((total, item) => total + (item.price * item.quantity), 0),
},
});
export default store;
3.6. Creating Vue Components
Develop reusable components for products, services, and navigation:
3.6.1. Navbar Component
<!-- src/components/Navbar.vue -->
<template>
<nav>
<router-link to="/">Home</router-link>
<router-link to="/products">Products</router-link>
<router-link to="/services">Services</router-link>
<router-link v-if="!isAuthenticated" to="/login">Login</router-link>
<router-link v-if="!isAuthenticated" to="/register">Register</router-link>
<button v-if="isAuthenticated" @click="logout">Logout</button>
<router-link to="/order">Order ({{ cartCount }})</router-link>
</nav>
</template>
<script>
import { mapState, mapActions } from 'vuex';
export default {
computed: {
...mapState(['isAuthenticated', 'cart']),
cartCount() {
return this.cart.length;
},
},
methods: {
...mapActions(['logout']),
},
};
</script>
<style scoped>
nav {
display: flex;
gap: 1rem;
background-color: #f8f8f8;
padding: 1rem;
}
</style>
3.6.2. Product Card Component
<!-- src/components/ProductCard.vue -->
<template>
<div class="product-card">
<img :src="product.photo_url" alt="Product Image" />
<h3>{{ product.name }}</h3>
<p>{{ product.description }}</p>
<p>Price: ${{ product.price }}</p>
<button @click="addToCartHandler">Add to Cart</button>
</div>
</template>
<script>
import { mapActions } from 'vuex';
export default {
props: {
product: Object,
},
methods: {
...mapActions(['addToCart']),
addToCartHandler() {
this.addToCart({
id: this.product.id,
name: this.product.name,
price: this.product.price,
quantity: 1,
});
},
},
};
</script>
<style scoped>
.product-card {
border: 1px solid #ddd;
padding: 1rem;
text-align: center;
}
.product-card img {
max-width: 100%;
height: auto;
}
</style>
4. Implementing Security and Scalability
Ensuring the application is secure and scalable is crucial for maintaining user trust and handling growing traffic.
4.1. Security Best Practices
- Password Hashing: Use
bcryptto hash user passwords before storing them in the database. - JWT Authentication: Implement JWTs for secure and stateless user sessions.
- Input Validation and Sanitization: Validate and sanitize all user inputs to prevent SQL injection and XSS attacks.
- Environment Variables: Store sensitive information like database credentials and JWT secrets in environment variables using a
.envfile. - Secure HTTP Headers: Utilize
helmetto set secure HTTP headers. - Rate Limiting: Implement rate limiting with
express-rate-limitto mitigate brute-force attacks.
4.2. Scalability Strategies
- Connection Pooling: Use PostgreSQL connection pooling to manage database connections efficiently.
- Caching: Implement caching mechanisms, such as Redis, to store frequently accessed data and reduce database load.
- Load Balancing: Distribute incoming traffic across multiple server instances using a load balancer to ensure high availability.
- Microservices Architecture: Consider breaking the application into microservices for better scalability and maintainability.
- Containerization: Use Docker to containerize the application, facilitating easy deployment and scaling.
4.3. Database Optimization
- Indexing: Create indexes on commonly queried fields to speed up data retrieval.
- Query Optimization: Analyze and optimize slow queries to enhance performance.
- Partitioning: Use table partitioning for large tables to improve query performance and manageability.
5. Connecting Frontend with Backend
Integrate the Vue 3 frontend with the Node.js backend to enable seamless data flow and user interaction.
5.1. API Integration with Axios
Use Axios to communicate with the backend APIs for fetching and manipulating data:
// src/main.js
import { createApp } from 'vue';
import App from './App.vue';
import router from './router/index.js';
import store from './store/index.js';
import axios from 'axios';
const app = createApp(App);
axios.defaults.baseURL = 'http://localhost:5000/api';
app.config.globalProperties.$axios = axios;
app.use(store);
app.use(router);
app.mount('#app');
5.2. Handling Authentication
Manage user sessions by storing JWT tokens and attaching them to Axios requests:
// src/store/index.js (Excerpt)
actions: {
async login({ commit }, credentials) {
const response = await axios.post('/auth/login', credentials);
const token = response.data.token;
localStorage.setItem('token', token);
commit('SET_TOKEN', token);
axios.defaults.headers.common['Authorization'] = `Bearer ${token}`;
},
logout({ commit }) {
localStorage.removeItem('token');
commit('LOGOUT');
delete axios.defaults.headers.common['Authorization'];
},
},
5.3. Placing Orders
Enable users to place orders for products and services:
// src/views/OrderView.vue
<template>
<div>
<h1>Order Summary</h1>
<div v-if="cart.length">
<ul>
<li v-for="(item, index) in cart" :key="index">
{{ item.name }} - ${{ item.price }} x {{ item.quantity }}
<button @click="removeFromCart(index)">Remove</button>
</li>
</ul>
<p>Total: ${{ totalPrice }}</p>
<button @click="placeOrder">Place Order</button>
</div>
<div v-else>
<p>Your cart is empty.</p>
</div>
</div>
</template>
<script>
import { mapState, mapActions, mapGetters } from 'vuex';
import axios from 'axios';
export default {
computed: {
...mapState(['cart']),
...mapGetters(['cartTotalPrice']),
totalPrice() {
return this.cartTotalPrice.toFixed(2);
},
},
methods: {
...mapActions(['removeFromCart', 'clearCart']),
async placeOrder() {
try {
const orderItems = this.cart.map(item => ({
product_id: item.product_id || null,
service_id: item.service_id || null,
quantity: item.quantity,
price: item.price,
}));
const response = await axios.post('/orders', {
total_amount: this.totalPrice,
items: orderItems,
});
alert('Order placed successfully!');
this.clearCart();
} catch (error) {
alert('Failed to place order.');
}
},
},
};
</script>
<style scoped>
button {
margin-top: 1rem;
}
</style>
6. Finalizing and Deploying the Application
With the backend and frontend properly set up and integrated, the next steps involve testing, optimizing, and deploying the application.
6.1. Testing
- Unit Testing: Write unit tests for both backend routes and frontend components to ensure individual parts function correctly.
- Integration Testing: Test the interaction between different parts of the application, such as API endpoints and frontend requests.
- End-to-End Testing: Simulate real user scenarios to verify the application's overall functionality.
6.2. Deployment
Choose reliable platforms and services for deploying your application:
- Backend: Deploy the Node.js server on platforms like Heroku, AWS Elastic Beanstalk, or DigitalOcean.
- Frontend: Host the Vue 3 application on platforms like Netlify, Vercel, or GitHub Pages.
- Database: Use managed PostgreSQL services such as AWS RDS, Heroku Postgres, or Azure Database for PostgreSQL.
- Environment Variables: Securely manage environment variables using the deployment platform's configuration settings.
6.3. Continuous Integration and Deployment (CI/CD)
Implement CI/CD pipelines to automate testing and deployment, ensuring code quality and rapid delivery.
-
Use tools like GitHub Actions, Travis CI, or Jenkins to set up automated workflows.
-
Automate tasks such as running tests, building the project, and deploying to production upon successful commits.
6.4. Monitoring and Maintenance
Ensure the application remains performant and secure post-deployment:
- Monitoring: Use monitoring tools like New Relic, Datadog, or Prometheus to track application performance and detect issues.
- Logging: Implement comprehensive logging to record errors and significant events for troubleshooting.
- Regular Updates: Keep dependencies and packages up to date to mitigate security vulnerabilities.
Conclusion
Building a comprehensive e-commerce platform involves meticulous planning and execution across database design, backend development, frontend integration, and security measures. By leveraging PostgreSQL, Node.js, and Vue 3, you can create a scalable, secure, and user-friendly website that effectively manages products, services, and user interactions. Implementing best practices in security and scalability ensures the platform remains robust and reliable as user demand grows.
References
- How to Create a Product Database for Your Online Store | Sales Layer
- Database design for site with many product categories | Stack Overflow
- eCommerce Database Design | DEV Community
- PostgreSQL Official Documentation
- Vue.js Official Guide
- Express.js Official Website
- JSON Web Tokens - jwt.io
- Vuex Official Documentation
Last updated January 20, 2025