datasource.client ^v2.7.203

Creates a configurable client for working with a datasource.

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

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.

  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.

  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.

  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.

  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.

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.

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.

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

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

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.

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

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:

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

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