Why I don't use ORMs

September 04, 2024

After years of working with various JavaScript ORMs like Prisma, Drizzle, and Sequelize, I've settled on using Kysely, a type-safe SQL query builder for TypeScript. In this article, I'll explain why I made this choice and explore the trade-offs between ORMs and query builders.

What are ORMs?

Object-Relational Mapping (ORM) tools are libraries that help developers work with databases using object-oriented programming concepts. They abstract away the SQL and let you interact with your database using your programming language's native syntax.

Here's an example using Prisma to fetch a user with their related posts:

const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: {
    posts: {
      where: { published: true },
      include: {
        comments: true,
      },
    },
  },
});

Query Builders: A Different Approach

Query builders, on the other hand, provide a programmatic way to construct SQL queries while staying closer to the actual SQL syntax. They offer type safety and autocompletion without abstracting away the underlying SQL concepts.

Here's the same query using Kysely:

const userWithPosts = await db
  .selectFrom("user")
  .where("id", "=", 1)
  .innerJoin("post", "post.userId", "user.id")
  .innerJoin("comment", "comment.postId", "post.id")
  .where("post.published", "=", true)
  .selectAll()
  .execute();

The Good Parts of ORMs

1. Easy to Get Started

ORMs provide a gentle learning curve for developers who aren't comfortable with SQL. They offer intuitive APIs that feel natural to use within your programming language.

2. Automatic Schema Management

One of the strongest features of modern ORMs is their schema management. For example, Prisma allows you to define your database schema in a declarative way:

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

You can then use commands like prisma db push or prisma db pull to sync your database schema.

3. Automatic Relation Handling

ORMs excel at handling relationships between tables. Here's how easy it is to fetch nested data with Prisma:

const users = await prisma.user.findMany({
  include: {
    posts: {
      include: {
        comments: true,
      },
    },
  },
});

4. SQL Knowledge Optional

For developers who aren't comfortable with SQL or are just starting out, ORMs provide a way to work with databases without needing to learn SQL syntax.

The Problems with ORMs

1. Learning Curve Paradox

While ORMs are supposedly easier to use, you end up having to learn their specific syntax and conventions. Instead of learning SQL - a standard that works across different databases and tools - you're learning a tool-specific abstraction.

2. Schema Definition Overhead

You need to learn and maintain schema definitions in the ORM's specific format. Here's an example of what you need to learn with Prisma:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  profile   Profile?
  posts     Post[]
  // Need to learn all these special annotations
  createdAt DateTime @default(now()) @map("created_at")
  @@map("users")
}

3. Performance Issues

ORMs often introduce significant overhead. Prisma, for example, requires binary executables to run on the host machine. With large databases, this can lead to slower TypeScript compilation and runtime performance issues. A recent discussion on Reddit highlighted how Prisma's type generation can become extremely slow with large schemas.

4. Limited Query Control

Perhaps the most concerning issue is the lack of control over the final SQL query. At the time of writing this article, Prisma doesn't support proper JOIN operations. Instead, it performs multiple separate queries and joins the results in memory - a practice that can lead to serious performance issues at scale.

For example, a seemingly simple query like this:

const result = await prisma.user.findMany({
  include: {
    posts: true,
    profile: true,
  },
});

Actually generates multiple separate SQL queries instead of using JOINs.

5. Edge Compatibility Issues

As more applications move to edge computing, ORM compatibility becomes crucial. Prisma, for instance, isn't edge-compatible unless you use their Prisma Accelerate service, which adds both complexity and cost to your application.

Why I Chose Kysely

After experiencing these limitations, I switched to Kysely. It offers:

  • Full type safety
  • Native SQL-like syntax that's easy to understand if you know SQL
  • Direct control over query generation
  • Better performance without extra runtime dependencies
  • Edge compatibility out of the box

The learning curve might be steeper initially if you don't know SQL, but I believe it's worth investing in SQL knowledge rather than learning ORM-specific abstractions that might change or become obsolete.

Conclusion

While ORMs like Prisma offer convenience and ease of use, especially for smaller projects, their limitations become more apparent as your application grows. By using a query builder like Kysely, you get the benefits of type safety and a good developer experience while maintaining control over your database queries and performance.

Remember, the best tool depends on your specific needs. If you're building a small application and want to move fast, an ORM might be the right choice. But for applications that need to scale or require fine-grained control over database operations, consider using a query builder instead.