Prisma Schema Basics — Defining Models and Data Types

In this tutorial, you will learn about Prisma Schema Basics. We cover key concepts, practical examples, and best practices to help you master this topic.

The Prisma schema is the single source of truth for your database shape, defining models, fields, data types, constraints, and relationships in a declarative syntax that generates the type-safe client.

What You'll Learn

By the end of this lesson you will write Prisma schema models with fields and data types, use attributes and functions for constraints, configure datasource and generator, and organize schema for readability.

Why It Matters

The schema is the foundation of every Prisma project. All migrations, client queries, and type definitions derive from it. A well-written schema ensures correct database structure and type-safe queries.

Real-World Use

DodaZIP's schema.prisma defines 15+ models representing users, files, processing jobs, and billing. Each model uses Prisma's scalar types, attributes, and relations to accurately represent the business domain.

flowchart LR
    A[schema.prisma] -->|prisma generate| B[TypeScript Types]
    A -->|prisma migrate| C[Database Tables]
    B --> D[Type-safe Client Code]
    D -->|Queries| C
    style A fill:#2d3748,color:#fff

Schema Structure

The schema consists of datasource, generator, and model blocks.

// prisma/schema.prisma

// Datasource: database connection configuration
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Generator: code generation configuration
generator client {
  provider = "prisma-client-js"
}

// Models: database table definitions
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

# schema_structure.py
# Understanding schema sections

def schema_sections():
    print("Schema Sections:")
    print()
    print("1. datasource db")
    print("   - provider: postgresql, mysql, sqlite, mongodb")
    print("   - url: Connection string from environment")
    print()
    print("2. generator client")
    print("   - provider: prisma-client-js")
    print("   - Additional options: binaryTargets, previewFeatures")
    print()
    print("3. model definitions")
    print("   - model Name { ... }")
    print("   - Fields with types and attributes")
    print("   - Relations to other models")

schema_sections()

Data Types and Attributes

Prisma provides scalar types matching database column types.

model Example {
  // Numeric types
  id        Int     @id @default(autoincrement())
  bigId     BigInt
  floatVal  Float
  decimalVal Decimal @db.Decimal(10, 2)
  
  // String types
  name      String
  shortText String?  // Optional (nullable)
  
  // Boolean
  active    Boolean @default(true)
  
  // DateTime
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  // JSON
  metadata  Json?   // PostgreSQL only
  
  // Enum
  role      Role    @default(USER)
}

enum Role {
  USER
  ADMIN
  MODERATOR
}

# data_types.py
# Prisma scalar types

def scalar_types():
    types = {
        "String": "Variable-length text. Max length depends on database.",
        "Boolean": "True/false value. Often used for flags.",
        "Int": "32-bit integer. Use for IDs, counts.",
        "BigInt": "64-bit integer. For large numbers.",
        "Float": "Floating-point number. For approximate values.",
        "Decimal": "Exact precision number. For money.",
        "DateTime": "Date and time with timezone.",
        "Json": "JSON data (PostgreSQL, MongoDB).",
        "Bytes": "Binary data.",
    }
    
    print("Prisma Scalar Types:")
    for name, desc in types.items():
        print(f"  {name:12s} | {desc}")

scalar_types()

Attributes and Functions

Attributes control field behavior.

model FieldAttributes {
  id         Int     @id @default(autoincrement())
  uniqueCol  String  @unique
  defaultVal String  @default("default")
  nowDefault DateTime @default(now())
  updated    DateTime @updatedAt
  mapCol     String  @map("mapped_column_name")
  
  @@unique([field1, field2])
  @@index([field1, field2])
  @@map("table_name")
}

# attributes.py
# Common Prisma attributes

def common_attributes():
    attrs = {
        "@id": "Marks a field as primary key",
        "@default(autoincrement())": "Auto-incrementing integer (serial)",
        "@default(uuid())": "Generate UUID (use with String type)",
        "@default(now())": "Current timestamp for DateTime fields",
        "@updatedAt": "Auto-update timestamp on modification",
        "@unique": "Unique constraint on the field",
        "@map()": "Map field or table to different database name",
        "@relation()": "Define relation between models",
        "?": "Make a field optional (nullable)",
        "@db.Text": "Specify native database type",
    }
    
    print("Common Prisma Attributes:")
    for attr, desc in attrs.items():
        print(f"  {attr:35s} {desc}")

common_attributes()

Naming Conventions

Follow Prisma's naming conventions for consistency.

# naming.py
# Prisma naming conventions

def naming_conventions():
    print("Prisma Naming Conventions:")
    print()
    print("models: PascalCase, singular")
    print("  ✓ User, Post, Comment")
    print("  ✗ users, postsTable")
    print()
    print("fields: camelCase")
    print("  ✓ firstName, createdAt, postId")
    print("  ✗ first_name, created_at")
    print()
    print("enums: PascalCase")
    print("  ✓ Role, Status, Color")
    print()
    print("relations: Named based on role")
    print("  ✓ author User @relation(fields: [authorId], references: [id])")

naming_conventions()

Common Mistakes

1. Forgetting the datasource block: The schema must include a datasource block. Without it, prisma generate and migrate fail.

2. Mixing camelCase and snake_case: Prisma uses camelCase for fields. The @map attribute maps to snake_case in the database.

3. Not using @updatedAt for audit fields: The @updatedAt attribute automatically sets the timestamp on every update, eliminating manual tracking.

4. Using Int for IDs in production: For production, consider String with @default(uuid()) or BigInt for Distributed Systems.

5. Not using enums for fixed values: Enums provide type safety and validation. Use them instead of String for fields with a fixed set of values.

Practice Questions

1. What sections must a Prisma schema have? datasource, generator, and at least one model.

2. What attribute marks a field as the primary key? @id.

3. How do you make a field optional (nullable)? Add a question mark after the type: String?.

4. What does @updatedAt do? It automatically updates the field's timestamp on every record modification.

5. Challenge: Write a Prisma schema for a library system with Book, Author, and Member models. Include appropriate data types, attributes, and constraints.

FAQ

Mini Project

Write a Prisma schema for an e-commerce application with Product, Category, Order, and Customer models. Include appropriate data types, constraints, and default values.

def ecommerce_schema():
    print("E-Commerce Schema:")
    print()
    print("model Customer {")
    print("  id        String   @id @default(uuid())")
    print("  email     String   @unique")
    print("  name      String")
    print("  orders    Order[]")
    print("  createdAt DateTime @default(now())")
    print("}")
    print()
    print("model Product {")
    print("  id          String   @id @default(uuid())")
    print("  name        String")
    print("  price       Decimal  @db.Decimal(10, 2)")
    print("  category    Category @relation(fields: [categoryId], references: [id])")
    print("  categoryId  String")
    print("  inStock     Boolean  @default(true)")
    print("  createdAt   DateTime @default(now())")
    print("}")
    print()
    print("model Order {")
    print("  id         String   @id @default(uuid())")
    print("  customer   Customer @relation(fields: [customerId], references: [id])")
    print("  customerId String")
    print("  total      Decimal  @db.Decimal(10, 2)")
    print("  status     OrderStatus @default(PENDING)")
    print("  createdAt  DateTime @default(now())")
    print("}")
    print()
    print("enum OrderStatus { PENDING PROCESSING SHIPPED DELIVERED }")

ecommerce_schema()

What's Next

Next: Prisma Data Model for advanced modeling.