# Database Relations

## Relation Pattern

Always chain `.relation()` on `db.uuid()` for foreign keys — a plain `db.uuid()` without `.relation()` creates a UUID column with no FK constraint and no GraphQL navigation:

```typescript
userId: db.uuid().relation({
  type: "n-1",
  toward: { type: user },
  backward: "userRoles",
}).description("Foreign key to User"),
```

### Parameters

- `type`: `"n-1"` (many-to-one), `"1-1"` (one-to-one), or `"keyOnly"` (FK only, no navigation)
- `toward.type`: The related type definition (import it), or `"self"` for self-referencing FKs (e.g., parent-child hierarchies)
- `backward`: Field name for reverse navigation from the related type

### Why `.relation()` matters

- Foreign key constraints (referential integrity)
- Forward navigation: `UserRole.user`
- Backward navigation: `User.userRoles`
- GraphQL relational queries

## Intra-Module Foreign Keys

Same-module types are imported directly — never add them to `CreateTypeParams`:

```typescript
import { order } from "./order";

orderId: db.uuid().relation({
  type: "n-1",
  toward: { type: order },
  backward: "lines",
}),
```

## Cross-Module Foreign Keys

When a FK references a type from another module, use the DB type injection pattern from [cross-module-dependency.md](cross-module-dependency.md#db-type-injection-srcdbts):

## Junction Tables (Many-to-Many)

Junction tables need relations on both foreign keys plus a composite unique index:

```typescript
import { user } from "./user";
import { role } from "./role";

db.table("UserRole", {
  userId: db.uuid().relation({
    type: "n-1",
    toward: { type: user },
    backward: "userRoles",
  }),
  roleId: db.uuid().relation({
    type: "n-1",
    toward: { type: role },
    backward: "userRoles",
  }),
}).indexes({
  fields: ["userId", "roleId"],
  unique: true,
  name: "user_role_unique_idx",
});
```

### Naming Conventions

- `backward` field: Use plural of the junction table name (e.g., `"userRoles"`, `"rolePermissions"`)
- Index name: `{table}_unique_idx` pattern

## Composite Indexes

Use `.indexes()` (not `.uniqueIndex()`) for multi-column constraints:

```typescript
.indexes({
  fields: ["fieldA", "fieldB"],
  unique: true,
  name: "descriptive_idx_name",
})
```