How to Integrate MySQL Database to a Shopify Remix App?

In this tutorial, we will learn about how to integrate Shopify Remix App with MySQL Database.

Certainly! To integrate a Shopify Remix app with a MySQL database, you need to update the Prisma schema reflecting the change in the data source. Before that, learn How to Create Shopify Remix App.

Below is a step-by-step guide to modify the Prisma schema for connecting to a MySQL database:

Steps to Integrate MySQL Database to a Shopify Remix App:

Step 1:  Update Prisma Schema

Open the Schema.prisma file located in the App->Prisma path. Update the data source to use MySQL instead of SQLite.

Before:

// App->Prisma->Schema.prisma
     datasource db {
     provider=”sqlite”
     url=”file:dev.sqlite”
}

After:

// App->Prisma->Schema.prisma
     datasource db {
     provider=”mysql”
     url=env("DATABASE_URL")
}

• The provider is changed to “mysql” to indicate that you’re using MySQL as the database provider.
• The url attribute is set to env(“DATABASE_URL”), which suggests that the database URL should be provided as an environment variable.

Note: If you are migrating from SQLite to MySQL, clean up your migration files first. In the App->Prisma->migration directory, remove all the old SQLite migration files so that there will be no conflicts and so that it will be a clean migration to MySQL.

Step 2: Setup the Environment Variable

Since you are using env(“DATABASE_URL”), you must set up the DATABASE_URL environment variable pointing to your MySQL database.

Option A: If you already have an .env file:

Example .env file:

DATABASE_URL=mysql://your_username:your_password@localhost:3306/your_database

Replace your_username, your_password, and your_database with your MySQL database credentials and information.

Option B: If you don’t have an .env file:

If you don’t have an existing .env file, you can create one. Alternatively, if you are using a tool like Shopify CLI, you can use the following command to pull the environment variables from your Shopify app to generate the .env file:

npm run shopify app env pull

It fetches environment variables from your Shopify app, and either creates or updates the `.env` file in the project directory. After that, you must see the contents of `.env` having all needed environment variables along with DATABASE_URL.  Make sure to review the generated .env file to confirm that the DATABASE_URL is correctly set with your MySQL database credentials  

Step 3: Apply Migrations

Now, go to the terminal and enter the command below to reflect the changes in the database

npx prisma migrate dev

Step 4: Testing

Test the Shopify Remix application with regards to whether it has managed to connect successfully to your MySQL database. Add a few sample data, retrieve data, and perform basic CRUD operations to confirm that the integration is working as expected.

Conclusion

In this article, we learned how to integrate a Shopify Remix app with a MySQL database by updating the Prisma Schema, setting up environment variables, applying migrations, and updating the database connection in the application code. This integration allows your app to interact with the MySQL database seamlessly.

Happy Coding!