[FEATURE]: API for to 'flatten' response shapes when querying data from complex schemas and their relations.

[loicleray]

Describe what you want

Overview / Explanation

Default aggregation behaviour of Drizzle Queries is to provide nested response objects with the table name of the relation as the key, and an object of the information it contains as it's value.

This makes sense when a project is simple, but is quite cumbersome when dealing with many relations or complex schemas; especially where a schema has many-to-many relations (via join tables). Accessing the data from deeply nested and repeated objects is inefficient / unintuitive.

One-to-one, one-to-many relationships are easier to follow with simple schemas - but messy to access when there are a lot of columns in a table.

Responses can be modified with custom functions that remap responses into the desired shape. Though requires getting more data than needed from the database, then reducing it with JS. That process defeats the stated purpose of drizzle queries:

Relational queries are meant to provide you with a great developer experience for querying nested relational data from an SQL database, avoiding multiple joins and complex data mappings. - Drizzle Docs

Alternatively, you could just provide raw SQL and call it a day. But accessing aggregated related data in a flattened response is a common enough process to warrant a quality of life way of doing it...

This issue has been mentioned previously (here, here, here, here). Purpose of raising this issue is to have a definitive response from the drizzle team and provide work around solutions for those experiencing similar frustrations.

---

Example Schemas

Note using the drizzle postgres adapter in the examples below. Minor modifications necessary if you're using SQLite, etc.

Build Schemas

import {
  pgTable,
  text,
  integer,
  serial,
  primaryKey,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// Define Schema for Users
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  username: text('username').notNull().unique(),
});

// Define Schema for Posts
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  userId: integer('user_id')
    .notNull()
    .references(() => users.id),
  title: text('title').notNull(),
  content: text('content').notNull(),
});

// Define Schema for CategoryTags
export const categoryTags = pgTable('category_tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull().unique(),
});

// Define Schema for join table "posts_to_category_tags"
export const postToCategory = pgTable(
  'posts_to_category_tags',
  {
    postId: integer('post_id')
      .notNull()
      .references(() => posts.id),
    categoryTagId: integer('category_tag_id')
      .notNull()
      .references(() => categoryTags.id),
  },
  // Composite Primary Key on join table
  (t) => ({
    pk: primaryKey({ columns: [t.postId, t.categoryTagId] }),
  })
);

Define Schema Relations

// Define relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
  categories: many(postToCategory),
}));

export const categoryTagsRelations = relations(categoryTags, ({ many }) => ({
  posts: many(postToCategory),
}));

export const postToCategoryRelations = relations(postToCategory, ({ one }) => ({
  post: one(posts, {
    fields: [postToCategory.postId],
    references: [posts.id],
  }),
  category: one(categoryTags, {
    fields: [postToCategory.categoryTagId],
    references: [categoryTags.id],
  }),
}));

Instantiate your db

// Get db instance, instantiate drizzle db...
const connection = //...your connection
const db = drizzle(connection,
// ... your schema(s) and options)
);

---

Ways of fetching "posts" data and shapes of responses

Option 1 - Default nested querying

Explanation: Least complex method, least intuitive result.

// Getting responses from the database.
export async function fetchPostsOption1() {
  try {
    const dbResponse = await db.query.posts.findMany({
      with: {
        author: true,
        categoryTags: { with: { categoryTag: true } },
      },
    });

    return dbResponse;
  } catch (error) {
    console.error('Error fetching post.', error);
    // handle error...
    return [];
  }
}

It is annoying to access items in response in this shape.

userId and author refer to the same user, effectively duplicating content references despite; one-to-many (one author to many posts).

Similar is true for many-to-many via a join table. The categoryTags is an array of objects which contain a postId, categoryTagId, the categoryTag key and its object value.

// Shape of response from fetchPostsOption1()
[
  {
    "id": 1,
    "userId": 101,
    "title": "The Art of Programming",
    "content": "Programming is both a science and an art. It requires logical thinking and creative problem-solving...",
    "author": {
      "id": 101,
      "username": "codeMaster"
    },
    "categoryTags": [
      {
        "postId": 1,
        "categoryTagId": 201,
        "categoryTag": {
          "id": 201,
          "name": "Technology"
        }
      },
      {
        "postId": 1,
        "categoryTagId": 202,
        "categoryTag": {
          "id": 202,
          "name": "Programming"
        }
      }
    ]
  }
  //   ... other posts
]

Option 2

Explanation: Slightly reduce duplicates with a more complex query.

// To get properly nested data for getting many-to-many data.
export async function fetchPostsOption2() {
  try {
    const dbResponse = await db.query.posts.findMany({
      with: {
        author: true,
        categoryTags: {
          columns: {},
          with: {
            categoryTag: {
              columns: {
                id: true,
                name: true,
              },
            },
          },
        },
      },
    });
    return dbResponse;
  } catch (error) {
    console.error('Error fetching post.', error);
    // handle error
    return [];
  }
}

userId and author still have duplicate content, but the categoryTags is an array of objects which categoryTag key and the object itself. There is no longer a postId, categoryTagId in each of the categoryTags object.

Still annoying to access content in a response of this shape, but reduces references to the join table (removes postId and categoryTagId).

[
  {
    "id": 1,
    "userId": 101,
    "title": "The Art of Programming",
    "content": "Programming is both a science and an art. It requires logical thinking and creative problem-solving...",
    "author": {
      "id": 101,
      "username": "codeMaster"
    },
    "categoryTags": [
      {
        "categoryTag": {
          "id": 201,
          "name": "Technology"
        }
      },
      {
        "categoryTag": {
          "id": 202,
          "name": "Programming"
        }
      }
    ]
  }
  //   ... other posts
]

Option 3

Explanation: Most intuitive response, most complex fetchFunction() which transforms database response and reshapes it with javascript = which is less efficient than getting data from a single database call.

// To get properly nested data for getting many-to-many data.
export async function fetchPostsOption3() {
  try {
    const dbResponse = await db.query.posts.findMany({
      with: {
        author: true,
        categoryTags: { with: { categoryTag: true } },
      },
    });

    // Transform the results to match the desired format
    const transformedResults = dbResponse.map((post) => ({
      ...post,
      categoryTags: post.categoryTags.map((jct) => jct.categoryTag),
    }));
    return transformedResults;
  } catch (error) {
    console.error('Error fetching post.', error);
    // handle error
    return [];
  }
}

The categoryTags is an array of categoryTag objects. There is no longer nested and duplicated references to the categoryTag objects. userId and author still have duplicate content, but could also be fixed with the transform.

Far less annoying to access content in a response of this shape, but less efficient than if response was to be collected from SQL directly. Javascript is reshaping data after getting it from the database.

[
  {
    "id": 1,
    "userId": 101,
    "title": "The Art of Programming",
    "content": "Programming is both a science and an art. It requires logical thinking and creative problem-solving...",
    "author": {
      "id": 101,
      "username": "codeMaster"
    },
    "categoryTags": [
      {
        "id": 201,
        "name": "Technology"
      },
      {
        "id": 202,
        "name": "Programming"
      }
    ]
  }
  //   ... other posts
]

---

Feature request to fix behaviour.

Suggesting a feature to be able to override the default behavior of drizzle queries that access related tables to avoid duplicate references to the same object. Solutions would provide a "flattened" object.

Using the example schemas above, the ideal response would have the following shape:

[
  {
    "id": 1,
    "author": {
      "id": 101,
      "username": "codeMaster"
    },
    "title": "The Art of Programming",
    "content": "Programming is both a science and an art. It requires logical thinking and creative problem-solving...",
    "categoryTags": [
       {
          "id": 201,
          "name": "Technology"
        }
      {
          "id": 202,
          "name": "Programming"
        }
    ]
  },
      // ...other posts
]

Drizzle team would be better suited to answering this, but some API modification to get data in the shape above would be awesome - ideally straight from the database in one step.

export async function fetchPostsFlatResponse() {
  try {
    const dbResponse = await db.query.posts.findMany({
    //   "flatwith:" instead of "with:"
      flatwith: {
        author: true,
        categoryTags: { with: { categoryTag: true } },
      },
    });
  }
}

I may be overlooking existing solutions or misunderstanding some aspects of Drizzle ORM. If I am mistaken or you have a cleaner approach to this issue, I'd be incredibly grateful if you shared it.

Thank you for your hard work, Drizzle Team!

[kedom1337]

This issue perfectly outlines what I have been looking for as well. I have been messing with a few ORM's and ergonomically transforming relational queries into a nested presentation seems to be something they all struggle with. Dirzzle ORM is already really good with that. Adding this flatwith functionality would be the cherry on top.

[henrikwirth]

Totally agree with this, I had a hard time to understand what was going on with relations in general for many-to-many. Some more documentation would be very helpful. Especially since there is no Infer functions in place yet for queries with relations.