ORMs vs Raw SQL + DAL in TypeScript/NestJ
or: Abstractions, reality, and where things break
ORMs vs Raw SQL + DAL in TypeScript/NestJS: Abstractions, Reality, and Where Things Break
It is difficult to build a backend in TypeScript/NestJS without stumbling into the ORM vs raw SQL debate. On one side, object-relational mappers promise clean models, migrations, and less boilerplate. On the other, a thin Data Access Layer over raw SQL promises honesty, control, and predictable performance.
Both camps are very sure of themselves. Reality, as usual, is messier.
The interesting part isn’t “which is better” in the abstract. The interesting part is where each approach starts to fail, and what that says about how teams should think about databases, architecture, and developer competence.
The comfort of ORMs: entities, decorators, and the illusion of simplicity
In the NestJS world, ORMs feel natural. Decorators, metadata, and classes are everywhere, so mapping database tables to TypeScript models seems almost inevitable.
A typical setup might look like this using a classic ORM class-with-decorators model:
@Entity()
export class User {
@PrimaryGeneratedColumn('uuid')
id!: string;
@Column({ unique: true })
email!: string;
@Column({ name: 'password_hash' })
passwordHash!: string;
@Column({ default: true })
active!: boolean;
@OneToMany(() => Session, (s) => s.user)
sessions!: Session[];
@ManyToMany(() => Role, (r) => r.users)
@JoinTable()
roles!: Role[];
}
@Entity()
export class Session {
@PrimaryGeneratedColumn('uuid')
id!: string;
@ManyToOne(() => User, (u) => u.sessions)
@JoinColumn({ name: 'user_id' })
user!: User;
@Column({ name: 'last_login', type: 'timestamp' })
lastLogin!: Date;
}
@Entity()
export class Role {
@PrimaryGeneratedColumn('uuid')
id!: string;
@Column({ unique: true })
name!: string;
@ManyToMany(() => User, (u) => u.roles)
users!: User[];
}
Then a service does something like:
export const findActiveUsersWithSessionsAndRoles = async(
userRepo: Repository,
) => {
return userRepo.find({
where: { active: true },
relations: ['sessions', 'roles'],
});
};
From a developer’s perspective this is pleasant. There is a shared domain language: User, Session, Role. There’s a single place to look for properties and relations. Refactors are guided by TypeScript types. Tooling can plug into the model. A new developer can navigate the code without deciphering SQL right away.
For a lot of applications, this is enough. Internal tools, dashboards, small SaaS products with modest data volume—the ORM does exactly what it says on the tin. It removes a mountain of glue code and lets the team ship.
There are real benefits here. ORMs make schema evolution and migrations feel coherent: they usually ship with their own migration generators, schema diffing, and CLI tooling. But that doesn’t mean migrations are an ORM-only feature. Libraries like pg-migrate, node-pg-migrate, or umzug exist specifically to bring structured migrations to raw SQL setups as well. The capability isn’t unique to ORMs; ORMs just bundle it.
Similarly, ORMs don’t have a monopoly on validation. A stack using zod or a similar schema library can validate data before and after hitting the database, independently of whether the query was built by an ORM or hand-written. In a raw SQL + DAL setup, it’s entirely reasonable to validate input payloads, domain objects, and even query results against Zod schemas.
This is closer to the Angular vs React discussion than it looks at first glance. ORMs are Angular-like: batteries included, highly opinionated. Raw SQL + DAL is more React-like: you assemble the router, state management, and data layer from focused packages. Nobody claims React “doesn’t have a router” just because it’s not built into react itself. In the same way, it’s not accurate to say that raw SQL “doesn’t have migrations,” “doesn’t have validation,” or “doesn’t have transactions” just because those concerns aren’t wrapped in a single ORM package.
For the bulk of routine CRUD work, the convenience of an ORM is a legitimate win. The trouble appears when relational complexity grows.
Databases are not code, and SQL is not an implementation detail
A relational database is not an object store that happens to use tables. It is a query engine built around sets, predicates, and joins. It operates on a different mental model than OOP code.
This matters the moment the needs move beyond “give me this entity and its relations.”
Consider a query like: “Fetch all active users, with their last login timestamp, their active subscription, and a count of unread notifications.” Someone reaching for the ORM might try:
export const getActiveUsersWithMeta = async(
userRepo: Repository,
) => {
return userRepo.find({
where: { active: true },
relations: ['sessions', 'subscriptions', 'notifications'],
});
};
It looks harmless. It is not.
A relational engine doesn’t think “load these arrays onto a User object.” It thinks in terms of join graphs, cardinality, and result sets. Depending on the ORM, that single call might generate a massive multi-join query, or a series of follow-up queries, or an N+1 pattern hidden behind a nice API.
The developer wanted “some data for some users.” The database sees “here’s a cartesian mess with inconsistent filters and no clear intent.”
The underlying problem is conceptual. The entity model assumes that each user “has” sessions, “has” subscriptions, “has” notifications. That model breaks down when the query is actually about relationships between sets: latest session per user, at most one active subscription, and aggregate counts of unread notifications.
Relational logic fits SQL. It does not fit the mental model of “user object with arrays hanging off it” nearly as well.
In SQL, the same query might look more like this:
SELECT
u.id,
u.email,
s.last_login AS "lastLogin",
sub.id AS "subscriptionId",
COALESCE(n.unread_count, 0) AS "unreadCount"
FROM users u
LEFT JOIN LATERAL (
SELECT last_login
FROM sessions
WHERE user_id = u.id
ORDER BY last_login DESC
LIMIT 1
) s ON true
LEFT JOIN subscriptions sub
ON sub.user_id = u.id
AND sub.status = 'active'
LEFT JOIN (
SELECT user_id, COUNT(*) AS unread_count
FROM notifications
WHERE read = false
GROUP BY user_id
) n ON n.user_id = u.id
WHERE u.active = true;
This is not “prettier,” but it is honest. It expresses the actual shape of the problem. The database can reason about it. The developer can read it, index it, explain it.
Once the data access layer is built around entities instead of queries, this kind of thinking tends to show up late, usually under load, usually accompanied by slow query logs and confusion about what the ORM is doing.
Hidden complexity: when ORMs shield the wrong things
One argument often made in favor of ORMs is that they “protect” developers who don’t know SQL very well. Instead of writing complex queries, they can compose high-level methods with type safety and documentation.
In practice, ORMs don’t remove complexity. They move it.
The complexity of joins, cardinality, indexes, and query plans doesn’t disappear. It is still there, encoded in generated SQL that nobody wrote and few people ever read. When performance problems finally surface, the team is forced to debug that SQL anyway—but now they are working backwards from an abstraction that hides what actually happened.
The hardest situation is a backend developer who does not understand SQL, debugging ORM-generated SQL in production, under pressure. The abstraction that was supposed to protect them has done the opposite: it delayed the moment when understanding SQL becomes inevitable.
Databases are not “just storage.” For most backends, the database is the central piece of infrastructure. Treating SQL as an optional specialization rather than a core skill leaves teams flying blind.
It’s one thing if a junior developer cannot write a perfect query on day one. It’s another if the team culture never expects backend engineers to understand basic relational thinking at all.
Why teams still reach for ORMs anyway
Despite all of this, ORMs remain extremely popular—and not just because of inertia or ignorance.
They solve real problems that raw SQL + DAL does not solve automatically.
There is the issue of schema evolution: adding columns, renaming fields, migrating data, keeping dev/stage/prod in sync. ORMs bring migrations, schema tracking, and often a coherent story for how code and schema move together. Raw SQL stacks can do the same thing using migration tools like pg-migrate or umzug, but someone has to choose, wire, and enforce them.
There is the issue of consistency. An ORM encourages uniform ways to load data, to handle transactions, to apply tenant filters, to enforce soft deletes, to do audit logging. Cross-cutting policies can be expressed centrally with hooks, decorators, or middleware. A DAL can do this too, but only if someone designs it with that discipline from day one and keeps it that way over years.
There is the issue of team composition. In many organizations, not every backend developer has real depth in SQL. Expecting everyone to write raw SQL for every operation can work in a highly senior team with strong database culture. In an average team, the risk of data corruption and quietly bad queries is non-trivial.
And there is the ergonomics question. For the majority of day-to-day CRUD operations, an ORM genuinely improves productivity. It handles mapping, hydration, parameter binding, type conversions, and common patterns so developers can focus on business rules. The fact that it can fall over on the 10% of harder queries does not negate the fact that it handles a large amount of routine work well.
Ignoring those benefits doesn’t make for honest engineering.
The architectural fracture line
The real tension doesn’t show up in “simple CRUD vs complex query.” It shows up at the architectural level.
An ORM pushes systems toward an entity-centric view of the world. Services, controllers, and even API responses are built around entity models and their relations. Over time, the domain model in code drifts to reflect what the ORM is good at, rather than what the business needs.
Data flows become “whatever the ORM returns” instead of “what the use case actually needs.” Entities start leaking into all layers. Circular reference chains appear. Performance tuning turns into “tweak the relations and loading strategies until it looks OK” instead of “design a query for this specific access pattern.”
A DAL built around raw SQL tends to pull the architecture in the opposite direction. Instead of starting from “User entity has many Sessions,” it starts from “this use case needs this shape of data with these guarantees.” The SQL expresses that shape explicitly. The DAL function becomes the contract.
In NestJS, that might look like this:
export type UserOverviewRow = {
id: string;
email: string;
roleCount: number;
lastLogin: Date | null;
};
export const getUserOverview = async (
db: DbClient,
userId: string,
): Promise => {
return db.queryOne(
`
SELECT u.id,
u.email,
COUNT(r.*) AS "roleCount",
s.last_login AS "lastLogin"
FROM users u
LEFT JOIN user_roles r ON r.user_id = u.id
LEFT JOIN LATERAL (
SELECT last_login
FROM sessions
WHERE user_id = u.id
ORDER BY last_login DESC
LIMIT 1
) s ON true
WHERE u.id = $1
GROUP BY u.id, s.last_login
`,
[userId],
);
};
A service calls getUserOverview, consumes a well-defined shape, and returns an API response. There is no argument about whether the User model “should” know its own role count or its last login. The query is written for this use case. The database planner can optimize it as a single query. The mental model matches the actual SQL.
You can push this further with validation:
const UserOverviewSchema = z.object({
id: z.string().uuid(),
email: z.string().email(),
roleCount: z.number().int().nonnegative(),
lastLogin: z.date().nullable(),
});
export const safeGetUserOverview = async(
db: DbClient,
userId: string,
) => {
const row = await getUserOverview(db, userId);
if (!row) return null;
return UserOverviewSchema.parse(row);
};
Now the boundary is explicit: SQL expresses the data shape, Zod enforces it, and the calling code gets a validated object. No ORM required, but the safety net is comparable.
This approach feels more tedious early on. There is no global entity model to lean on. There is no magical “just add a relation” trick. But it scales in a different way: each query is honest, explicit, and explainable.
Competence, not ideology
The discussion often degenerates into ideology: “ORMs are trash” vs “SQL is legacy.” That misses the actual dividing line.
The real question is: what skills does the team have, and what constraints does the system face?
A team that cannot read a query plan and has never thought about transaction isolation will struggle with handwritten SQL in subtle, dangerous ways. In that environment, an ORM truly can act like a safety harness. Not because it never makes mistakes, but because it narrows the set of mistakes developers are likely to make.
A team comfortable with SQL, indexing, and relational design, on the other hand, pays a very different price for using an ORM. In that environment, the abstraction is not primarily protecting anyone. It is getting in the way of the work they actually want to do: designing precise, efficient queries and keeping the database behavior transparent.
The database itself does not care which camp the team belongs to. It will execute whatever it is given. The question is whether the team can predict, reason about, and improve what the database is doing. That is fundamentally a SQL competence question, not a framework choice.
Where the argument quietly lands
Look at enough real systems and a pattern appears.
Many teams start with an ORM, enjoy the convenience, and then gradually carve out escape hatches: custom repositories, raw queries for performance-critical paths, specialized reporting queries that bypass the entity model entirely. The ORM remains, but the most important parts of the data access path are no longer “pure ORM.”
Other teams start with raw SQL and a homegrown DAL, then slowly re-implement ORM-like behavior: helper functions for inserting and updating, generic mapping logic, conventions around soft deletes and auditing, even small internal query builders. They discover that some level of abstraction really does help avoid glue code.
The end state is often a hybrid. That alone is revealing.
For many codebases, the fallback when things get serious is not “use more ORM.” It is “drop down to SQL.” The path rarely goes in the opposite direction.
That suggests something important: the relational model and SQL remain the ground truth. Any abstraction that pretends otherwise eventually has to yield to that reality.
A thin DAL over raw SQL acknowledges this from the beginning. It doesn’t deny the value of abstractions, but it anchors them firmly in the query layer instead of in an object model that only fits cleanly until the first real join storm rolls in.
Frameworks, ORMs, decorators, and entities all come and go. The database remains. Systems tend to age more gracefully when their data access logic speaks the database’s language directly, rather than translating everything back and forth through a worldview that was never designed for joins in the first place.
That doesn’t make ORMs useless. It just means that, when systems grow up, the side that understands SQL usually ends up in charge.