Designing a database schema is often one of the slowest parts of starting a new backend project. You either spend time writing SQL by hand or carefully crafting Prisma models before you can even write your first endpoint.
In this article, I’ll show you a faster workflow using Prisma and StackRender that lets you go from an empty project to a working backend in minutes.
We’ll:
- Design a PostgreSQL database visually using StackRender
- Deploy the database instantly
- Pull the schema into Prisma automatically
- Start building and testing backend endpoints
Prerequisites
Before we start, make sure you have:
- Node.js installed
- A PostgreSQL database (local or remote)
- Prisma installed and configured in your Node.js project
If you’re new to Prisma, follow the official setup guide before continuing.
Prisma 7 Install guide
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. There are no tables or constraints created.
This is the perfect starting point.
generator client {
provider = "prisma-client-js"
output = "../src/generated/prisma"
}
datasource db {
provider = "postgresql"
}
Designing the Database with StackRender
Instead of manually designing the schema, we’ll use StackRender, a free and open-source database schema generator.
Inside StackRender:
- Create a new database
- Name it
ecommerce_db - Choose PostgreSQL as the database type
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.
Generating an Ecommerce Schema Using AI
Using StackRender’s AI prompt feature, enter:
Design a multi-vendor ecommerce database
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.
Exporting and Deploying the Database
Once the schema looks good:
- Open the Code section in StackRender
- Export the generated SQL script
Take this SQL script and run it against your PostgreSQL database (for example, using pgAdmin).
After execution, all tables, constraints, and relationships are created automatically.
At this point, your database is fully deployed without writing SQL by hand.
Pulling the Database into Prisma
Now that the database is live, we can sync it back into Prisma.
Run:
npx prisma db pull
Prisma will introspect the database and automatically generate your schema.prisma file based on the existing tables and relations.
This gives you fully defined Prisma models instantly.
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)
}
model categories {
id Int @id @default(autoincrement())
name String @unique
description String?
products products[]
}
model order_items {
id Int @id @default(autoincrement())
order_id Int
product_id Int
vendor_id Int
quantity Int
price_per_item Decimal @db.Decimal(10, 2)
subtotal 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: NoAction, onUpdate: NoAction)
vendors vendors @relation(fields: [vendor_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}
model orders {
id Int @id @default(autoincrement())
user_id Int
order_date DateTime? @default(now()) @db.Timestamptz(6)
total_amount Decimal @db.Decimal(10, 2)
currency orders_currency_enum
order_status orders_order_status_enum @default(pending)
shipping_address String?
billing_address String?
created_at DateTime? @default(now()) @db.Timestamptz(6)
updated_at DateTime? @default(now()) @db.Timestamptz(6)
order_items order_items[]
users users @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
payments payments?
shipments shipments?
}
model payments {
id Int @id @default(autoincrement())
order_id Int @unique
payment_date DateTime? @db.Timestamptz(6)
amount Decimal @db.Decimal(10, 2)
payment_method String?
transaction_id String? @unique
payment_status payments_payment_status_enum @default(unpaid)
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 product_images {
id Int @id @default(autoincrement())
product_id Int
image_url String
alt_text String?
display_order Int?
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[]
}
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
}
Testing Everything with a Real Endpoint
To verify that everything works end to end, we can create a simple backend feature.
In this example, we implement a POST /product endpoint using:
- Express for the server
- Zod for request validation
- Prisma for database access
router.post("/product", async (request: Request, response: Response) => {
try {
const result = createProductSchema.safeParse(request.body)
if ( ! result.success) {
response.status(400).json(result) ; return ;
}
const { data } = result ;
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 ,
}
})
response.status(200).json({ success : true , data : product }) ;
} catch (error) {
console.log (error) ;
response.status(500).json({ error, success: false })
}
})
The endpoint:
- Receives product data from the client
- Validates the input using Zod
- Inserts the product and its images into the database using Prisma
- Returns the created product with its category, vendor, and images
Before sending any request, the product table is empty.
After sending a test request (for example using Postman), a new product record appears in the database, along with its related images.
This confirms that the schema, Prisma models, and backend logic are all working together correctly.
Why This Workflow Works So Well
This approach allows you to:
- Design databases visually
- Avoid writing SQL manually
- Skip writing Prisma models by hand
- Move from schema design to backend code much faster
It’s especially useful when starting new projects or prototyping ideas quickly.
Conclusion
By combining StackRender and Prisma, you get a smooth workflow from database design to production-ready backend code.
If you’re building backends with Prisma and PostgreSQL, this setup can save you a lot of time and reduce friction during the early stages of development.
Feel free to check the resources linked below to get started with StackRender and Prisma.
Top comments (0)