// loaders.gl // SPDX-License-Identifier: MIT // Copyright (c) vis.gl contributors import {tableFromIPC, tableToIPC, type Table} from 'apache-arrow'; import type {AsyncDuckDBConnection} from '@duckdb/duckdb-wasm'; import type {Schema} from '@loaders.gl/core/schema'; import type { SourceLoader, CoreAPI, TableQuerySource, TableMetadata } from '@loaders.gl/core/loader-utils'; import {convertArrowToTable} from '@loaders.gl/core/schema-utils'; import {makeDuckDBTableQuerySource, isDuckDBQuerySchema} from './duckdb-table-query'; import {SQLDataSource, SQL_SOURCE_DEFAULT_OPTIONS} from './sql-source'; import type { SQLAdapter, SQLAdapterFactoryContext, SQLCatalogInfo, SQLColumnInfo, SQLQueryOptions, SQLSourceOptions, SQLTableInfo, SQLArrowInsertOptions } from './sql-types'; import { convertRowsToArrowTable, convertSQLColumnsToSchema, escapeSqlString, isNodeRuntime } from './sql-utils'; // __VERSION__ is injected by babel-plugin-version-inline // @ts-ignore TS2304: Cannot find name '__VERSION__'. const VERSION = typeof __VERSION__ !== 'undefined' ? __VERSION__ : 'latest'; /** DuckDB-backed SQL source. */ export const DuckDBSQLSource = { dataType: null as unknown as DuckDBSQLDataSource, batchType: null as never, name: 'DuckDBSQLSource', id: 'duckdb-sql', module: 'sql', version: VERSION, extensions: ['duckdb', 'db'], mimeTypes: [], type: 'duckdb-sql', fromUrl: true, fromBlob: false, options: { sql: { resultFormat: 'auto', adapterOptions: {}, browserAdapterFactory: undefined, nodeAdapterFactory: undefined }, duckdb: { accessMode: 'auto', bundles: undefined, databasePath: undefined, remoteUrl: undefined, workerUrl: undefined }, snowflake: { account: undefined, database: undefined, schema: undefined, token: undefined, warehouse: undefined, role: undefined, authenticator: undefined } }, defaultOptions: SQL_SOURCE_DEFAULT_OPTIONS, testURL: (url: string): boolean => /^duckdb:\/\//i.test(url), createDataSourceSync: ( data: string, options: SQLSourceOptions, coreApi?: CoreAPI ): DuckDBSQLDataSource => new DuckDBSQLDataSource(data, options, coreApi) } as const satisfies SourceLoader; /** SQLDataSource specialization for DuckDB. */ export class DuckDBSQLDataSource extends SQLDataSource { /** Resolves a reusable common query handle for a named DuckDB table. */ async getTable(name: string, options: {signal?: AbortSignal} = {}): Promise { const table = await this.resolveTable(name, options.signal); const schema = await this.getTableSchema(table); options.signal?.throwIfAborted(); return makeDuckDBTableQuerySource(this, table, schema); } /** Reports whether the table's schema can be represented exactly by the common reader. */ override async getTableMetadata( name: string, options: {signal?: AbortSignal} = {} ): Promise { const metadata = await super.getTableMetadata(name, options); return { ...metadata, queryable: Boolean(metadata.schema && isDuckDBQuerySchema(metadata.schema)) }; } /** Lists table names without claiming compatibility before schema discovery. */ override async getTablesMetadata( options: {signal?: AbortSignal} = {} ): Promise { return (await super.getTablesMetadata(options)).map(({name}) => ({name})); } /** Uploads an Arrow table through IPC. Supported by the browser DuckDB-Wasm adapter. */ async insertArrowTable(table: Table, options: SQLArrowInsertOptions): Promise { await this.insertArrowIPC(tableToIPC(table, 'stream'), options); } /** Uploads a complete Arrow IPC stream to a browser DuckDB-Wasm table. */ async insertArrowIPC(bytes: Uint8Array, options: SQLArrowInsertOptions): Promise { const adapter = await this.getAdapter(); if (!adapter.insertArrowIPC) { throw new Error('This DuckDB adapter does not support Arrow IPC uploads.'); } await adapter.insertArrowIPC(bytes, options); } constructor(data: string, options: SQLSourceOptions, coreApi?: CoreAPI) { super( data, options, DuckDBSQLSource.type, SQL_SOURCE_DEFAULT_OPTIONS, createDuckDBAdapter, coreApi ); } } async function createDuckDBAdapter(context: SQLAdapterFactoryContext): Promise { if (context.options.duckdb?.remoteUrl) { throw new Error('Remote DuckDB adapters are not implemented yet.'); } if (isNodeRuntime()) { return await createDuckDBNodeAdapter(context); } return await createDuckDBBrowserAdapter(context); } async function createDuckDBNodeAdapter(context: SQLAdapterFactoryContext): Promise { const duckdb = await import('@duckdb/node-api'); const databasePath = getDuckDBDatabasePath(context.url, context.options); const configuration = context.options.duckdb?.accessMode && context.options.duckdb.accessMode !== 'auto' ? {access_mode: context.options.duckdb.accessMode} : undefined; let connection: any; const executeRows = async ( sqlText: string, options: SQLQueryOptions = {} ): Promise[]> => { if (!connection) { const instance = await duckdb.DuckDBInstance.create(databasePath, configuration); connection = await instance.connect(); } const reader = await connection.runAndReadAll(sqlText, options.parameters); return reader.getRowObjectsJson(); }; return { capabilities: { supportsArrow: true, supportsMetadata: true, runtime: 'node', isDynamic: true }, async connect(): Promise { if (!connection) { const instance = await duckdb.DuckDBInstance.create(databasePath, configuration); connection = await instance.connect(); } }, async close(): Promise { if (connection?.closeSync) { connection.closeSync(); } connection = null; }, async listCatalogs(): Promise { const rows = await executeRows(` SELECT DISTINCT catalog_name FROM information_schema.schemata ORDER BY catalog_name `); return rows.map(row => ({catalogName: String(row.catalog_name)})); }, async listSchemas(catalogName?: string) { const filter = catalogName ? `WHERE catalog_name = '${escapeSqlString(catalogName)}'` : ''; const rows = await executeRows(` SELECT catalog_name, schema_name FROM information_schema.schemata ${filter} ORDER BY catalog_name, schema_name `); return rows.map(row => ({ catalogName: row.catalog_name ? String(row.catalog_name) : undefined, schemaName: String(row.schema_name) })); }, async listTables(options?: { catalogName?: string; schemaName?: string; }): Promise { const filters = [ options?.catalogName ? `table_catalog = '${escapeSqlString(options.catalogName)}'` : undefined, options?.schemaName ? `table_schema = '${escapeSqlString(options.schemaName)}'` : undefined, `table_schema NOT IN ('information_schema', 'pg_catalog')` ].filter(Boolean); const whereClause = filters.length ? `WHERE ${filters.join(' AND ')}` : ''; const rows = await executeRows(` SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables ${whereClause} ORDER BY table_catalog, table_schema, table_name `); return rows.map(row => ({ catalogName: row.table_catalog ? String(row.table_catalog) : undefined, schemaName: row.table_schema ? String(row.table_schema) : undefined, tableName: String(row.table_name), tableType: row.table_type ? String(row.table_type) : undefined })); }, async getTableSchema(options: { catalogName?: string; schemaName?: string; tableName: string; }): Promise { const filters = [ options.catalogName ? `table_catalog = '${escapeSqlString(options.catalogName)}'` : undefined, options.schemaName ? `table_schema = '${escapeSqlString(options.schemaName)}'` : undefined, `table_name = '${escapeSqlString(options.tableName)}'` ].filter(Boolean); const rows = await executeRows(` SELECT table_catalog, table_schema, table_name, column_name, data_type, is_nullable, ordinal_position FROM information_schema.columns WHERE ${filters.join(' AND ')} ORDER BY ordinal_position `); const columns = rows.map( row => ({ catalogName: row.table_catalog ? String(row.table_catalog) : undefined, schemaName: row.table_schema ? String(row.table_schema) : undefined, tableName: row.table_name ? String(row.table_name) : undefined, columnName: String(row.column_name), sqlType: String(row.data_type), nullable: row.is_nullable === 'YES', ordinalPosition: Number(row.ordinal_position) }) satisfies SQLColumnInfo ); return convertSQLColumnsToSchema(columns); }, async executeRows( sqlText: string, options: SQLQueryOptions = {} ): Promise[]> { return await executeRows(sqlText, options); }, async executeArrow(sqlText: string, options: SQLQueryOptions = {}) { const rows = await executeRows(sqlText, options); return convertRowsToArrowTable(rows); } }; } async function createDuckDBBrowserAdapter(context: SQLAdapterFactoryContext): Promise { const duckdb = await import('@duckdb/duckdb-wasm'); const logger = new duckdb.ConsoleLogger(); const selectedBundles = await duckdb.selectBundle(duckdb.getJsDelivrBundles()); const bundles = { ...selectedBundles, ...context.options.duckdb?.bundles }; const workerUrl = context.options.duckdb?.workerUrl || selectedBundles.mainWorker; if (!workerUrl) { throw new Error('DuckDBSQLSource could not resolve a worker bundle URL.'); } const worker = await duckdb.createWorker(workerUrl); const database = new duckdb.AsyncDuckDB(logger, worker); await database.instantiate(bundles.mainModule, bundles.pthreadWorker); const connection = await database.connect(); const executeRows = async ( sqlText: string, options: SQLQueryOptions = {} ): Promise[]> => { const result = await executeDuckDBBrowserQuery(connection, sqlText, options.parameters); return convertArrowToTable(tableFromIPC(result), 'object-row-table').data; }; return { capabilities: { supportsArrow: true, supportsMetadata: true, runtime: 'browser', isDynamic: true }, async insertArrowIPC(bytes, options): Promise { // DuckDB transfers the buffer to its worker; retain the caller's IPC bytes. await connection.insertArrowFromIPCStream(new Uint8Array(bytes), { name: options.tableName, schema: options.schemaName, create: options.create ?? true }); }, async connect(): Promise {}, async close(): Promise { await connection.close(); await database.terminate(); worker.terminate(); }, async listCatalogs(): Promise { const rows = await executeRows(` SELECT DISTINCT catalog_name FROM information_schema.schemata ORDER BY catalog_name `); return rows.map(row => ({catalogName: String(row.catalog_name)})); }, async listSchemas(catalogName?: string) { const filter = catalogName ? `WHERE catalog_name = '${escapeSqlString(catalogName)}'` : ''; const rows = await executeRows(` SELECT catalog_name, schema_name FROM information_schema.schemata ${filter} ORDER BY catalog_name, schema_name `); return rows.map(row => ({ catalogName: row.catalog_name ? String(row.catalog_name) : undefined, schemaName: String(row.schema_name) })); }, async listTables(options?: { catalogName?: string; schemaName?: string; }): Promise { const filters = [ options?.catalogName ? `table_catalog = '${escapeSqlString(options.catalogName)}'` : undefined, options?.schemaName ? `table_schema = '${escapeSqlString(options.schemaName)}'` : undefined, `table_schema NOT IN ('information_schema', 'pg_catalog')` ].filter(Boolean); const whereClause = filters.length ? `WHERE ${filters.join(' AND ')}` : ''; const rows = await executeRows(` SELECT table_catalog, table_schema, table_name, table_type FROM information_schema.tables ${whereClause} ORDER BY table_catalog, table_schema, table_name `); return rows.map(row => ({ catalogName: row.table_catalog ? String(row.table_catalog) : undefined, schemaName: row.table_schema ? String(row.table_schema) : undefined, tableName: String(row.table_name), tableType: row.table_type ? String(row.table_type) : undefined })); }, async getTableSchema(options: { catalogName?: string; schemaName?: string; tableName: string; }): Promise { const filters = [ options.catalogName ? `table_catalog = '${escapeSqlString(options.catalogName)}'` : undefined, options.schemaName ? `table_schema = '${escapeSqlString(options.schemaName)}'` : undefined, `table_name = '${escapeSqlString(options.tableName)}'` ].filter(Boolean); const rows = await executeRows(` SELECT table_catalog, table_schema, table_name, column_name, data_type, is_nullable, ordinal_position FROM information_schema.columns WHERE ${filters.join(' AND ')} ORDER BY ordinal_position `); const columns = rows.map( row => ({ catalogName: row.table_catalog ? String(row.table_catalog) : undefined, schemaName: row.table_schema ? String(row.table_schema) : undefined, tableName: row.table_name ? String(row.table_name) : undefined, columnName: String(row.column_name), sqlType: String(row.data_type), nullable: row.is_nullable === 'YES', ordinalPosition: Number(row.ordinal_position) }) satisfies SQLColumnInfo ); return convertSQLColumnsToSchema(columns); }, async executeRows( sqlText: string, options: SQLQueryOptions = {} ): Promise[]> { return await executeRows(sqlText, options); }, async executeArrow(sqlText: string, options: SQLQueryOptions = {}) { const result = await executeDuckDBBrowserQuery(connection, sqlText, options.parameters); return tableFromIPC(result); } }; } /** Returns raw IPC so DuckDB's Arrow objects never cross into loaders.gl's Arrow runtime. */ async function executeDuckDBBrowserQuery( connection: AsyncDuckDBConnection, sqlText: string, parameters?: SQLQueryOptions['parameters'] ): Promise { // Keep DuckDB's lower-level connection handle confined to this IPC boundary. return await connection.useUnsafe(async (bindings, connectionId) => { if (!parameters) { return await bindings.runQuery(connectionId, sqlText); } const values = Array.isArray(parameters) ? parameters : Object.values(parameters); const statementId = await bindings.createPrepared(connectionId, sqlText); try { return await bindings.runPrepared(connectionId, statementId, values); } finally { await bindings.closePrepared(connectionId, statementId); } }); } function getDuckDBDatabasePath(url: string, options: SQLSourceOptions): string { if (options.duckdb?.databasePath) { return options.duckdb.databasePath; } const parsedUrl = new URL(url); const pathname = decodeURIComponent(parsedUrl.pathname || ''); if (!pathname || pathname === '/') { return ':memory:'; } return pathname.slice(1) || ':memory:'; }