# Database Overview

> Database architecture for the TanStack Start Prisma SaaS Kit: PostgreSQL, Prisma ORM, multi-tenancy, and type-safe queries.

*Canonical: https://makerkit.dev/docs/tanstack-prisma/database/overview*

---

The TanStack Start Prisma SaaS Kit uses **PostgreSQL** as its default database and **Prisma ORM** for type-safe database operations. You get a production-ready setup with connection pooling, multi-tenant isolation, and in-memory testing out of the box.

{% callout title="Definition" %}
**Prisma ORM** is a TypeScript-first database toolkit that generates a type-safe client from your schema definition. It replaces raw SQL with auto-completed, type-checked queries.
{% /callout %}

{% sequence title="Database Configuration" description="Configure and use the database layer in the kit." %}
[Prisma Configuration](./prisma)

[Database Schema](./schema)

[Migrations](./migrations)

[Client Usage](./client)

[Rate Limit Service](./rate-limit-service)

[Testing Utilities](./testing)
{% /sequence %}

## Technology Stack

| Component | Purpose | Why It Matters |
|-----------|---------|----------------|
| PostgreSQL | Relational database | ACID compliance, JSON support, excellent hosting options |
| Prisma ORM 7 | Type-safe client | Full TypeScript inference, declarative schema, generated client |
| Prisma Migrate | Schema migrations | Version-controlled SQL migrations generated from your schema |
| PGlite | In-memory PostgreSQL | Fast isolated tests without Docker or external services |

## Package Structure

The database layer lives in the `@kit/database` package:

```
packages/database/
├── src/
│   ├── adapters/
│   │   └── postgres.ts        # PostgreSQL connection with pooling
│   ├── prisma/
│   │   ├── schema.prisma      # Data model definitions
│   │   ├── migrations/        # Version-controlled migrations
│   │   └── generated/         # Auto-generated Prisma Client
│   ├── auth/
│   │   └── context.ts         # Multi-tenant auth context utilities
│   ├── services/
│   │   └── rate-limit.service.ts
│   ├── test-utils/            # Testing utilities with PGlite
│   ├── client.ts             # Database client export
│   └── index.ts              # Package exports
├── prisma.config.ts          # Prisma configuration
└── package.json
```

## Why PostgreSQL?

PostgreSQL handles the core requirements of a SaaS application:

- **ACID compliance** for reliable transactions
- **JSON/JSONB support** for flexible metadata storage
- **Full-text search** without external dependencies
- **Excellent hosting options**: Supabase, Neon, Railway, AWS RDS, and self-hosted

The kit uses the `pg` driver with connection pooling configured for both production and development environments.

## Why Prisma ORM?

Prisma provides a declarative, type-safe data layer:

- **Type-safe queries** with full TypeScript inference from your schema
- **Declarative data modeling** with relations, `@map`, and indexes
- **Great DX** with autocomplete, readable errors, and Prisma Studio
- **Powerful querying** with relations, filtering, aggregations, and transactions
- **Version-controlled migrations** generated from your schema

## Connection Handling

The adapter uses a singleton pattern to prevent connection exhaustion during development:

```typescript {% title="packages/database/src/adapters/postgres.ts" %}
import { PrismaPg } from '@prisma/adapter-pg';
import { Pool } from 'pg';

import { databaseUrl } from '../database-url';
import { PrismaClient } from '../prisma/generated/client';

function createPrismaClient() {
  const pool = new Pool({ connectionString: databaseUrl });
  const adapter = new PrismaPg(pool);

  return new PrismaClient({ adapter });
}

let db: PrismaClient;

if (process.env.NODE_ENV === 'production') {
  db = createPrismaClient();
} else {
  // Reuse client across hot reloads
  if (!global.prisma) {
    global.prisma = createPrismaClient();
  }

  db = global.prisma;
}

export { db };
```

The client uses the `@prisma/adapter-pg` driver adapter over a `pg` connection pool, which works with hosted PostgreSQL providers that use transaction pooling (Supabase, Neon, and similar).

## Multi-Tenancy Model

Multi-tenancy is enforced at the **application layer**. All tenant data is isolated by an `organizationId` foreign key, and queries must filter by it. There is no Row Level Security; ownership is checked in code.

The kit provides lightweight auth context utilities to carry the current user and organization through your services:

```typescript
import { createOrgAuthContext } from '@kit/database';
import { db } from '@kit/database';

const ctx = createOrgAuthContext(userId, organizationId, role);

// Always filter tenant-owned data by the context's organizationId
const projects = await db.project.findMany({
  where: { organizationId: ctx.organizationId },
});
```

This pattern prevents accidental cross-tenant data access: every query on tenant-scoped data includes the `organizationId` filter.

## Quick Reference

| Task | Command |
|------|---------|
| Regenerate client | `pnpm --filter @kit/database prisma:generate` |
| Create and apply a migration | `pnpm --filter @kit/database prisma:migrate` |
| Reset the database | `pnpm --filter @kit/database prisma:reset` |
| Open Prisma Studio | `pnpm --filter @kit/database prisma:studio` |
| Run tests | `pnpm --filter @kit/database test:unit` |

## Common Mistakes to Avoid

**Forgetting to filter by organizationId**: Every query on tenant-scoped data must include an organization filter. Cross-tenant leaks happen when the filter is omitted.

**Creating new connections on every request**: Always use the singleton `db` export from `@kit/database`. Creating clients manually leads to pool exhaustion.

**Editing the generated client**: The `src/prisma/generated/` directory is auto-generated. Edit `schema.prisma` and run `prisma:generate` instead.

{% faq
   title="Frequently Asked Questions"
   items=[
     {"question": "How does connection pooling work in development?", "answer": "The adapter uses a global singleton PrismaClient to prevent connection exhaustion during hot reloads. In production, a fresh client is created. The client uses the @prisma/adapter-pg driver adapter over a pg connection pool, which works with the transaction pooling mode used by hosted PostgreSQL providers."},
     {"question": "What PostgreSQL version should I use?", "answer": "PostgreSQL 16 or later is recommended. Newer versions provide better performance for JSON operations and query optimization."},
     {"question": "How is multi-tenancy enforced?", "answer": "Multi-tenancy is enforced at the application layer. All tenant data is scoped by an organizationId foreign key, and queries must filter by it in code. There is no Row Level Security. Use createOrgAuthContext() to carry the current organization through your services."},
     {"question": "What is PGlite and why is it used for testing?", "answer": "PGlite is PostgreSQL compiled to WebAssembly that runs in-memory. It provides real PostgreSQL behavior without Docker or external services, making tests fast, isolated, and portable. Each test suite gets a fresh database with the migrations applied."}
   ]
/%}

---

**Next:** [Prisma Configuration →](./prisma)