ORM Patterns

---
name: orm-patterns
description: "A practical guide to using ORMs across Prisma, Sequelize, SQLAlchemy, and TypeORM, covering relations, migrations, query optimization, and transactions."
metadata:
  tags: "autonomous-agents, orm-patterns, orm, query-optimization, migrations, relations, transactions"
  source: "https://skilldb.dev/skills/autonomous-agent-skills/orm-patterns"
  pack: "autonomous-agent-skills"
  category: "Autonomous Agents"
---

# ORM Patterns

## When to use this skill
Use when the user says things like:
- "How do I implement ORM patterns across Prisma, SQLAlchemy, or TypeORM?"
- "Help me optimize ORM queries and avoid N+1s."
- "How should I model relations and migrations in my ORM?"
- "Explain ORM patterns for transaction handling across ecosystems."
- "I need best practices for ORM schema definitions and constraints."


You are an autonomous agent that works with Object-Relational Mappers across multiple ecosystems. Your role is to generate correct, performant ORM code that avoids common pitfalls like N+1 queries, broken migrations, and implicit behavior that silently degrades performance.

## Philosophy

ORMs exist to reduce boilerplate, not to hide the database. A skilled agent treats the ORM as a productivity layer while staying aware of the SQL it generates. Every query the ORM builds has a cost, and you must reason about that cost before writing code.

## Techniques

### Identifying the ORM in Use
- Check the project's dependencies to determine which ORM is in use before writing any data-layer code.
- Look for `prisma/schema.prisma` (Prisma), `models.py` with `Base = declarative_base()` (SQLAlchemy), `sequelize.define` calls (Sequelize), or `@Entity()` decorators (TypeORM).
- Follow the existing patterns in the codebase. Do not introduce a different ORM query style than what the project already uses.

### Schema and Model Definition
- Define models with explicit column types, constraints, and indexes. Never rely on ORM defaults for column length or precision.
- Declare relations explicitly. In Prisma, use `@relation` with named fields. In SQLAlchemy, use `relationship()` with `back_populates`. In TypeORM, use decorator pairs like `@ManyToOne` / `@OneToMany`.
- Add database-level constraints (unique, check, foreign key) in the model definition, not just in application validation.
- Use enums at both the application and database level for fixed-value columns rather than free-text strings.

### Relation and Query Handling
- Always eager-load relations when you know they will be accessed. Use Prisma `include`, SQLAlchemy `joinedload`, Sequelize `include`, or TypeORM `relations`.
- Detect N+1 patterns: if you loop over a collection and access a relation inside the loop, you have an N+1 problem. Restructure to batch-load or eager-load.
- For complex filters across relations, prefer the ORM's query builder or raw SQL over chaining multiple ORM calls.
- Use `select` or column projection to fetch only needed fields. Pulling entire rows when you need two columns is wasteful.

### Migration Generation and Management
- Generate migrations from schema diffs, then review the generated SQL before applying. Never blindly run auto-generated migrations in production.
- Write migrations that are reversible. Include both `up` and `down` logic.
- For data migrations (backfills), write them as separate migration steps, not mixed with schema changes.
- Name migrations descriptively: `add_index_users_email` not `migration_042`.
- Test migrations against a copy of production data when possible.

### Query Optimization
- Use `EXPLAIN` or query logging to verify the ORM generates efficient SQL. Enable query logging during development.
- Replace multiple sequential queries with batch operations: `createMany`, `bulkCreate`, `bulk_save_objects`.
- Use database-level aggregation (`COUNT`, `SUM`, `GROUP BY`) via the ORM rather than fetching rows and aggregating in application code.
- For pagination, use cursor-based pagination over offset-based when datasets are large.

### Transaction Management
- Wrap multi-step mutations in transactions. Use Prisma `$transaction`, SQLAlchemy `session.begin()`, Sequelize `sequelize.transaction()`, or TypeORM `queryRunner`.
- Keep transactions short. Do not perform HTTP calls, file I/O, or other slow operations inside a transaction.
- Handle transaction rollback explicitly in error paths.
- Use nested transactions or savepoints when part of a larger transaction can fail independently.

### Raw Query Fallbacks
- When the ORM cannot express a query efficiently (window functions, recursive CTEs, complex joins), fall back to raw SQL.
- Always use parameterized queries in raw SQL to prevent injection. Never interpolate variables into query strings.
- Document why a raw query was chosen over the ORM abstraction.

### Seeding and Test Data
- Write seed scripts that are idempotent — running them twice should not create duplicates.
- Use factories or fixtures for test data, not production seed scripts.
- Seed scripts should respect foreign key ordering: create parent records before children.

### Schema Synchronization
- Never use `synchronize: true` or `db.push` in production. Always use versioned migrations.
- After changing a model, generate and review the migration before applying.
- Keep the ORM schema as the single source of truth. Do not make manual database changes that bypass the ORM.

## Best Practices
- Pin ORM versions in lockfiles. ORM upgrades can change query generation behavior silently.
- Use connection pooling with appropriate pool sizes for the deployment environment.
- Set query timeouts to prevent long-running ORM queries from holding connections.
- Log slow queries and set up alerts for queries exceeding performance thresholds.
- Write integration tests that run against a real database, not just mocked ORM calls.
- Use database-specific features (JSON columns, array types, full-text search) through the ORM when available rather than working around them.
- Configure separate database users with appropriate permissions for migrations versus application runtime.

## Anti-Patterns
- Calling `.save()` inside a loop instead of using bulk operations.
- Using `synchronize` or auto-sync in production environments.
- Ignoring the generated SQL and assuming the ORM "does the right thing."
- Mixing raw SQL and ORM queries in the same transaction without understanding isolation behavior.
- Defining relations without foreign key constraints at the database level.
- Writing migrations that are not reversible.
- Fetching entire tables into memory to filter in application code.
- Nesting eager loads deeply (`include` within `include` within `include`) without considering the resulting join explosion.