146 lines
4.8 KiB
Markdown
146 lines
4.8 KiB
Markdown
# Database
|
|
|
|
Negareh API uses **PostgreSQL** with **MikroORM 6**. Schema changes are managed through migrations — never rely on `synchronize` in production.
|
|
|
|
## Connection
|
|
|
|
Configuration lives in `src/config/mikro-orm.config.ts` and reads from environment variables:
|
|
|
|
| Variable | Description |
|
|
|----------|-------------|
|
|
| `DB_HOST` | PostgreSQL host |
|
|
| `DB_PORT` | PostgreSQL port |
|
|
| `DB_USER` | Database user |
|
|
| `DB_PASS` | Database password |
|
|
| `DB_NAME` | Database name |
|
|
| `NODE_ENV` | `production` disables auto schema ensure |
|
|
|
|
In non-production, MikroORM can auto-create/update the database schema on startup (`ensureDatabase`). In production, only migrations should change the schema.
|
|
|
|
## Entity conventions
|
|
|
|
All persistent models extend `BaseEntity`:
|
|
|
|
```typescript
|
|
// src/common/entities/base.entity.ts
|
|
@Filter({ name: 'notDeleted', cond: { deletedAt: null }, default: true })
|
|
export abstract class BaseEntity {
|
|
@PrimaryKey({ type: 'string', columnType: 'char(26)' })
|
|
id: string = ulid();
|
|
|
|
@Property({ defaultRaw: 'now()', columnType: 'timestamptz' })
|
|
createdAt: Date = new Date();
|
|
|
|
@Property({ nullable: true, columnType: 'timestamptz' })
|
|
deletedAt?: Date;
|
|
}
|
|
```
|
|
|
|
### Rules
|
|
|
|
- **Primary keys** — ULID strings (`char(26)`), not auto-increment integers.
|
|
- **Timestamps** — `timestamptz` with UTC (`forceUtcTimezone: true` in config).
|
|
- **Soft deletes** — Set `deletedAt` instead of hard-deleting rows. The `notDeleted` filter excludes soft-deleted records by default.
|
|
- **Indexes** — Add `@Index` on foreign keys and columns used in `WHERE` / `ORDER BY` clauses.
|
|
- **Table names** — Explicit `tableName` in `@Entity({ tableName: 'users' })` when the name differs from the class.
|
|
|
|
### Relations
|
|
|
|
- Use MikroORM decorators: `@ManyToOne`, `@OneToMany`, `@ManyToMany`.
|
|
- Initialize `OneToMany` with `new Collection<T>(this)`.
|
|
- Use `populate` in queries to load relations and avoid N+1.
|
|
|
|
### Updates
|
|
|
|
```typescript
|
|
wrap(entity).assign(partialDto);
|
|
await em.flush();
|
|
```
|
|
|
|
## Repositories
|
|
|
|
Custom data access extends `EntityRepository<Entity>`:
|
|
|
|
```typescript
|
|
@Injectable()
|
|
export class UserRepository extends EntityRepository<User> {
|
|
constructor(readonly em: EntityManager) {
|
|
super(em, User);
|
|
}
|
|
|
|
async findAllPaginated(dto: FindUsersDto): Promise<PaginatedResult<User>> {
|
|
// build FilterQuery, findAndCount with limit/offset
|
|
}
|
|
}
|
|
```
|
|
|
|
Register repositories as providers in the feature module alongside `MikroOrmModule.forFeature([Entity])`.
|
|
|
|
## Migrations
|
|
|
|
Migrations live in `database/migrations/` and are emitted as TypeScript.
|
|
|
|
### Commands
|
|
|
|
| Script | Action |
|
|
|--------|--------|
|
|
| `npm run migration:create` | Generate migration from entity diff |
|
|
| `npm run migration:blank` | Create empty migration file |
|
|
| `npm run migration:up` | Apply pending migrations |
|
|
| `npm run migration:down` | Revert last migration |
|
|
| `npm run migration:list` | List all migrations |
|
|
| `npm run migration:pending` | Show unapplied migrations |
|
|
| `npm run migration:fresh` | Drop and re-run all migrations |
|
|
|
|
### Guidelines
|
|
|
|
- **Always use migrations** for schema changes in shared environments.
|
|
- Migrations are **transactional** (`allOrNothing: true`).
|
|
- `safe: true` prevents destructive drops in generated migrations — review each file before applying.
|
|
- Complex logic (triggers, functions) belongs in migrations — see `Migration20260626140000` for invoice total recalculation triggers.
|
|
- **Never remove data** in migrations unless explicitly requested.
|
|
|
|
### Workflow
|
|
|
|
1. Modify entity files.
|
|
2. Run `npm run migration:create`.
|
|
3. Review the generated SQL in `database/migrations/`.
|
|
4. Run `npm run migration:up`.
|
|
5. Commit the migration file with the entity changes.
|
|
|
|
## Seeders
|
|
|
|
Seeders populate development/staging data. Entry point: `src/seeders/DatabaseSeeder.ts`.
|
|
|
|
```bash
|
|
npm run db:seed # Run all seeders
|
|
npm run db:reset # Drop + create + migrate + seed (destructive)
|
|
```
|
|
|
|
Seeder structure:
|
|
|
|
```
|
|
src/seeders/
|
|
├── DatabaseSeeder.ts # Orchestrates seed order
|
|
├── <entity>.seeder.ts # Per-entity seed logic
|
|
└── data/ # Static seed data arrays
|
|
```
|
|
|
|
Seed order matters — permissions and roles must exist before admins; categories before products.
|
|
|
|
## Query guidelines
|
|
|
|
- Avoid `SELECT *` in raw queries; MikroORM entity loads are fine but use `fields` option when you only need a subset.
|
|
- Design filters to hit indexes (`$ilike` on indexed columns sparingly; prefer exact match on indexed fields).
|
|
- Use `findAndCount` for paginated lists.
|
|
- Wrap multi-step writes in `em.transactional(async (em) => { ... })`.
|
|
|
|
## Connection pool
|
|
|
|
Pool settings are tuned per environment in `mikro-orm.config.ts`:
|
|
|
|
- Production: min 5, max 20 connections
|
|
- Development: min 2, max 10 connections
|
|
|
|
Statement and idle timeouts are set to 60 seconds to prevent hung transactions.
|