---
url: /process-functions/datasource-client.md
description: Creates a configurable client for working with a datasource.
---

# datasource.client 

Creates a configurable client for working with a datasource.

::: tip Why use `datasource.client`?
This newer client API is the **preferred** way to run datasource queries.

Compared to the older `datasource.select()`, `datasource.query()` and `datasource.insert()` methods, it provides:

* **Streaming rows** instead of loading everything into memory\
  (ideal for large datasets)
* **Fine-grained configuration**\
  (error handling, return types, fetch size, query timeouts)
* **Supports BigQuery and other future non JDBC Datasources**
* **Uses a connection pool**
  doesn't create a new connection per query like old methods, that would of potentially exhausted database connections.
* **Termination** of a process will also terminate any ongoing queries.

If you are writing new scripts, you should prefer `datasource.client()` over legacy datasource methods.
:::

## Creating a client

```js
const client = datasource.client("Internal Datastore");
```

#### Parameters

* `datasource` – The name of a datasource.

The returned `client` exposes configuration methods (chainable) and query methods.

## Configuration methods

All configuration methods are **chainable** and optional.\
If you don't call them, sensible defaults are used.

### Quick overview

| Method                   | Type    | Default | Applies to      | Description                                 |
| ------------------------ | ------- | ------- | --------------- | ------------------------------------------- |
| `alwaysReturnStrings`    | boolean | `false` | JDBC + BigQuery | Return everything as strings (legacy mode). |
| `suppressErrors`         | boolean | `true`  | JDBC + BigQuery | Log errors instead of throwing.             |
| `setFetchSize`           | number  | `0`     | JDBC only       | Hint to driver for row prefetch window.     |
| `setQueryTimeoutSeconds` | number  | `0`     | JDBC only       | Query timeout in seconds.                   |

* ### `client.alwaysReturnStrings(flag)`

  Controls how column values are returned to your script.

  ```js
  client.alwaysReturnStrings(true | false);
  ```

  * **Default:** `false`

  * When `false` (recommended):
    * Numeric columns are returned as JavaScript numbers where possible.
    * Binary/byte columns may be exposed as base64-compatible data for scripts.

  * When `true`:
    * All values are returned as **strings**, matching the behaviour of the older datasource APIs.

  Use `true` if you want to preserve compatibility with older MODLR scripts that assume everything is a string.

* ### `client.suppressErrors(flag)`

  Controls whether SQL/driver errors are thrown or just logged.

  ```js
  client.suppressErrors(true | false);
  ```

  * **Default:** `true`
  * When `true`:
    * Errors are logged and the client attempts to continue, similar to the older datasource helpers.
  * When `false`:
    * Errors will be thrown into your script so you can handle them explicitly with `try` / `catch`.

  This is useful when you need strict error handling in complex scripts.

* ### `client.setFetchSize(size)`

  Configures the JDBC fetch/streaming window, if supported by the driver.

  ```js
  client.setFetchSize(500);
  ```

  * **Default:** `0` (use driver default)
  * **Applies to:** JDBC datasources only (ignored for BigQuery).

  This controls how many rows are prefetched from the database at a time when streaming via `select`. Larger values may reduce round trips but increase memory usage per stream.

* ### `client.setQueryTimeoutSeconds(seconds)`

  Sets a query timeout (in seconds), if supported by the driver.

  ```js
  client.setQueryTimeoutSeconds(30);
  ```

  * **Default:** `0` (no timeout)
  * **Applies to:** JDBC datasources only (ignored for BigQuery).

  Use this to prevent long-running queries from blocking forever.

## Generic Methods

### `select(sql, [params])`

Executes a SELECT and returns a **row iterator**. Rows are streamed as you iterate; they are **not** all loaded into memory.

```js
const rows = client.select(
    "SELECT id, email, created_at FROM users WHERE active = ?",
    [true],
);

for (const row of rows) {
    console.log(row.id, row.email, row.created_at);
}
```

#### Notes

* `params` is optional. When supplied, `?` placeholders in `sql` are bound positionally.
* The result is **iterable** (e.g. usable in `for…of`).
* Once iteration completes, the underlying cursor is closed and the rows are no longer available.
* On JDBC datasources, this supports streaming to avoid pulling the entire result set into memory.
* Nested queries are supported in a limited way (e.g. running another query inside the loop), but should be **avoided** in normal code.

If the script is cancelled, the iterator will stop yielding rows.

***

### `first(sql, [params])`

Executes a SELECT and returns the **first row** as a plain object, or `null`/`undefined` when there are no results.

```js
const user = client.first("SELECT id, email FROM users WHERE id = ?", [42]);

if (user) {
    console.log(`User email: ${user.email}`);
} else {
    console.log("User not found.");
}
```

This is a convenience wrapper over `select` when you only care about a single row.

***

### `selectAll(sql, [params])`

Executes a SELECT and returns **all rows as an array** (not streamed).

Use this when you do not want an itterator and get a list of rows returned, but be aware, all rows will be in memory.

```js
const rows = client.selectAll(
    "SELECT id, email, status FROM users WHERE active = ?",
    [true],
);

console.log("Total active users:", rows.length);

for (const row of rows) {
    console.log(row.id, row.email, row.status);
}
```

***

### `execute(sql, [params])`

Executes a write statement (INSERT/UPDATE/DELETE/DDL) and returns the **number of affected rows** and the **generated key** (if applicable and the datasource supports it).

```js
const result = client.execute(
    "UPDATE users SET last_login_at = CURRENT_TIMESTAMP WHERE id = ?",
    [42],
);

if (result.affected === 1) {
    console.log("User login timestamp updated.");
}
```

```js
const result = client.execute(`INSERT INTO users ("John", "Doe", ?)`, [50]);

console.log(result.generatedKey); // Returns ID of John in this example.
```

#### Parameters

* `sql` – An `INSERT`, `UPDATE`, `DELETE` or `CREATE`/`ALTER`/`DROP` statement.
* `params` – Optional list of parameterized values to bind to `?` placeholders.

## BigQuery Client Specifics

When the datasource is a BigQuery connection, `datasource.client` exposes read-oriented helpers and metadata listing.

```js
// List datasets within a project
const datasets = bq.listDatasets("my_project_id");

// List tables within a dataset
const tables = bq.listTables("my_project_id.reporting");
```

### Query Parameters

BigQuery supports **named parameters**, not positional `?` placeholders like JDBC.\
Parameters must be named `p1`, `p2`, `p3`, … and supplied as shown below:

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

console.log(row);
```

### BigQuery updates

At this time, **write operations via `client.update` are not supported for BigQuery**.

## Closing Resources

For normal usage you **do not need** to manually close anything; cursors and connections will be returned to their connection pools automatically when:

* A `select` iterator finishes reading all rows, or
* The script completes or cancels.

Manual closing is exposed only for advanced scenarios:

```js
const client = datasource.client("Reporting Database");

// Close all open iterators created by this client and return connections to the pool
client.close();

// If you kept a specific iterator:
const iterator = client.select("SELECT * FROM users");
for (const row of iterator) {
    // ...
}
// You may explicitly close just this one:
iterator.close();
```

Use this only when you are managing complex, long-lived scripts that hold on to multiple iterators.

## Full Example

```js
// Create a client for your datasource
const client = datasource.client("Internal Datastore")
    .suppressErrors(false); // throw errors instead of silently logging

try {
    // Fetch a single row
    const user = client.first("SELECT id, email FROM users WHERE id = ?", [42]);

    if (user) {
        console.log("User found:", user.email);
    } else {
        console.log("User not found");
    }

    // Stream multiple rows (preferred for large datasets)
    const rows = client.select("SELECT id, email FROM users WHERE active = ?", [
        true,
    ]);

    for (const row of rows) {
        console.log(`User ${row.id}: ${row.email}`);
    }

    // Load all rows into memory
    const allUsers = client.selectAll(
        "SELECT id, status FROM users WHERE active = ?",
        [true],
    );

    console.log("Total active users:", allUsers.length);

    // Execute a write operation
    const result = client.execute(
        "UPDATE users SET last_login_at = CURRENT_TIMESTAMP WHERE id = ?",
        [42],
    );

    console.log("Rows affected:", result.affected);

    // Insert example with generated key
    const insert = client.execute(
        "INSERT INTO users (name, age) VALUES (?, ?)",
        ["John Doe", 30],
    );

    console.log("New user ID:", insert.generatedKey);
} catch (err) {
    console.error("Datasource error:", err.message);
} finally {
    // Optional: only needed for advanced/manual control.
    // Clients and open iterators are automatically cleaned up when the script completes or is terminated.
    client.close();
}
```