Fetch data: select()

Performs vertical filtering with SELECT.

1final data = await supabase
2  .from('cities')
3  .select('name');

Notes#

By default, Supabase projects will return a maximum of 1,000 rows. This setting can be changed in Project API Settings. It's recommended that you keep it low to limit the payload size of accidental or malicious requests. You can use range() queries to paginate through your data.
select() can be combined with Modifiers
select() can be combined with Filters
If using the Supabase hosted platform apikey is technically a reserved keyword, since the API gateway will pluck it out for authentication. It should be avoided as a column name.

Examples#

Getting your data#

1final data = await supabase
2  .from('cities')
3  .select('name');

Selecting specific columns#

You can select specific fields from your tables.

1final data = await supabase
2  .from('countries')
3  .select('''
4    name,
5    cities (
6      name
7    )
8  ''');

Query foreign tables#

If your database has relationships, you can query related tables too.

1final data = await supabase
2  .from('products')
3  .select('''
4    id,
5    supplier:supplier_id ( name ),
6    purchaser:purchaser_id ( name )
7  ''');

Query the same foreign table multiple times#

Sometimes you will need to query the same foreign table twice. In this case, you can use the name of the joined column to identify which join you intend to use. For convenience, you can also give an alias for each column. For example, if we had a shop of products, and we wanted to get the supplier and the purchaser at the same time (both in the users) table:

1final data = await supabase
2  .from('messages')
3  .select('*, users!inner(*)')
4  .eq('users.username', 'Jane');

Filtering with inner joins#

If you want to filter a table based on a child table's values you can use the !inner() function. For example, if you wanted to select all rows in a message table which belong to a user with the username "Jane":

1final data = await supabase
2  .from('messages')
3  .select('*, users!inner(*)')
4  .eq('users.username', 'Jane');

Querying with count option#

You can get the number of rows by using the count option. Allowed values for count option are exact, planned and estimated.

1final res = await supabase.from('cities').select(
2      'name',
3      const FetchOptions(
4        count: CountOption.exact,
5      ),
6    );
7
8final count = res.count;

Querying JSON data#

If you have data inside of a JSONB column, you can apply select and query filters to the data values. Postgres offers a number of operators for querying JSON data. Also see PostgREST docs for more details.

1final data = await supabase
2  .from('users')
3  .select('''
4    id, name,
5    address->street
6  ''')
7  .eq('address->postcode', 90210);

Return data as CSV#

By default the data is returned in JSON format, however you can also request for it to be returned as Comma Separated Values.

1final data = await supabase
2  .from('users')
3  .select()
4  .csv();