At Cerebrum we recently came across a tricky bug, where we would see records being repeated when paginating through a list of records.
The pagination was implemented in a simple way, using skip and take arguments to retrieve a subset of records from the database, one page at a time.
After investigating the issue, we found that the bug was happening because we were using the orderBy argument to sort the records, but the orderBy argument is not guaranteed to return the records in the same order every time. This occurs if you are sorting your results on a non-unique field, and there are multiple records with the same value for that field. In this case, the database can return the records in an arbitrary order, which can change between queries. For example, given the following records, sorting by the color alone can cause results to be returned in an inconsistent order, as the color is non-unique and the database provides no guarantees about how it will sort rows with the same value.
The most straightforward way to resolve this issue is to apply a secondary sort to the query using a unique field. This acts to “stabilize” the sort, causing the records to be returned in the same order on every query. In our example above, we have the “VIN” field, which is guaranteed to be unique. If we first sort by color, and then by VIN, we can guarantee that the order of cars we return from our database is always in the same order. We hotfixed the issue, by applying the secondary sort on the id field, which (in our schema) is guaranteed to be unique. This resolved the issue, but it was not a long term solution, as we would have to remember to apply this secondary sort to every query that uses the orderBy argument.
By adding a middleware to Prisma, we can automatically apply the secondary sort to every query that uses the orderBy argument, without having to remember to do it manually, removing a potential source of bugs in the future. Going back to our example above, the middleware would detect that you are making a query that orders by the “color” field, which is non-unique, and will stabilize the sort by adding a secondary sort on the first unique field it finds in the model, in this case, the"VIN" field.
We’ve packaged this middleware as a npm package (@cerebruminc/prisma-stable-sort-middleware) so that we can share it with the wider community, and more easily re-use the logic across our backend services. You can check out the source code here.
