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

<!-- skill-version: 1.0.0 -->

# 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://`):

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

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

```json
{
  "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)

```javascript
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:

```javascript
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:

```javascript
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:

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

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

```javascript
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`:

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

```javascript
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:

```bash
# 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:

```sql
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:

```javascript
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:

```javascript
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:**
```javascript
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:**
```javascript
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:**
```javascript
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 |