Working with Multiple Schemas
1. Configuring Multiple Datasources
Note: Prisma supports exactly one datasource block per schema. Use one PrismaClient per physical database for cross-database workloads.
| Approach | Detail |
|---|
| Multiple schemas | One client per database |
| File layout | prisma/schemas/billing/schema.prisma |
| Generators | Distinct output paths |
2. Using Database Schemas
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
schemas = ["public", "auth", "billing"]
}
model User { id Int @id ... @@schema("auth") }
| Aspect | Detail |
|---|
| Preview | previewFeatures = ["multiSchema"] |
| Default schema | First in schemas |
3. Switching Between Schemas
| Approach | Detail |
|---|
| Schema attribute | Per model @@schema |
| Search path | Set via URL param ?schema=auth |
4. Managing Cross-Schema Queries
| Aspect | Detail |
|---|
| Same DB | Joins allowed (PG) |
| Different DBs | No joins; orchestrate in app |
5. Implementing Multi-Tenancy Pattern
Single DB / shared schema
- tenantId column on every row
- Cheap, scalable
- Requires row-level security
DB-per-tenant
- Isolated data
- Higher cost
- One PrismaClient per tenant
| Model | Recommended For |
|---|
| Shared schema | SaaS with many small tenants |
| Schema-per-tenant | Mid-size with regulatory needs |
| DB-per-tenant | Enterprise, hard isolation |
6. Using Database-Specific Schema Features
| Feature | DB |
|---|
| CHECK constraints | PG/MySQL (preview) |
| Generated columns | PG/MySQL via raw SQL |
| Domain types | PG only |
7. Handling Per-Schema Migrations
| Strategy | Detail |
|---|
| One migrations dir per schema | --schema flag |
| Ordering | Apply shared dependencies first |
8. Synchronizing Multiple Schemas
| Tool | Use |
|---|
| CI script | Loop migrate deploy per schema |
| DB events | Publish via Pulse / CDC |
9. Configuring Per-Schema Connection Pools
| Aspect | Detail |
|---|
| Per client | Separate pools by design |
| Total cap | Sum of all pools ≤ DB max |
10. Testing with Multiple Schemas
| Pattern | Detail |
|---|
| Per-test schema | Generate random schema name |
| Reset | DROP SCHEMA after suite |