@deepnote/database-integrations

@deepnote/database-integrations

The database-integrations package defines the Deepnote database integrations.

Overview

This package provides TypeScript types and utilities for working with Deepnote SQL block integrations and other database integrations, including:

• Integration types
• Authentication method types
• Integration metadata schemas

Supported Integrations

• DataFrame SQL (pandas-dataframe)
• Amazon Athena (athena)
• Amazon Redshift (redshift)
• ClickHouse (clickhouse)
• Databricks (databricks)
• Dremio (dremio)
• Google AlloyDB (alloydb)
• Google BigQuery (big-query)
• Google Spanner (spanner)
• MariaDB (mariadb)
• Materialize (materialize)
• Microsoft SQL Server (Azure SQL) (sql-server)
• MindsDB (mindsdb)
• MongoDB (mongodb)
• MySQL (mysql)
• PostgreSQL (pgsql)
• Snowflake (snowflake)
• Trino (trino)

API reference

Integration types

• types
  • type DatabaseIntegrationType – All supported database integration types
  • type DatabaseIntegrationTypeWithSslSupport – Database integration types that support SSL (subset of DatabaseIntegrationType)
  • type SqlIntegrationType – SQL integration types (subset of DatabaseIntegrationType), excludes mongodb
• runtime values
  • databaseIntegrationTypes – Array of all supported database integration types (DatabaseIntegrationType[])
  • databaseIntegrationTypesWithSslSupport – Array of all database integration types that support SSL (DatabaseIntegrationTypeWithSslSupport[])
  • sqlIntegrationTypes – Array of all SQL integration types (SqlIntegrationType[])

Integration metadata

• zod schemas
  • databaseMetadataSchemasByType – Zod schemas for validating integration metadata by integration type

    databaseMetadataSchemasByType['big-query'].safeParse({ ... })
    

• types
  • type DatabaseIntegrationMetadataByType – Integration metadata types by integration type

    const metadata: DatabaseIntegrationMetadataByType['big-query'] = { ... }
    

Environment variables and configuration

• utilities
  • getEnvironmentVariablesForIntegrations(integrations: Array<DatabaseIntegrationConfig>, params: { projectRootDirectory: string, snowflakePartnerIdentifier?: string }) – Creates a list of environment variables to be set when executing a notebook that uses the integrations, the variables are in a format consumed by the Deepnote Toolkit
    • arguments:
      • integrations – list of active integrations
      • params.projectRootDirectory – project root directory, used to construct paths to CA certificates
      • params.snowflakePartnerIdentifier (optional) – Snowflake partner identifier, used to construct Snowflake connection URLs; it is used for Snowflake's internal telemetry/analytics, it does not affect the functionality of the integration
    • returns:
      • envVars: Array<EnvVar> – List of environment variables to be set when executing a notebook that uses the integrations
        • The variable names produced have the following format:
          • *INTEGRATION_NAME*_*METADATA_KEY* - for each metadata field of the integration
          • SQL_*INTEGRATION_ID* - SQL Alchemy config for each SQL integration
      • errors: Array<Error> – List of errors that occurred when generating the environment variables. These are not thrown to allow partial functionality, even when some integrations are misconfigured.
  • getSqlAlchemyInput(sqlIntegration: SqlIntegrationConfig, params: { projectRootDirectory: string, snowflakePartnerIdentifier?: string }) – Creates a SQL Alchemy config for a SQL integration, used to connect to the database from SQL blocks. This is used internally within getEnvironmentVariablesForIntegrations().
    • arguments:
      • sqlIntegration – SQL integration configuration
      • params.projectRootDirectory – project root directory, used to construct paths to CA certificates
      • params.snowflakePartnerIdentifier (optional) – see above
    • returns:
      • SqlAlchemyInput | null – SQL Alchemy config for the integration, null when the integration has federated authentication
    • throws:
      • BigQueryServiceAccountParseError – when parsing BigQuery service account fails
      • SpannerServiceAccountParseError – when parsing Spanner service account fails
• types
  • type DatabaseIntegrationConfig – Configuration for an integration when generating environment variables
    • type – integration type
    • id – integration ID (usually UUID)
    • name – integration name
    • metadata – integration metadata
    • federated_auth_method (optional) – federated authentication method, used only when the integration has federated authentication enabled. The auth method configured via the metadata is also checked, these should match.
  • type SqlIntegrationConfig – Configuration for a SQL integration when generating environment variables (subset of DatabaseIntegrationConfig, excluding non-SQL integrations)
  • type SqlAlchemyInput - SQL Alchemy config for a SQL integration
• errors
  • BigQueryServiceAccountParseError – When parsing BigQuery service account fails
    • cause: Error – The original error
  • SpannerServiceAccountParseError – When parsing Spanner service account fails
    • cause: Error – The original error

Auth methods

These values can be used to configure the integrations that have multiple methods for authentication.

• types
  • type DatabaseAuthMethod – Authentication method for a database integration
  • type AwsAuthMethod – Authentication method for an AWS integration
  • type BigQueryAuthMethod – Authentication method for a BigQuery integration
  • type SnowflakeAuthMethod – Authentication method for a Snowflake integration
  • type FederatedAuthMethod – Authentication method for a federated authentication integration
• runtime values
  • AwsAuthMethods – All supported AWS authentication methods
  • BigQueryAuthMethods – All supported BigQuery authentication methods
  • DatabaseAuthMethods – All supported database authentication methods
  • SnowflakeAuthMethods – All supported Snowflake authentication methods
  • federatedAuthMethods – All supported federated authentication methods

Usage in TypeScript

Example: Show list of available integrations

import { databaseIntegrationTypes } from "@deepnote/database-integrations";

console.log(databaseIntegrationTypes); // ['athena', 'redshift', …]

Example: Validate integration metadata

import { databaseMetadataSchemasByType } from "@deepnote/database-integrations";

const validationResult = databaseMetadataSchemasByType["big-query"].safeParse({
  authMethod: BigQueryAuthMethods.ServiceAccount,
  service_account: "my-account",
});
if (validationResult.error) {
  console.error(validationResult.error);
}

Example: Getting environment variables for configured integrations

import { getEnvironmentVariablesForIntegrations } from "@deepnote/database-integrations";

const integrations = [
  {
    type: "pgsql",
    id: "my-postgres",
    name: "My PostgreSQL Integration",
    metadata: {
      host: "my-host",
      user: "my-user",
      password: "my-password",
      database: "my-database",
    },
  },
];

const { envVars } = getEnvironmentVariablesForIntegrations(integrations, {
  projectRootDirectory: "/path/to/project",
});

// envVars = [
//   { name: "INTEGRATION_MY_POSTGRES_HOST", value: "my-host" },
//   { name: "INTEGRATION_MY_POSTGRES_USER", value: "my-user" },
//   { name: "INTEGRATION_MY_POSTGRES_PASSWORD", value: "my-password" },
//   { name: "INTEGRATION_MY_POSTGRES_DATABASE", value: "my-database" },
//   { name: "SQL_MY_POSTGRES", value: '{"url": "postgresql://my-user:my-password@my-host/my-database", "params": {}, "param_style": "pyformat"}' },
// ]

Example: Mapped types

import {
  type DatabaseIntegrationMetadataByType,
  type DatabaseIntegrationType,
} from "@deepnote/database-integrations";

const myIntegrationDocsLinks: Record<
  DatabaseIntegrationType,
  { docsLink: string }
> = {
  "big-query": { docsLink: "https://example.com/my-docs/bq" },
  // …
};

Usage in notebooks

SQL block execution (set up in @deepnote/blocks) picks the environment variables and passes them to the Deepnote Toolkit (_dntk.execute_sql())

MongoDB usage

MongoDB is not a SQL integration, it is not used via SQL blocks but rather via code blocks and pymongo.

import os
from pymongo import MongoClient

# Connect to MongoDB using the connection string from environment variables
client = MongoClient(os.environ["INTEGRATION_NAME_CONNECTION_STRING"])
db = client[os.environ["INTEGRATION_NAME_DATABASE"]]
# Get the users collection
users_collection = db["users"]
# Find all documents in the users collection
users = list(users_collection.find())

Note: The INTEGRATION_NAME_ prefix of the env variables is constructed using the following logic.

Environment variable names used by the utilities in the Shell and Utilities volume of IEEE Std 1003.1-2001 consist solely of uppercase letters, digits, and the '_' (underscore) from the characters defined in the Portable Character Set and do not begin with a digit.

import re

def convert_to_environment_variable_name(integration_name: str) -> str:
  without_first_digit = f"_{integration_name}" if re.match(r'^\d', integration_name) else integration_name
  upper_cased = without_first_digit.upper()
  return re.sub(r'[^\w]', '_', upper_cased)