SQL Quickstart

Overview

Orvanta supports multiple SQL databases including PostgreSQL, MySQL, Microsoft SQL Server, BigQuery, Snowflake, Amazon Redshift, Oracle, and DuckDB. Each requires creating a dedicated resource before use.

Create resource

Resources store configuration and credentials as JSON objects, with resource types defining the required schema. All officially supported resource types are available on the Orvanta Hub.

PostgreSQL

To connect to a PostgreSQL instance (including Supabase or Neon.tech):

1. Navigate to the Resources page.
2. Click Add resource and select PostgreSQL type.
3. Fill in connection details and test the connection.

A sample PostgreSQL resource is provided at path f/examples/demo_orvantashowcases for testing.

Supabase integration

When creating a PostgreSQL resource, select Add a Supabase DB to access a setup wizard. Choose your organization, select a database, enter the password, and complete setup.

Sequin integration

Developers can build on third-party services like Salesforce or HubSpot using SQL through Sequin integration.

MySQL

To connect to MySQL:

1. Go to the Resources page.
2. Click Add resource and select MySQL type.
3. Fill in connection information.

Property	Type	Description	Default	Required
host	string	Instance host		false
port	number	Instance port	3306	false
user	string	Username		true
database	string	Database name		true
password	string	User’s password		true

MS SQL

To connect to Microsoft SQL Server:

1. Go to the Resources page.
2. Click Add resource and select ms_sql_server type.
3. Fill in connection details.

Property	Type	Description	Default	Required
host	string	Instance host		true
port	number	Instance port		false
user	string	Username		false
dbname	string	Database name		true
password	string	User’s password		false
integrated_auth	bool	Use Windows Integrated Authentication	false	false
aad_token	object	OAuth token AD		false
instance_name	string	Named instance		false
encrypt	bool	Enable TLS encryption	true	false
trust_cert	bool	Trust server certificate	true	false
ca_cert	string	CA certificate		false

Authentication methods

MS SQL supports three authentication approaches:

1. Username/Password: Provide user and password fields.
2. Azure AD (Entra): Use the aad_token field with OAuth.
3. Windows Integrated Authentication (Kerberos): Enable integrated_auth.

For Azure AD via Entra, add scope https://database.windows.net//.default to the Orvanta OAuth instance settings.

Windows Integrated Authentication

Requirements:

• Worker must have a valid Kerberos ticket via kinit or keytab.
• Valid /etc/krb5.conf with correct realm configuration.
• Service account with database permissions.

Docker/Kubernetes setup requires mounting a keytab file, configuring /etc/krb5.conf, and optionally running kinit at container startup or using the KRB5_KTNAME environment variable.

To specify read-only intent, add -- ApplicationIntent=ReadOnly to the script.

BigQuery

To connect to BigQuery:

1. Go to the Resources page.
2. Click Add resource and select BigQuery type.
3. Fill in service account credentials.

Property	Type	Description	Required
auth_provider_x509_cert_url	string	Auth provider X.509 certificate URL	false
client_x509_cert_url	string	Client X.509 certificate URL	false
private_key_id	string	ID of private key	false
client_email	string	Service account email	false
private_key	string	Private key	false
project_id	string	Google Cloud project ID	true
token_uri	string	OAuth 2.0 token URI	false
client_id	string	OAuth 2.0 client ID	false
auth_uri	string	OAuth 2.0 authorization URI	false
type	string	Authentication method type	false

Setup steps:

1. Go to Google Cloud Console.
2. Select project, navigate to IAM & Admin > Service accounts.
3. Create a service account with name and description.
4. Assign BigQuery roles (Admin or Data Editor).
5. Generate a JSON key and download.
6. Use the downloaded JSON file properties in the resource.

Snowflake

Connect to Snowflake via OAuth or a standard resource:

• For OAuth: Create a snowflake_oauth resource if an OAuth connection exists. See the Snowflake App with user roles guide for details.
• For standard connection: Select the Snowflake resource type and fill in credentials.

Property	Type	Description	Required
account_identifier	string	Format: <orgname>-<account_name>	true
private_key	string	Private key for authentication	true
public_key	string	Public key for authentication	true
warehouse	string	Snowflake warehouse to use	false
username	string	Username for login	true
database	string	Database name	true
schema	string	Schema within database	false
role	string	Role to assume	false

Setup details:

• Account identifier: Found in the Snowflake web interface URL format https://app.snowflake.com/orgname/account_name/.
• Username: Create a user in the Snowflake interface under ACCOUNT > Users.
• Keys: Generate using OpenSSL. Example: openssl rsa -pubout -in private_key.pem -out public_key.pem.
• Warehouse, schema, database, role: Found in the Snowflake web interface or via SQL queries.

Amazon Redshift

Redshift is compatible with PostgreSQL resources and scripts:

1. Select PostgreSQL resource type.
2. Get the endpoint from the AWS console under CLUSTERS > your cluster.
3. Extract connection details from the endpoint (example: default-workgroup.475893240789.us-east-1.redshift-serverless.amazonaws.com:5439/dev).

From the endpoint, deduce:

• host: default-workgroup.475893240789.us-east-1.redshift-serverless.amazonaws.com
• port: 5439
• dbname: dev

Note

Redshift SQL differs from PostgreSQL. For example, RETURNING * syntax is unsupported.

Oracle

To connect to an Oracle database:

1. Go to the Resources page.
2. Click Add resource and select Oracle type.
3. Fill in credentials.

Property	Type	Description	Required
database	string	Database name	true
user	string	Username	true
password	string	User’s password	true

Find the database name in the Oracle configuration. Create a user in the Oracle interface under the Users section.

DuckDB

DuckDB scripts run in-memory out-of-the-box without additional setup.

Create script

From the Home page, click the +Script button. Name it, provide a summary, and select a language (PostgreSQL, MySQL, MS SQL, BigQuery, or Snowflake).

Additional customization is available in the settings section at any time.

PostgreSQL

Arguments format:

-- $1 name1 = default arg
-- $2 name2
INSERT INTO demo VALUES ($1::TEXT, $2::INT) RETURNING *

Database resource specified via UI or -- database resource_path line in the script.

MySQL

Arguments format:

-- :name1 (text) = default arg
-- :name2 (int)
INSERT INTO demo VALUES (:name1, :name2)

Database resource specified via UI or -- database resource_path line.

MS SQL

Arguments format:

-- @P1 name1 (varchar) = default arg
-- @P2 name2 (int)
INSERT INTO demo VALUES (@P1, @P2)

Database resource specified via UI or -- database resource_path line.

BigQuery

Arguments format:

-- @name1 (string) = default arg
-- @name2 (integer)
-- @name3 (string[])
INSERT INTO `demodb.demo` VALUES (@name1, @name2, @name3)

Database resource specified via UI or -- database resource_path line.

Snowflake

Arguments format:

-- ? name1 (varchar) = default arg
-- ? name2 (int)
INSERT INTO demo VALUES (?, ?)

Database resource specified via UI or -- database resource_path line.

Amazon Redshift

Follow PostgreSQL instructions but write valid Redshift SQL. Example without RETURNING:

-- $1 name1 = default arg
-- $2 name2
INSERT INTO demo VALUES ($1::TEXT, $2::INT)

Oracle

Arguments format:

-- database f/your/path
-- :name1 (text) = default arg
-- :name2 (int)
-- :name3 (int)
INSERT INTO demo VALUES (:name1, :name2);
UPDATE demo SET col2 = :name3 WHERE col2 = :name2;

DuckDB

Arguments format:

-- $name1 (text) = default arg
-- $name2 (int)
INSERT INTO demo VALUES ($name1, $name2)

S3 objects can be passed as arguments with type s3object:

-- $file (s3object)
SELECT * FROM read_parquet($file)

For workspace files using s3:// notation:

Primary storage:

SELECT * FROM read_parquet('s3:///path/to/file.parquet')

Secondary storage:

SELECT * FROM read_parquet('s3://<secondary_storage>/path/to/file.parquet')

Glob patterns supported:

SELECT * FROM read_parquet('s3:///myfiles/*.parquet')

Attach other database resources:

ATTACH '$res:u/demo/amazed_postgresql' AS db (TYPE postgres);
SELECT * FROM db.public.friends;

Database resource specified via UI or -- database resource_path line.

Result collection

Control script return values with the result_collection directive:

Collection strategy	Output
last_statement_all_rows (default)	Array of records
last_statement_first_row	Record
last_statement_all_rows_scalar	Array of scalars
last_statement_first_row_scalar	Scalar
all_statements_all_rows	Array of array of records
all_statements_first_row	Array of records
all_statements_all_rows_scalar	Array of array of scalars
all_statements_first_row_scalar	Array of scalars
legacy (deprecated)	Behavior before flag introduction

Examples:

-- result_collection=all_statements_first_row_scalar
SELECT 1;
SELECT 2;
SELECT 3;
-- Result: [1, 2, 3]

-- result_collection=last_statement_all_rows
INSERT INTO my_table VALUES ('a', 'b', 'c');
INSERT INTO my_table VALUES ('1', '2', '3');
SELECT * FROM my_table;
-- Result: [
--   { "col1": "a", "col2": "b", "col3": "c" },
--   { "col1": "1", "col2": "2", "col3": "3" }
-- ]

Contextual variables

Use contextual variables wrapped in %% in queries:

SELECT '%%WM_WORKSPACE%%'

Raw queries

Safe interpolated arguments

Safe string interpolation allows flexibility for table/column names while preventing SQL injection. Parameters are checked at runtime and must follow these rules:

• Non-empty string
• Characters are alphabetical (ASCII only), numeric, or underscore (_)
• Does not start with a number
• If an enum, must be a defined variant

Example with enum:

-- :daily_minimum_calories (int)
-- %%table_name%% fruits/vegetables/cereals
SELECT name, calories FROM %%table_name%% WHERE calories > daily_minimum_calories

Example without enum (applies other rules only):

-- :daily_minimum_calories (int)
-- %%table_name%%
SELECT name, calories FROM %%table_name%% WHERE calories > daily_minimum_calories

Unsafe interpolation on REST script

TypeScript, Deno, or Python clients allow string interpolation but risk SQL injection:

SELECT * FROM ${table}

Use with caution only in trusted environments.

PostgreSQL examples

TypeScript (Bun):

import * as orvanta from 'orvanta-client';
import { Client } from 'pg';

type Postgresql = {
  host: string,
  port: number,
  user: string,
  dbname: string,
  sslmode: string,
  password: string,
  root_certificate_pem: string
}

export async function main(query = 'SELECT * FROM demo', pg_resource: Postgresql) {
  const client = new Client({
    host: pg_resource.host,
    port: pg_resource.port,
    user: pg_resource.user,
    password: pg_resource.password,
    database: pg_resource.dbname,
    ssl: pg_resource.ssl,
  });
  try {
    await client.connect();
    const res = await client.query(query);
    await client.end();
    return res.rows;
  } catch (error) {
    console.error('Database query failed:', error);
    throw error;
  }
}

TypeScript (Deno):

import {
  type Sql,
} from "https://deno.land/x/[email protected]/mod.ts";
import { Client } from "https://deno.land/x/[email protected]/mod.ts"

type Postgresql = {
  host: string;
  port: number;
  user: string;
  dbname: string;
  sslmode: string;
  password: string;
};

export async function main(db: Postgresql, query: Sql = "SELECT * FROM demo;") {
  if (!query) {
    throw Error("Query must not be empty.");
  }
  const { rows } = await pgClient(db).queryObject(query);
  return rows;
}

export function pgClient(db: any) {
  let db2 = {
    ...db,
    hostname: db.host,
    database: db.dbname,
    tls: {
        enabled: false,
    },
  }
  return new Client(db2)
}

Python:

from typing import TypedDict, Dict, Any
import psycopg2

class postgresql(TypedDict):
    host: str
    port: int
    user: str
    dbname: str
    sslmode: str
    password: str
    root_certificate_pem: str

def main(query: str, db_config: postgresql) -> Dict[str, Any]:
    conn = psycopg2.connect(
        host=db_config["host"],
        port=db_config["port"],
        user=db_config["user"],
        password=db_config["password"],
        dbname=db_config["dbname"],
        sslmode=db_config["sslmode"],
        sslrootcert=db_config["root_certificate_pem"],
    )
    cur = conn.cursor()
    cur.execute(query)
    rows = cur.fetchall()
    cur.close()
    conn.close()
    columns = [desc[0] for desc in cur.description]
    result = [dict(zip(columns, row)) for row in rows]
    return result

MySQL examples

TypeScript (Bun):

import { createConnection } from 'mysql';

type Mysql = {
  ssl: boolean,
  host: string,
  port: number,
  user: string,
  database: string,
  password: string
}

export async function main(mysqlResource: Mysql, query: string): Promise<any> {
  return new Promise((resolve, reject) => {
    const connection = createConnection({
      host: mysqlResource.host,
      port: mysqlResource.port,
      user: mysqlResource.user,
      password: mysqlResource.password,
      database: mysqlResource.database,
      ssl: mysqlResource.ssl
    });
    connection.connect(err => {
      if (err) {
        reject(err);
        return;
      }
      connection.query(query, (error, results) => {
        connection.end();
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      });
    });
  });
}

TypeScript (Deno):

import { createPool as createMysqlPool } from "npm:mysql2/promise";

type Mysql = {
  ssl: boolean,
  host: string,
  port: number,
  user: string,
  database: string,
  password: string
}

export async function main(
  mysqlResource: Mysql,
  query: string,
): Promise<any> {
  const sslConfig = mysqlResource.ssl ? { rejectUnauthorized: true } : false;
  const pool = createMysqlPool({
    host: mysqlResource.host,
    user: mysqlResource.user,
    database: mysqlResource.database,
    password: mysqlResource.password,
    port: mysqlResource.port,
    ssl: sslConfig,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
  });
  try {
    const [rows] = await pool.query(query);
    return rows;
  } catch (error) {
    throw new Error(`Failed to execute query: ${error}`);
  } finally {
    await pool.end();
  }
}

Python:

from typing import TypedDict
import mysql.connector as mysql_connector

class mysql(TypedDict):
    ssl: bool
    host: str
    port: float
    user: str
    database: str
    password: str

def main(mysql_credentials: mysql, query: str) -> str:
    connection = mysql_connector.connect(
        host=mysql_credentials["host"],
        user=mysql_credentials["user"],
        password=mysql_credentials["password"],
        database=mysql_credentials["database"],
        port=int(mysql_credentials["port"]),
        ssl_disabled=not mysql_credentials["ssl"],
    )
    cursor = connection.cursor()
    cursor.execute(query)
    result = cursor.fetchone()
    cursor.close()
    connection.close()
    return str(result[0])

Customize your script

After completing the script, click Deploy to save it to the workspace. Use it in flows, apps, or standalone.

Customization options include:

• Metadata (path, name, description)
• Runtime settings (concurrency limits, worker group, cache, dedicated workers)
• Generated UI

What’s next?

Real-world use cases may require:

• Passing variables and secrets
• Connecting to resources
• Triggering scripts via various methods
• Composing scripts in flows, low-code apps, or full-code apps
• Database studio for database visualization and management in apps
• Sharing scripts on the Orvanta Hub

Scripts are immutable with a hash for each deployment. Referencing by path refers to the latest deployed hash.

Database studio

From the App editor, use the Database studio component to visualize and manage databases (PostgreSQL, MySQL, MS SQL, Snowflake, BigQuery supported).

Capabilities:

• Display table content
• Edit table content by editing cells directly (when editable)
• Add a new row
• Delete a row

Streaming large query results to S3 (Enterprise feature)

When SQL scripts return too much data exceeding the 10,000 row limit, use the s3 flag to stream query results to file storage.