database-data-layer

Files (1)

• SKILL.md

SKILL.md (first lines)

# Database / Data Layer

## Stack-First Principle

Choose your data layer based on the stack, then stick to the pattern:

| Stack | Primary ORM | Migration Tool | Notes |
|-------|-------------|----------------|-------|
| **Node/TypeScript** | Prisma | Prisma Migrate | Default for full-stack JS |
| **Node/TypeScript** | Drizzle | Drizzle Kit | Lighter than Prisma, SQL-like |
| **Python** | SQLAlchemy | Alembic | Mature, flexible |
| **Python** | Django ORM | Django migrations | Only if using Django |
| **Go** | GORM | golang-migrate | Most common Go ORM |
| **Go** | sqlx + sqlc | goose | More SQL control |
| **Rust** | Diesel | Diesel CLI | Type-safe, compile-time checked |
| **Rust** | SQLx | sqlx migrate | Async, compile-time checked |

## Prisma Patterns (Default for Meta-Architect)

### Schema Design Rules

```
model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  profile   Profile?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

enum Role {
  USER
  ADMIN
}
```

- **@id** — always use `cuid()` or `uuid()`, never auto-increment ints for distributed systems
- **@unique** — add for every natural key (email, slug, externalId)
- **Relations** — always explicit (both sides), never implicit
- **@default** — set sensible defaults, not null
- **@updatedAt** — on every entity that gets mutated
- **Optional vs Required** — `String?` is optional, `String` is required. Be intentional.

### Relation Patterns

```prisma
// One-to-one
model User { profile Profile? }
model Profile { user User @relation(fields: [userId], references: [id]); userId String @unique }

// One-to-many
model User { posts Post[] }
model Post { author User @relation(fields: [authorId], references: [id]); authorId String }

// Many-to-many
model Post { tags Tag[] @relation("PostTags") }
model Tag { posts Post[] @relation("PostTags") }
```

### Migration Workflow

```bash
# Initial setup
npx prisma migrate dev --name init

# Development (auto-deletes data — never in prod)
npx prisma migrate dev --name add_user_role

# Production-safe
npx prisma migrate deploy           # apply pending migrations
npx prisma migrate reset --force    # only for dev/test reset

# Check status
npx prisma migrate status
npx prisma migrate diff --from-empty --to-schema-datamodel schema.prisma
```

**Golden rules:**
- Never edit migration files manually. Create a new migration.
- `prisma migrate dev` is for development only — it resets data.
- `prisma migrate deploy` is for production — it never resets.
- Always review the generated SQL before deploying.

### Seed Data Patterns

```typescript
// prisma/seed.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  // Upsert to make seeds idempotent
  const user = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin',
      role: 'ADMIN',
    },
  });
  console.log('Seeded:', user);
}

main().catch(console.error).finally(() => prisma.$disconnect());
```

```json
// package.json
{ "prisma": { "seed": "tsx prisma/seed.ts" } }
```

```
npx prisma db seed
```

From UgochukwuChidera/Opencode-Agents, GPL-3.0.

More from UgochukwuChidera/Opencode-Agents