SQL Quickstart

Overview

Orvanta supports multiple SQL databases including PostgreSQL, MySQL, BigQuery, Amazon Redshift, and DuckDB. Each requires creating a dedicated connection before use.

Create connection

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

PostgreSQL

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

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

Supabase integration

When creating a PostgreSQL connection, 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 Connections page.
2. Click Add connection 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

BigQuery

To connect to BigQuery:

1. Go to the Connections page.
2. Click Add connection 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 connection.

Amazon Redshift

Redshift is compatible with PostgreSQL connections and scripts:

1. Select PostgreSQL connection 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.

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, BigQuery, or DuckDB).

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 connection 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 connection 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 connection 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)

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 connections:

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

Database connection 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 '%%OV_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 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;
  }
}

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);
        }
      });
    });
  });
}

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 or standalone.

Customization options include:

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

What’s next?

Real-world use cases may require:

• Passing variables and secrets
• Connecting to connections
• Triggering scripts via various methods
• Composing scripts in flows
• Database studio for database visualization and management
• 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

Use the Database studio drawer to visualize and manage databases (PostgreSQL, MySQL, 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.