Updates for postgREST 11

[steve-chavez]

• [ ] Add undefinedToDefault option for the insert() and upsert() methods.
  • Should close https://github.com/supabase/postgrest-js/issues/173
  • https://github.com/PostgREST/postgrest/pull/2672
  • https://github.com/PostgREST/postgrest/pull/2723
• [ ] Add modifier: 'any' | 'all' option to the eq,like,ilike,gt,gte,lt,lte filters
  • supabase.from('countries').select().eq('id', [1,2,3,4,5,6,7,8], {modifier: 'any'})
  • The main motivation is to avoid using unwiledyor operations. It makes a filter accept an array.
  • https://github.com/PostgREST/postgrest/pull/2738

---

Add modifier: 'any' | 'all' option to the eq,like,ilike,gt,gte,lt,lte filters

This also makes me think about not, which doesn't have good DX. Maybe we can add another modifier to all operators like .eq('id', 1, {negate: true})

[soedirgo]

This also makes me think about not, which doesn't have good DX. Maybe we can add another modifier to all operators like .eq('id', 1, {negate: true})

This doesn't cover the DX shortcoming in the general case - we'd still have issues with nested filters. I prefer if we properly solve this in v3, we can rework the filtering approach to be more of a fluent/builder pattern, e.g. Prisma.

[soedirgo]

I'm not sure about supporting any/all for eq, gt, gte, lt, lte: for eq there's in, for the rest, you could do a min/max on the array right?

Looks useful for like, ilike though - and I think textSearch as well. Though I'd prefer adding different methods for them since function overloading is a bit annoying for autocomplete, e.g. likeAnyOf, ilikeAnyOf

[steve-chavez]

I'm not sure about supporting any/all for eq, gt, gte, lt, lte: for eq there's in, for the rest, you could do a min/max on the array right?

Yeah agree, so not needed there.

Looks useful for like, ilike though - and I think textSearch as well. Though I'd prefer adding different methods for them since function overloading is a bit annoying for autocomplete, e.g. likeAnyOf, ilikeAnyOf

Agree with likeAnyOf, ilikeAnyOf, would the all variants be useful as well? likeAllOf, ilikeAllOf.

any/all on textSearch doesn't work right now, but could be added later.

[github-actions[bot]]

:tada: This PR is included in version 1.6.0 :tada:

The release is available on:

• GitHub release
• npm package (@latest dist-tag)

Your semantic-release bot :package::rocket:

[steve-chavez]

@soedirgo One advantage to using any over eq is that unlike in, you don't have to quote the values inside the array, in case they have special characters.

With that, maybe we could reconsider adding a eqAny.

[steve-chavez]

This doesn't cover the DX shortcoming in the general case - we'd still have issues with nested filters.

@soedirgo Could you put an example of why it would fail with nested filters?

It should work as:

.eq('nested.id', 1, {negate: true})

right?

[soedirgo]

One advantage to using any over eq is that unlike in, you don't have to quote the values inside the array, in case they have special characters.

We'll need some form of escaping for both in and eq(any) - e.g. for eq(any) characters like {, }, , will break things.

Could you put an example of why it would fail with nested filters?

Sorry, I meant filters like and, or - you'd still have to use manual PostgREST syntax for those. Ideally things should work the same whether or not you're filtering as part of an and/or.