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) {
  return host.replace('https://', '').replace('http://', '').split('/')[0];
}

function normalizeHost(host) {
  host = host.trim().replace(/\/+$/, '');
  if (!host.startsWith('https://') && !host.startsWith('http://')) {
    host = 'https://' + host;
  }
  return host;
}

async function runPKCEFlow(host, clientId, clientSecret, redirectUri, scope) {
  if (!redirectUri) redirectUri = DEFAULT_REDIRECT_URI;
  if (!scope) scope = 'all-apis';
  
  host = normalizeHost(host);
  
  // Generate PKCE verifier and challenge
  const verifier = crypto.randomBytes(48).toString('base64url');
  const challenge = crypto.createHash('sha256').update(verifier).digest('base64url');
  const state = crypto.randomBytes(24).toString('base64url');
  
  const redirectUrl = new URL(redirectUri);
  const port = redirectUrl.port || '8040';
  const callbackPath = redirectUrl.pathname || '/callback';
  
  const authParams = new URLSearchParams({
    response_type: 'code',
    client_id: clientId,
    redirect_uri: redirectUri,
    scope: scope,
    code_challenge: challenge,
    code_challenge_method: 'S256',
    state: state,
  });
  const authUrl = `${host}/oidc/v1/authorize?${authParams.toString()}`;
  
  return new Promise((resolve, reject) => {
    let server;
    
    const timeout = setTimeout(() => {
      if (server) server.close();
      reject(new Error('Timed out waiting for browser sign-in (2 minutes)'));
    }, 2 * 60 * 1000);
    
    server = http.createServer(async (req, res) => {
      const reqUrl = new URL(req.url, `http://${req.headers.host}`);
      
      if (reqUrl.pathname !== callbackPath) {
        res.writeHead(404);
        res.end('Not found');
        return;
      }
      
      const code = reqUrl.searchParams.get('code');
      if (!code) {
        clearTimeout(timeout);
        server.close();
        res.writeHead(400);
        res.end('Missing authorization code');
        reject(new Error('No authorization code received'));
        return;
      }
      
      res.writeHead(200, { 'Content-Type': 'text/html; charset=utf-8' });
      res.end(`<!DOCTYPE html><html><head><meta charset="utf-8"><title>Success</title></head>
        <body style="font-family:sans-serif;text-align:center;padding:50px;">
        <h1 style="color:green;">Authentication Successful!</h1>
        <p>You can close this tab.</p></body></html>`);
      
      clearTimeout(timeout);
      server.close();
      
      // Exchange code for token
      try {
        const fetch = (await import('node-fetch')).default;
        const tokenUrl = `${host}/oidc/v1/token`;
        const params = new URLSearchParams({
          grant_type: 'authorization_code',
          code: code,
          redirect_uri: redirectUri,
          client_id: clientId,
          code_verifier: verifier,
        });
        if (clientSecret) params.set('client_secret', clientSecret);
        
        const response = await fetch(tokenUrl, {
          method: 'POST',
          headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
          body: params.toString(),
        });
        
        const tokenData = await response.json();
        if (!tokenData.access_token) {
          reject(new Error(`Token exchange failed: ${JSON.stringify(tokenData)}`));
          return;
        }
        resolve(tokenData.access_token);
      } catch (err) {
        reject(err);
      }
    });
    
    server.listen(parseInt(port), () => {
      console.log(`Browser opened for sign-in. Waiting for redirect...`);
      open(authUrl).catch(() => {
        console.log(`Open this URL: ${authUrl}`);
      });
    });
  });
}

async function main() {
  const host = process.env.DATABRICKS_HOST;
  const httpPath = process.env.DATABRICKS_HTTP_PATH;
  const clientId = process.env.DATABRICKS_U2M_CLIENT_ID;
  const clientSecret = process.env.DATABRICKS_U2M_CLIENT_SECRET;
  const redirectUri = process.env.DATABRICKS_REDIRECT_URI || DEFAULT_REDIRECT_URI;

  const token = await runPKCEFlow(host, clientId, clientSecret, redirectUri, 'all-apis');

  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(`U2M Custom OAuth OK: ${rows[0].cnt} trips`);

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

main().catch(console.error);

Env vars: DATABRICKS_HOST, DATABRICKS_HTTP_PATH, DATABRICKS_U2M_CLIENT_ID, DATABRICKS_U2M_CLIENT_SECRET

Dependencies: npm install open node-fetch

Setup: Create OAuth app in Account Console → Settings → App connections. Add redirect URI http://localhost:8040/callback.

---

U2M Token-Env Authentication (Headless/CI)

Pass a pre-obtained OAuth access token:

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;
  let token = process.env.DATABRICKS_ACCESS_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(`U2M Token-Env OK: ${rows[0].cnt} trips`);

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

main().catch(console.error);

Env vars: DATABRICKS_HOST, DATABRICKS_HTTP_PATH, DATABRICKS_ACCESS_TOKEN (or DATABRICKS_TOKEN)

---

Error Handling Patterns

Error Classification

function classifyError(error) {
  const errMsg = error.message || String(error);
  const statusCode = error.statusCode || error.status;
  
  if (statusCode === 401 || /invalid_client|invalid credentials|401/i.test(errMsg)) {
    return { type: 'AUTHENTICATION_ERROR', retryable: false, 
             action: 'Check DATABRICKS_TOKEN or OAuth credentials' };
  }
  if (statusCode === 403 || /permission denied|403/i.test(errMsg)) {
    return { type: 'PERMISSION_DENIED', retryable: false,
             action: 'Grant CAN_USE on SQL warehouse to service principal' };
  }
  if (/table.*not found|schema.*not found|catalog.*not found/i.test(errMsg)) {
    return { type: 'RESOURCE_NOT_FOUND', retryable: false,
             action: 'Check table name exists and is accessible' };
  }
  if (/syntax error|parse error|AnalysisException/i.test(errMsg)) {
    return { type: 'SQL_SYNTAX_ERROR', retryable: false,
             action: 'Fix SQL syntax' };
  }
  if (statusCode === 429 || /rate limit|too many requests|429/i.test(errMsg)) {
    return { type: 'RATE_LIMITED', retryable: true,
             action: 'Implement exponential backoff' };
  }
  if (statusCode === 503 || /service unavailable|warehouse.*starting|503/i.test(errMsg)) {
    return { type: 'WAREHOUSE_UNAVAILABLE', retryable: true,
             action: 'Wait for warehouse startup, retry with backoff' };
  }
  if (/ECONNREFUSED|ENOTFOUND|network|connection/i.test(errMsg)) {
    return { type: 'NETWORK_ERROR', retryable: true,
             action: 'Check network connectivity to Databricks' };
  }
  if (/timeout|ETIMEDOUT/i.test(errMsg)) {
    return { type: 'TIMEOUT', retryable: true,
             action: 'Increase timeout or check warehouse status' };
  }
  return { type: 'UNKNOWN_ERROR', retryable: false, action: 'Check full error message' };
}

function isRetryable(error) {
  return classifyError(error).retryable;
}

Retry Pattern with Exponential Backoff

const DEFAULT_RETRY_CONFIG = {
  maxRetries: 3,
  initialBackoff: 1000,
  maxBackoff: 30000,
  multiplier: 2.0,
};

function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

function isRetryableError(error) {
  const msg = String(error.message || error);
  const code = error.statusCode || error.status;
  
  if (code === 429 || code === 503 || code === 502 || code === 500) return true;
  if (/rate limit|too many requests|service unavailable|temporarily unavailable/i.test(msg)) return true;
  if (/ECONNRESET|ETIMEDOUT|connection reset/i.test(msg)) return true;
  return false;
}

async function queryWithRetry(session, query, options = {}, retryConfig = {}) {
  const config = { ...DEFAULT_RETRY_CONFIG, ...retryConfig };
  let lastError;
  let backoff = config.initialBackoff;
  
  for (let attempt = 0; attempt <= config.maxRetries; attempt++) {
    try {
      const queryOperation = await session.executeStatement(query, {
        runAsync: true,
        maxRows: 10000,
        ...options,
      });
      const rows = await queryOperation.fetchAll();
      await queryOperation.close();
      return rows;
    } catch (error) {
      lastError = error;
      
      if (!isRetryableError(error) || attempt >= config.maxRetries) {
        throw error;
      }
      
      console.log(`[Retry ${attempt + 1}/${config.maxRetries}] Waiting ${backoff}ms. Error: ${error.message}`);
      await sleep(backoff);
      backoff = Math.min(backoff * config.multiplier, config.maxBackoff);
    }
  }
  throw lastError;
}

---

Multi-Auth Pattern

Support multiple auth types in a single application using APP_AUTH_TYPE:

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

function resolveAuthType() {
  let authType = (process.env.APP_AUTH_TYPE || '').trim().toLowerCase();
  const aliases = {
    '1': 'pat',
    '2': 'oauth_m2m',
    'm2m': 'oauth_m2m',
    '3': 'u2m_custom_oauth_app',
    'u2m': 'u2m_custom_oauth_app',
    '4': 'u2m_token_env',
    'token_env': 'u2m_token_env',
  };
  return aliases[authType] || authType || 'pat';
}

async function buildConnectOptions(host, httpPath, authType) {
  const baseOptions = {
    host: serverHostname(host),
    path: httpPath,
    userAgentEntry: 'YourCompany_YourProduct/1.0.0',
  };

  switch (authType) {
    case 'pat':
      return { ...baseOptions, token: process.env.DATABRICKS_TOKEN };
      
    case 'oauth_m2m':
      return {
        ...baseOptions,
        authType: 'databricks-oauth',
        oauthClientId: process.env.DATABRICKS_CLIENT_ID,
        oauthClientSecret: process.env.DATABRICKS_CLIENT_SECRET,
      };
      
    case 'u2m_custom_oauth_app': {
      const token = await runPKCEFlow(
        host,
        process.env.DATABRICKS_U2M_CLIENT_ID,
        process.env.DATABRICKS_U2M_CLIENT_SECRET,
        process.env.DATABRICKS_REDIRECT_URI || 'http://localhost:8040/callback',
        'all-apis'
      );
      return { ...baseOptions, token: token };
    }
      
    case 'u2m_token_env':
      return {
        ...baseOptions,
        token: process.env.DATABRICKS_ACCESS_TOKEN || process.env.DATABRICKS_TOKEN,
      };
      
    default:
      throw new Error(`Unsupported APP_AUTH_TYPE: ${authType}`);
  }
}

APP_AUTH_TYPE	Auth flow	Env vars required
pat (default)	Personal Access Token	DATABRICKS_TOKEN
oauth_m2m or m2m	OAuth M2M (client credentials)	DATABRICKS_CLIENT_ID, DATABRICKS_CLIENT_SECRET
u2m_custom_oauth_app or u2m	U2M custom OAuth app (PKCE)	DATABRICKS_U2M_CLIENT_ID, DATABRICKS_U2M_CLIENT_SECRET
u2m_token_env	U2M pre-obtained token	DATABRICKS_ACCESS_TOKEN or DATABRICKS_TOKEN

---

Session and Query Pattern

const client = new DBSQLClient();
const connection = await client.connect(connectOptions);
const session = await connection.openSession();

const queryOperation = await session.executeStatement(
  'DESCRIBE TABLE samples.nyctaxi.trips',
  {
    runAsync: true,   // Recommended: don't block server thread
    maxRows: 10000,   // Enables direct results (server returns data inline)
  }
);

const rows = await queryOperation.fetchAll();  // All rows; use fetchChunk() for large datasets
await queryOperation.close();

// Process rows - each row is an object with column names as keys
for (const row of rows) {
  const colName = row.col_name;
  const dataType = row.data_type;
  const comment = row.comment != null ? row.comment : 'NULL';  // Handle nullable
  console.log(`${colName} | ${dataType} | ${comment}`);
}

await session.close();
await client.close();  // Always close to release server resources

---

Auth Isolation

Run tests with a clean environment using env -i to avoid credential conflicts:

# PAT test - only set PAT-related vars
env -i PATH=$PATH HOME=$HOME \
  DATABRICKS_HOST=$DATABRICKS_HOST \
  DATABRICKS_HTTP_PATH=$DATABRICKS_HTTP_PATH \
  DATABRICKS_TOKEN=$DATABRICKS_TOKEN \
  node your_script.js

# OAuth M2M test - only set M2M vars (no DATABRICKS_TOKEN)
env -i PATH=$PATH HOME=$HOME \
  DATABRICKS_HOST=$DATABRICKS_HOST \
  DATABRICKS_HTTP_PATH=$DATABRICKS_HTTP_PATH \
  DATABRICKS_CLIENT_ID=$DATABRICKS_CLIENT_ID \
  DATABRICKS_CLIENT_SECRET=$DATABRICKS_CLIENT_SECRET \
  node your_script.js

# U2M Custom OAuth App test - only set U2M vars
env -i PATH=$PATH HOME=$HOME \
  DATABRICKS_HOST=$DATABRICKS_HOST \
  DATABRICKS_HTTP_PATH=$DATABRICKS_HTTP_PATH \
  DATABRICKS_U2M_CLIENT_ID=$DATABRICKS_U2M_CLIENT_ID \
  DATABRICKS_U2M_CLIENT_SECRET=$DATABRICKS_U2M_CLIENT_SECRET \
  node your_script.js

# U2M Token-Env test - pre-obtained token
env -i PATH=$PATH HOME=$HOME \
  DATABRICKS_HOST=$DATABRICKS_HOST \
  DATABRICKS_HTTP_PATH=$DATABRICKS_HTTP_PATH \
  DATABRICKS_ACCESS_TOKEN=$MY_OAUTH_TOKEN \
  node your_script.js

Why auth isolation? Mixing credentials (e.g., setting both DATABRICKS_TOKEN and DATABRICKS_CLIENT_ID) can cause unexpected behavior. Always test with only the credentials for the specific auth type.

---

Troubleshooting

Common Errors

Error	Cause	Solution
getaddrinfo ENOTFOUND	Network/host issue	Verify DATABRICKS_HOST URL
invalid path	Missing warehouse path	Set DATABRICKS_HTTP_PATH=/sql/1.0/warehouses/<id>
401 Unauthorized	Invalid credentials	Check token/secret
403 PERMISSION_DENIED	SP lacks permission	Grant CAN_USE on SQL warehouse to service principal
timeout	Query timeout	Increase timeout or check warehouse status
503 Service Unavailable	Warehouse starting	Wait and retry with backoff

OAuth-Specific Errors

Error	Cause	Solution
OAuth application not available	Account not enabled for OAuth	Enable OAuth in account console
redirect_uri mismatch	URI not registered	Add redirect URI in App connections
invalid_client	Wrong client ID/secret	Verify credentials match registered OAuth app
invalid_grant	Auth code expired/reused	Auth codes are single-use; restart OAuth flow

Driver Behavior Notes

• M2M auto-refresh: The driver fetches token from https://<host>/oidc/v1/token and refreshes automatically.
• Info logging: The driver logs Created DBSQLClient and initializing thrift client at info level. This is normal.
• DESCRIBE TABLE results: Returns objects with col_name, data_type, comment. The comment field can be null.

---

Validation Query

Verify User-Agent telemetry is being recorded correctly:

SELECT event_time, user_agent, action_name, request_params
FROM system.access.audit
WHERE event_time > current_timestamp() - INTERVAL 1 HOUR
  AND lower(user_agent) LIKE '%yourcompany%'
ORDER BY event_time DESC
LIMIT 10;

---

Connection Pool Pattern

For high-throughput applications, use a session pool pattern:

class DatabricksConnectionPool {
  constructor(config) {
    this.client = null;
    this.connection = null;
    this.sessionPool = [];
    this.maxSessions = config.maxSessions || 5;
  }

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

  async acquireSession() {
    if (this.sessionPool.length > 0) {
      return this.sessionPool.pop();
    }
    return await this.connection.openSession();
  }

  releaseSession(session) {
    this.sessionPool.push(session);
  }

  async executeQuery(query) {
    const session = await this.acquireSession();
    try {
      const op = await session.executeStatement(query, { runAsync: true, maxRows: 10000 });
      const rows = await op.fetchAll();
      await op.close();
      return rows;
    } finally {
      this.releaseSession(session);
    }
  }

  async shutdown() {
    for (const session of this.sessionPool) {
      await session.close();
    }
    await this.client.close();
  }
}

---

U2M Token Refresh Pattern

For long-running U2M applications, track token expiry and refresh proactively:

class U2MConnectionManager {
  constructor(config) {
    this.config = config;
    this.token = null;
    this.tokenExpiresAt = 0;
    this.refreshBufferMs = 5 * 60 * 1000;  // Refresh 5 min before expiry
  }

  async refreshToken() {
    this.token = await runPKCEFlow(
      this.config.host,
      this.config.clientId,
      this.config.clientSecret,
      this.config.redirectUri,
      'all-apis'
    );
    this.tokenExpiresAt = Date.now() + 55 * 60 * 1000;  // 55 min TTL
    await this.reconnect();
  }

  async reconnect() {
    if (this.client) await this.client.close();
    this.client = new DBSQLClient();
    this.connection = await this.client.connect({
      host: serverHostname(this.config.host),
      path: this.config.httpPath,
      token: this.token,
      userAgentEntry: 'YourCompany_YourProduct/1.0.0',
    });
    this.session = await this.connection.openSession();
  }

  async executeQuery(query) {
    if (Date.now() >= this.tokenExpiresAt - this.refreshBufferMs) {
      await this.refreshToken();
    }
    const op = await this.session.executeStatement(query, { runAsync: true, maxRows: 10000 });
    const rows = await op.fetchAll();
    await op.close();
    return rows;
  }
}

---

Implementation Learnings

1. Host normalization: The driver expects bare hostname without https://. Always strip scheme before passing to connect().

2. No built-in U2M browser: Unlike Go SQL driver which has u2m.NewAuthenticator(), Node.js driver requires custom PKCE implementation for U2M browser flows.

3. userAgentEntry placement: Pass userAgentEntry directly in connect() options (not nested in another object).

4. Session lifecycle: Always call session.close() and client.close() in cleanup. Use try/finally patterns.

5. Async operations: Use runAsync: true for executeStatement() to avoid blocking. Fetch results with fetchAll() then close() the operation.

6. Port management for PKCE: Kill lingering processes on callback port before running U2M tests to avoid "port in use" errors.

7. CLIENT_ID Variables: Use DATABRICKS_CLIENT_ID for service principals (M2M) and DATABRICKS_U2M_CLIENT_ID for custom OAuth apps (U2M). Never mix these.

PKCE Flow Implementation Details

When implementing custom OAuth U2M PKCE flows in Node.js, follow these patterns:

Use proper event handling for HTTP server:

const server = http.createServer((req, res) => {
  const url = new URL(req.url, `http://localhost:${port}`);
  if (url.pathname === callbackPath) {
    const code = url.searchParams.get('code');
    if (code) {
      res.writeHead(200, { 'Content-Type': 'text/html' });
      res.end('<html><body><h1>Authentication Successful</h1><p>You can close this tab.</p></body></html>');
      resolve(code);
      server.close();
    }
  }
});

Wrap in Promise with timeout:

const code = await Promise.race([
  new Promise((resolve, reject) => { /* server logic */ }),
  new Promise((_, reject) => 
    setTimeout(() => reject(new Error('Timeout waiting for auth')), 120000)
  )
]);

Open browser after server is listening:

server.listen(port, () => {
  console.log(`Listening on port ${port}...`);
  open(authUrl);  // Use 'open' package for cross-platform browser opening
});

---

Key Differences from Other Language Drivers

Aspect	Node.js SQL Driver	Go SQL Driver	Java JDBC
Package	@databricks/sql	databricks-sql-go	databricks-jdbc
Install	npm install @databricks/sql	go get github.com/databricks/databricks-sql-go	Maven dependency
User-Agent	userAgentEntry option	WithUserAgentEntry()	UserAgentEntry connection property
M2M Config	authType: 'databricks-oauth' + oauthClientId/Secret	m2m.NewAuthenticator()	AuthMech=11 + Auth_Client_ID/Secret
Host format	Bare hostname (no https://)	Bare hostname	Full URL or hostname
Connection	client.connect() → connection.openSession()	sql.OpenDB(connector)	DriverManager.getConnection()
U2M Browser	Custom PKCE implementation required	u2m.NewAuthenticator() (built-in)	Not built-in