Prisma Database Setup

Prisma Database Setup is a community skill for configuring database connections and schemas with Prisma, covering datasource configuration, model definitions, relation types, field attributes, and multi-database support for initial project setup.

What Is This?

Overview

Prisma Database Setup provides patterns for initializing and configuring Prisma in a new project. It covers datasource block configuration for PostgreSQL, MySQL, SQLite, and MongoDB connection strings, model definition syntax with scalar fields, enums, and default values for table structure, relation types including one-to-one, one-to-many, and many-to-many with explicit join tables, field attributes like @id, @unique, @default, and @map for column customization, and generator configuration for Prisma Client output and preview features. The skill enables developers to set up Prisma schemas that accurately model their database structure with proper types, constraints, and relationships from the start.

Who Should Use This

This skill serves developers starting new projects with Prisma ORM, teams configuring database connections for different environments, and engineers designing relational data models using the Prisma schema language.

Why Use It?

Problems It Solves

Setting up database connections with proper SSL, pooling, and environment-specific configuration needs careful handling. Defining data models with correct field types and constraints requires understanding Prisma schema syntax. Modeling complex relationships like self-referential trees and polymorphic associations demands specific schema patterns. Configuring Prisma for different database providers needs provider-specific attribute adjustments.

Core Highlights

Datasource configurator sets up connection strings with SSL and pooling parameters. Model builder defines tables with typed fields, constraints, and indexes. Relation designer creates typed associations between models with referential actions. Generator setup configures client output paths and preview features.

How to Use It?

Basic Usage

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

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

enum Role {
  USER
  ADMIN
  EDITOR
}

model User {
  id        String   @id
    @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime
    @default(now())
  updatedAt DateTime @updatedAt

  @@index([email])
  @@map("users")
}

model Profile {
  id     String @id
    @default(cuid())
  bio    String?
  avatar String?
  user   User   @relation(
    fields: [userId],
    references: [id],
    onDelete: Cascade)
  userId String @unique

  @@map("profiles")
}

Real-World Examples

// Complex relations schema
model Post {
  id        String   @id
    @default(cuid())
  title     String
  slug      String   @unique
  content   String
  published Boolean  @default(false)
  author    User     @relation(
    fields: [authorId],
    references: [id])
  authorId  String
  tags      Tag[]
  comments  Comment[]
  createdAt DateTime
    @default(now())

  @@index([authorId])
  @@index([slug])
  @@map("posts")
}

model Tag {
  id    String @id
    @default(cuid())
  name  String @unique
  posts Post[]

  @@map("tags")
}

model Comment {
  id       String   @id
    @default(cuid())
  text     String
  post     Post     @relation(
    fields: [postId],
    references: [id],
    onDelete: Cascade)
  postId   String
  author   User     @relation(
    "comments",
    fields: [authorId],
    references: [id])
  authorId String
  parent   Comment? @relation(
    "replies",
    fields: [parentId],
    references: [id])
  parentId String?
  replies  Comment[]
    @relation("replies")
  createdAt DateTime
    @default(now())

  @@index([postId])
  @@index([authorId])
  @@map("comments")
}

Advanced Tips

Use @map and @@map attributes to follow database naming conventions while keeping Prisma model names in PascalCase. Define composite unique constraints with @@unique for multi-field uniqueness like tenant plus slug combinations. Use environment variables for connection strings to keep credentials out of schema files.

When to Use It?

Use Cases

Set up a blog platform schema with users, posts, tags (many to many), and threaded comments. Configure a multi-tenant SaaS schema with tenant scoping on all models and composite indexes. Initialize a project that connects to PostgreSQL in production and SQLite for local development.

Related Topics

Database schema design, ORM configuration, data modeling, database relationships, and connection management.

Important Notes

Requirements

Node.js project with prisma and @prisma/client packages installed. A supported database with connection credentials. Environment file with DATABASE_URL configured for the target environment.

Usage Recommendations

Do: use @default(cuid()) or @default(uuid()) for primary keys to avoid sequential ID enumeration. Add @@index on foreign key fields that are used in relation queries. Define onDelete behavior explicitly on relations to prevent accidental cascade deletions.

Don't: use autoincrement IDs when cuid or uuid provides better distribution for distributed systems. Skip @updatedAt on models that need audit tracking. Store connection strings with passwords directly in the schema file instead of using env().

Limitations

Some database-specific column types require @db attributes that differ between PostgreSQL, MySQL, and SQLite. Prisma schema does not support database views natively and requires raw SQL for view creation. Many-to-many relations use implicit join tables that cannot have additional fields without explicit modeling.