DatasourceClient ^v2.7.203

Runs SQL queries and statements against a JDBC or BigQuery datasource.

const client = datasource.client("Warehouse");

for (const row of client.select("SELECT year, month, product, amount FROM sales WHERE year = ?", ["2026"])) {
    cube.set(row.amount, "Sales", row.year, row.month, row.product, "Actual");
}

Constructor

datasource.client datasource.client(datasource) DatasourceClient

Creates a DatasourceClient for a JDBC or BigQuery datasource. Throws when the datasource doesn't exist or isn't a supported type.

Parameter	Type	Description
datasource	string	The datasource's name or id.

// A client with default settings
const client = datasource.client("Warehouse");

// A client that throws on errors
const client = datasource.client("Warehouse")
    .suppressErrors(false);

The preferred way to query a datasource

Use datasource.client instead of the older datasource.select, datasource.insert and datasource.update. It streams rows instead of loading them all into memory, keeps numbers as numbers, reuses pooled connections, supports BigQuery, and stops running queries when the process is cancelled.

Overview

• Reading: select() streams rows one at a time, selectAll() loads every row into an array, and first() returns just the first row. Each row is an object keyed by column name.
• Writing: execute() runs INSERT, UPDATE, DELETE and DDL statements such as CREATE TABLE.
• Parameters: bind values to ? placeholders through the params array, in order, rather than building them into the SQL. BigQuery uses named parameters instead; see BigQuery.
• Types: numbers come back as numbers. Call alwaysReturnStrings(true) to get every value as a string, like the older datasource functions.
• Errors: by default a failed query is logged and returns no rows, so the script keeps running. Call suppressErrors(false) to have errors thrown instead.
• Settings are chainable: each setting method returns the client, so you can chain them onto datasource.client(...).
• Closing: the client is closed automatically when the process finishes.
• Loading many rows: don't call execute() once per row. Use datasource.createBatch and insert each row into the batch.

select()

// Streams rows; memory stays flat however many rows there are
for (const row of client.select("SELECT product, amount FROM sales WHERE year = ?", ["2026"])) {
    console.log(row.product + " = " + row.amount);
}

selectAll()

// Loads every row into an array
const rows = client.selectAll("SELECT product, amount FROM sales WHERE year = ?", ["2026"]);
console.log(rows.length + " rows");

first()

// Just the first row, or null
const row = client.first("SELECT MAX(amount) AS peak FROM sales WHERE year = ?", ["2026"]);
console.log(row === null ? "No sales" : "Peak: " + row.peak);

BigQuery

BigQuery uses named parameters @p1, @p2, @p3 and so on, passed as an object, instead of ? placeholders:

const row = client.first(
    "SELECT * FROM `my_project.reporting.orders` WHERE order_id = @p1 AND country = @p2 LIMIT 1",
    { p1: "AUS-103939", p2: "AU" }
);

Write statements through execute() aren't supported on BigQuery. listDatasets() and listTables() work only on BigQuery.

Methods

select select(query, params, rowLimit) iterable

Runs a SELECT query and returns its rows as an iterable, read with for...of. Rows are streamed rather than loaded into memory, which makes this the best choice for large results.

Parameter	Type	Description
query	string	The SQL query. Use ? placeholders for values.
params	array	Optional. Values for the ? placeholders, in order.
rowLimit	number	Optional. The most rows to return. 0 or omitted means no limit.

Once iteration finishes, the rows are no longer available. To stop reading early and release the query, call close() on the iterable.

selectAll selectAll(query, params) array

Runs a SELECT query and returns every row in an array. Every row is held in memory, so use select() for large results.

Parameter	Type	Description
query	string	The SQL query. Use ? placeholders for values.
params	array	Optional. Values for the ? placeholders, in order.

first first(query, params) object | null

Runs a SELECT query and returns the first row, or null when there are no rows. Use it for single-value lookups.

Parameter	Type	Description
query	string	The SQL query. Use ? placeholders for values.
params	array	Optional. Values for the ? placeholders, in order.

execute execute(statement, params) object

Runs an INSERT, UPDATE, DELETE or DDL statement. Returns an object with:

• affected: the number of rows changed.
• generatedKey: the key generated by an insert, or null when there isn't one.

Parameter	Type	Description
statement	string	The SQL statement. Use ? placeholders for values.
params	array	Optional. Values for the ? placeholders, in order.

close close()

Closes the client, any open select() iterables, and releases its connection. The client closes automatically when the process finishes, so only call this to release the connection early, such as in a long-running process that's finished with the database.

alwaysReturnStrings alwaysReturnStrings(enabled) DatasourceClient

Sets whether every value is returned as a string. Off by default, so numbers come back as numbers. Turn it on for older scripts that expect strings.

Parameter	Type	Description
enabled	boolean	true to return every value as a string.

suppressErrors suppressErrors(enabled) DatasourceClient

Sets whether errors are logged or thrown. On by default: errors are written to the process log and the call returns no rows. Pass false to have errors thrown, so you can handle them with try / catch.

Parameter	Type	Description
enabled	boolean	true to log errors, false to throw them.

setFetchSize setFetchSize(size) DatasourceClient

Sets how many rows the database driver fetches at a time while select() streams. Larger values mean fewer round trips but more memory. JDBC only; ignored on BigQuery.

Parameter	Type	Description
size	number	Rows per fetch. 0, the default, uses the driver's own setting.

setQueryTimeoutSeconds setQueryTimeoutSeconds(seconds) DatasourceClient

Sets how long a query can run before it's stopped. JDBC only; ignored on BigQuery.

Parameter	Type	Description
seconds	number	The timeout in seconds. 0, the default, means no timeout.

listDatasets listDatasets(projectId) string[]

Returns the names of the datasets in a BigQuery project. Returns an empty array on other datasource types, or when the lookup fails and errors are suppressed.

Parameter	Type	Description
projectId	string	The BigQuery project.

listTables listTables(dataset) string[]

Returns the names of the tables in a BigQuery dataset. Returns an empty array on other datasource types, or when the lookup fails and errors are suppressed.

Parameter	Type	Description
dataset	string	The BigQuery dataset.

Examples

Load query results into a cube

const client = datasource.client("Warehouse");

const rows = client.select(
    "SELECT region, month, SUM(amount) AS total FROM sales WHERE year = ? GROUP BY region, month",
    ["2026"]
);

for (const row of rows) {
    cube.set(row.total, "Sales", "2026", row.month, row.region, "Actual");
}

Insert a row and read its key

const client = datasource.client("Warehouse");

const result = client.execute(
    "INSERT INTO sales (product, amount, year) VALUES (?, ?, ?)",
    ["Widget", 1200, "2026"]
);

console.log("Inserted id " + result.generatedKey + ", rows affected " + result.affected);

Handle errors yourself

const client = datasource.client("Warehouse").suppressErrors(false);

try {
    client.execute("UPDATE sales SET amount = ? WHERE id = ?", [1300, 42]);
} catch (err) {
    script.fail("Update failed: " + err.message);
}

List every table in a BigQuery project

const client = datasource.client("Analytics");

for (const dataset of client.listDatasets("my-project")) {
    for (const table of client.listTables(dataset)) {
        console.log(dataset + "." + table);
    }
}

Related

• datasource.createBatch: loads many rows into a table.