Query your data

Filter, search, sort, page, count and aggregate your Plexivo Postgres rows from JavaScript, Flutter or HTTPS, under row-level security.

Every query runs inside your database as the person who sent it, so row-level security decides which rows come back. You write it with the client library, in the style of GORM or of Supabase, or as a query string over HTTPS.

The tables on this page#

Profiles anyone can read, and posts that are public once published. Run it with the secret key, as in Raw SQL:

sql

create table profiles (
  id uuid primary key default auth.uid(),
  username text unique not null,
  country text
);
create table posts (
  id bigint generated always as identity primary key,
  author_id uuid not null default auth.uid() references profiles (id),
  title text not null,
  tags text[] not null default '{}',
  meta jsonb not null default '{}',
  likes int not null default 0,
  published boolean not null default false,
  created_at timestamptz not null default now(),
  search tsvector generated always as (to_tsvector('english', title)) stored
);
alter table profiles enable row level security;
alter table posts enable row level security;
create policy profiles_read on profiles for select using (true);
create policy profiles_own on profiles for insert to authenticated with check (id = auth.uid());
create policy posts_read on posts for select using (published or author_id = auth.uid());
create policy posts_own on posts for all to authenticated
  using (author_id = auth.uid()) with check (author_id = auth.uid());

Some rows to query, written as a signed-in user:

import { createClient } from '@plexivo/client';

const db = createClient(PLEXIVO_DATA_URL, PLEXIVO_PUBLISHABLE_KEY);
await db.auth.signUp({ email, password });

await db.from('profiles').create({ username: 'asha', country: 'IN' });
await db.from('posts').create([
  { title: 'Fast cars in Pune', tags: ['cars'], likes: 12, published: true, meta: { city: 'Pune' } },
  { title: 'Postgres for beginners', tags: ['postgres', 'sql'], likes: 30, published: true, meta: { city: 'Chennai' } },
  { title: 'A draft', likes: 0 }
]);

dart

import 'package:plexivo_client/plexivo_client.dart';

final db = PlexivoClient(dataUrl, publishableKey);
await db.signUp(email: email, password: password);

await db.from('profiles').create({'username': 'ravi', 'country': 'IN'});
await db.from('posts').createMany([
  {'title': 'Fast cars in Pune', 'tags': ['cars'], 'likes': 12, 'published': true, 'meta': {'city': 'Pune'}},
  {'title': 'Postgres for beginners', 'tags': ['postgres', 'sql'], 'likes': 30, 'published': true, 'meta': {'city': 'Chennai'}},
  {'title': 'A draft', 'likes': 0},
]);

Filters#

Chain conditions; they all must match. Awaiting the query runs it and gives you the rows.

const popular = await db.from('posts')
  .select('id, title, likes')
  .where('published', true)
  .where('likes', '>=', 10)
  .whereILike('title', '%pune%')
  .whereContains('tags', ['cars'])
  .whereJson('meta', 'city', 'Pune')
  .whereNotIn('id', [999]);

// Groups: (likes > 20 or tagged sql) and published
const either = await db.from('posts')
  .where((q) => q.where('likes', '>', 20).orWhere('tags', 'contains', ['sql']))
  .where('published', true);

// Full-text search on a tsvector column
const found = await db.from('posts').whereSearch('search', 'fast cars', 'english');

dart

final popular = await db.from('posts')
    .select('id, title, likes')
    .where('published', true)
    .where('likes', '>=', 10)
    .whereILike('title', '%pune%')
    .whereContains('tags', ['cars'])
    .whereJson('meta', 'city', 'Pune')
    .whereNotIn('id', [999]);

final found = await db.from('posts').whereSearch('search', 'fast cars', 'english');

Condition	Client library	Query string
Equal, not equal	`where(c, v)`, `where(c, '!=', v)`	`c=eq.v`, `c=neq.v`
Greater, less	`'>'`, `'>='`, `'<'`, `'<='`	`gt`, `gte`, `lt`, `lte`
Pattern, any case	`whereLike`, `whereILike`	`like`, `ilike` (`*` as the wildcard)
Regular expression	`where(c, '~', re)`, `'~*'`	`match`, `imatch`
In a list	`whereIn`, `whereNotIn`	`in.(1,2,3)`
Null, true, false	`whereNull`, `whereNotNull`	`is.null`, `is.true`
Array or JSON contains, contained by, overlaps	`whereContains`, `where(c, '<@', v)`, `whereOverlaps`	`cs`, `cd`, `ov`
Inside a JSON column	`whereJson(c, 'a.b', v)`	Client libraries only
Full-text search	`whereSearch`, `textSearch`	`fts`, `plfts`, `phfts`, `wfts`
Not, or	`whereNot`, `orWhere`, groups	`not.` before any operator, `or=(…)`

Supabase names work too: eq, neq, gt, ilike, in, is, contains, or('likes.gt.20,tags.cs.{sql}'), match, textSearch and filter. In Dart, in and is are inFilter and isFilter.

A query can hold up to 300 conditions. To filter by related rows, such as posts whose author is from India, see Joins.

Order#

Sort by one column or several, put empty values first or last, or sort by a column of a related row:

const newest = await db.from('posts').order('created_at', 'desc').order('id');

const byLikes = await db.from('posts').order('likes', { ascending: false, nullsFirst: false });

// By the author’s username: a many-to-one relation, through posts.author_id
const byAuthor = await db.from('posts').order('profiles.username', 'asc').order('id');

dart

final byAuthor = await db.from('posts')
    .order('profiles.username', 'asc', 'last')
    .order('id');

Sorting by a related row works for a to-one relation, where this table holds the foreign key. A post whose author you cannot read sorts as empty. To sort the related rows themselves, see Joins.

Pages and counts#

const page2 = await db.from('posts').order('id').page(2, 20);        // or .limit(20).offset(20), or .range(20, 39)

const { data, total, totalPages } = await db.from('posts').order('id').paginate({ page: 1, perPage: 20 });

const published = await db.from('posts').where('published', true).count();

// Rows and the total in one request
const { count } = await db.from('posts').select('id, title', { count: 'exact' }).range(0, 9);

dart

final page = await db.from('posts').order('id').paginate(page: 1, perPage: 20);
print('${page.data.length} of ${page.total}');

final published = await db.from('posts').where('published', true).count();

A read returns at most 1,000 rows. To go through every row, use findInBatches, which pages by primary key so no row is skipped or read twice.

Aggregates#

const totalLikes = await db.from('posts').where('published', true).sum('likes');
const average = await db.from('posts').avg('likes');

const stats = await db.from('posts').aggregate({ count: true, sum: ['likes'], max: ['likes'] });

// Group by, having and order by an aggregate
const perAuthor = await db.from('posts')
  .groupBy('author_id')
  .having('count', '>=', 1)
  .order('count(*)', 'desc')
  .aggregate({ count: true, sum: ['likes'] });

dart

final totalLikes = await db.from('posts').where('published', true).sum('likes');

final perAuthor = await db.from('posts')
    .groupBy('author_id')
    .having('sum(likes) >= ?', 10)
    .order('count(*)', 'desc')
    .aggregate({'count': true, 'sum': ['likes']});

sum() and avg() give you numbers. In aggregate(), sums and averages arrive as exact decimal text, so no precision is lost; convert them with Number() or num.parse() where you need to. For a count of related rows on each row, such as each post’s comments, use withCount in Joins.

Over HTTPS#

From any language, the same query as a query string. The syntax is PostgREST’s, so Supabase queries keep working:

bash

curl "$PLEXIVO_DATA_URL/rest/v1/posts?select=id,title,likes&published=is.true&likes=gte.10&order=profiles(username).asc,id&limit=20&count=exact" \
  -H "apikey: $PLEXIVO_PUBLISHABLE_KEY" \
  -H "Authorization: Bearer $ACCESS_TOKEN"

The answer is {"data": [...], "count": 2}. With count=exact, count is the total across every page.

Next steps#

Updated 28 October 2026