Performing CRUD Operations using Prisma, MySQL, and Express
In the world of web development, creating a robust and efficient backend for your application is crucial. Fortunately, modern tools and frameworks make this task easier than ever. In this article, we'll dive into building a RESTful Express.js application using Prisma as our database ORM, with MySQL as the database of choice. We'll focus on practical code examples to help you understand the concepts and steps involved in creating a full-fledged CRUD (Create, Read, Update, Delete) API.
What Prisma Brings to the Table?
Before we jump into code, let's briefly discuss why Prisma is a great choice for working with databases in your Node.js applications. Prisma is an Object-Relational Mapping (ORM) tool that simplifies database interactions by allowing you to work with databases using a type-safe query language called Prisma Query Language (PQL). Here's what Prisma brings to the table:
Type Safety: Prisma generates TypeScript types for your database schema, ensuring that your queries are type-safe at compile time. This eliminates common runtime errors associated with incorrect SQL queries.
Productivity: Prisma provides a powerful API for database operations, making it easier to perform CRUD operations and complex queries without writing raw SQL.
Database Agnostic: Prisma supports multiple databases, including MySQL, PostgreSQL, SQLite, and SQL Server, allowing you to switch databases with minimal code changes.
Schema Migrations: Prisma's migration system simplifies the process of evolving your database schema over time, ensuring that your application's database stays in sync with your codebase.
Now that we've highlighted the benefits of using Prisma, let's dive into building our Express.js application.
Prerequisites
Before diving into building our Express.js application with Prisma and MySQL for CRUD operations, there are some prerequisites you should have in place:
Node.js and npm (Node Package Manager): Make sure you have Node.js installed on your machine. You can download it from nodejs.org. npm, which comes bundled with Node.js, is required for managing project dependencies.
MySQL Server: Ensure that MySQL is properly installed on your system. If you haven't installed it, you can download and install the MySQL Community Server from the official website. During installation, set a strong root password.
MySQL Database and User: Create a MySQL database and user for your application. Follow the steps outlined in the article to create a database and user with appropriate privileges.
Prisma CLI: Install the Prisma CLI globally on your machine by running the following command:
npm install -g prisma
With these prerequisites in place, you'll be ready to proceed with building your Express.js application using Prisma and MySQL for CRUD operations.
Setting Up the Project
To get started, make sure you have Node.js and npm (or yarn) installed on your machine. Create a new directory for your project and initialize it:
mkdir express-prisma-crud
cd express-prisma-crud
npm init -y
Next, install the necessary dependencies: Express, Prisma, and a MySQL driver:
npm install express prisma @prisma/client mysql2 nodemon
Creating the Database Schema
For this example, let's create a simple database schema for managing a list of books. Create a schema.prisma file in your project directory and define the schema:
//schema.prisma
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model book_stores {
id Int @id @default(autoincrement())
name String @db.VarChar(255) @unique
address String?
link String?
subscribers Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}After defining the schema, you can use Prisma's migration tool to apply the schema changes to your database. Run the following commands to create and apply migrations:
npx prisma migrate dev --name book_stores_table_initBuilding the Express Application
Now, let's create an Express.js application that allows us to perform CRUD operations on the "book_stores" table. We'll divide this section into different parts to cover each operation.
Part 1: Setting Up Express and Prisma
Create an app.js file in your project directory. This file will serve as our Express application:
const express = require('express');
const { PrismaClient } = require('@prisma/client');
const app = express();
const prisma = new PrismaClient();
app.use(express.json());
// ... (Next, we'll implement the CRUD operations)In the code above, we imported Express, Prisma, and created an Express application. We also set up Prisma by creating an instance of PrismaClient.
Part 2: Creating a Bookstore (Create Operation)
Let's implement the Create operation to add a new bookstore to our database:
// Create a new book store
app.post('/bookstores', async (req, res) => {
try {
const { name, address, link, subscribers } = req.body;
const bookstore = await prisma.book_stores.create({
data: {
name,
address,
link,
subscribers,
},
});
res.json(bookstore);
} catch (error) {
console.error(error);
res.status(500).json({ error: 'Error creating book store' });
}
});In the code above, we define a route that listens to POST requests on /bookstores. It expects JSON data in the request body with fields like name, address, link, and subscribers. We use Prisma's create method to insert a new bookstore into the database.
Part 3: Retrieving Bookstores (Read Operations)
Now, let's implement the Read operations to retrieve bookstores:
// Get all book stores
app.get('/bookstores', async (req, res) => {
try {
const bookstores = await prisma.book_stores.findMany();
res.json(bookstores);
} catch (error) {
console.error(error);
res.status(500).json({ error: 'Error fetching book stores' });
}
});
// Get a single book store by ID
app.get('/bookstores/:id', async (req, res) => {
const { id } = req.params;
try {
const bookstore = await prisma.book_stores.findUnique({
where: { id: parseInt(id) },
});
if (!bookstore) {
return res.status(404).json({ error: 'Book store not found' });
}
res.json(bookstore);
} catch (error) {
console.error(error);
res.status(500).json({ error: 'Error fetching book store' });
}
});In the code above, we define two routes: one to get all bookstores and another to get a specific bookstore by its ID. We use Prisma's findMany and findUnique methods for querying the database.
Part 4: Updating and Deleting Bookstores (Update and Delete Operations)
Next, we'll implement the Update and Delete operations:
// Update a book store by ID
app.put('/bookstores/:id', async (req, res) => {
const { id } = req.params;
const { name, address, link, subscribers } = req.body;
try {
const updatedBookstore = await prisma.book_stores.update({
where: { id: parseInt(id) },
data: {
name,
address,
link,
subscribers,
},
});
res.json(updatedBookstore);
} catch (error) {
console.error(error);
res.status(500).json({ error: 'Error updating book store' });
}
});
// Delete a book store by ID
app.delete('/bookstores/:id', async (req, res) => {
const { id } = req.params;
try {
await prisma.book_stores.delete({
where: { id: parseInt(id) },
});
res.json({ message: 'Book store deleted successfully' });
} catch (error) {
console.error(error);
res.status(500).json({ error: 'Error deleting book store' });
}
});
In the code above, we create two routes: one to update a bookstore by its ID and another to delete a bookstore by its ID. We use Prisma's update and delete methods for these operations.
Part 5: Running the Express App
Finally, start your Express.js application:
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
console.log(`Server is running on port ${PORT}`);
});By doing a little tweak in the script, we can use the command
npm start to run our server.
You just have to change the script to “start” : “nodemon app.js”
Testing the CRUD operations using POSTMAN
Now that we've implemented CRUD operations in our Express.js application using Prisma and MySQL, it's time to test them using Postman. Below are step-by-step instructions on how to test each operation along with screenshots of the Postman requests.
1. Creating a New Bookstore (POST)
To create a new bookstore, we'll send a POST request to the /bookstores endpoint. In Postman:
Set the request type to POST.
Enter the request URL as http://localhost:3000/bookstores (or your app's URL if it's running on a different port or domain).
In the request body, select raw and set the body format to JSON (application/json).
Provide JSON data for creating a book store, as shown in the screenshot below:
2. Retrieving All Bookstores (GET)
To retrieve a list of all bookstores, we'll send a GET request to the /bookstores endpoint. In Postman:
Set the request type to GET.
Enter the request URL as http://localhost:3000/bookstores (or your app's URL).
Click the "Send" button to retrieve a list of all book stores. The response will include all bookstores in your database.
The response will be as shown in the screenshot:
3. Retrieving a Specific Bookstore by ID (GET)
To retrieve a specific bookstore by its ID, we'll send a GET request to the /bookstores/{id} endpoint, replacing {id} with the actual ID of the bookstore we want to retrieve. In Postman:
Set the request type to GET.
Enter the request URL as http://localhost:3000/bookstores/{id} where {id} is the ID of the bookstore you want to fetch.
Click the "Send" button to retrieve the specific bookstore. The response will include the details of the requested bookstore.
The response will be as shown in the screenshot:
4. Updating a Bookstore (PUT)
To update a bookstore, we'll send a PUT request to the /bookstores/{id} endpoint, replacing {id} with the ID of the bookstore we want to update. In Postman:
Set the request type to PUT.
Enter the request URL as http://localhost:3000/bookstores/{id} where {id} is the ID of the bookstore you want to update.
In the request body, select raw and set the body format to JSON (application/json).
Provide JSON data with the fields you want to update, as shown below:
5. Deleting a Bookstore by ID (DELETE)
To delete a bookstore, we'll send a DELETE request to the /bookstores/{id} endpoint, replacing {id} with the ID of the bookstore we want to delete. In Postman:
Set the request type to DELETE.
Enter the request URL as http://localhost:3000/bookstores/{id} where {id} is the ID of the bookstore you want to delete.
Click the "Send" button to delete the bookstore. You should receive a response confirming the successful deletion.
By following these steps and using the provided screenshots as a reference, you can easily test the CRUD operations of your Express.js application using Postman. Ensure that your application is running while performing these tests to interact with the live database.
Conclusion
In this article, we explored how to perform CRUD operations using Prisma, MySQL, and Express. We started by introducing Prisma and its advantages, such as type safety, automatic query generation, database migrations, and performance optimizations. We then walked through the process of setting up an Express.js application, implementing CRUD operations, and testing them using Postman.
I hope this article will give you a better understanding of prisma,mysql databases. Happy Coding!