Node.js & PostgreSQL Secure Session Management

This project is a scalable and extensible Node.js application that demonstrates how to handle user sessions securely using a PostgreSQL database as the session store. It follows the Model-View-Controller (MVC) design pattern for a clean and maintainable codebase.

Features

• MVC Architecture: Separates business logic, database interactions, and routing for scalability.
• Persistent Sessions: User sessions are stored in a PostgreSQL database, so they persist across server restarts.
• Secure Authentication: Includes user registration and login with password hashing using bcrypt.
• Environment-based Configuration: Uses a .env file to manage sensitive data like database credentials and session secrets, keeping them out of the source code.
• Centralized Database & Session Config: Configuration is modular and easy to manage.

Prerequisites

• Node.js (v14 or later)
• PostgreSQL
• An empty PostgreSQL database

1. Setup

First, clone the repository and install the dependencies.

git clone <your-repository-url>
cd nodejs-postgres-sessions
npm install

2. Database Setup

Connect to your PostgreSQL instance and run the following SQL commands to create the necessary users and session tables.

-- Create the table to store user information
CREATE TABLE "users" (
    "id" SERIAL PRIMARY KEY,
    "username" VARCHAR(255) UNIQUE NOT NULL,
    "password_hash" VARCHAR(255) NOT NULL,
    "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create the table for connect-pg-simple to store session data
CREATE TABLE "session" (
  "sid" varchar NOT NULL COLLATE "default",
  "sess" json NOT NULL,
  "expire" timestamp(6) NOT NULL
)
WITH (OIDS=FALSE);

ALTER TABLE "session" ADD CONSTRAINT "session_pkey" PRIMARY KEY ("sid") NOT DEFERRABLE INITIALLY IMMEDIATE;

3. Configuration

Create a .env file in the root of the project. This file will store your environment variables. Copy the contents of .env.example (if provided) or use the template below.

Important: The SESSION_SECRET should be a long, random, and unique string.

# ------------------
# APPLICATION CONFIG
# ------------------
NODE_ENV=development
PORT=3000

# ------------------
# DATABASE CONFIG
# ------------------
DB_HOST=localhost
DB_PORT=5432
DB_USER=your_postgres_username
DB_PASSWORD=your_postgres_password
DB_NAME=your_database_name

# ------------------
# SESSION CONFIG
# ------------------
SESSION_SECRET="replace-with-a-very-long-and-random-secret-string"
SESSION_LIFETIME=2592000000 # 30 days in milliseconds

4. Running the Application

Once the database and configuration are set up, you can start the server.

npm start

The application will be running at http://localhost:3000.

Project Structure Explained

• config/: Contains configuration files for the database and session management.
• controllers/: Handles the request/response logic. It acts as the bridge between models and routes.
• models/: Manages data and database interaction. All SQL queries are encapsulated here.
• routes/: Defines the application's endpoints and maps them to controller functions.
• views/: Contains simple HTML files for the UI.
• app.js: The main entry point of the application. It initializes Express, sets up middleware, and connects the routes.
• .env: Stores sensitive configuration variables.
• .gitignore: Specifies which files Git should ignore (e.g., node_modules, .env).