FerroxLabs ORM Specialist
Purpose
Select, configure, and optimize ORMs for production applications. This skill covers ORM patterns, performance pitfalls, query optimization, transaction management, and the decision framework for when to use (or bypass) an ORM.
ORM Pattern Selection
Active Record vs Data Mapper
ACTIVE RECORD:
The model class contains both data AND database logic.
Model.find(), model.save(), model.delete()
ORMS: Sequelize, ActiveRecord (Ruby), Eloquent (Laravel)
PROS:
+ Simple, intuitive API
+ Fast prototyping
+ Less boilerplate
CONS:
- Business logic and persistence mixed
- Harder to test (models depend on database)
# ... (condensed) ...
RECOMMENDATION:
Small projects/prototypes: Active Record
Large/enterprise projects: Data Mapper
TypeScript projects: Prisma (unique approach, excellent DX)
Prisma Patterns
Schema Definition
// prisma/schema.prisma
generator client {
provider = "prisma-client-js"
previewFeatures = ["fullTextSearch", "views"]
}
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
model User {
id String @id @default(uuid())
email String @unique
# ... (condensed) ...
enum Role {
ADMIN
EDITOR
VIEWER
}
Query Patterns
// Basic CRUD
const user = await prisma.user.create({
data: { email: 'john@example.com', name: 'John' },
});
const user = await prisma.user.findUnique({
where: { id: userId },
});
const user = await prisma.user.update({
where: { id: userId },
data: { name: 'Updated Name' },
});
# ... (condensed) ...
orderBy: { createdAt: 'desc' },
take: 10,
},
},
});
Prisma with Transactions
// Interactive transaction
const [order, inventory] = await prisma.$transaction(async (tx) => {
// Create order
const order = await tx.order.create({
data: { userId, total: calculateTotal(items) },
});
// Create order items
await tx.orderItem.createMany({
data: items.map(item => ({
orderId: order.id,
productId: item.productId,
quantity: item.quantity,
price: item.price,
# ... (condensed) ...
}, {
maxWait: 5000, // Max wait to acquire transaction
timeout: 10000, // Max transaction duration
isolationLevel: 'Serializable',
});
TypeORM Patterns
Entity Definition (Data Mapper)
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, OneToMany, CreateDateColumn, Index } from 'typeorm';
@Entity('users')
export class User {
@PrimaryGeneratedColumn('uuid')
id: string;
@Column({ unique: true })
@Index()
email: string;
@Column()
name: string;
# ... (condensed) ...
author: User;
@Column({ name: 'author_id' })
authorId: string;
}
Repository Pattern
@Injectable()
export class UserRepository {
constructor(
@InjectRepository(User)
private readonly repo: Repository<User>,
) {}
async findById(id: string): Promise<User | null> {
return this.repo.findOne({
where: { id },
relations: ['posts'],
});
}
# ... (condensed) ...
.skip((filters.page - 1) * filters.pageSize)
.take(filters.pageSize)
.getManyAndCount();
}
}
N+1 Prevention
Detecting N+1
SYMPTOMS:
- Multiple identical queries in logs (SELECT ... WHERE id = ?)
- Slow endpoints that load lists with relations
- Database CPU spikes during list queries
DETECTION TOOLS:
- Prisma: prisma.$on('query', ...) event listener
- TypeORM: logging: true in connection config
- SQLAlchemy: echo=True
- General: Query logging middleware
Prevention Strategies
// Strategy 1: Eager loading (include/join)
// Load relations in the initial query
const users = await prisma.user.findMany({
include: { posts: true, profile: true },
});
// SQL: SELECT users.*, posts.*, profiles.* FROM users
// LEFT JOIN posts ON ...
// LEFT JOIN profiles ON ...
// Strategy 2: Batch loading (DataLoader)
// For GraphQL: see graphql-builder skill
// Strategy 3: Explicit join with QueryBuilder
const users = await userRepo.createQueryBuilder('user')
# ... (condensed) ...
const userIds = users.map(u => u.id);
const posts = await prisma.post.findMany({
where: { authorId: { in: userIds } },
});
// Manually associate posts with users
Eager vs Lazy Loading
EAGER LOADING:
Load relations immediately with the main query.
Use when: You know you'll need the relation.
await prisma.user.findMany({ include: { posts: true } });
LAZY LOADING:
Load relations on first access (triggers additional query).
Use when: Relation is optional, may not be accessed.
// TypeORM with lazy relations
@ManyToOne(() => User, { lazy: true })
author: Promise<User>; // Returns Promise, query on await
const post = await postRepo.findOne({ where: { id } });
const author = await post.author; // Query fires here
RECOMMENDATION:
- Default to eager loading with explicit includes
- Avoid lazy loading in loops (causes N+1)
- Use select/include to control exactly what is loaded
- Never load relations you don't need
Transaction Management
Transaction Patterns
// Pattern 1: Callback-based (Prisma)
await prisma.$transaction(async (tx) => {
await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } });
await tx.account.update({ where: { id: toId }, data: { balance: { increment: amount } } });
await tx.transfer.create({ data: { fromId, toId, amount } });
});
// Pattern 2: QueryRunner (TypeORM)
const queryRunner = dataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();
try {
await queryRunner.manager.save(entity1);
# ... (condensed) ...
@Transactional()
async transfer(fromId: string, toId: string, amount: number) {
// All queries here are in the same transaction
}
}
Transaction Isolation Levels
READ UNCOMMITTED:
Can read uncommitted changes from other transactions.
Almost never used.
READ COMMITTED (default for most DBs):
Only reads committed data.
Same query in same transaction may return different results.
REPEATABLE READ:
Reads are consistent within a transaction.
Phantom reads possible (new rows can appear).
SERIALIZABLE:
Full isolation, transactions appear sequential.
Highest consistency, lowest throughput.
RECOMMENDATION:
Default: READ COMMITTED (good balance)
Financial: SERIALIZABLE or REPEATABLE READ with explicit locks
Analytics: READ COMMITTED (stale data is OK)
Raw Query Fallbacks
// Prisma: Raw queries for complex SQL
const results = await prisma.$queryRaw<UserStats[]>`
SELECT
u.id,
u.name,
COUNT(p.id) as post_count,
AVG(p.view_count) as avg_views
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
WHERE u.created_at > ${startDate}
GROUP BY u.id, u.name
HAVING COUNT(p.id) > ${minPosts}
ORDER BY avg_views DESC
LIMIT ${limit}
# ... (condensed) ...
WHERE u.role = $1
GROUP BY u.id
ORDER BY post_count DESC`,
[role]
);
When to Use Raw SQL
USE RAW SQL when:
- Complex aggregations (GROUP BY, HAVING, window functions)
- Bulk updates/deletes (UPDATE ... WHERE ... not easily expressed)
- Performance-critical queries (ORM adds overhead)
- Database-specific features (JSONB operators, full-text search)
- Complex JOINs with conditions
- CTEs (WITH ... AS ...)
- UPSERT with complex conflict handling
STAY WITH ORM when:
- Simple CRUD operations
- Basic filtering and pagination
- Relation loading (includes/joins)
- Type-safe query building
- Migration management
Migration Management
Migration Best Practices
// Prisma migrations
// Generate migration from schema changes
// npx prisma migrate dev --name add_user_avatar
// Migration file: prisma/migrations/20250115_add_user_avatar/migration.sql
ALTER TABLE "users" ADD COLUMN "avatar_url" TEXT;
// TypeORM migration generation
// npx typeorm migration:generate src/migrations/AddUserAvatar
// Generated migration
export class AddUserAvatar1705309200000 implements MigrationInterface {
async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "users" ADD "avatar_url" varchar`);
}
async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "users" DROP COLUMN "avatar_url"`);
}
}
Migration Safety Rules
1. NEVER edit applied migrations
2. ALWAYS test against production-sized data
3. CREATE indexes CONCURRENTLY (PostgreSQL)
4. ADD columns as nullable first, then backfill, then add NOT NULL
5. SEPARATE schema and data migrations
6. INCLUDE down migration when possible
7. USE transaction for multi-statement migrations
8. TIME migrations against production data volume
DANGEROUS OPERATIONS:
- DROP COLUMN (data loss)
- ALTER COLUMN type (may lock table)
- ADD NOT NULL without default (fails if existing rows have NULL)
- CREATE INDEX (locks table -- use CONCURRENTLY)
- RENAME COLUMN/TABLE (breaks code referencing old name)
Connection Management
// Prisma connection configuration
const prisma = new PrismaClient({
datasources: {
db: {
url: ENV_CONFIG_VALUE,
},
},
log: ENV_CONFIG_VALUE === 'development'
? ['query', 'info', 'warn', 'error']
: ['error'],
});
// Connection pool settings via DATABASE_URL
// postgresql://user:pass@host:5432/db?
# ... (condensed) ...
// Singleton pattern for Prisma in serverless
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma ?? new PrismaClient();
if (ENV_CONFIG_VALUE !== 'production') globalForPrisma.prisma = prisma;
ORM Architecture Checklist
- ORM pattern selected (Active Record vs Data Mapper)
- Schema/model definitions match database conventions (naming, types)
- N+1 queries detected and prevented (eager loading, DataLoader)
- Relations explicitly loaded (no implicit lazy loading in loops)
- Transactions used for multi-step mutations
- Appropriate isolation level set for transaction type
- Raw SQL used for complex queries beyond ORM capabilities
- Connection pool sized appropriately (2 * CPU cores + 1)
- Migrations are forward-only and zero-downtime compatible
- Query logging enabled in development for performance analysis
- Select/include used to avoid over-fetching columns
- Indexes defined for all filterable/sortable columns
- Soft deletes considered for audit-sensitive data
- Graceful shutdown disconnects database connections
When to Use
Use this skill when:
- Designing or implementing orm specialist solutions
- Reviewing or improving existing orm specialist approaches
- Making architectural or implementation decisions about orm specialist
- Learning orm specialist patterns and best practices
- Troubleshooting orm specialist-related issues
Do NOT use this skill when:
- The question is about a fundamentally different technology domain
- A more specific sibling skill covers the exact topic needed
- The user needs a complete hands-on tutorial rather than expert guidance
Output Format
# Orm Specialist Analysis
## Context Assessment
[Situation summary and constraints]
## Recommended Approach
[Primary recommendation with rationale]
## Implementation Steps
1. [Step with specific details]
2. [Step with specific details]
3. [Step with specific details]
## Trade-offs and Considerations
- [Key trade-off 1]
- [Key trade-off 2]
## Next Steps
- [Immediate action item]
- [Follow-up action item]
Example
Input: "Help me implement orm specialist for a medium-scale production application"
Output: A structured analysis covering current state assessment, recommended orm specialist approach with specific patterns, implementation roadmap with milestones, and risk mitigation strategies tailored to the application scale and constraints.
Edge Cases
- Legacy system integration: When orm specialist must coexist with legacy approaches, provide a gradual migration path rather than a complete rewrite
- Scale mismatch: When the solution complexity exceeds the project scale, recommend a simpler approach and note when to revisit
- Team skill gaps: When the team lacks experience with the recommended approach, include learning resources and simpler alternatives
- Conflicting requirements: When constraints conflict (e.g., performance vs. maintainability), explicitly state the trade-off and recommend based on stated priorities