databricks-isv-nodejs-sql-driver

<!-- 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) {