Prisma Workarounds

Prisma is an ORM allowing you to connect to database in a type-safe and easy manner without having to write any explicit SQL. And it works really well for doing normal CRUD operations and even for advanced scenarios. However there are couple of scenarios where it currently lacks supports and requires workarounds. This blog post goes over scenarios which are currently not supported by Prisma and highlights possible workarounds without having to resort to raw sql.

Content

• Aggregating Data from Relation
• Aggregation Query with Joins
• Next Steps

Aggregating Data from Relation

When building dashboards with stats/charts or in general when you want to show stats for related tables, it is common to aggregate data based on join. Lets consider a simple example. Lets say you have two tables

• Widget => List of different widget
• Item => Actual inventory of widgets
• ItemCategory => List of item categories

If you want to show list of widget and for each, show count of items for that particular widget, Prisma has you covered and you can use _count operator to get number of items for a given widget

const widgetsWithItemsCount = await prisma.widget.findMany({
  include: {
    _count: {
      select: { items: true },
    },
  },
});

const widgetsWithItemsCount = await prisma.widget.findMany({
  include: {
    _count: {
      select: { items: true },
    },
  },
});

But if you want to sum cost of carrying inventory by widget, there is no equivalent _sum. It is quite straight forward in sql

SELECT widget.widgetId, widget.widgetName, (SELECT SUM(item.cost) FROM item WHERE item.widgetId = widget.widgetId)
FROM widget

SELECT widget.widgetId, widget.widgetName, (SELECT SUM(item.cost) FROM item WHERE item.widgetId = widget.widgetId)
FROM widget

Simplest way to achieve this currently in Prisma without another query is to include items as part of original query and then just aggregate using code.

const widgetsWithItems = await prisma.widget.findMany({
  include: {
    items: true,
  },
});
for (const widget of widgetsWithItems) {
  widget.cost = widget.items.reduce((p: number, c: { cost?: number }) => p + (c.cost ?? 0), 0);
}

const widgetsWithItems = await prisma.widget.findMany({
  include: {
    items: true,
  },
});
for (const widget of widgetsWithItems) {
  widget.cost = widget.items.reduce((p: number, c: { cost?: number }) => p + (c.cost ?? 0), 0);
}

Above work around is simple, has advantage of not requiring any more round trip to database and works well if you have a limited set of items for a given widget. If you have lots of child items, it might be more performant to use $queryRaw.

Aggregation Query with Joins

It is common in a relation database to do a join to a look-up table or a parent table when aggregating data, particularly useful to dereference foreign keys and show user readable labels. However, currently Prisma does not support relation/join on aggregation (group by) queries.

Lets take same widget and item tables. Lets say you want to aggregate item cost by some grouping of item category. You would normally have an itemCategory table and the item model with have a foreign key "itemCategoryId" from the itemCategory table.

Lets first do simple group by query

const data = await prisma.item.groupBy({
  by: ['itemCategoryId'],
  _sum: { cost: true },
});

const data = await prisma.item.groupBy({
  by: ['itemCategoryId'],
  _sum: { cost: true },
});

Above will give you a list of item cost by category. But you also need item category name, to show useful data to the user. In order to get the category name, we will have to do another query

let itemCategoryIdList = data.map((i) => i.itemCategoryId);
const itemCategoryIdLookup = await prisma.itemCategory.findMany({
  select: {
    itemCategoryId: true,
    categoryName: true,
  },
  where: {
    itemCategoryId: { in: itemCategoryIdList },
  },
});

let itemCategoryIdList = data.map((i) => i.itemCategoryId);
const itemCategoryIdLookup = await prisma.itemCategory.findMany({
  select: {
    itemCategoryId: true,
    categoryName: true,
  },
  where: {
    itemCategoryId: { in: itemCategoryIdList },
  },
});

And now "join" them in code

const rows = data.map((d) => ({
  categoryName: itemCategoryIdLookup.find((l) => l.itemCategoryId === d.itemCategoryId)?.categoryName,
  categoryCost: d._sum.cost,
}));

const rows = data.map((d) => ({
  categoryName: itemCategoryIdLookup.find((l) => l.itemCategoryId === d.itemCategoryId)?.categoryName,
  categoryCost: d._sum.cost,
}));

This gives you category name and cost for each category! This incurs addition database round trip and requires more code, but advantage is that you are not introducing multiple approaches (Prisma and SQL) and code is easy to maintain. But it won't work for all cases and in particular for large data sets, it might be more performant to go with $queryRaw route and include join in SQL itself.

SELECT itemCategory.categoryName, sum(item.cost)
FROM itemCategory
INNER JOIN item ON itemCategory.itemCategoryId = item.itemCategoryId
GROUP BY itemCategory.categoryName

SELECT itemCategory.categoryName, sum(item.cost)
FROM itemCategory
INNER JOIN item ON itemCategory.itemCategoryId = item.itemCategoryId
GROUP BY itemCategory.categoryName

Next Steps

Hopefully you have better understanding of Prisma capabilities and have an handle on how to implement work arounds for few of common scenario's that Prisma currently does not support.

RemixFast

RemixFast allows you to visually build app and will auto generate appropriate Prisma queries as well as auto generating work around code! To see generated code for yourself, follow How to Build Mint tutorial and have a look at building Dashboard.