Prisma’s distinct option does not use SQL SELECT DISTINCT

I’m very new to the world of JavaScript as well as web development in general. Recently, I started a web application project and decided to use Prisma as the ORM piece. This decision was largely influenced by the positive feedback I gathered from HackerNews. My thinking was that Prisma would enable me to execute simple queries quickly, facilitate easy migrations, and allow me to focus on the application itself.

However, I encountered an issue. I had a table with approximately 320,000 rows, and I intentionally didn’t normalize a specific field. I assumed that since the data wouldn’t grow, I could perform a distinct query to retrieve a list of categories instead of making a new table for it. It wouldn’t have been a problem if I were writing the query manually. However, Prisma selects the entire table and performs an in-memory search for distinct values.

The performance was significantly slower. As I was reviewing query times to enhance page load speed, I was bewildered by the potential issue with my distinct query. After some investigation, I stumbled upon the documented behavior. While I understand the rationale behind it, I find it counterintuitive that despite having such a powerful ORM tool, I still need to resort to writing raw SQL to achieve reasonable performance for a “select distinct” query.

Moreover, one of the primary reasons for choosing Prisma was to avoid delving into SQL completely. Yet, here I was, delving into an abstraction of SQL. I’m puzzled as to why performing a “select distinct” operation is considered an edge-case scenario. I’m still using it and because I’m so new to the ecosystem, I try to maintain an open mind. Nevertheless, due to this issue and a few other Prisma unsupported Postgres features that I use, a significant portion of my queries now consist of raw SQL. This situation seems to counteract the purpose of adopting this added complexity, doesn’t it?