Fetch data: select()

Perform a SELECT query on the table or view.

1const { data, error } = await supabase
2  .from('countries')
3  .select()

Parameters#

  • columnsoptional
    Query

    The columns to retrieve, separated by commas

  • optionsrequired
    object

    Named parameters

      Properties

    • countoptional
      exact
      |
      planned
      |
      estimated

      Count algorithm to use to count rows in the table or view.

      "exact": Exact but slow count algorithm. Performs a COUNT(*) under the hood.

      "planned": Approximated but fast count algorithm. Uses the Postgres statistics under the hood.

      "estimated": Uses exact count for low numbers and planned count for high numbers.

    • headoptional
      boolean

      When set to true, data will not be returned. Useful if you only need the count.

Notes#

  • By default, Supabase projects return a maximum of 1,000 rows. This setting can be changed in your project's 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 Filters
  • select() can be combined with Modifiers
  • apikey is a reserved keyword if you're using the Supabase Platform and should be avoided as a column name](https://github.com/supabase/supabase/issues/5465).

Examples#

Getting your data#

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan'),
8  (2, 'Albania'),
9  (3, 'Algeria');

Selecting specific columns#

You can select specific fields from your tables.

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan'),
8  (2, 'Albania'),
9  (3, 'Algeria');

Query foreign tables#

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

1create table
2  countries (id int8 primary key, name text);
3create table
4  cities (
5    id int8 primary key,
6    country_id int8 not null references countries,
7    name text
8  );
9
10insert into
11  countries (id, name)
12values
13  (1, 'Germany'),
14  (2, 'Indonesia');
15insert into
16  cities (id, country_id, name)
17values
18  (1, 2, 'Bali'),
19  (2, 1, 'Munich');

Query foreign tables through a join table#

If you're in a situation where your tables are NOT directly related, but instead are joined by a join table, you can still use the select() method to query the related data. The join table needs to have the foreign keys as part of its composite primary key.

1create table
2  users (
3    id int8 primary key,
4    name text
5  );
6create table
7  teams (
8    id int8 primary key,
9    name text
10  );
11-- join table
12create table
13  users_teams (
14    user_id int8 not null references users,
15    team_id int8 not null references teams,
16    -- both foreign keys must be part of a composite primary key
17    primary key (user_id, team_id)
18  );
19
20insert into
21  users (id, name)
22values
23  (1, 'Kiran'),
24  (2, 'Evan');
25insert into
26  teams (id, name)
27values
28  (1, 'Green'),
29  (2, 'Blue');
30insert into
31  users_teams (user_id, team_id)
32values
33  (1, 1),
34  (1, 2),
35  (2, 2);

Query the same foreign table multiple times#

If you need to query the same foreign table twice, use the name of the joined column to identify which join to use. You can also give each column an alias.

1create table
2  users (id int8 primary key, name text);
3
4create table
5  messages (
6    sender_id int8 not null references users,
7    receiver_id int8 not null references users,
8    content text
9  );
10
11insert into
12  users (id, name)
13values
14  (1, 'Kiran'),
15  (2, 'Evan');
16
17insert into
18  messages (sender_id, receiver_id, content)
19values
20  (1, 2, '👋');

Filtering through foreign tables#

If the filter on a foreign table's column is not satisfied, the foreign table returns [] or null but the parent table is not filtered out.

1create table
2  countries (id int8 primary key, name text);
3create table
4  cities (
5    id int8 primary key,
6    country_id int8 not null references countries,
7    name text
8  );
9
10insert into
11  countries (id, name)
12values
13  (1, 'Germany'),
14  (2, 'Indonesia');
15insert into
16  cities (id, country_id, name)
17values
18  (1, 2, 'Bali'),
19  (2, 1, 'Munich');

If you want to filter out the parent table rows, use the !inner hint:

1const { data, error } = await supabase
2  .from('cities')
3  .select('name, countries!inner(*)')
4  .eq('countries.name', 'Estonia')

Querying with count option#

You can get the number of rows by using the count option. For example, to get the table count without returning all rows:

1create table
2  countries (id int8 primary key, name text);
3
4insert into
5  countries (id, name)
6values
7  (1, 'Afghanistan'),
8  (2, 'Albania'),
9  (3, 'Algeria');

Querying JSON data#

You can select and filter data inside of JSON columns. Postgres offers some operators for querying JSON data.

1create table
2  users (
3    id int8 primary key,
4    name text,
5    address jsonb
6  );
7
8insert into
9  users (id, name, address)
10values
11  (1, 'Avdotya', '{"city":"Saint Petersburg"}');