Moving to Drizzle and back

In the ever-evolving world of software development, exploring new tools and technologies is essential. As a seasoned software engineer, I’ve always advocated for trying new approaches. Recently, I decided to put my own advice into practice by experimenting with Drizzle, an alternative to my usual ORM of choice.

My primary requirements for an ORM are straightforward: excellent developer experience, a clean API for queries and mutations, and robust TypeScript support. Unfortunately, Drizzle fell short of these expectations, particularly in one crucial use case.

Consider a simple schema with users and groups:

export const users = mysqlTable("users", {
  id: varchar("id", { length: 256 }).primaryKey(),
  name: varchar("name", { length: 256 }).notNull(),
});

export const groups = mysqlTable("groups", {
  id: varchar("id", { length: 256 }).primaryKey(),
  name: varchar("name", { length: 256 }).notNull(),
});

I opted for varchar as the id type, generating UUIDs in the application rather than relying on database-generated ids. This approach sidesteps potential issues with MySQL’s LAST_INSERT_ID() function in serverless environments.

Creating a many-to-many relationship between these tables in Drizzle requires manual creation of a junction table:

export const usersToGroups = mysqlTable(
  "users_to_groups",
  {
    userId: varchar("user_id")
      .notNull()
      .references(() => users.id),
    groupId: varchar("group_id")
      .notNull()
      .references(() => groups.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  }),
);

// Relation definitions omitted for brevity

While this setup seems reasonable, the API for querying related data leaves much to be desired. Fetching a single user with their associated group requires a convoluted query structure:

await db.query.users.findFirst({
  with: {
    groups: {
      with: {
        group: true,
      },
      where: eq(usersToGroupsRelations.userId, input.id),
    },
  },
  where: eq(usersToGroupsRelations.id, input.id),
});

The resulting data structure is equally cumbersome:

{
  "id": "XRdUD20AfH422nXKI72HW",
  "name": "test",
  "groups": [
    {
      "userId": "XRdUD20AfH422nXKI72HW",
      "groupId": "_VQnVW4cZj4HvnCC1W9Lr",
      "group": {
        "id": "_VQnVW4cZj4HvnCC1W9Lr",
        "name": "sdf",
        "description": "sdf",
        "quantity": 0,
        "unit": "kg"
      }
    }
  ]
}

This experience led me back to Prisma, which offers a more intuitive API and cleaner data structures for managing relationships. While Drizzle may have its merits, it failed to provide the seamless developer experience I sought in this particular scenario.

In the end, this journey reinforced the importance of thoroughly evaluating tools against specific use cases before committing to a switch. Sometimes, the grass isn’t always greener on the other side, and there’s value in recognizing when to stick with a tried-and-true solution.