Supabase Conditional Queries with Filter Chaining

Use the PostgrestFilterBuilder to prepare your query before execution conditionally.

When working with Supabase, you often need to build different queries based on the parameters you pass. For example, suppose you have a simple table named sections with the following fields:

CREATE TABLE sections
(
    id uuid PRIMARY KEY,
    name text NOT NULL,
    locale text,
    tags text[],
    data jsonb
)

Now, you want to create a function to find sections by name and some optional parameters. The function signature might look like this:

export const findSections = async (name: string, options?: Options) => { ... }

As options, you can pass the following object:

type Options = {
  locale?: string | null;
  range?: {
    from: number;
    to: number;
  };
  tags?: string[] | null;
}

Here are some use cases of how you use the function:

// Find all sections by name and locale equals null
const sections = await findSections('name')

// Find all sections by name and locale equals 'en'
const sections = await findSections('name', { locale: 'en' })

// Find all sections by name and tags 'hello' and 'world'
const sections = await findSections('name', { tags: ['hello', 'world'] })

// Find all sections by name and use all options
const sections = await findSections('name', { 
  locale: 'en', 
  range: { from: 0, to: 9 }, 
  tags: ['hello', 'world']
})

How do you implement this function?

Supabase offers a feature called Conditional Filter Chaining. In this example, you see it in action:

export const findSections = async (name: string, options?: Options) => {
  const supabase = createClient<Database>(
    process.env.SUPABASE_URL,
    process.env.SUPABASE_ANON_KEY
  )

  // returns a PostgrestFilterBuilder
  let query = supabase.from('sections').select()

  query = options?.locale
    ? query.match({ name, locale: options.locale })
    : query.match({ name }).is('locale', null)

  if (options?.tags) {
    query = query.contains('tags', options.tags)
  }

  if (options?.range) {
    query = query.range(options.range.from, options.range.to)
  }

  // execute the query
  const {data, error} = await query

  // error handling
  return data
}

Conclusion

The PostgrestFilterBuilder provides a convenient way to build custom queries before executing them. This makes Supabase a very flexible tool. I love working with Supabase, especially when using all the database features like database views and stored procedures.

About the Author (Markus Tripp):
I’m a freelance web developer and Shopify consultant — and I’m the creator of Headcode CMS (www.headcodecms.com), a 100% open-source headless CMS for Next.js 13 App Router, Server Components, and Server Actions. Watch the video below for a quick product demo: