databricks-isv-nodejs-sql-driver

name	databricks-isv-nodejs-sql-driver
description	PWAF-compliant Databricks SQL Driver for Node.js (@databricks/sql): PAT, OAuth M2M, OAuth U2M (custom OAuth app PKCE + token-env), userAgentEntry telemetry. Use when building or testing integrations that run SQL queries via a Databricks SQL warehouse.

Databricks SQL Driver for Node.js (ISV)

Use this skill when implementing or testing Databricks SQL Driver for Node.js (@databricks/sql) integrations for PWAF-compliant SQL query execution via a Databricks SQL warehouse.

PWAF Documentation Links

Resource	URL
PWAF Home	https://databrickslabs.github.io/partner-architecture/
Authentication Best Practices	https://databrickslabs.github.io/partner-architecture/isv-partners/lakehouse-patterns/access-auth/
OAuth M2M Guide	https://databrickslabs.github.io/partner-architecture/isv-partners/lakehouse-patterns/access-auth/oauth-m2m
OAuth U2M Guide	https://databrickslabs.github.io/partner-architecture/isv-partners/lakehouse-patterns/access-auth/oauth-u2m
User-Agent (SQL Drivers)	https://databrickslabs.github.io/partner-architecture/isv-partners/telemetry-attribution/sql-drivers
Databricks SQL Driver for Node.js	https://docs.databricks.com/aws/en/dev-tools/nodejs-sql-driver
npm	https://www.npmjs.com/package/@databricks/sql

Requirements

• Package: @databricks/sql v1.5.0+
• Node.js: 14+
• SQL Warehouse: Required (DATABRICKS_HTTP_PATH)
• Install: npm install @databricks/sql

Authentication Decision Guide

Which authentication method to use?

Production / automated workloads?
  → OAuth M2M (authType: 'databricks-oauth')  ✅ RECOMMENDED

User-interactive (ISV custom OAuth app)?
  → U2M Custom OAuth App (PKCE flow)  ✅ SUPPORTED

Already have an OAuth access token?
  → U2M Token-Env (token pass-through)  ✅ SUPPORTED

Local development/testing only?
  → PAT (token option)  ⚠️ LIMITED

Note: These patterns do NOT use the driver's built-in OAuth app for U2M. ISV/partner applications should register custom OAuth apps in App connections for proper branding, audit trails, and scoped permissions.

Authentication Comparison

Method	PWAF Status	Auto Token Refresh	Browser Required	Use Case
PAT	⚠️ Limited	No	No	Testing only
OAuth M2M	✅ Recommended	Yes (driver-native)	No	Production/automated
U2M Custom OAuth	✅ Recommended (ISV)	No	Yes	Interactive (custom app)
U2M Token-Env	✅ Supported	No	No	Headless/CI

ISV Note: For user-interactive flows, use U2M Custom OAuth with your registered OAuth app (Account Console → App connections). This provides custom branding, audit trails, and scoped permissions. Do NOT use the driver's built-in OAuth app for ISV applications.

CLIENT_ID Distinction (CRITICAL)

Variable	Purpose	Used By
DATABRICKS_CLIENT_ID	M2M service principal UUID	OAuth M2M only
DATABRICKS_U2M_CLIENT_ID	Custom OAuth app client ID	U2M custom OAuth app

Using the wrong client_id causes: "OAuth application with client_id not available in Databricks account"

Token Lifetime Summary

Auth Type	Token TTL	Refresh Strategy
PAT	User-configured (90 days default)	Generate new token manually
OAuth M2M	~1 hour	Driver handles automatically
U2M Custom OAuth App	~1 hour	Re-authenticate via PKCE, reconnect
U2M Token-Env	~1 hour	Application must handle

---

Host Normalization Helper

The driver's host option expects a bare hostname (no https://):

function serverHostname(host) {
  return host
    .replace('https://', '')
    .replace('http://', '')
    .split('/')[0];
}

---

Environment Variables Reference

Variable	Required For	Description
DATABRICKS_HOST	All	Workspace URL (e.g., https://myworkspace.cloud.databricks.com)
DATABRICKS_HTTP_PATH	All	SQL warehouse HTTP path (e.g., /sql/1.0/warehouses/abc123)
DATABRICKS_TOKEN	PAT	Personal access token
DATABRICKS_CLIENT_ID	OAuth M2M	Service principal UUID
DATABRICKS_CLIENT_SECRET	OAuth M2M	Service principal OAuth secret
DATABRICKS_U2M_CLIENT_ID	U2M Custom OAuth	Custom OAuth app client ID from App connections
DATABRICKS_U2M_CLIENT_SECRET	U2M Custom OAuth	Custom OAuth app client secret
DATABRICKS_REDIRECT_URI	U2M Custom OAuth (optional)	Custom redirect URI (default: http://localhost:8040/callback)
DATABRICKS_ACCESS_TOKEN	U2M Token-Env	Pre-obtained OAuth access token
APP_AUTH_TYPE	Multi-auth	Auth type selector: pat, oauth_m2m, u2m_custom_oauth_app, u2m_token_env

Important: Do not mix M2M and U2M environment variables. Use env -i for clean test environments.

---

Driver Options Reference

Connection Options

Option	Type	Default	Description
host	string	(required)	Databricks workspace hostname (no https://)
path	string	(required)	SQL warehouse HTTP path
token	string	-	Access token (PAT or OAuth)
authType	string	-	Auth type: 'databricks-oauth' for M2M
oauthClientId	string	-	M2M service principal client ID
oauthClientSecret	string	-	M2M service principal secret
userAgentEntry	string	-	Required for PWAF (format: Company_Product/Version)

Query Options (executeStatement)

Option	Type	Default	Description
runAsync	boolean	false	Execute asynchronously (recommended: true)
maxRows	number	10000	Max rows for direct results
queryTimeout	number	0	Query timeout in seconds (0 = no limit)

Example with Options

const connection = await client.connect({
  host: serverHostname(host),
  path: httpPath,
  authType: 'databricks-oauth',
  oauthClientId: clientId,
  oauthClientSecret: clientSecret,
  userAgentEntry: 'YourCompany_YourProduct/1.0.0',
});

const queryOperation = await session.executeStatement(query, {
  runAsync: true,
  maxRows: 10000,
  queryTimeout: 300,  // 5 minutes
});

---

Required Dependencies

{
  "dependencies": {
    "@databricks/sql": "^1.5.0",
    "node-fetch": "^2.7.0",
    "open": "^8.4.2"
  }
}

Package	Purpose	Required For
@databricks/sql	Databricks SQL Driver	All auth types
node-fetch	HTTP client for token exchange	U2M Custom OAuth
open	Opens browser for user sign-in	U2M Custom OAuth

---

Complete Examples

PAT Authentication (Testing Only)

const { DBSQLClient } = require('@databricks/sql');

const USER_AGENT = 'YourCompany_YourProduct/1.0.0';

function serverHostname(host) {
  return host.replace('https://', '').replace('http://', '').split('/')[0];
}

async function main() {
  const host = process.env.DATABRICKS_HOST;
  const httpPath = process.env.DATABRICKS_HTTP_PATH;
  const token = process.env.DATABRICKS_TOKEN;

  const client = new DBSQLClient();
  const connection = await client.connect({
    host: serverHostname(host),
    path: httpPath,
    token: token,
    userAgentEntry: USER_AGENT,
  });

  const session = await connection.openSession();
  const queryOperation = await session.executeStatement(
    'SELECT COUNT(*) as cnt FROM samples.nyctaxi.trips',
    { runAsync: true, maxRows: 10000 }
  );

  const rows = await queryOperation.fetchAll();
  await queryOperation.close();
  console.log(`PAT OK: ${rows[0].cnt} trips`);

  await session.close();
  await client.close();
}

main().catch(console.error);

Env vars: DATABRICKS_HOST, DATABRICKS_HTTP_PATH, DATABRICKS_TOKEN

---

OAuth M2M Authentication (Production Recommended)

Uses the driver's built-in M2M authenticator that handles token fetch and refresh:

const { DBSQLClient } = require('@databricks/sql');

const USER_AGENT = 'YourCompany_YourProduct/1.0.0';

function serverHostname(host) {
  return host.replace('https://', '').replace('http://', '').split('/')[0];
}

async function main() {
  const host = process.env.DATABRICKS_HOST;
  const httpPath = process.env.DATABRICKS_HTTP_PATH;
  const clientId = process.env.DATABRICKS_CLIENT_ID;
  const clientSecret = process.env.DATABRICKS_CLIENT_SECRET;

  const client = new DBSQLClient();
  const connection = await client.connect({
    host: serverHostname(host),
    path: httpPath,
    authType: 'databricks-oauth',
    oauthClientId: clientId,
    oauthClientSecret: clientSecret,
    userAgentEntry: USER_AGENT,
  });

  const session = await connection.openSession();
  const queryOperation = await session.executeStatement(
    'SELECT COUNT(*) as cnt FROM samples.nyctaxi.trips',
    { runAsync: true, maxRows: 10000 }
  );

  const rows = await queryOperation.fetchAll();
  await queryOperation.close();
  console.log(`OAuth M2M OK: ${rows[0].cnt} trips`);

  await session.close();
  await client.close();
}

main().catch(console.error);

Env vars: DATABRICKS_HOST, DATABRICKS_HTTP_PATH, DATABRICKS_CLIENT_ID, DATABRICKS_CLIENT_SECRET

Setup: Create service principal in Account Console → Settings → Service principals. Generate OAuth secret. Grant CAN_USE on the SQL warehouse.

---

U2M Custom OAuth App (PKCE) - Recommended for ISVs

For user-interactive flows with a custom OAuth app:

const { DBSQLClient } = require('@databricks/sql');
const crypto = require('crypto');
const http = require('http');
const { URL, URLSearchParams } = require('url');
const open = require('open');

const USER_AGENT = 'YourCompany_YourProduct/1.0.0';
const DEFAULT_REDIRECT_URI = 'http://localhost:8040/callback';

function serverHostname(host) {