# 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](https://shubhamsinghbundela.hashnode.dev/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**

```javascript
npm init -y
```

It Creates `package.json`

* * *

### Step 2: Installing Dependencies

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

```javascript
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`

```javascript
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`

```javascript
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**

```javascript
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**

```javascript
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 use`user_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:

```javascript
npm install bcrypt
```

Then import it in your project:

```javascript
const bcrypt = require("bcrypt");
```

* * *

**Signup API Implementation**

```javascript
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:

```plaintext
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**

![](https://cdn.hashnode.com/uploads/covers/624226a5db84f8c50fa5b247/91a8a2b2-b62e-4dff-b653-df6e55dbfcb9.png align="center")

* * *

### 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:

```csharp
npm install jsonwebtoken
```

Import it:

```javascript
const jwt = require("jsonwebtoken");
```

* * *

**Signin API Implementation**

```javascript
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**

![](https://cdn.hashnode.com/uploads/covers/624226a5db84f8c50fa5b247/994f9dd4-bee4-4950-8a0b-292b5271c4f2.png align="center")

* * *

### **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**

```javascript
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.

```javascript
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**

![](https://cdn.hashnode.com/uploads/covers/624226a5db84f8c50fa5b247/5f6b1a1a-4492-4ff0-96dd-fab5653b0de1.png align="center")

* * *

### **Step 10: Get All Todos**

```js
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**

![](https://cdn.hashnode.com/uploads/covers/624226a5db84f8c50fa5b247/62444f9d-7437-494b-9fca-150a84a80f8b.png align="center")

* * *

### **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](https://github.com/shubhamsinghbundela/Todo-Application-SQL)