📝 PostgreSQL To-Do List

This project is a simple ToDoList application built with Express.js, Node.js, PostgreSQL, and EJS.
It allows users to manage their tasks by adding, editing, and deleting items from their to-do list.

🚀 Getting Started

Prerequisites

Before you begin, ensure you have the following installed:

• PostgreSQL
• pgAdmin, management tool for PostgreSQL (for local development)
• TablePlus, Database Management Tool (for production)
• Render, Cloud hosting service. (for deploying the project)
• Node.js
• npm

Local Development Setup 🛠️

1. Clone the repository:

   git clone https://github.com/your-username/todo-list-project.git
   cd todo-list-project

2. Install dependencies:

   npm install

3. Set up PostgreSQL:

   • Open pgAdmin and create a new database named permalist.

   • Update the database configuration in your project's environment variables file (.env):

     DB_HOST=localhost
     DB_USER=your-username
     DB_PASSWORD=your-password
     DB_DATABASE=permalist
     DB_PORT=5432

4. Run the application:

   npm start

Your server is now running on: http://localhost:3000

---

Production Setup 🛠️

1. Database Setup on Render:

   • Create a new PostgreSQL database on Render.
   • Note down the database URL provided by Render.

2. Connect using TablePlus:

   • Open TablePlus and create a new connection using the Render database URL.

3. Deploy the application on Render:

   • Push your code to a repository (e.g., GitHub).

   • Connect your Render service to the repository.

   • Set the environment variables in Render:

     DB_HOST=your-render-database-host
     DB_USER=your-render-database-username
     DB_PASSWORD=your-render-database-password
     DB_DATABASE=your-render-database-name
     DB_PORT=5432

   • Deploy the service.

🛠️ Technologies Used

• Backend:
  • Node.js
  • Express.js
• Frontend:
  • javascript
  • EJS templating
• Database:
  • PostgreSQL
  • pgAdmin (for local development)
  • TablePlus (for production)
• Deployment:
  • Render, cloud hosting web service, and PostgreSQL DB

📂 Project Structure

├── views
│   ├── footer.ejs
│   ├── header.ejs
│   ├── index.ejs
├── index.js
├── .env
├── queries.sql
├── package.json
├── README.md

---

📋 API Endpoints

These endpoints provide full CRUD (Create, Read, Update, Delete) functionality for managing tasks in the To-Do List application.
These code examples demonstrate how to interact with these endpoints using the fetch API.

• GET /tasks - Retrieve all tasks and render the main page
• POST /tasks - Create a new task
• PUT /tasks/:id - Update a task
• DELETE /tasks/:id - Delete a task

GET / 📋

Retrieve all items from the to-do list and render the index page.

• URL: /
• Method: GET
• Response:
  • 200 OK: Renders the index.ejs page with the list of items.

app.get("/", async (req, res) => {
  try {
    // Query the database to get all items, ordered by their ID in ascending order
    const result = await db.query("SELECT * FROM items ORDER BY id ASC");
    
    // Store the result rows in the items array
    items = result.rows;

    // Render the index.ejs template with the current date and list of items
    res.render("index.ejs", {
      listTitle: currentDate() + " To-Do List",
      listItems: items,
    });
  } catch (err) {
    // Log any errors that occur

```javascript
    console.log(err);
  }
});

POST /add ➕

Add a new item to the to-do list.

• URL: /add
• Method: POST
• Request Body:
  • newItem (string): The title of the new to-do item.
• Response:
  • 302 Found: Redirects to / after adding the item.

app.post("/add", async (req, res) => {
  // Get the new item title from the request body
  const item = req.body.newItem;
  
  try {
    // Insert the new item into the database
    await db.query("INSERT INTO items (title) VALUES ($1)", [item]);
    
    // Redirect to the homepage to show the updated list
    res.redirect("/");
  } catch (err) {
    // Log any errors that occur
    console.log(err);
  }
});

POST /edit ✏️

Edit an existing item in the to-do list.

• URL: /edit
• Method: POST
• Request Body:
  • updatedItemTitle (string): The updated title of the to-do item.
  • updatedItemId (number): The ID of the to-do item to be updated.
• Response:
  • 302 Found: Redirects to / after editing the item.

app.post("/edit", async (req, res) => {
  // Get the updated item title and ID from the request body
  const item = req.body.updatedItemTitle;
  const id = req.body.updatedItemId;

  try {
    // Update the item in the database
    await db.query("UPDATE items SET title = ($1) WHERE id = $2", [item, id]);
    
    // Redirect to the homepage to show the updated list
    res.redirect("/");
  } catch (err) {
    // Log any errors that occur
    console.log(err);
  }
});

POST /delete ❌

Delete an item from the to-do list.

• URL: /delete
• Method: POST
• Request Body:
  • deleteItemId (number): The ID of the to-do item to be deleted.
• Response:
  • 302 Found: Redirects to / after deleting the item.

app.post("/delete", async (req, res) => {
  // Get the ID of the item to be deleted from the request body
  const id = req.body.deleteItemId;
  
  try {
    // Delete the item from the database
    await db.query("DELETE FROM items WHERE id = $1", [id]);
    
    // Redirect to the homepage to show the updated list
    res.redirect("/");
  } catch (err) {
    // Log any errors that occur
    console.log(err);
  }
});

---

Screenshots 🖼️

pgAdmin Tool (for local development):

Render (for deploying the project + hosted PostgreSQL DB) + TablePlus (DB for production):

Tableplus - local and production Workspace

📫 Connect with me 😊

Copyright © Shani Bider, 2024