Prisma PHP Logo

Prisma MySQL Setup

A comprehensive guide to integrating MySQL with Prisma PHP. Learn why the Shadow Database is critical for data integrity and how to configure it properly.

Setup Workflow

  1. 1 Define schema.prisma provider.
  2. 2 Configure Shadow Database URL.
  3. 3 Update prisma.config.ts.
  4. 4 Run initial migration.

Why Shadow Database?

MySQL does not support DDL transactions (rollback on error). If a migration fails halfway, your DB is corrupted.

The Solution: Prisma creates a temporary "Shadow DB" to test migrations before touching your production data. It allows Prisma to detect Schema Drift safely.

1. Schema Definition

Set your provider to mysql in your schema file.

prisma/schema.prisma 
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
}

2. Environment Variables

Define your main connection and your shadow database connection.

.env 
# Main Database (Production/Dev)
DATABASE_URL="mysql://user:pass@localhost:3306/my_app"

# Shadow Database (Temporary for Migrations)
SHADOW_DATABASE_URL="mysql://user:pass@localhost:3306/my_app_shadow"

Ensure the user has CREATE DATABASE privileges to create the shadow DB automatically.

3. Prisma Configuration

Wire everything together in your type-safe configuration file. This is where you explicitly tell Prisma to use the Shadow DB.

prisma.config.ts 
import { defineConfig, env } from "prisma/config";
import "dotenv/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  
  datasource: {
    url: env("DATABASE_URL"),
    
    // Enable Shadow Database for Schema Drift detection
    shadowDatabaseUrl: env("SHADOW_DATABASE_URL"),
  },
});

Scenario A: Remote Database (cPanel)

Connecting to a live server from local.

  1. Log in to cPanel and find Remote MySQL.
  2. Add your current IP address to the access list.
  3. Update HOST in your .env to your domain/IP.

Scenario B: Local Production

Using localhost for production securely.

  1. Set HOST to localhost.
  2. Ensure strict firewall rules on your server.
  3. This is more secure as the DB is not exposed to the public web.
Environment Config

While separating production and shadow databases is best practice, in a local XAMPP environment, you can technically use the same database for both if resources are limited. Just ensure your .env URLs reflect this.

Ready to Migrate?

You have successfully integrated MySQL with Prisma PHP. The next step is to apply these changes to your database structure.

Go to Prisma Command