npm package: @sprout-idws/sprout-prisma
Description: Reusable NestJS Prisma package: database URL helpers, PrismaClient module with lifecycle, and upsert retry utility
Author: Sprout
Latest version: 1.0.1

# @sprout-idws/sprout-prisma

Reusable **Prisma + PostgreSQL** helpers for NestJS: build connection URLs from split env vars (Sprout-style file mounts), wire **Prisma 7 driver adapter** (`@prisma/adapter-pg` + `pg` `Pool`), dynamic `prismaModule()`, and a small **upsert retry** helper for race-prone unique constraints.

## Purpose

- Align Prisma with deployments where `DATABASE_URL` may be absent but `DATABASE_HOST`, `DATABASE_USER`, etc. are set from configs/secrets.
- Use a shared lifecycle pattern: connect on module init, disconnect and close the pool on destroy.
- Optional `getPrismaConfigOptions()` for `prisma.config.ts` style setup with a computed URL.

## Installation

```bash
npm install @sprout-idws/sprout-prisma @prisma/client @prisma/adapter-pg pg
```

Peer dependencies: `@nestjs/common` (v10+), `@prisma/client` (v5+). Runtime dependencies include `@prisma/adapter-pg` and `pg`.

## Environment variables

### Connection

| Variable | Role |
|----------|------|
| `DATABASE_URL` | If set, used as the primary URL (pool query params appended by `buildDatabaseUrlWithPoolConfig`) |
| `DATABASE_USER`, `DATABASE_PASSWORD`, `DATABASE_HOST`, `DATABASE_PORT`, `DATABASE_NAME` | Used when `DATABASE_URL` is unset (`buildDatabaseUrl`) |

Default database name in `buildDatabaseUrl` is `brasmaq-backoffice` when `DATABASE_NAME` is unset—override `DATABASE_NAME` for other apps.

### Pool (query string on URL)

| Variable | Default |
|----------|---------|
| `DB_POOL_CONNECTION_LIMIT` | `10` |
| `DB_POOL_TIMEOUT_SECONDS` | `30` |
| `DB_POOL_CONNECT_TIMEOUT_SECONDS` | `10` |

## Functionality

### `buildDatabaseUrl()` / `buildDatabaseUrlWithPoolConfig(url)`

Pure helpers to compose or augment the PostgreSQL URL.

### `createPrismaService(PrismaClientClass)`

Returns a Nest injectable class extending your generated `PrismaClient` that:

- Builds the URL, creates a `pg` `Pool`, uses `PrismaPg` adapter
- Calls `$connect()` in `onModuleInit`, `$disconnect()` and `pool.end()` in `onModuleDestroy`
- Enables query logging in development (`error`, `warn`)

### `prismaModule(PrismaClientClass)`

Dynamic module that registers the created service as the provider **for your client class token** and exports it (not global).

Example:

```typescript
import { Module } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';
import { prismaModule } from '@sprout-idws/sprout-prisma';

@Module({
  imports: [prismaModule(PrismaClient)],
  exports: [prismaModule(PrismaClient)],
})
export class DatabaseModule {}
```

### `getPrismaConfigOptions(schemaPath)`

Returns `{ schema, datasource: { url: buildDatabaseUrl() } }` for use in Prisma config files that expect a resolved URL at config load time.

### `upsertWithRetry(upsertOp, fallbackOp)`

Runs `upsertOp()`; on Prisma **`P2002`** (unique constraint), runs `fallbackOp()` (for example an update). If fallback fails with **`P2025`** (record not found), retries the upsert up to **3** times with a short delay—mitigates concurrent create races.

## Repository

[`sprout-typescript-backend`](https://github.com/sprout-libs/sprout-typescript-backend) — `packages/sprout-prisma`.