@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

$3

- 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[])

$3

- zod schemas
- databaseMetadataSchemasByType – Zod schemas for validating integration metadata by integration type
``ts
databaseMetadataSchemasByType['big-query'].safeParse({ ... })
`
- types
- type DatabaseIntegrationMetadataByType – Integration metadata types by integration type
`ts
const metadata: DatabaseIntegrationMetadataByType['big-query'] = { ... }
`

$3

- utilities
- getEnvironmentVariablesForIntegrations(integrations: Array, 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 – 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 – 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

$3

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

$3

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

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

$3

`ts
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);
}
`

$3

`ts
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"}' },
// ]
`

$3

`ts
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())

$3

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

`python
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.

`python
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)
``