Skip to content
Aurabase Logo
aurabase

Database

PostgreSQL 18, a dedicated database per project on your organization's cluster, isolated schemas, auto-generated TypeScript types, native RLS. No forks, no rewrite proxies — your extensions, triggers and PL/pgSQL work as is.

Explanation8 min readLevel intermediateRevised on Apr 15, 2026

Postgres 18, without abstraction#

The aura-db service exposes a generic CRUD on top of a PostgreSQL 18: each project has its own database, hosted on your organization's PostgreSQL cluster (a cluster entirely dedicated to a single project on the enterprise offer). Each SDK call is translated into native SQL via a thin layer that respects the RLS policies injected into the session.

You can also bypass the SDK and point psql, Prisma, Drizzle, Kysely, Metabase or pg_dump at your database: standard PostgreSQL wire protocol, TLS, a role that can only reach your project's database. See Connect directly below. (A deployment that does not publish the PostgreSQL port says so in Studio › Settings › Connection: use the SDK, the REST API, the SQL editor or the CLI, aura db raw.)

Tip

A pg_dump gives you your complete database (schema, data, extensions, policies). No lock-ins, trivial outbound migration.

The SDK calls PostgREST, which calls Postgres#

Under the hood, the SDK serializes your .from().select()… calls into an HTTP request to aura-db which delegates to a PostgREST sidecar. PostgREST translates to SQL and opens a pooled connection via PgBouncer with the search_path of the injected project.

internal flowtext
SDK        → HTTPS    GET /v1/db/sensors?select=id,name
Gateway    → aura-db  Session verification & routing
aura-db    → PgBouncer → PostgreSQL 18

# Connection with search_path project
SET LOCAL search_path = project_a7f3_, public;
SET LOCAL request.jwt.claims = '{ "sub": "u_9k2", "tenant": "t_4c" }';
SELECT id, name, temperature FROM sensors WHERE active = true LIMIT 50;
Info

RLS policies have access to auth.uid(), auth.role(), auth.tenant(). These helpers read request.jwt.claims injected by the gateway — impossible to tamper with on the client side.

Six native abilities#

  • Schema-per-project. Partitioning applied by PostgreSQL: no tenant_id column, one connection role per project, rights never opened to PUBLIC.
  • Versioned migrations. Versioned SQL, atomic rollback, zero-downtime. TypeScript types regenerate with each migration.
  • Codegen TypeScript & Rust. Automatic generation of types for tables, views, enums and RPC functions from your live schema.
  • Native RLS. Testable declarative policies. The SDK injects the claim tenant into each connection.
  • Allowlisted extensions. 14 extensions installed by the platform when a project is created: pgvector, pgcrypto, uuid-ossp, pg_trgm, pg_graphql, pg_stat_statements, citext, hstore, ltree, unaccent, btree_gin, btree_gist, fuzzystrmatch, intarray.
  • Database branches. aura branch create copies a project (schema, policies, functions, triggers, views and rows) into a new project with its own URL and keys, deleted automatically after its time to live. A copy made at creation time, not a live replica.

CRUD complete, typed#

The four basic operations share the same .from(table)surface. Returned types are inferred from your schema — open your IDE, autocomplete works on columns, joins and returns.

queries.tstypescript
// List the last 50 active sensors
const { data, error } = await aura
  .from('sensors')
  .select('id, name, temperature, location!inner(city)')
  .eq('active', true)
  .order('captured_at', { ascending: false })
  .limit(50)
Tip

Need more complex SQL? Create a Postgres function and call it via aura.rpc('function_name', args). Functions benefit from the same RLS policies as tables.

14 allowlisted extensions ready#

14 allowlisted extensions, ready on every new project

The platform installs them when the project is created (a project role cannot run CREATE EXTENSION itself, so there is no activation step to forget). List what is installed with aura db extensions list or Studio › Database › Extensions; aura db extensions enable <name> and POST /v1/db/{project_id}/schema/extensions re-apply an allowed one.

  • pgvector (vector)
  • pg_graphql (installed when you enable GraphQL)
  • pg_stat_statements
  • pgcrypto
  • uuid-ossp
  • pg_trgm
  • citext
  • hstore
  • ltree
  • unaccent
  • btree_gin
  • btree_gist
  • fuzzystrmatch
  • intarray
Info

postgis, pg_cron, timescaledb, pgmq, and pg_partman are not bundled in the standard managed image. For scheduled workloads, trigger an Edge Function on a cron schedule in Cron. A project created before this change has only the five historical extensions (vector, pgcrypto, uuid-ossp, pg_trgm, pg_graphql) until the platform runs its next schema update; you cannot add the others yourself, because only the database operator can install them.

Connect directly: psql, Prisma, Drizzle, pg_dump#

Each project has its own PostgreSQL login role (proj_xxxxxxxx) and a host of the form <project-slug>.db.<your-domain> on port 5432. The password is issued on demand and shown once: the platform does not keep it in clear, so a lost password is replaced, never recovered.

connect.shbash
# Host, port, database and role of the linked project (no password)
aura db connection

# Issue a password and print the full connection string (revokes the previous password)
aura db connection --new-password

# Open psql on it (psql asks for the password)
aura connect

aura db connection --new-password prints a string you can paste anywhere:

connection stringtext
postgresql://proj_ef13db60:<password>@my-app-ab12cd34.db.example.com:5432/project_694efb7f_...?sslmode=require

The same card exists in Studio › Settings › Connection › Database (Generate password / Reset password).

  • TLS is required. The endpoint refuses plaintext connections (sslmode=require). On a local k3d stack the certificate is self-signed (*.db.localhost), so sslmode=verify-full fails there by design; production uses a certificate issued for *.db.<your-domain>.
  • Confined to your project. The router picks your database from the role name and forces it: asking for another database name still lands in yours, and presenting your role on another project's host is refused. Your role is not a superuser and does not bypass RLS; to run a migration as the table owner, use BEGIN; SET LOCAL ROLE tenant_<project-uuid>_admin; …; COMMIT;.
  • Tables you create here are private to the platform. Row Level Security cannot be switched on automatically for a table created over this connection (that needs a PostgreSQL superuser, which a managed project never exposes), so the public keys get no privilege on it: the REST API answers permission denied to the anon key and to signed-in users, while the service_role key, Studio and your own role read and write it. To expose it to the SDK, enable RLS, grant, then add a policy (see Row-Level Security): ALTER TABLE posts ENABLE ROW LEVEL SECURITY; GRANT SELECT ON posts TO aura_anon; CREATE POLICY …. PostgREST learns about the new table when you run NOTIFY pgrst, 'reload schema'. Tables created over this connection before this rule existed are closed the next time you issue a password (aura db connection --new-password), unless they already have RLS enabled.
  • Transaction pooler. Connections go through PgBouncer in transaction mode. Session state does not carry over from one transaction to the next: use SET LOCAL inside a transaction, and expect named prepared statements, LISTEN and session-level advisory locks not to be reliable (Prisma: add pgbouncer=true to the URL).
  • Client version. pg_dump refuses a server newer than itself: use a PostgreSQL 18 client (pg_dump --version).
Info

Direct access depends on the deployment publishing the PostgreSQL port. It is on for the local k3d stack and for deployments whose Helm values enable it (ingress.tcp.enabled behind ingress-nginx, or pgproxy.nodePort); the Studio and aura db connection tell you when it is not.

To go further#