PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Mapping Query Builder Results to Nested Objects in Drizzle

I’m transitioning to Drizzle ORM from a NestJS-TypeORM background :

  • TypeORM: Both the repository and query builder return similar structured results, making it easy to work with joined data.
  • Drizzle: db.query() returns nicely structured nested data (e.g., users and their posts grouped together). Query Builder (db.select()) returns raw, flattened data requiring additional processing to achieve a similar structure.

Example:
db.query result:

[{
    "id": 10,
    "name": "Dan",
    "posts": [
        {
            "id": 1,
            "content": "SQL is awesome",
            "authorId": 10
        },
        {
            "id": 2,
            "content": "But check relational queries",
            "authorId": 10
        }
    ]
}]

Query Builder result:

const result: {
    user: { id: number; name: string };
    pets: { id: number; name: string; ownerId: number } | null;
}[] = await db
    .select()
    .from(users)
    .leftJoin(pets, eq(users.id, pets.ownerId));

I’m looking for a mechanism or best practice to map Query Builder results into a nested format (similar to db.query) so that my API responses are consistent and clients don’t need to check different formats.

Any advice, patterns, or libraries you’d recommend for this? How do you handle this in your Drizzle-based projects?

Thanks in advance!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After I saw this discussion, I managed to write a generic function for this

    const result = await this.db.select({ 
      ...getTableColumns(this.firstSchema), 
      [this.firstFieldName]: sql'json_agg(${this.secondSchema})' 
   }) 
   .from(this.firstSchema) 
   .leftJoin(this.secondSchema, eq(this.firstSchema[this.firstFieldId], this.secondSchema.id))
   .groupBy(this.firstSchema.id)

  2. 0

    In Drizzle, db.query serves as a full phase ORM, while db.select provides a lightweight query builder. Although you may encounter some issues, it operates much faster.

    Here’s the corrected version of your query:

    import { eq, getTableColumns } from 'drizzle-orm';
await db
  .select({
    ...getTableColumns(users),
    pets: sql<{ id: number; name: string; ownerId: number }[]>`
      COALESCE(
        json_agg(
          json_build_object(
            'id', ${pets}.id,
            'name', ${pets}.name,
            'ownerId', ${pets}.ownerId
          )
        ) 
        filter (WHERE ${pets}.id IS NOT NULL), '[]'
    ` 
  })
  .from(users)
  .leftJoin(pets, eq(users.id, pets.ownerId))
  .groupBy(users.id);

    Note that db.select does not support multiple nested layers; it only allows for one nested layer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search