Add the ability to map a query's result using the query builder

[L-Mario564]

Hello everyone. I'm here to propose a feature that could enhance the query builder without changing the core API.
What I propose is that the query builder should expose some way for the developer to transform the result of a query after it has already been mapped, meaning that the underlying SQL is not changed at all. I'll provide an example so you can get a better idea as to how this would work:

const user = await db
  .select()
  .from(users)
  .where(eq(users.id, 1))
  .$transform((result) => result[0]);

In the above example, we're transforming the query's result via the $transform method, which accepts a callback that, in this case, serves the purpose of returning only the first row. This doesn't change anything at the database level, as the callback function would be called after the query is executed and the result is mapped by the ORM.

Currently, you'd have to do something like this to achieve the same thing:

const results = await db
  .select()
  .from(users)
  .where(eq(users.id, 1));
const user = results[0];

The example is very simple, therefore, the difference is barely noticeable and you could definitely argue that the above is more readable than the first snippet, but what about more advanced examples? What if we wanted to apply multiple transformations? Abstracting the callback into reusable functions?

Here's a slightly more advanced example:

function findFirstOrThrow<T extends Record<string, unknown>[]>(result: T): T[number] {
  if (!result[0]) {
    throw Error('No records were found');
  }
  return result[0];
}

function mapUserProfilePicUrl<T extends InferSelectModel<typeof users>>(user: T): T & { profilePicUrl: string } {
  return {
    ...user,
    profilePicUrl: `https://assets.domain.com/profile-pic/${user.id}`
  };
}

// Do this
const user = await db
  .select()
  .from(users)
  .where(eq(users.id, 1))
  .$transform(findFirstOrThrow)
  .$transform(mapUserProfilePicUrl);

// Instead of this
const user = mapUserProfilePicUrl(
  findFirstOrThrow(
    await db
      .select()
      .from(users)
      .where(eq(users.id, 1))
  )
);

findFirst and findFirstOrThrow are commonly requested features in the core API of Drizzle and this would be a nice way to incorporate those, with the added bonus of letting the user customize the behavior of said functions, and of course, you can do a lot more advanced things with this feature.

function mapCount<T extends Record<string, unknown>[]>(result: T): number {
  return result[0].count;
}

function findFirstOrThrow(errMessage: string) {
  return <T extends Record<string, unknown>[]>(result: T): T[number] => {
    if (!result[0]) {
      throw Error(errMessage);
    }
    return result[0];
  };
}

function mapOneToMany<OneKey extends string, ManyKey extends string>(oneKey: OneKey, manyKey: ManyKey) {
  return <
    One extends Record<string, unknown>,
    Many extends Record<string, unknown>,
    T extends (Record<OneKey, One> & Record<ManyKey, Many>)[]
  >(result: T): T[number][OneKey] & Record<ManyKey, T[number][ManyKey][]> => {
    const one = result[0][oneKey];
    const many = result.map((row) => row[manyKey]);
    const mapped: Record<string, unknown> = one;
    mapped[manyKey] = many;
    return mapped as any;
  }
}

// Returns: number
const userCount = await db.select({ count: count() }).from(users).$transform(mapCount);

// Returns: { username: string }
const user = await db
  .select({ username: users.username })
  .from(users)
  .where(eq(users.id, 1))
  .$transform(findFirstOrThrow('User with ID of 1 doesn\'t exist'));

/* 
 * Returns: {
 *   username: string;
 *   posts: {
 *     title: string;
 *   }[];
 *  }
 */
const userWithPosts = await db
  .select({
    user: {
      username: users.username
    },
    posts {
      title: posts.title
    }
  })
  .from(users)
  .innerJoin(posts, eq(posts.postedById, users.id))
  .where(eq(users.id, 2))
  .$transform(mapOneToMany('user', 'posts'))

Not the best writer here, so apologies if the wording is a little weird, but hope that the examples were enough to get my point across. Would like to know what the Drizzle community and team think about this enhancement. I can also to file a PR that implements this feature if this is something that the community wants.

[Angelelz]

This is already possible using the .then() method on all the query builders.
Maybe you'll like to open a documentation issue to raise awareness about this pattern.

[L-Mario564]

Good idea. Should I open a PR on the docs repo? I'm willing to redact a section that lets the user know that the query builders extend/implement Promise and letting them know how they can use that to their advantage (like the examples written above, but of course, using the then method).

[Angelelz]

I would just create an issue for now, just to see if the drizzle team are on board.

[L-Mario564]

Issue created: drizzle-team/drizzle-orm-docs#183

[brestmoor]

Hey! Is something like this possible using relational queries but in joins? lets say my query is
db.query.chapter.findFirst({ with: {subchapters: true} })
and I want to apply some mapping on subchapters (select only the first subchapter or add a new field).
I noticed I can add then at the end of the query, but my original query consists of multiple joins, and that would cause me to rewrite and modify the big resulting object. Instead I am looking to do this at join level something like:
db.query.chapter.findFirst({ with: {subchapters: true, then: (s) => s[0]} })