# Backend Prisma Database

> Prisma schema conventions, migrations, soft-delete extensions, BaseService delete pattern, bilingual fields, and PostgreSQL sequence reset. Use when creating Prisma models, migrations, database queries, or soft-delete logic.

- Skill: `xmuhameed/backend-prisma-database` (Agent Skill)
- Install (CLI): `npx skillmds@latest add xmuhameed/backend-prisma-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/xmuhameed/backend-prisma-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: xmuhameed (https://skillmd.com/u/xmuhameed)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/xmuhameed/backend-prisma-database

---


# Prisma & Database

## New Apps (Prisma 8)

```bash
npm install prisma@latest @prisma/orm-postgres
```

- `prisma.config.ts` with `defineConfig`
- `npx prisma migrate dev --name description`
- Node.js 22.18+

## Model Conventions

```prisma
model category {
  id        String    @id @default(uuid())   # UUID for catalog
  name_en   String                          # bilingual
  name_ar   String
  slug      String    @unique
  deleted   DateTime?                       # soft delete
  sort      Int       @default(0)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  @@index([deleted])
}
```

| ID type | Use for |
|---------|---------|
| UUID `@default(uuid())` | Catalog entities (category, product, user) |
| Int `@id @default(autoincrement())` | Orders, junction tables, logs |

## Soft Delete

`PrismaService` uses Client Extensions — auto-injects `where: { deleted: null }` on findMany/count/findFirst.

Setting `deleted: new Date()` = soft delete.

## Delete Pattern

```typescript
// First delete → soft
await prisma.feature.update({ where: { id }, data: { deleted: new Date() } });

// Second delete on same record → hard + cleanup relations
await prisma.junction.deleteMany({ where: { featureId: id } });
await prisma.feature.delete({ where: { id } });
```

Or extend `BaseService` → `handleDelete(model, id)` handles soft→hard + sequence reset.

## Search

```typescript
where.OR = [
  { name_en: { contains: search, mode: 'insensitive' } },
  { name_ar: { contains: search, mode: 'insensitive' } },
];
```

## Relations

```typescript
// Connect on create
type: { connect: { id: dto.typeId } }

// Include on read
include: { type: true, ProductCategory: true }
```

## Migrations

**New apps:** `prisma migrate dev` only.

**Legacy:** manual SQL in `prisma/migrations/*.sql` + edit schema.

## Sequence Reset

After hard delete on autoincrement tables: `SequenceResetService` or `POST /maintenance/reset-sequences`.

## Seed

`prisma/seed.ts` — `npx prisma db seed`