Migrating Your Shopify App from SQLite to Google Cloud SQL

While SQLite is convenient for local development, using a more robust database like Google Cloud SQL can enhance performance and scalability for your production Shopify app. This guide will walk you through the process of switching your app from SQLite to Cloud SQL.

Check out this article if you need to deploy Shopify to Cloud Run.

Prerequisites:

• A Google Cloud Project with billing enabled.
• A Shopify App created and configured.
• Basic understanding of SQL and Prisma (or your preferred ORM).

Steps:

1. Set up Cloud SQL:

• In the Google Cloud Console, create a Cloud SQL instance:

• Select MySQL / PostgreSQL as the database engine (compatible with Prisma). In this example, I will choose PostgreSQL as the main database.

• Create a database user and grant appropriate permissions.

If you want to test or work on the database locally, you should add your public IP address (whatismyipaddress) to the authorized networks section.

2. Update Prisma configuration:

• Update your Prisma schema file (schema.prisma) to reflect the new database details:

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

• Replace env("DATABASE_URL") with a dynamic environment variable that will hold the Cloud SQL connection string.

3. Configure environment variables:

• Create a new environment variable named DATABASE_URL in your preferred environment management tool (e.g., .env file).

For the local machine:

# Remember to replace USERNAME, PASSWORD, DATABASE_NAME, PUBLIC_IP with your own values
DATABASE_URL=postgresql://USERNAME:PASSWORD@localhost:5432/DATABASE_NAME?host=PUBLIC_IP

For Cloud Run:

# Remember to replace USERNAME, PASSWORD, DATABASE_NAME, PROJECT_ID, REGION & INSTANCE with your own values

DATABASE_URL:postgresql://USERNAME:PASSWORD@localhost:5432/shopify?host=/cloudsql/PROJECT_ID:REGION:INSTANCE
INSTANCE_UNIX_SOCKET:/cloudsql/PROJECT_ID:REGION:INSTANCE
INSTANCE_CONNECTION_NAME:PROJECT_ID:REGION:INSTANCE

After setting up locally, you can try to run the following command to migrate the schema

npx prisma migrate dev

4. Deploy your app:

• Deploy your app to a hosting platform that supports environment variables.
• Ensure the environment variables containing the Cloud SQL connection string are accessible to your app during deployment.
• Make sure that connect the Cloud SQL instance to Cloud Run and add the Cloud SQL client role to the Cloud Run service account
• Remember to enable Cloud SQL Admin API

5. Update data migration (if applicable):

• If you have existing data in your local SQLite database, you’ll need to migrate it to Cloud SQL. Explore tools like Prisma Migrate or custom scripts to achieve this.

Additional Considerations:

• Security: Implement proper authentication and authorization mechanisms to secure your Cloud SQL instance.
• Monitoring: Set up monitoring for your Cloud SQL instance to track performance and identify issues.
• Backup and restore: Regularly back up your database to prevent data loss.

Benefits of using Cloud SQL:

• Scalability: Cloud SQL scales seamlessly to handle increased traffic and data growth.
• Durability: Google manages backups and ensures high availability of your data.
• Security: Cloud SQL offers built-in security features to protect your database.

Conclusion:

By migrating your Shopify app from SQLite to Cloud SQL, you gain the benefits of a robust, scalable, and secure database solution, paving the way for a more performant and reliable production environment.