How to search a Supabase table in Flutterflow

The ability to search a Supabase table in Flutterflow can give your apps a much broader scope in the kind of information you can return to your user. They could for example curate a list of favourites from the search and have the results emailed to them, all from within Supabase. This video walks you through the process and by the end you will be able to search keywords and return the data to your Flutterflow app.

We will be using a custom action that calls a Postgres function. We search the whole of the table for terms that match or contain your keyword. We use a job search example and our user can query job type, country, city etc and will return all related terms. Because we are making the search a broader term, if our user searches manager, it will return all roles with manager in the name, such as Production Manger, Shift Manager etc.

We are returning a JSON with the search data back to Flutterflow, where we map the values to a data table via a custom data type. There is a lot to unpack here, but there are a number of lessons which can be applied in different areas of your applications.

Here is the video. All of the custom code used in this video is available below.

Custom code

The custom actions are not the challenging area of this, mapping the JSON correctly after the data is returned is probably the area that needs the most thought. As always remember to change the names of the functions and parameters to the ones you are using.

Flutterflow Custom Action

We only need one custom action here. This is the Flutterflow code for calling the rpc procedure in Supabase

import 'package:supabase_flutter/supabase_flutter.dart';

Future<List<dynamic>> searchTable(String search) async {
  try {
    final supabase = SupaFlow.client;
    final response = await supabase.rpc('search_table', params: {
      'p_search_term': search,
    });
    // Print the raw response for debugging purposes
    print('Raw Response: $response');

   

    return response;
  } catch (e) {
    print('Exception: $e');
    // Return an empty list in case of an exception
    return [];
  }
}

Supabase stored procedures for searching a table

Although we search the whole table in the example, with a large dataset, searching specific columns will be much more efficient, so that is the first of the functions here. Remember to rename the column_1, column_2 values to the columns in your table.

CREATE OR REPLACE FUNCTION search_table_column(p_search_term text)
RETURNS JSON
AS $$
DECLARE
    result_json JSON;
BEGIN
    SELECT COALESCE(json_agg(row_to_json(t)), '[]')::json
    INTO result_json
    FROM (
        SELECT SELECT your_column_1, your_column_2, .....
        FROM your_table
        WHERE LOWER(your_table.your_column_1) LIKE LOWER('%' || p_search_term || '%')
    ) t;

    RETURN result_json;
END;
$$ LANGUAGE plpgsql;

This second function is the one to search the whole of the Supabase table and return the results to Flutterflow as a JSON.

CREATE OR REPLACE FUNCTION search_table(p_search_term text)
RETURNS JSON
AS $$
DECLARE
    result_json JSON;
BEGIN
    SELECT COALESCE(json_agg(row_to_json(t)), '[]')::json
    INTO result_json
    FROM (
        SELECT your_column_1, your_column_2, .....
        FROM your_table
        WHERE
            to_jsonb(your_table)::text ILIKE '%' || p_search_term || '%'
    ) t;

    RETURN result_json;
END;
$$ LANGUAGE plpgsql;

How to search a Supabase table in Flutterflow

With just one custom action and a stores Postgres procedure, we are able to return a set of results to our users based on the keyword. As long as we are mindful when mapping the JSON values to our preferred method of displaying the information, this method is not too tricky to accomplish.