Performance concern about getAllParentsForFolder using while loop

[EricTsai83]

I have a question regarding the implementation of getAllParentsForFolder in queries.ts. Currently, it uses a while loop to retrieve all parent folders. I'm wondering if this approach could impact performance, especially when dealing with deeply nested structures.

  getAllParentsForFolder: async function (folderId: number) {
    const parents = [];
    let currentId: number | null = folderId;
    while (currentId !== null) {
      const folder = await db
        .selectDistinct()
        .from(foldersSchema)
        .where(eq(foldersSchema.id, currentId));

      if (!folder[0]) {
        throw new Error("Parent folder not found");
      }
      parents.unshift(folder[0]);
      currentId = folder[0]?.parent;
    }
    return parents;
  },

Is there a more efficient way to achieve the same result?
Is SQL CTE better for this type of query by using sql operator? Ex.QUERIES-WITH-RECURSIVE
I’d appreciate any insights or recommendations. Thanks!

[CuteNikki]

You're right that the current getAllParentsForFolder implementation uses a while loop that triggers multiple sequential queries to the database. This could definitely have a performance impact when dealing with deeply nested folder structures, especially if there are lots of folders in the hierarchy.
I think you’re spot on that a recursive CTE would be a cleaner and more efficient solution here.
This should reduce the number of round trips to the DB, especially when the folder tree gets deep.

Why it was originally done this way:
I suspect the current implementation was kept simple to make the logic easier to follow for beginners (given that this is a tutorial repo). Recursive CTEs can sometimes be intimidating for people newer to SQL, so the loop might have been a more approachable option.

That said, I'd also be curious what Theo thinks about balancing simplicity vs performance here!

[Mr-Unforgettable]

Does drizzle-orm has batching and Recursive CTE api/functionality available. I was unable to find any.

[CuteNikki]

@Mr-Unforgettable As of now, Drizzle ORM does not have a built-in batching API.
However, you can manually achieve batching by using

• transactions and chunking operations
• Writing custom bulk insert/update queries using Drizzle’s SQL utilities (sql tagged template) or using the insert().values([...]) approach for bulk inserts.

Examples:

await db.insert(users).values([
  { name: "John" },
  { name: "Jane" },
  { name: "Doe" },
]);

import { sql } from 'drizzle-orm';

const query = sql`
  WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = ${employeeId}
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON s.id = e.manager_id
  )
  SELECT * FROM subordinates;
`;

const result = await db.execute(query);

TL;DR: Recursive CTE: Not natively exposed via an abstraction, but you can use raw SQL.

[Senitram666]

I tried my own recursive CTE while following the tutorial, but I got a problem with the types.
I'm not new to SQL but I'm new to typescript, so, I made the call with DB.execute and got a response that was a tuple with rows and data. I destructured the data and got a type ResultSetHeader.
then I tried

const breadcrumbsData = (breadcrumbs[0] as typeof folders_schema.$inferSelect[]) ?? [];

but my ide was upset about making the conversion, saying that I could only convert this way by first converting the data to unknow.
I'd like to hear about what would be the right way of dealing with these types in this case

[Mr-Unforgettable]

You could make the data an unknown type first and then handle it with Type guards and Type assertions to satisfy your ide.
Here is this article I had: https://medium.com/@vinhle95/the-unknown-type-in-typescript-939ec261294b. It might give you some idea about unknown types and how to handle them.