Sospeter Mong'are

Posted on Jan 2

Building a Simple Node.js + Express Backend Using PostgreSQL and an ORM (Beginner Guide)

#programming #productivity #ai #webdev

If you’re new to backend development with Node.js, this guide will help you build a simple but real backend using:

Node.js
Express.js
PostgreSQL
Sequelize ORM

By the end of this article, you’ll understand:

What an ORM is
How Express works
How Node.js talks to PostgreSQL without writing raw SQL
How to build a simple Users API

What We Are Building

We’ll build a simple Users API with three endpoints:

Create a user
Get all users
Get a user by ID

This is the foundation of most real-world backends.

What Is an ORM? (In Simple Terms)

An ORM (Object Relational Mapper) allows you to work with the database using JavaScript objects instead of SQL queries.

Instead of writing this:

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

You write this:

User.create({ name: "John", email: "john@example.com" });

The ORM (Sequelize) translates your JavaScript into SQL behind the scenes.

Step 1: Install Node.js

Check if Node.js is installed:

node -v
npm -v

If not, install it from nodejs.org.

Step 2: Create the Project

mkdir simple-express-postgres
cd simple-express-postgres
npm init -y

This creates a basic Node.js project.

Step 3: Install Dependencies

npm install express sequelize pg pg-hstore
npm install nodemon --save-dev

What each package does:

express → web framework
sequelize → ORM
pg → PostgreSQL driver
pg-hstore → required by Sequelize
nodemon → auto-restart server during development

Step 4: Project Folder Structure

A clean beginner-friendly structure:

simple-express-postgres/
│
├── server.js
├── config/
│   └── database.js
│
├── models/
│   └── User.js
│
├── controllers/
│   └── user.controller.js
│
├── routes/
│   └── user.routes.js

Each folder has a single responsibility:

models → database structure
controllers → logic
routes → URLs
config → database setup

Step 5: Setup PostgreSQL Database

Create a database:

CREATE DATABASE simple_express_orm;

Make sure PostgreSQL is running on port 5432.

Step 6: Connect Node.js to PostgreSQL (Sequelize)

`config/database.js`

const { Sequelize } = require("sequelize");

const sequelize = new Sequelize(
  "simple_express_orm",
  "postgres",
  "YOUR_PASSWORD",
  {
    host: "localhost",
    dialect: "postgres",
    port: 5432,
    logging: false
  }
);

module.exports = sequelize;

This file tells Sequelize:

Which database to connect to
Which database engine to use (Postgres)

Step 7: Create Your First Model (User)

`models/User.js`

const { DataTypes } = require("sequelize");
const sequelize = require("../config/database");

const User = sequelize.define("User", {
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true,
    validate: {
      isEmail: true
    }
  }
});

module.exports = User;

This model represents a users table in PostgreSQL.

You didn’t write SQL, but Sequelize will create:

CREATE TABLE "Users" (...)

Step 8: Controller (Business Logic)

`controllers/user.controller.js`

const User = require("../models/User");

// Create a user
exports.createUser = async (req, res) => {
  try {
    const user = await User.create(req.body);
    res.status(201).json(user);
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
};

// Get all users
exports.getUsers = async (req, res) => {
  const users = await User.findAll();
  res.json(users);
};

// Get user by ID
exports.getUserById = async (req, res) => {
  const user = await User.findByPk(req.params.id);

  if (!user) {
    return res.status(404).json({ message: "User not found" });
  }

  res.json(user);
};

Controllers contain logic, not URLs.

Step 9: Routes (API Endpoints)

`routes/user.routes.js`

const express = require("express");
const router = express.Router();
const controller = require("../controllers/user.controller");

router.post("/", controller.createUser);
router.get("/", controller.getUsers);
router.get("/:id", controller.getUserById);

module.exports = router;

Routes map URLs to controller functions.

Step 10: Setup Express Server

`server.js`

const express = require("express");
const app = express();
const sequelize = require("./config/database");

app.use(express.json());

// Routes
const userRoutes = require("./routes/user.routes");
app.use("/api/users", userRoutes);

// Sync database
sequelize.sync({ alter: true })
  .then(() => console.log("Database connected"))
  .catch(err => console.error(err));

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

This file:

Starts Express
Connects to PostgreSQL
Syncs models
Starts the server

Step 11: Run the Project

Update package.json:

"scripts": {
  "start": "node server.js",
  "dev": "nodemon server.js"
}

Run:

npm run dev

Step 12: Test the API

Create User

POST /api/users

{
  "name": "John Doe",
  "email": "john@example.com"
}

Get All Users

GET /api/users

Get User by ID

GET /api/users/1

What You Have Learned

How Express handles requests
How Sequelize maps JavaScript to PostgreSQL
How ORMs replace raw SQL
Proper backend folder structure
Basic CRUD operations

Recommended Next Steps

Use environment variables (dotenv)
Add update & delete endpoints
Add authentication (JWT)
Learn Sequelize migrations
Add relationships (User → Posts)

Final Thought

This simple project is already production-relevant.
Every large Node.js backend starts exactly like this — just with more features added over time.

DEV Community