Teaching CRUD with ExpressJS, Prisma, MySQL and React: Beginner’s Guide to Building a To-Do List App

CRUD stands for Create, Read, Update, and Delete, which are the four basic operations that can be performed on data. In this tutorial, we will be building a To-Do List app using ExpressJS, Prisma, MySQL, and React. This tutorial is aimed at beginners who are looking to learn how to build a full-stack web application.

Before we begin, make sure you have Node.js and npm installed on your machine. You will also need to have MySQL installed and running. If you haven’t already, you can download and install MySQL from the official website.

Step 1: Setting up the backend with ExpressJS and Prisma

1. Create a new directory for your project and navigate to it in your terminal.

2. Initialize a new Node.js project by running the following command:

npm init -y

1. Install Express, Prisma, and other necessary packages by running the following command:

npm install express prisma mysql2

1. Create a new file named server.js and set up a basic Express server. Here is an example of how it could look:

const express = require('express');
const app = express();
const port = 5000;

app.get('/', (req, res) => {
  res.send('Hello World!');
});

app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

1. Initialize Prisma in your project by running the following command:

npx prisma init

This will create a new directory named prisma with the necessary files to set up your database schema and connect to your MySQL database.

1. Configure your Prisma schema in the schema.prisma file located in the prisma directory. Here is an example of how it could look for a To-Do list app:

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Task {
  id        Int      @id @default(autoincrement())
  title     String
  completed Boolean
}

1. Run the following command to generate the Prisma client for your project:

npx prisma generate

1. Set up your MySQL database connection in a .env file. Add the following line to the file:

DATABASE_URL=mysql://username:password@localhost:3306/db_name

Replace username, password, and db_name with your MySQL credentials and database name.

1. Update the Express server code to connect to your MySQL database using the Prisma client. Here is an example of how it could look:

const express = require('express');
const { PrismaClient } = require('@prisma/client');

const prisma = new PrismaClient();
const app = express();
const port = 5000;

app.get('/', async (req, res) => {
  const tasks = await prisma.task.findMany();
  res.json(tasks);
});

app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});

1. Run the Express server using the following command:

node server.js

You should now be able to access your server at http://localhost:5000 and see an empty array as the response.

Step 2: Setting up the frontend with React

1. Create a new directory named client in your project and navigate to it in your terminal.

2. Initialize a new React project by running the following command:

npx create-react-app .

1. Install Axios for making HTTP requests to your backend server by running the following command:

npm install axios

1. Create a new component named TaskList in the src directory of your React project. Here is an example of how it could look:

import React, { useState, useEffect } from 'react';
import axios from 'axios';

const TaskList = () => {
  const [tasks, setTasks] = useState([]);

  useEffect(() => {
    axios.get('http://localhost:5000').then((response) => {
      setTasks(response.data);
    });
  }, []);

  return (
    <div>
      {tasks.map((task) => (
        <div key={task.id}>
          <h3>{task.title}</h3>
          <p>Completed: {task.completed ? 'Yes' : 'No'}</p>
        </div>
      ))}
    </div>
  );
};

export default TaskList;

1. Import and render the TaskList component in the App component. Here is an example of how it could look:

import React from 'react';
import TaskList from './TaskList';

function App() {
  return (
    <div>
      <h1>To-Do List</h1>
      <TaskList />
    </div>
  );
}

export default App;

1. Update the App component to make it the root component of your React app. Here is an example of how it could look:

import React from 'react';
import ReactDOM from 'react-dom';
import App from './App';

ReactDOM.render(
  <React.StrictMode>
    <App />
  </React.StrictMode>,
  document.getElementById('root')
);

1. Start the React development server using the following command:

npm start

You should now see your To-Do list app running on http://localhost:3000 with the tasks being fetched from your Express backend server.

Step 3: Implementing CRUD operations in your To-Do list app

Now that you have set up the basic structure of your To-Do list app, it’s time to implement the CRUD operations to create, read, update, and delete tasks.

1. Create a new route in your Express server to handle the creation of tasks. Here is an example of how it could look:

app.post('/addTask', async (req, res) => {
  const { title, completed } = req.body;
  const newTask = await prisma.task.create({
    data: {
      title,
      completed,
    },
  });
  res.json(newTask);
});

1. Create a new route in your Express server to handle the updating of tasks. Here is an example of how it could look:

app.put('/updateTask/:id', async (req, res) => {
  const { id } = req.params;
  const { title, completed } = req.body;
  const updatedTask = await prisma.task.update({
    where: { id: parseInt(id) },
    data: {
      title,
      completed,
    },
  });
  res.json(updatedTask);
});

1. Create a new route in your Express server to handle the deletion of tasks. Here is an example of how it could look:

app.delete('/deleteTask/:id', async (req, res) => {
  const { id } = req.params;
  const deletedTask = await prisma.task.delete({
    where: { id: parseInt(id) },
  });
  res.json(deletedTask);
});

1. Update the TaskList component in your React app to add the functionality to create, update, and delete tasks. Here is an example of how it could look:

import React, { useState, useEffect } from 'react';
import axios from 'axios';

const TaskList = () => {
  const [tasks, setTasks] = useState([]);
  const [title, setTitle] = useState('');
  const [completed, setCompleted] = useState(false);

  const fetchTasks = () => {
    axios.get('http://localhost:5000').then((response) => {
      setTasks(response.data);
    });
  };

  useEffect(() => {
    fetchTasks();
  }, []);

  const addTask = () => {
    axios.post('http://localhost:5000/addTask', { title, completed }).then(() => {
      fetchTasks();
      setTitle('');
      setCompleted(false);
    });
  };

  const deleteTask = (id) => {
    axios.delete(`http://localhost:5000/deleteTask/${id}`).then(() => {
      fetchTasks();
    });
  };

  return (
    <div>
      <input
        type="text"
        value={title}
        onChange={(e) => setTitle(e.target.value)}
        placeholder="Enter task title"
      />
      <button onClick={addTask}>Add Task</button>
      {tasks.map((task) => (
        <div key={task.id}>
          <h3>{task.title}</h3>
          <p>Completed: {task.completed ? 'Yes' : 'No'}</p>
          <button onClick={() => deleteTask(task.id)}>Delete</button>
        </div>
      ))}
    </div>
  );
};

export default TaskList;

Congratulations! You have now successfully built a To-Do list app with CRUD functionality using ExpressJS, Prisma, MySQL, and React. Feel free to further customize and expand upon this app to suit your needs or explore other features you can add. Happy coding!