[FEATURE]: Add findUnique to relational queries

[jgoux]

Describe what you want

Hello 👋,

Relational queries are pushing the DX of Drizzle closer than Prisma, I love it!

In order to close the gap, it would be great to have findUnique implemented. It's basically like findFirst but restricted to the filter of at least one ID/PK/Unique column (https://www.prisma.io/docs/reference/api-reference/prisma-client-reference/#filter-on-non-unique-fields-with-userwhereuniqueinput).

[dankochetov]

I'm not sure I understand what's the difference between findUnique and findFirst, to be honest. What's stopping you from using the findFirst and filter by the same columns you would use with findUnique?

[jgoux]

It's to help the developer get a unique entity efficiently without errors as all these columns are indexed. You don't have to know by heart the unique columns, the types will help you.

[verheyenkoen]

@dankochetov

If someone wrongfully removes a unique constraint (or forgets to implement it in the first place), you'd introduce a "silent" bug with .findFirst(). There could be multiple rows returned and Drizzle would just pick the first one from the result set. With .findUnique() you'd get an error thrown if there is more than one matching row and you have a chance to catch the bug sooner I guess.

Also, .findUnique() is semantically a clear mention of the fact that there can only be one of the selected rows, which you wouldn't have with .findFirst({ where: eq(table.arbitraryColumnName, 'arbitraryValue') }).

[luxaritas]

Something else I'd like to add on here - it would be incredibly useful if Drizzle (like Prisma!) could automatically apply the dataloader pattern to findUnique queries. That way, in a GraphQL setup for example, you could call findUnique multiple times from Drizzle and it can automatically batch them up

[Angelelz]

Is this conceptually different from #889?

[jakeleventhal]

@Angelelz @dankochetov this is conceptually different from findFirst

Say have a table called someTable. I have a unique index on id and on 3 fields (can have duplicates, but not duplicate combo of all 3), say field1, field2, and field3. In prisma, I am able to query on that unique combination and do something like so:

await prisma.someTable.findUnique({
  where: { // The only possible fields I can use here are `id` or `field1_field2_field3`
    field1_field2_field3: { // This type requires all 3 specified
      field1: 'asdf',
      field2: 'asdf',
      field3: 'asdf'
    }
  }
});

If I have another field name in the table that is not unique, I am not able to specify this in the where query. The reason that this feature is helpful is that it makes for easier DX since you don't need to remember exactly what defines unique indexes. Moreover, if you remove a field from a unique index, then the type checker will say that field3 is not a key of the where clause. It would require it to update to this:

await prisma.someTable.findUnique({
  where: {
    field1_field2: { // `field1_field2_field3` is no longer a key
      field1: 'asdf',
      field2: 'asdf'
    }
  }
});

This feature prevents a lot of bugs where you expect a unique result, but could actually have multiple results since you misunderstood or forgot the exact unique indexes.

There should be some way to have a findUnique function that is essentially a copy of findFirst except with different type defs for the where clause based on where the unique indexes are defined.

Slightly related: The same unique keys should be able to be specified as the target in upsert operations.

For other readers, is there any good workaround to do this?

[Angelelz]

There is still no feature parity between Prisma and drizzle yet. I personally don't have any idea what that API is doing for you under the hood? Is it just filtering by those 3 fields? Is it looking at your actual indexes making sure is unique? Does it throw and error if more that one is received back? Does it add a limit 1 to the query? I would suggest to find out what is it that you need from findUnique exactly, in terms of SQL queries, and go from there.

[jakeleventhal]

Full spec of how findUnique should work:

• Under the hood, it should behave the same as findFirst (limit 1 and return the result)
• There should be no runtime errors thrown if no value is found; it should just behave like findFirst and return null
• The only real difference is in the API that the developer sees. findUnique should just alter the where type interface to exclude any types that do not have unique indexes

Example:

export const table1 = pgTable(
  'Table1',
  {
    id: text('id')
      .primaryKey()
      .$defaultFn(() => sql`gen_random_uuid()::text`)
      .notNull(),
    name: text('name').notNull(),
    notes: text('notes').notNull(),
    orderId: text('orderId').notNull(),
    productId: text('productId').notNull(),
    purchaseDate: timestamp('purchaseDate', { mode: 'string', precision: 3 }).notNull(),
  },
  (table) => ({
    nameKey: uniqueIndex('name_key').on(table.name),
    productIdOrderIdKey: uniqueIndex('Table1_productId_orderId_key').on(table.productId, table.orderId)
  })
);

// These should be the only types since they are derived from the unique indexes
type Table1FindUniqueWhere = |
  { id: string } |  // Included because primary key is also unique
  { name: string } |
  { productId: string; orderId: string }

await db.query.table1.findUnique({})
                                 ^^ Missing required where clause
await db.query.table1.findUnique({
  where: {}
         ^^ Does not match Table1FindUniqueWhere
})
await db.query.table1.findUnique({
  where: { notes: 'asdf' }
         ^^^^^^^^^^^^^^^^^ Does not match Table1FindUniqueWhere (notes is not a unique field)
})
await db.query.table1.findUnique({
  where: { orderId: 'asdf' }
         ^^^^^^^^^^^^^^^^^^^ Does not match Table1FindUniqueWhere since productId is not also included
})

// Valid
await db.query.table1.findUnique({
  where: { orderId: 'asdf', productId: 'asdf' }
})

// Valid
await db.query.table1.findUnique({
  where: { name: 'asdf' }
})

// Valid
await db.query.table1.findUnique({
  where: { id: 'asdf' }
})

[luxaritas]

Find unique should just alter the where type interface to exclude any types that do not have unique indexes

I'd like to recommend amending this slightly: Instead of excluding types that do not have unique indexes, it should require at least one fully-specified unique index. That way a) you can still provide additional filters, such as when pulling back a user based on their unique username and account status (Prisma implemented this later on with their "extended where" feature) and b) imagine you have (a,b) as a compound unique index, (c,d) as a compound unique index, and e as a unique index - you want to require the presence of (a and b) or (c and d) or e.

[hilja]

If I’m not mistaken in Prisma this works with delete too:

await prisma.someTable.delete({
  where: {
    field1_field2_field3: {
      field1: 'asdf',
      field2: 'asdf',
      field3: 'asdf'
    }
  }
});

[stabildev]

Please add this. It's easy to forget adding a where clause with findFirst. findUnique could be just like findFirst with required where argument