How to Delete MySQL Database Data using Shopify Remix App?

Hello Shopify Friends,

In this tutorial, we’ll explore how to implement data deletion functionality in a Shopify Remix app that interacts with a MySQL database. Specifically, we’ll focus on deleting user data from the database using React components, Remix route handlers, and Prisma ORM.

Shopify Remix simplifies MySQL database management within the Shopify environment. Check out some of the MySQL Database Data operations you can perform with the Shopify Remix App.

• How to Integrate MySQL Database to a Shopify Remix App?
• How to Add Data into MySQL Database in Shopify Remix App?
• How to Display MySQL Database Data into your Shopify Remix App?

Deleting database data is a common task in e-commerce, whether it’s removing outdated product listings, purging customer information, or clearing test data. With Shopify Remix, this process becomes straightforward.

Here’s a step-by-step guide to deleting MySQL database data using the Shopify Remix app

Steps to Delete MySQL Database Data using Shopify Remix App:

Step 1: Setting up Your Data Model

The first step is to define your data model. For our example, we’ll use a ‘User’ table with columns for ‘id’ and ‘name’. Ensure your MySQL database is set up and contains the necessary data. Here’s an example model definition:

For example you have User table :

model User {
  id Int @id @default(autoincrement())
  name String
}

This model defines a ‘User’ table with columns for ‘id’ and ‘name’. The @id and @default(autoincrement()) annotations signify that ‘id’ is the primary key and automatically increments with each new entry.

Step 2: Fetching Data From Database

To fetch data from our MySQL database, we’ll use the loader function in our route file. This function queries the database using a library like Prisma and returns the user data. This data is then made available in the component through  UseLoaderData. Here’s an example of the loader function:

// Example loader function in route file

import { useLoaderData } from "@remix-run/react";
import { json } from "@remix-run/node";
import db from "../db.server";

export async function loader() {
    const users = await db.user.findMany();
    return json({ users });
}

Step 3: Implementing Data Deletion

In your React component, display the fetched user data and provide a mechanism for users to delete their data. This involves sending a request to a designated route handler that handles the deletion operation. Here’s an example of how you can implement this functionality:

import { useLoaderData } from "@remix-run/react";
import { Card, Page, List } from "@shopify/polaris";
import db from "../db.server";

export async function loader() {
    const users = await db.user.findMany();
    return {
        data: users
    };
}

export default function Index() {
    const { data: users } = useLoaderData();
   
    const handleDelete = async (id) => {
        const formData = new FormData();
        formData.set("id", JSON.stringify(id));

        const response = await fetch(`/app/deleteItem/${id}`, {
            method: "POST",
            body: formData,
        });
    };
    
    return (
        <Page>
            <Card>
                <List type="bullet">
                    {users.map((user) => (
                        <List.Item key={user.id}>
                            {user.name}
                            <button onClick={() => handleDelete(user.id)}>Delete</button>
                        </List.Item>
                    ))}
                </List>
            </Card>
        </Page>
    );
}

Step 4: Handling Deletion in Route Handler

// deleteItem route handler

import { redirect } from "@remix-run/node";
import db from "../db.server";

export async function action({ request }) {
      const res = await request.formData();
      const id = res.get("id");
      const data = await db.user.delete({ where: { id: JSON.parse(id) } });
      return redirect("/app/index");
}

Conclusion:

By following these steps, you can seamlessly integrate data deletion functionality into your Shopify Remix app, allowing users to manage their data efficiently and securely. This enhances the user experience and ensures compliance with data privacy regulations.

Happy Coding!