Shopping cart

Subtotal 0,00 د.إ Subtotal: 0,00 د.إ

View cartCheckout

Prisma Data Modeling & Migrations Deep Dive

by SanjewaExpress Js
Front
Back
Right
Left
Top
Bottom
DATA MODELING

Prisma Data Modeling & Migrations Deep Dive

Your database schema is the backbone of your application. Get it wrong and you’re refactoring forever. Get it right and everything else flows naturally. In Blog 1: Getting Started with Prisma ORM & MySQL, we initialized Prisma with MySQL. Now let’s go deeper — modeling real data, understanding relationships, and managing schema changes like a pro.
SCHEMA

Prisma Schema Deep Dive

The `schema.prisma` file uses Prisma Schema Language (PSL) — a clean, human-readable DSL. According to the Prisma Schema Reference, every model maps directly to a MySQL database table.

📘 
// prisma/schema.prisma

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

model Post {
  id          Int       @id @default(autoincrement())
  title       String    @db.VarChar(255)
  content     String?   @db.Text
  published   Boolean   @default(false)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  authorId    Int
  author      User      @relation(fields: [authorId], references: [id])

  @@map("posts")
}
MySQL Tip
Use `@db.VarChar(255)` and `@db.Text` to control MySQL column types explicitly. Without them, Prisma uses sensible defaults, but explicit types give you full control.
FEILD
The Fine-Grained Controls

Field Attributes

Attribute Purpose MySQL Example
@id Primary key id Int @id
@default() Default value @default(autoincrement())
@unique Unique constraint email String @unique
@updatedAt Auto-update timestamp updatedAt DateTime @updatedAt
@map() Map to DB column name @map("user_name")
@db.VarChar(n) MySQL VARCHAR type name String @db.VarChar(100)
@db.Text MySQL TEXT type content String? @db.Text
@db.TinyInt(1) MySQL boolean/tinyint active Boolean @db.TinyInt(1)
BLOCK
Table-Level Configuration

Block Attributes

📘 
model User {
  id       Int    @id @default(autoincrement())
  email    String @db.VarChar(191)
  username String @db.VarChar(100)
  role     Role   @default(USER)

  @@unique([email, username])             // Composite unique constraint
  @@index([email])                        // Speed up email queries (MySQL index)
  @@index([role, createdAt(sort: Desc)])  // Composite index with sort
  @@map("users")                          // Maps to "users" table in MySQL
}

enum Role {
  USER
  ADMIN
  MODERATOR
}
MySQL Note
MySQL has a 767-byte index prefix limit. Use `@db.VarChar(191)` for email fields that will be indexed (191 chars × 4 bytes UTF8MB4 = 764 bytes). This is critical in MySQL 5.7 and below; MySQL 8 with `innodb_large_prefix` enabled lifts this limit.
Pro Tip

`@@index` is crucial for performance. Add indexes on any field used in `WHERE` clauses or `ORDER BY`. (prisma.io/docs/concepts/components/prisma-schema/indexes)

RELATIONS
One-to-One, One-to-Many, Many-to-Many

Relations

Relations are where Prisma truly shines compared to raw SQL. The type safety across relations is a game-changer.
One-to-Many (Most Common)
One `User` has many `Post`s:
📘 
model User {
  id    Int    @id @default(autoincrement())
  email String @unique @db.VarChar(191)
  posts Post[]

  @@map("users")
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String @db.VarChar(255)
  authorId Int
  author   User   @relation(fields: [authorId], references: [id],
                            onDelete: Cascade)

  @@index([authorId])   // ← Always index foreign keys in MySQL!
  @@map("posts")
}
MySQL Best Practice
Always add `@@index([foreignKeyField])` on the child model. MySQL does NOT automatically create indexes on foreign key columns in Prisma-managed tables. This is a common performance pitfall.
One-to-One
One `User` has one `Profile`:
📘 
model User {
  id      Int      @id @default(autoincrement())
  profile Profile?

  @@map("users")
}

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String @db.Text
  userId Int    @unique
  user   User   @relation(fields: [userId], references: [id],
                           onDelete: Cascade)

  @@map("profiles")
}
Many-to-Many (Implicit)
Prisma creates the join table automatically:
📘 
model Post {
  id   Int    @id @default(autoincrement())
  tags Tag[]

  @@map("posts")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique @db.VarChar(100)
  posts Post[]

  @@map("tags")
}
Tip
For Many-to-Many with extra fields on the join table, use explicit join models. See:<a href=’https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations’ target=’_blank’>prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations</a>
MIGRATE
The Definitive Answer

Prisma Migrate vs `db push`

This is one of the most frequently confused aspects of Prisma:
prisma db push prisma migrate dev
Use For Prototyping / local dev Production / team projects
Migration Files Not created Created & versioned
Data Loss Risk May reset DB Safe, reviewable SQL
CI/CD Ready No Yes
MySQL Support Full Full

Create and apply your first migration:

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

datasource db {
  provider = "mysql"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  post Post[]

  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String   @db.VarChar(255)
  content   String?  @db.Text
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  authorId  Int
  author    User     @relation(fields: [authorId], references: [id])

  @@map("posts")
}


// migration
// npx prisma migrate dev --name init

This creates `prisma/migrations/[timestamp]_init/migration.sql` — review it:

🗃️ 
-- CreateTable
CREATE TABLE `users` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,
    UNIQUE INDEX `users_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Notice Prisma uses `utf8mb4` charset and `utf8mb4_unicode_ci` collation by default — the correct choice for full Unicode support in MySQL (including emojis).
SEED

Seeding the Database

Seed scripts populate your database with initial/test data. Create `prisma/seed.ts`:
📘 
// prisma/seed.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  // Upsert to avoid duplicate errors on repeated seed runs
  const alice = await prisma.user.upsert({
    where: { email: '[email protected]' },
    update: {},
    create: {
      email: '[email protected]',
      name: 'Alice Johnson',
      posts: {
        create: [
          { title: 'Hello Prisma!', published: true },
          { title: 'Getting Started with MySQL' },
        ],
      },
    },
  });

  console.log('Seeded:', alice);
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());
Add to `package.json`:
📋 
"scripts": {
  "seed": "ts-node --project tsconfig.prisma.json prisma/seed.ts"
}
Run it:
💻 
npm run seed

Explore project snapshots or discuss custom web solutions.

More About Me
STUDIO
Your Free Database UI

Prisma Studio

Start Prisma Studio with;
Browse your MySQL tables, filter records, create, edit, and delete data — all without writing a single SQL query. Perfect for development and debugging.
💻 
npx prisma studio
# Opens at http://localhost:5555
# Port might be different. Use port based on your output

Give me six hours to chop down a tree and I will spend the first four sharpening the axe.

Abraham Lincoln
FAQ's

Frequently Asked Questions

Prisma Migrate only applies additive changes automatically. If a migration would cause data loss (e.g., dropping a column), it warns you and may reset your dev DB. Production uses `prisma migrate deploy` — which never resets data.

Yes! Use `prisma db pull` to introspect your existing MySQL database and generate a `schema.prisma` automatically.

Prisma handles this automatically — all generated tables use `utf8mb4_unicode_ci`. If you're using an existing database, ensure your MySQL server is configured with `character_set_server = utf8mb4`.

Yes. MySQL natively supports ENUM columns. Define: `enum Role { USER ADMIN }` in `schema.prisma` and Prisma maps it to a MySQL `ENUM` column.

Use `@map()` to decouple your Prisma field name from the MySQL column name: `firstName String @map("first_name")`. This lets you rename in your code without touching the DB column — avoiding destructive migrations.

Blogs

Related Blogs

Comments are closed