Building a Todo App with Express + PostgreSQL

What We’re Building

Before diving into the code, let’s understand what we are going to build.

In this project, we’ll create a secure Todo Backend API using Express.js and PostgreSQL (via Neon).

Core Features

• User Signup (with password hashing)

• User Signin (with JWT authentication)

• Secure APIs using Middleware

• Create Todo (user-specific)

• Get Todos (only your own todos)

• Prevent SQL Injection attacks

---

Before We Start…

If you’re completely new to backend development, I’ve already built a similar Todo app using MongoDB:

Building a Todo App using Express.js + MongoDB

That version uses a NoSQL database, while in this blog we’ll use PostgreSQL (SQL).

This will help you understand:

• Difference between SQL vs NoSQL

• When to use which database

---

Step 1: Initialize Project

npm init -y

It Creates package.json

---

Step 2: Installing Dependencies

Since I’m building the backend with Express and PostgreSQL, I installed:

npm install express 
npm install pg

• express → For creating the server and APIs

• pg → PostgreSQL client for Node.js

---

Step 3: Setup Express Server

First, I created a basic Express server:

Create file index.js

const express = require("express");
const app = express();

app.use(express.json());

app.listen(3000, () => {
  console.log("Server is running on port 3000");
});

---

Step 4: Setting Up PostgreSQL (NeonDB)

Steps I followed:

1. Signed up on Neon

2. Created a new project

3. Got the connection string on Dashboard

---

Step 4: Connecting Express to PostgreSQL

Now comes the important part — connecting the database to our backend.

I used Pool from the pg library:

Let's created a separate file: model.js

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: "postgresql://neondb_owner:YOUR_PASSWORD@YOUR_HOST/neondb?sslmode=require&channel_binding=require"
});

module.exports = {
    pool: pool
}

This connection string is provided by Neon after creating the project.

---

Step 5: Designing the Database

For this app, I created two tables using the SQL Editor in Neon:

• users → Stores user information

• todo → Stores tasks created by users

Users Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Todo Table

CREATE TABLE todo (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Understanding the Relationship

This is where things get interesting.

• Each todo belongs to a user i.e. You cannot create a todo without a valid user

• We use user_id as a foreign key i.e. we useuser_id in todo refers to id in users

What does ON DELETE CASCADE mean?

• If a user is deleted → all their todos are automatically deleted from todo table

---

Why SQL is Strictly Typed

One important thing to notice while designing the database is that PostgreSQL (used via Neon) is a strictly typed database.

This means we must define the type of each column in advance.

For example:

• username → VARCHAR(255) (must be text)

• user_id → INTEGER (must be a number)

• created_at → TIMESTAMP (must be a valid date-time)

Because of this:

You cannot insert random data types into the database.

---

Step 6: Signup Route

Before writing code, let’s understand the goal.

What we want to achieve:

• Allow a new user to register

• Store user details in database

• Prevent duplicate users

• Secure the password

---

Why Password Hashing is Important

Instead of storing the actual password, we store a hashed version of it.

What is hashing?

• Converts a password into a random-looking string

• It is a one-way function (cannot be reversed easily)

So even if your database is leaked, attackers cannot see real passwords.

---

Before using hashing, install the library:

npm install bcrypt

Then import it in your project:

const bcrypt = require("bcrypt");

---

Signup API Implementation

app.post("/signup", async (req, res) => {
  const username = req.body.username;
  const password = req.body.password;

  const hashedPassword = await bcrypt.hash(password, 10);

  const userExist = await pool.query(
    "SELECT * FROM users WHERE username = $1",
    [username],
  );

  if (userExist.rows[0]) {
    return res.status(403).json({
      message: "User with this username already exists",
    });
  }

  const response = await pool.query(
    "INSERT INTO users (username, password) VALUES (\(1, \)2) RETURNING id, username, password",
    [username, hashedPassword],
  );

  res.status(200).json({
    userId: response.rows[0].id,
    message: "Signup Done",
  });
});

---

Why We Use $1 Instead of Direct Values in SQL ?

You might be tempted to write a query like this:

const query = `SELECT * FROM users WHERE username = '${username}'`;

This may work—but it is dangerous and result to SQL Injection.

---

What is SQL Injection?

SQL Injection happens when a user sends malicious input that changes your query.

---

Final Result: Testing /signup API Using Postman

---

Step 7: Signin + JWT Authentication

After signup, the next step is allowing users to log in and get a token for authentication.

What we want to achieve

• Verify user credentials (username + password)

• Compare hashed password securely

• Generate a token after successful login

• Send that token to the client

---

Install JWT Library

Before using tokens, install:

npm install jsonwebtoken

Import it:

const jwt = require("jsonwebtoken");

---

Signin API Implementation

app.post("/signin", async (req, res) => {
  const username = req.body.username;
  const password = req.body.password;

  const userExist = await pool.query(
    "SELECT * FROM users WHERE username = $1",
    [username],
  );

  if (!userExist.rows[0]) {
    return res.status(404).json({
      message: "User not found",
    });
  }

  const correctPassword = await bcrypt.compare(
    password,
    userExist.rows[0].password,
  );

  if (correctPassword) {
    const token = jwt.sign(
      {
        userId: userExist.rows[0].id,
      },
      "shubham123",
    );

    return res.status(200).json({
      token,
    });
  } else {
    return res.status(403).json({
      message: "Password is invalid",
    });
  }
});

---

Final Result: Testing /signin API Using Postman

---

Step 8: Authentication Middleware (Protecting Routes)

Now that we have JWT-based login, we need a way to protect routes so only logged-in users can access them.

For that, we create a middleware.

---

Let's created a separate file: middleware.js

Auth Middleware Implementation

const jwt = require("jsonwebtoken");
const { pool } = require("./models.js");

async function authMiddleware(req, res, next) {
  try {
    const token = req.headers.token;

    if (!token) {
      return res.status(401).json({
        message: "Token missing",
      });
    }

    const decode = jwt.verify(token, "shubham123");

    const userExist = await pool.query(
      "SELECT * FROM users WHERE id = $1",
      [decode.userId],
    );

    if (!userExist.rows[0]) {
      return res.status(404).json({
        message: "User not found",
      });
    }

    req.userId = decode.userId;
    next();
  } catch (err) {
    return res.status(403).json({
      message: "Invalid or expired token",
    });
  }
}

module.exports = {
  authMiddleware,
};

---

Step 9: Create Todo

Before writing the code, let’s understand

What are we trying to achieve?

We want:
1. Only logged-in users should be able to create a todo i.e. Each todo should be linked to the specific user who created it

When user tries to create a todo, first authentication happens using authMiddleware.

• It verifies the token

• Extracts userId

• Attaches it to req.userId

Then we use this userId to link the todo with the logged-in user.

So each todo belongs to a specific user, and later we can fetch user-specific todos.

app.post("/todo", authMiddleware, async (req, res) => {
  const title = req.body.title;

  const newTodo = await pool.query(
    "INSERT INTO todo (title,user_id) VALUES (\(1, \)2) RETURNING id", [title, req.userId]);

  res.status(200).json({
    id: newTodo.rows[0].id,
    message: "todo get created",
  });
});

---

Final Result: Testing POST /todo API Using Postman

---

Step 10: Get All Todos

app.get("/todo", authMiddleware, async (req, res) => {
  
  const allTodos = await pool.query("SELECT * FROM todo WHERE user_id = $1", [req.userId]);

  res.status(200).json({
    todos: allTodos.rows,
  });
});

---

Final Result: Testing GET /todo API Using Postman

---

Complete Source Code

I’ve uploaded the complete project on GitHub. You can check it here:

GitHub Repo:
https://github.com/shubhamsinghbundela/Todo-Application-SQL