Why I Gave Every Merchant Their Own PostgreSQL Schema

When building Shopit — a multi-tenant e-commerce marketplace for Pakistani bazaar merchants — I had to answer the question every multi-tenant app developer eventually faces:

Where does one merchant's data end and another's begin?

There are three classic answers:

1. Shared tables — everyone in the same table, separated by a merchant_id column

2. Schema-per-tenant — same database, but each tenant gets their own isolated PostgreSQL schema

3. Database-per-tenant — completely separate databases per tenant

I went with option 2. Here's why, and what it actually looks like in practice.

The Problem With Shared Tables

The obvious choice is shared tables. Slap a merchant_id on every row, filter on every query, done. It's simple, migrations are trivial, and it works fine — until it doesn't.

The moment you start thinking about Shopit seriously, shared tables fall apart:

• Every query needs a WHERE merchant_id = ?. Miss one, and merchant A sees merchant B's orders. Congratulations, you've built a data breach.

• You can't customize schema per merchant. Every merchant gets the exact same columns whether they need them or not.

• One merchant's massive product catalog slows down queries for everyone else.

• You can't easily export or wipe a single merchant's data without touching shared tables.

Shopit isn't just a database — each merchant gets their own storefront, their own URL, their own brand. It made no sense for the data model to be a flat pile where everyone's products live next to each other.

Why Not Database-Per-Tenant?

The nuclear option. Maximum isolation, complete independence.

Also complete overkill for where Shopit is right now, and a connection management nightmare. PostgreSQL connections are expensive. Spinning up a new database connection pool per merchant per request would destroy performance and rack up cloud costs fast.

Schema-per-tenant gives you 90% of the isolation benefits at a fraction of the operational cost.

Schema-Per-Tenant: How It Actually Works

In PostgreSQL, a schema is a namespace inside a database. Think of it like a folder. The same database can have:

public/           ← shared tables (users, merchants, auth)
merchant_abc123/  ← everything for merchant ABC
merchant_xyz789/  ← everything for merchant XYZ

Each merchant schema contains their own isolated copies of: products, orders, customers, categories, inventory — the works. Completely separate. No cross-contamination possible at the database level.

Provisioning: What Happens When a Merchant Signs Up

When a merchant creates an account on Shopit, the backend:

1. Creates the merchant record in the shared public schema

2. Creates a new PostgreSQL schema named after their merchant ID — something like merchant_<uuid>

3. Runs all the tenant-specific migrations into that fresh schema

4. From that point on, every request from that merchant hits their own isolated schema

It's like handing each merchant the keys to their own floor of the building. Same building, same plumbing — but their floor is entirely theirs.

The Prisma Search Path Trick

Here's the elegant part. Rather than maintaining a separate Prisma client per merchant (expensive, complex), Shopit uses a single Prisma client and dynamically sets the PostgreSQL search path before every query.

The search path tells PostgreSQL: "when I say products, I mean merchant_abc123.products."

await prisma.$executeRawUnsafe(
  `SET search_path TO "merchant_${merchantId}", public`
);

One line. That's it. Every subsequent query in that request context automatically hits the correct merchant's schema. No WHERE merchant_id = ? scattered across every query. No risk of accidentally leaking data across tenants. The database itself enforces the isolation.

It's one of those solutions that feels almost too clean once you see it working.

The Painful Part: Migrations

I won't pretend this is free. The tradeoff is migrations.

With shared tables, you run one migration and you're done. With schema-per-tenant, you have to run migrations across every single merchant schema. Ten merchants? Ten migrations. A thousand merchants? You need a script, a queue, and patience.

Every time I add a column, change an index, or restructure a table, I have to iterate over all tenant schemas and apply the migration to each one.

const merchants = await prisma.merchant.findMany();

for (const merchant of merchants) {
  await prisma.$executeRawUnsafe(
    `SET search_path TO "merchant_${merchant.id}"`
  );
  // run migration
}

It's more work. But it's the kind of work that makes the system genuinely scalable — not the kind of shortcut that comes back to haunt you when you hit real scale.

Was It Worth It?

Yes. Completely.

Each merchant's storefront on Shopit is their own world — their own URL, their own products, their own orders, their own brand. It would feel architecturally dishonest for all of that to secretly live in a shared pile in the database.

Schema isolation means:

• Zero risk of cross-tenant data leaks at the query level

• Each merchant can be scaled, backed up, or migrated independently

• The data model actually matches the product model

• Adding merchant-specific customizations later is trivial

The migrations are a pain. The search path trick makes the rest feel almost effortless.

Sometimes the harder architectural choice is just the right one.

Shopit is a multi-tenant e-commerce marketplace I'm building solo for Pakistani bazaar merchants. Still in v1. Please don't look at the commit history.