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.
Read rows#
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#
// 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
400withAll object keys must match. Fill the missing keys withnull. update()anddelete()without a filter are refused by the client with the error codeUNFILTERED_MUTATION, before any request is sent. To change every row on purpose, run SQL from a server.
One row, many rows, counts#
// 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#
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:
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():
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'],
})
const { data } = await aura.rpc('add', { a: 1, b: 2 }) // → 3
grant_execute_tois required to call it. Without it, nobody can execute the function. A role that is not listed gets403 permission denied for function. Bothauthenticatedandaura_authenticatedare accepted. This also holds for functions you create withdb.sql()or a migration on projects created after this change (the defaultEXECUTEprivilege ofPUBLICis revoked): addgrant 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()returns404 Could not find the function. - Functions via SQL (
db.sql()):CREATE FUNCTION/CREATE OR REPLACE FUNCTIONis allowed inSECURITY INVOKERmode (default);SECURITY DEFINERis rejected on/rawand must be created throughcreateFunction(POST /v1/db/{project_id}/functions) or CLI/migrations with explicitgrant_execute_to.
Run SQL from a server#
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()orrange()you ask for.meta.has_moreistruewhen more rows match; ask forcount: 'exact'to also getmeta.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;offsetgets slower the deeper you go. - Numbers beyond JavaScript's 2^53. A
bigintabove 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. Anumericis returned as a JSON number when it is exactly representable, and as an exact decimal string otherwise (long values such as12345678901234567890.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). Usecount: 'exact'when the number matters.- A
vectorcolumn comes back as a string such as"[0.1,0.2,0.3]": parse it withJSON.parseto get anumber[]. - 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. Writewith (security_invoker = false)explicitly only if you really want the view to bypass RLS. db.sql()retries a429after theRetry-Afterdelay (a refused request never ran)./rawis limited per project; for bulk loads use the typedinsert()with an array.