How to Integrate MySQL Database to a Shopify Remix App?

In this article, 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 to reflect 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: Before proceeding, ensure that you clean up your migration files if you are transitioning from SQLite to MySQL. In the App->Prisma->migration directory, delete all old SQLite migration files to avoid conflicts and ensure a clean migration to MySQL.

Step 2: Environment Variable Setup

Since you’re using env(“DATABASE_URL”), you need to set up the DATABASE_URL environment variable to point 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

This command will fetch the environment variables from your Shopify app and create or update the .env file in your project directory. After running this command, you should see the .env file with the necessary environment variables, including 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

After updating the Prisma schema, you need to apply the migrations to reflect the changes in the database. Run the following command in your terminal:

npx prisma migrate dev

Step 4: Testing

Test your Shopify Remix app to ensure that it successfully connects to the MySQL database. You can add some 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!