Node.js MySQL with Prisma - Schema, Migrations and Connections

To connect Node.js to MySQL with Prisma, you install Prisma, set a mysql:// connection string in DATABASE_URL, describe your tables in schema.prisma, run prisma migrate dev to create them, and query through a generated, fully typed client. In production you run prisma migrate deploy instead. This guide walks through each step with Prisma ORM 7 and Node.js 22 or 24, including the connection string format, the MySQL driver adapter and the mistakes that trip people up on real hosting.

Before you start

You need:

• Node.js 22 or 24 LTS
• A MySQL database - locally (for example in Docker) for development, and a managed database for production. See managed MySQL in South Africa.
• A new or existing Node.js project with TypeScript

Prisma 7 changed several things compared to earlier versions: the connection URL moves to prisma.config.ts, the client is generated into your source tree, and a driver adapter is used to talk to the database. If you are on Prisma 6, the concepts are identical but the setup differs - check the Prisma documentation for your version.

Step 1 - Install

npm install prisma @prisma/client @prisma/adapter-mariadb dotenv
npm install tsx typescript @types/node --save-dev
npx prisma init --datasource-provider mysql --output ../src/generated/prisma

The prisma CLI goes in regular dependencies because production runs prisma migrate deploy. The MariaDB adapter uses the mariadb driver, which also works with MySQL servers. prisma init creates a prisma/ folder with schema.prisma, a prisma.config.ts file and a .env file.

Prisma 7 is published as an ES module, so set "type": "module" in package.json and use a Node-style tsconfig.json:

{
  "compilerOptions": {
    "target": "ES2023",
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "rootDir": "src",
    "outDir": "dist"
  },
  "include": ["src"]
}

Step 2 - The connection string

Prisma's MySQL connection string looks like this:

# .env - never commit this file
DATABASE_URL="mysql://USER:PASSWORD@HOST:3306/DATABASE"

For example mysql://shop_app:Str0ngPass@localhost:3306/shop. Two things catch people out:

• Special characters in passwords must be URL-encoded: @ becomes %40, # becomes %23, / becomes %2F.
• The database must exist (or the user must have permission to create it). On managed hosting, create the database in the dashboard first.

Add .env to .gitignore. In production, set DATABASE_URL in your host's environment variables instead.

Step 3 - Configure Prisma

Make sure prisma.config.ts loads your environment and points at the URL:

import 'dotenv/config';
import { defineConfig, env } from 'prisma/config';

export default defineConfig({
  schema: 'prisma/schema.prisma',
  migrations: { path: 'prisma/migrations' },
  datasource: { url: env('DATABASE_URL') },
});

Step 4 - Define your schema

Edit prisma/schema.prisma:

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "mysql"
}

model Customer {
  id        Int      @id @default(autoincrement())
  email     String   @unique @db.VarChar(254)
  name      String   @db.VarChar(120)
  createdAt DateTime @default(now())
  orders    Order[]
}

model Order {
  id          Int      @id @default(autoincrement())
  totalCents  Int
  status      String   @default("pending") @db.VarChar(20)
  customerId  Int
  customer    Customer @relation(fields: [customerId], references: [id])
  createdAt   DateTime @default(now())

  @@index([customerId])
}

Store money as integer cents (R149.99 as 14999) to avoid floating-point rounding errors. On MySQL, set explicit @db.VarChar lengths for indexed strings; the default VARCHAR(191) is chosen to fit index limits on older configurations.

Add src/generated/ to .gitignore - it is regenerated on every build.

Step 5 - Create and apply migrations

In development:

npx prisma migrate dev --name init
npx prisma generate

migrate dev compares your schema to the database, writes a SQL migration to prisma/migrations/, and applies it. It also uses a temporary shadow database to detect drift, which requires permission to create databases. That is why you run migrate dev against a local database, not a managed production one.

Commit the prisma/migrations folder. Those SQL files are the history of your schema.

Step 6 - Query from Node.js

Create one Prisma client for the whole app, and build the adapter from DATABASE_URL:

// src/db.ts
import 'dotenv/config';
import { PrismaMariaDb } from '@prisma/adapter-mariadb';
import { PrismaClient } from './generated/prisma/client.js';

const url = new URL(process.env.DATABASE_URL!);

const adapter = new PrismaMariaDb({
  host: url.hostname,
  port: Number(url.port || 3306),
  user: decodeURIComponent(url.username),
  password: decodeURIComponent(url.password),
  database: url.pathname.slice(1),
  connectionLimit: 5,
});

export const prisma = new PrismaClient({ adapter });

// src/index.ts
import { prisma } from './db.js';

const customer = await prisma.customer.upsert({
  where: { email: '[email protected]' },
  update: {},
  create: { email: '[email protected]', name: 'Thabo' },
});

await prisma.order.create({
  data: { totalCents: 14999, customerId: customer.id },
});

const withOrders = await prisma.customer.findUnique({
  where: { id: customer.id },
  include: { orders: true },
});

console.log(withOrders);
await prisma.$disconnect();

Run it with npx tsx src/index.ts. The imports use .js extensions because that is what Node.js resolves after TypeScript compiles them; tsx maps them back to the .ts files during development. Prisma parameterises every query, so values like email are never concatenated into SQL. If you drop down to raw SQL, use the tagged template prisma.$queryRaw rather than $queryRawUnsafe.

Step 7 - Deploy to production

In production you never run migrate dev. Instead, apply the committed migrations:

{
  "scripts": {
    "build": "prisma generate && tsc",
    "migrate": "prisma migrate deploy",
    "start": "prisma migrate deploy && node dist/index.js"
  }
}

migrate deploy only applies pending migration files, needs no shadow database and never resets data. Running it in the start command works well for a single instance. If you run several instances, run migrations once as a separate step so they don't race each other.

With Git push to deploy, the flow becomes: change the schema, run migrate dev locally, commit the migration, push - and production applies it on deploy.

Common errors

Frequently asked questions

Can I use Prisma with an existing MySQL database?

Yes. Run npx prisma db pull to introspect the existing tables into schema.prisma, then generate the client. To start managing changes with migrations, create a baseline migration as described in the Prisma docs.

Should I use Prisma or mysql2 directly?

Prisma gives you type safety, migrations and a readable query API, which suits most apps. The plain mysql2 driver has less overhead and full SQL control, which some teams prefer for very query-heavy or reporting code. You can use both in one project.

Does Prisma work with MariaDB?

Yes. Prisma supports MariaDB with the mysql provider, and the MariaDB adapter used in this guide is built on the MariaDB driver.

Where should I run migrations for preview deployments?

Point previews at a separate preview database, never production. See preview deployments explained for strategies.

Need a MySQL database in Johannesburg next to your Node.js app? See managed databases or Node.js hosting.