drizzle-team / drizzle-team/drizzle-orm

[FEATURE]: SELECT DISTINCT ON support in RQB v2

Open
#5,759 0 comments 13 reactions 0 assignees View on GitHub
enhancement
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Feature hasn't been suggested before.

- [x] I have verified this feature I'm about to request hasn't been suggested before.

### Describe the enhancement you want to request

Hi!

I couldn't find this feature request anywhere so I decided to open this one.

`SELECT DISTINCT ON` / `SELECT DISTINCT` is a very powerful SQL feature and it's annoying not having it in the new RQB v2.

In my mind it should be a fairly simple implementation seeing that it just needs to add the `DISTINCT` or `DISTINCT ON (...)` after the `SELECT` clause and put the provided columns after that.

The API could follow Prisma's approach and look something like this:

```js
db.query.usersTable.findMany({
distinct: ["role"],
columns: {
id: true,
email: true,
role: true,
},
orderBy: {
role: "asc",
},
});
```

One important detail is that only PostgreSQL supports `DISTINCT ON`.

Because of that, the responsibility of only providing the correct columns for databases that support `DISTINCT ON` could fall on the user for now.

The generated SQL could then differ slightly depending on the database while still keeping the same API shape.

### PostgreSQL

Input:

```js
db.query.usersTable.findMany({
distinct: ["role"],
columns: {
id: true,
email: true,
role: true,
},
orderBy: {
role: "asc",
},
});
```

Output:

```sql
SELECT DISTINCT ON ("role") "id", "email", "role"
FROM "users"
ORDER BY "role" ASC;
```

### Non-PostgreSQL databases

Input:

```js
db.query.usersTable.findMany({
distinct: ["id", "email", "role"],
columns: {
id: true,
email: true,
role: true,
},
});
```

Output:

```sql
SELECT DISTINCT "id", "email", "role"
FROM "users";
```

This would provide a consistent API across databases while still exposing PostgreSQL's more powerful `DISTINCT ON` functionality when available.

Another approach could be having 2 different options, a simple `distinct` and a `distinctOn`, both receiving a list of columns.

Thank you!

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.