Skip to content
Aurabase Logo
aurabase

Query your database with the JavaScript SDK

The everyday flows of `aura.db`, the rules that refuse unsafe calls, and how to protect a table with row-level security.

How-to guide9 min readLevel intermediateRevised on Oct 12, 2026

Read rows#

read.tstypescript
const { data, error } = await aura
  .from('todos')
  .select('id, title, done')
  .eq('done', false)
  .order('created_at', { ascending: false })
  .limit(20)

aura.from('todos') is the form used throughout this documentation (it is a shortcut for aura.db.from('todos'), the same call). Every call returns { data, error } and never throws, see client options and errors.

Filters chain: eq, neq, gt, gte, lt, lte, like, ilike, in, is, and the others listed in the database reference. For a page of rows, use range(from, to).

Insert, update and delete#

write.tstypescript
// Insert one row, or an array of rows, and get them back
const { data } = await aura.from('todos').insert({ title: 'Write the docs' }).select()

// Upsert
await aura.from('todos').upsert({ id, title: 'Renamed' }).select()

// Update and delete need a filter
await aura.from('todos').update({ done: true }).eq('id', id)
await aura.from('todos').delete().eq('id', id)

A write returns nothing (data is null) unless you chain .select(): inserting 10,000 rows does not send 10,000 rows back. With .select() the rows come back, and a PATCH or POST that matched no row says why in meta.hint.

Two rules protect you from mistakes:

  • A batch insert needs the same keys in every row. Rows with different keys return 400 with All object keys must match. Fill the missing keys with null.
  • update() and delete() without a filter are refused by the client with the error code UNFILTERED_MUTATION, before any request is sent. To change every row on purpose, run SQL from a server.

One row, many rows, counts#

count.tstypescript
// Exactly one row: an error if there are none or several
const one = await aura.from('todos').select('*').eq('id', id).single()
// → error.status 406 when the filter matches zero or several rows

// Zero or one row: data is null when nothing matches
const maybe = await aura.from('todos').select('*').eq('id', id).maybeSingle()

// Rows and their total
const page = await aura.from('todos').select('*', { count: 'exact' }).range(0, 9)
// page.count is the total, whatever the range

// The total only, no rows
const { count } = await aura.from('todos').select('*', { count: 'exact', head: true })

count: 'exact' counts every matching row. 'planned' and 'estimated' are faster on very large tables and approximate.

Protect a table#

RLS is enabled by default through Aurabase, add policies to grant access

Tables created through Aurabase (db.sql(), migrations, Studio, or the table API) automatically get ENABLE and FORCE ROW LEVEL SECURITY (unless you explicitly include DISABLE ROW LEVEL SECURITY in your DDL batch). With no policies yet, anon and authenticated callers see []. Tables created via a direct Postgres connection need enable row level security explicitly. Add the policies you need:

SQLsql
create policy todos_own_read on todos
  for select to aura_authenticated
  using (auth.uid() = owner);

The canonical database roles are aura_anon, aura_authenticated and aura_service_role (group aliases anon, authenticated, service_role also map to them on managed clusters). auth.uid() is NULL for the public key and a user id for a signed-in user. See Row-Level Security.

A service_role key bypasses RLS by design: keep it on your server.

SQL functions and rpc#

Create a function from a server, then call it from anywhere with rpc():

server/setup.tstypescript
await admin.db.createFunction({
  name: 'add',
  args: [
    { name: 'a', data_type: 'int', mode: 'IN' },
    { name: 'b', data_type: 'int', mode: 'IN' },
  ],
  returns: 'int',
  language: 'sql',
  body: 'SELECT a + b',
  grant_execute_to: ['anon', 'authenticated'],
})
client.tstypescript
const { data } = await aura.rpc('add', { a: 1, b: 2 }) // → 3
  • grant_execute_to is required to call it. Without it, nobody can execute the function. A role that is not listed gets 403 permission denied for function. Both authenticated and aura_authenticated are accepted. This also holds for functions you create with db.sql() or a migration on projects created after this change (the default EXECUTE privilege of PUBLIC is revoked): add grant execute on function hello(text) to aura_authenticated; yourself.
  • A new function is callable after a few seconds. Until the API reloads its schema, rpc() returns 404 Could not find the function.
  • Functions via SQL (db.sql()): CREATE FUNCTION / CREATE OR REPLACE FUNCTION is allowed in SECURITY INVOKER mode (default); SECURITY DEFINER is rejected on /raw and must be created through createFunction (POST /v1/db/{project_id}/functions) or CLI/migrations with explicit grant_execute_to.

Run SQL from a server#

server/migrate.tstypescript
import { createAdminClient } from '@aurabase/aurabase-js'

const admin = createAdminClient(process.env.NEXT_PUBLIC_AURABASE_URL!, process.env.AURABASE_SERVICE_ROLE_KEY!)

await admin.db.sql(`
  create table if not exists todos (
    id uuid primary key default gen_random_uuid(),
    title text not null,
    done boolean not null default false,
    owner uuid,
    created_at timestamptz not null default now()
  )
`)

db.sql() needs a service_role key. It refuses a DELETE without a WHERE, and it does not read system catalogs such as pg_class or pg_roles. To list your tables, use admin.db.schema().

Pagination, row cap and exact numbers

  • A read returns at most 1,000 rows — the default and the hard ceiling, whatever limit() or range() you ask for. meta.has_more is true when more rows match; ask for count: 'exact' to also get meta.total.
  • Page through a big table with keyset pagination, which costs the same on the millionth page: aura.from('events').select('id,label').limit(1000).after('id', lastId) (it is .order('id').gt('id', lastId)). .cursor() is MongoDB-only; offset gets slower the deeper you go.
  • Numbers beyond JavaScript's 2^53. A bigint above 9,007,199,254,740,991 loses its last digits when parsed as a JS number: create the client with { parseBigIntAsString: true } to receive those values as strings. A numeric is returned as a JSON number when it is exactly representable, and as an exact decimal string otherwise (long values such as 12345678901234567890.123456789).
  • count: 'planned' and 'estimated' are only estimates. They come from PostgreSQL's statistics, which are empty on a table that was just loaded (a 60-row table can report 430). Use count: 'exact' when the number matters.
  • A vector column comes back as a string such as "[0.1,0.2,0.3]": parse it with JSON.parse to get a number[].
  • A view reads with your privileges. Views you create (SQL, migration) get security_invoker = true, so the policies of the underlying tables apply to the reader. Write with (security_invoker = false) explicitly only if you really want the view to bypass RLS.
  • db.sql() retries a 429 after the Retry-After delay (a refused request never ran). /raw is limited per project; for bulk loads use the typed insert() with an array.