Prisma + StackRender: Design Your Database and Start Building Your Backend

Source: Dev.to

What We’ll Do

• Design a PostgreSQL database visually using StackRender
• Deploy the database instantly
• Pull the schema into Prisma automatically
• Start building and testing backend endpoints

Prerequisites

• Node.js installed
• A PostgreSQL database (local or remote)
• Prisma installed and configured in your Node.js project

Note: If you’re new to Prisma, follow the official setup guide before continuing.

Starting With an Empty Project

We begin with a fresh Node.js project where Prisma is already set up. If you open your schema.prisma file, you’ll notice that it’s completely empty — no models, no relations, nothing defined yet. At the same time, the connected PostgreSQL database is also empty (no tables or constraints). This is the perfect starting point.

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

datasource db {
  provider = "postgresql"
}

Using StackRender to Design the Schema

Instead of manually designing the schema, we’ll use StackRender, a free and open‑source database schema generator.

1. Create a new database in StackRender

   • Name it ecommerce_db
   • Choose PostgreSQL as the database type

2. You’ll start with an empty diagram. From here you can either design the schema manually or import an existing database. For this example we’ll let the AI handle the initial design.

3. AI Prompt – In StackRender’s AI prompt feature, enter:

   Design a multi‑vendor ecommerce database

4. Within seconds StackRender generates a complete database diagram, including tables, fields, and relationships. Everything is fully editable — you can rename tables, adjust columns, and fine‑tune relationships before moving forward.

5. Once the schema looks good:

   • Open the Code section in StackRender
   • Export the generated SQL script

6. Run the exported SQL script against your PostgreSQL database (e.g., using pgAdmin). After execution, all tables, constraints, and relationships are created automatically. Your database is now live without writing SQL by hand.

Syncing the Live Database Back to Prisma

Now that the database is live, we can pull the schema into Prisma:

npx prisma db pull

Prisma introspects the database and automatically generates your schema.prisma file based on the existing tables and relations. Below is the resulting schema (truncated for brevity).

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

datasource db {
  provider = "postgresql"
}

model cart_items {
  id         Int       @id @default(autoincrement())
  cart_id    Int
  product_id Int
  quantity   Int
  added_at   DateTime? @default(now()) @db.Timestamptz(6)
  carts      carts     @relation(fields: [cart_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  products   products  @relation(fields: [product_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model carts {
  id         Int          @id @default(autoincrement())
  user_id    Int          @unique
  created_at DateTime?    @default(now()) @db.Timestamptz(6)
  updated_at DateTime?    @default(now()) @db.Timestamptz(6)
  cart_items cart_items[]
  users      users        @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

/* ... additional models omitted for brevity ... */

Tip: The generated models include all relations, enums, and default values, giving you a ready‑to‑use Prisma client.

Next Steps

1. Generate the Prisma client

   npx prisma generate

2. Start building API routes (e.g., using Express, Fastify, or NestJS) and import the generated client from ../src/generated/prisma.

3. Write integration tests against the live database to verify your endpoints.

4. Iterate – If you need to adjust the schema, go back to StackRender, modify the diagram, re‑export the SQL, run it, and run npx prisma db pull again.

Summary

By combining StackRender’s visual designer with Prisma’s introspection, you can:

• Skip manual SQL writing
• Keep your Prisma models perfectly in sync with the database
• Accelerate the “backend‑first” development cycle

Give this workflow a try on your next project and shave hours off your setup time!

Prisma Schema (cleaned)

model cart_items {
  id          Int      @id @default(autoincrement())
  cart_id     Int
  product_id  Int
  quantity    Int
  cart        carts    @relation(fields: [cart_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  product     products @relation(fields: [product_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
}

model categories {
  id          Int        @id @default(autoincrement())
  name        String
  description String?
  products    products[]
}

model order_items {
  id          Int      @id @default(autoincrement())
  order_id    Int
  product_id  Int
  quantity    Int
  price       Decimal  @db.Decimal(10, 2)
  orders      orders   @relation(fields: [order_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  products    products @relation(fields: [product_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
}

model orders {
  id               Int                         @id @default(autoincrement())
  user_id          Int
  total_amount     Decimal                     @db.Decimal(10, 2)
  currency         orders_currency_enum
  status           orders_order_status_enum   @default(pending)
  placed_at        DateTime?                  @default(now()) @db.Timestamptz(6)
  shipped_at       DateTime?                  @db.Timestamptz(6)
  delivered_at     DateTime?                  @db.Timestamptz(6)
  cancelled_at     DateTime?                  @db.Timestamptz(6)
  users            users                       @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  order_items      order_items[]
  shipments        shipments?
  payments         payments?
}

model payments {
  id               Int                         @id @default(autoincrement())
  order_id         Int                         @unique
  amount           Decimal                     @db.Decimal(10, 2)
  method           String?
  status           payments_payment_status_enum @default(unpaid)
  transaction_id   String?                     @unique
  payment_date     DateTime?                   @default(now()) @db.Timestamptz(6)
  orders           orders                      @relation(fields: [order_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
}

model product_images {
  id          Int      @id @default(autoincrement())
  product_id  Int
  url         String
  alt_text    String?
  products    products @relation(fields: [product_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
}

model products {
  id              Int              @id @default(autoincrement())
  vendor_id       Int
  category_id     Int?
  name            String
  description     String?
  price           Decimal          @db.Decimal(10, 2)
  stock_quantity  Int
  sku             String?          @unique
  created_at      DateTime?        @default(now()) @db.Timestamptz(6)
  updated_at      DateTime?        @default(now()) @db.Timestamptz(6)
  cart_items      cart_items[]
  order_items     order_items[]
  product_images  product_images[]
  categories      categories?      @relation(fields: [category_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  vendors         vendors          @relation(fields: [vendor_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  reviews         reviews[]
}

model reviews {
  id          Int       @id @default(autoincrement())
  product_id  Int
  user_id     Int
  rating      Int
  comment     String?
  review_date DateTime? @default(now()) @db.Timestamptz(6)
  products    products  @relation(fields: [product_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
  users       users     @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model shipments {
  id                 Int                               @id @default(autoincrement())
  order_id           Int                               @unique
  shipment_date      DateTime?                         @db.Timestamptz(6)
  tracking_number    String?                           @unique
  carrier            String?
  fulfillment_status shipments_fulfillment_status_enum @default(not_fulfilled)
  created_at         DateTime?                         @default(now()) @db.Timestamptz(6)
  updated_at         DateTime?                         @default(now()) @db.Timestamptz(6)
  orders             orders                            @relation(fields: [order_id], references: [id], onDelete: Cascade, onUpdate: NoAction)
}

model users {
  id               Int       @id @default(autoincrement())
  username         String    @unique
  email            String    @unique
  password_hash    String
  first_name       String?
  last_name        String?
  shipping_address String?
  billing_address  String?
  created_at       DateTime? @default(now()) @db.Timestamptz(6)
  updated_at       DateTime? @default(now()) @db.Timestamptz(6)
  carts            carts?
  orders           orders[]
  reviews          reviews[]
}

model vendor_payouts {
  id             Int       @id @default(autoincrement())
  vendor_id      Int
  payout_date    DateTime? @default(now()) @db.Timestamptz(6)
  amount         Decimal   @db.Decimal(10, 2)
  transaction_id String?   @unique
  vendors        vendors   @relation(fields: [vendor_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}

model vendors {
  id             Int              @id @default(autoincrement())
  name           String
  email          String           @unique
  phone_number   String?
  address        String?
  created_at     DateTime?        @default(now()) @db.Timestamptz(6)
  updated_at     DateTime?        @default(now()) @db.Timestamptz(6)
  order_items    order_items[]
  products       products[]
  vendor_payouts vendor_payouts[]
}

/* ---------- Enums ---------- */

enum orders_currency_enum {
  USD
  EUR
  GBP
}

enum orders_order_status_enum {
  pending
  processing
  shipped
  delivered
  cancelled
}

enum payments_payment_status_enum {
  unpaid
  paid
  refunded
}

enum shipments_fulfillment_status_enum {
  not_fulfilled
  fulfilled
  partially_fulfilled
}

Simple Backend Feature (POST /product)

The following example shows how to create a POST endpoint that:

1. Receives product data from the client.
2. Validates the payload with Zod.
3. Persists the product (and its images) using Prisma.
4. Returns the newly created product together with its vendor, category, and images.

Route Implementation (TypeScript)

import { Request, Response } from "express";
import { prisma } from "./prismaClient"; // adjust the import to your setup
import { createProductSchema, CreateProductImageInput } from "./schemas";

router.post("/product", async (request: Request, response: Response) => {
  try {
    // 1️⃣ Validate request body
    const result = createProductSchema.safeParse(request.body);
    if (!result.success) {
      response.status(400).json(result);
      return;
    }

    const { data } = result;

    // 2️⃣ Create product + nested images
    const product = await prisma.products.create({
      data: {
        ...data,
        product_images: {
          create: data.product_images.map(
            (productImage: CreateProductImageInput) => productImage
          ),
        },
      },
      include: {
        vendors: true,
        categories: true,
        product_images: true,
      },
    });

    // 3️⃣ Respond with the created entity
    response.status(200).json({ success: true, data: product });
  } catch (error) {
    console.error(error);
    response.status(500).json({ error, success: false });
  }
});

What the Endpoint Does

End‑to‑End Verification

1. Initial state – The products table is empty.
2. Send a test request (e.g., via Postman or curl) with a valid payload.
3. Result – A new row appears in products, and corresponding rows appear in product_images.
4. Response – The API returns the created product together with its related data.

This confirms that:

• The Prisma schema is correctly mapped to the database.
• The generated Prisma client works as expected.
• The validation layer (Zod) catches malformed input before it reaches the DB.

Why This Workflow Is Valuable

• Visual DB design – Start with a diagram or schema file; no hand‑written SQL needed.
• Automatic model generation – Prisma reads the schema and creates type‑safe models for you.
• Rapid prototyping – Jump from schema to a functional endpoint in minutes.
• Consistency – Types, validation, and database constraints stay in sync.

If you’re building back‑ends with Prisma and PostgreSQL, pairing them with a visual design tool (e.g., StackRender) can shave hours—or even days—off your setup time.

Resources

• StackRender – Visual database design & Prisma schema generation.
• Prisma Docs – https://www.prisma.io/docs
• Zod – https://github.com/colinhacks/zod

Happy coding! 🚀

• Website
• Github