Data tables

Overview

Orvanta Data Tables enable storage and querying of relational data with minimal setup using automatically managed databases. They provide a workspace-scoped approach to leverage SQL within workflows without exposing credentials. These tables integrate with full-code apps via SQL backend runnables and the Database Studio component for visual data browsing and editing.

Data tables support typed schema queries from TypeScript, Python, and DuckDB clients, and can be browsed, edited, and used as Postgres trigger sources without exposing underlying connection strings.

Getting started

Configure a Data Table by navigating to workspace settings → Data Tables. Superadmins can utilize a Custom instance database for zero-setup initialization.

Usage examples

TypeScript:

import * as orvanta from 'orvanta-client';

export async function main(user_id: string) {
  let sql = orvanta.datatable();
  let friend = await sql`SELECT * FROM friend WHERE id = ${user_id}`.fetchOne();
  return friend;
}

Python:

import orvanta

def main(user_id: str):
  db = orvanta.datatable()
  friend = db.query('SELECT * FROM friend WHERE id = $1', user_id).fetch_one()
  return friend

DuckDB:

ATTACH 'datatable' AS dt;
USE dt;
SELECT * FROM friend WHERE id = $user_id;

Schemas

Data tables support Postgres schemas for logical organization:

• Create, rename, and drop schemas from workspace settings
• Reference tables using schema.table syntax
• Switch default search path by appending :myschema to the data table name

Query methods

All query calls return statement objects with four execution methods:

• fetch() — all rows (default)
• fetchOne() / fetch_one() — first row or null
• fetchOneScalar() / fetch_one_scalar() — first column of first row
• execute() — run query returning nothing

For multi-statement queries, pass a result collection mode:

await sql`...`.fetch({ resultCollection: 'all_statements_all_rows' });

Available modes include last_statement_all_rows, last_statement_first_row, all_statements_all_rows, and scalar variants.

TypeScript also supports an alternative syntax:

await sql.query('SELECT * FROM friend WHERE id = $1', user_id).fetchOne();

Type-checked queries

The editor introspects data table schemas to type-check queries at edit time for TypeScript, Python, and DuckDB. String-interpolated parameters are transformed into safe parameterized queries. When schemas change, the editor re-introspects automatically.

Dynamic SQL with sql.raw

Use sql.raw(value) to inline strings directly without parameterization, useful for dynamic table or column names:

const sql = orvanta.datatable();
await sql`SELECT * FROM ${sql.raw(table)} WHERE age > ${age}`.fetch();

Caution

Ensure values come from trusted input to prevent SQL injection.

Assets integration

Data tables function as assets in Orvanta. The platform auto-detects data table references in code and determines Read (SELECT) versus Write mode (UPDATE, DELETE). Assets appear as nodes in flows, visualizing data dependencies between scripts.

Database Studio integration

Data tables serve as first-class sources in the Database Studio app component. Select “Data Table” as the component type, then use the table picker — no resource wiring required. Users access full Database Studio functionality without credential exposure.

Postgres resource integration

Data tables backed by Postgres resources can serve as sources for Postgres triggers, enabling reactions to inserts, updates, and deletes without distributing Postgres credentials.

Workspace scope

Data tables scope to individual workspaces. All workspace members access data tables; credentials remain managed internally by Orvanta and never expose to users.

Default data table

The main data table serves as the default. Scripts access it without specifying its name:

orvanta.datatable()  # Uses 'main' implicitly

Database types

Custom instance database

• Uses the Orvanta instance database
• Zero-setup, one-click provisioning
• Requires superadmin configuration
• Accessible only to workspaces defining a data table pointing to it

Postgres resource

• Attach a workspace Postgres resource to a data table
• Ideal for full control over database hosting while benefiting from Orvanta credential management

Permissions

Orvanta currently does not enforce database-level permissions in data tables. Any workspace member can execute full CRUD operations. Table and row-level permissions may be introduced in future versions.

Agent workers support

orvanta.datatable() and orvanta.ducklake() work on agent workers by default from TypeScript and Python. Agent workers automatically detect the execution environment and fall back to a run-and-wait flow against the main Orvanta API, maintaining script portability between regular and agent workers without code changes.

Related

• Ducklake — Query massive datasets in S3 with SQL.
• Persistent storage & databases — All storage options.