Support for SQL-level DISTINCT operation for PostgreSQL

[kibertoad]

Problem

#2835 only implemented in-memory DISTINCT filtering, which is not ideal when dealing with large amount of data.

Suggested solution

Support generating DISTINCT SQL query.

Additional context

Possible API:

const result = await prisma.user.findMany({
  where: {},
  distinctSql: ['id', 'email']
})

[janpio]

Some additional context from previous issues #2835:

According to the docs, this distinct feature doesn't actually generate a distinct query, but filters the result set programmatically. This isn't a great option when there are thousands of records. Is there an open feature request for generating true distinct queries?

prisma.io/docs/concepts/components/prisma-client/aggregation-grouping-summarizing#distinct-under-the-hood

[Druue]

Hey!

We’ve just added support for distinct to use native SQL in Prisma 5.7.0 for PostgreSQL with the nativeDistinct preview feature. Once enabled, DISTINCT ON will be used instead of performing the distinct operation in memory with a caveat for now where it will revert back to in memory processing when used in conjunction with orderBy.
We plan on building this out further so that we can remove this limitation and also add support for other databases.

If you’re curious, we’ve added an example SQL output in our latest release notes
We also have a dedicated preview feature feedback discussion here if you have any thoughts you’d like to share :)

Cheers!

[kibertoad]

@Druue Thanks a lot, this is fantastic! Any particular reasons for keeping it in preview? Is it potentially unstable and can eat my data if used in production?

[Druue]

Hey @kibertoad, it’s so that folks have the ability to opt-in/out as we continue to build the feature and listen to feedback around performance and correctness. So please try it out and let us know if it still returns the correct data, and does so faster than before!

[janpio]

Support for other databases has been split into its own issue here: #23846