Convert an E-Commerce SQL Schema to Prisma

E-Commerce Schema Conversion

An e-commerce database involves products, categories, customers, orders, and order line items with various relationship types. This example demonstrates converting a complete multi-table SQL schema to Prisma.

Example SQL

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(100) NOT NULL UNIQUE,
  parent_id INTEGER,
  FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  compare_at_price DECIMAL(10, 2),
  sku VARCHAR(50) UNIQUE,
  stock_quantity INTEGER NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT true,
  metadata JSONB NOT NULL DEFAULT '{}',
  category_id INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT chk_price CHECK (price >= 0),
  CONSTRAINT chk_stock CHECK (stock_quantity >= 0)
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_number VARCHAR(20) NOT NULL UNIQUE,
  customer_email VARCHAR(320) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  subtotal DECIMAL(10, 2) NOT NULL,
  tax DECIMAL(10, 2) NOT NULL DEFAULT 0,
  shipping DECIMAL(10, 2) NOT NULL DEFAULT 0,
  total DECIMAL(10, 2) NOT NULL,
  notes TEXT,
  ordered_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT chk_quantity CHECK (quantity > 0)
);

CREATE INDEX idx_products_category_id ON products (category_id);
CREATE INDEX idx_products_is_active ON products (is_active);
CREATE INDEX idx_orders_status ON orders (status);
CREATE INDEX idx_orders_ordered_at ON orders (ordered_at);
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
CREATE INDEX idx_order_items_product_id ON order_items (product_id);

Generated Prisma Schema

enum OrderStatus {
  pending
  processing
  shipped
  delivered
  cancelled
}

model Category {
  id       Int        @id @default(autoincrement())
  name     String     @unique @db.VarChar(100)
  slug     String     @unique @db.VarChar(100)
  parentId Int?       @map("parent_id")
  parent   Category?  @relation("CategoryTree", fields: [parentId], references: [id], onDelete: SetNull)
  children Category[] @relation("CategoryTree")
  products Product[]

  @@map("categories")
}

model Product {
  id             Int       @id @default(autoincrement())
  name           String
  slug           String    @unique
  description    String?   @db.Text
  price          Decimal   @db.Decimal(10, 2)
  compareAtPrice Decimal?  @map("compare_at_price") @db.Decimal(10, 2)
  sku            String?   @unique @db.VarChar(50)
  stockQuantity  Int       @default(0) @map("stock_quantity")
  isActive       Boolean   @default(true) @map("is_active")
  metadata       Json      @default("{}")
  categoryId     Int?      @map("category_id")
  category       Category? @relation(fields: [categoryId], references: [id], onDelete: SetNull)
  createdAt      DateTime  @default(now()) @map("created_at") @db.Timestamptz()
  updatedAt      DateTime  @updatedAt @map("updated_at") @db.Timestamptz()
  orderItems     OrderItem[]

  @@index([categoryId], map: "idx_products_category_id")
  @@index([isActive], map: "idx_products_is_active")
  @@map("products")
}

model Order {
  id            Int         @id @default(autoincrement())
  orderNumber   String      @unique @map("order_number") @db.VarChar(20)
  customerEmail String      @map("customer_email") @db.VarChar(320)
  status        OrderStatus @default(pending)
  subtotal      Decimal     @db.Decimal(10, 2)
  tax           Decimal     @default(0) @db.Decimal(10, 2)
  shipping      Decimal     @default(0) @db.Decimal(10, 2)
  total         Decimal     @db.Decimal(10, 2)
  notes         String?     @db.Text
  orderedAt     DateTime    @default(now()) @map("ordered_at") @db.Timestamptz()
  items         OrderItem[]

  @@index([status], map: "idx_orders_status")
  @@index([orderedAt], map: "idx_orders_ordered_at")
  @@map("orders")
}

model OrderItem {
  id          Int     @id @default(autoincrement())
  orderId     Int     @map("order_id")
  productId   Int     @map("product_id")
  productName String  @map("product_name")
  quantity    Int     @default(1)
  unitPrice   Decimal @map("unit_price") @db.Decimal(10, 2)
  order       Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  product     Product @relation(fields: [productId], references: [id])

  @@index([orderId], map: "idx_order_items_order_id")
  @@index([productId], map: "idx_order_items_product_id")
  @@map("order_items")
}

Schema Design Highlights

Self-referential categories — The Category model references itself via parentId for hierarchical category trees (e.g., Electronics > Laptops > Gaming Laptops).

CHECK to Enum — The status CHECK constraint is elevated to a Prisma enum OrderStatus, providing TypeScript type safety throughout your application.

Denormalized product_name — OrderItem stores the product name at purchase time, since the product name may change later but the order history should remain accurate.

Money as Decimal — All monetary fields use Decimal with explicit precision to avoid floating-point rounding errors.

JSON metadata — The Product.metadata field uses Prisma's Json type for flexible, schema-less attributes like color, dimensions, or specifications.

Query Examples

// Fetch order with items and product details
const order = await prisma.order.findUnique({
  where: { orderNumber: "ORD-2024-5001" },
  include: {
    items: {
      include: { product: true }
    }
  }
});

// List active products in a category
const products = await prisma.product.findMany({
  where: { categoryId: 5, isActive: true },
  orderBy: { createdAt: "desc" }
});