---
url: /process-functions/datasourceclient.md
description: Runs SQL queries and statements against a JDBC or BigQuery datasource.
---

#  DatasourceClient 

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

```js
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)`  {#datasource-client}

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

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

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

::: tip 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()](#select) streams rows one at a time, [selectAll()](#selectall) loads every row into an array, and [first()](#first) returns just the first row. Each row is an object keyed by column name.
* **Writing:** [execute()](#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](#bigquery).
* **Types:** numbers come back as numbers. Call [alwaysReturnStrings(true)](#alwaysreturnstrings) 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)](#suppresserrors) 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](/process-functions/batch#datasource-createbatch) and insert each row into the batch.

::: code-group

```js [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);
}
```

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

```js [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:

```js
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()](#listdatasets) and [listTables()](#listtables) work only on BigQuery.

## Methods

### select `select(query, params, rowLimit)`  {#select}

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)`  {#selectall}

Runs a SELECT query and returns every row in an array. Every row is held in memory, so use [select()](#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)`  {#first}

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)`  {#execute}

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()` {#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)`  {#alwaysreturnstrings}

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)`  {#suppresserrors}

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)`  {#setfetchsize}

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)`  {#setquerytimeoutseconds}

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)`  {#listdatasets}

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)`  {#listtables}

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

```js
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

```js
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

```js
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

```js
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](/process-functions/batch#datasource-createbatch): loads many rows into a table.