Read-only access to Salesforce orgs as DuckDB SQL tables over the official REST and Bulk APIs — OAuth refresh-token or JWT-bearer auth (credentials from inline options, environment variables, or an SFDX auth URL), native ATTACH, projection + predicate pushdown, COUNT pushdown, explicit server-side aggregates with GROUP BY, lazy/auto/Bulk transports with PK chunking (Bulk blob/base64 compatibility guard), a per-job API quota governor, parent + grandparent relationship STRUCT columns with diagnostics, queryAll (archived + deleted), Tooling-API fast schema, and metadata helpers (manual cache refresh, picklist values, record types). Requires DuckDB v1.5.4 or newer.
Maintainer(s):
flozer
Installing and Loading
INSTALL salesforce FROM community;
LOAD salesforce;
Example
-- Published build is signed: INSTALL salesforce FROM community; LOAD salesforce;
-- Connect with an OAuth refresh-token Connected App.
-- Set SF_CLIENT_ID, SF_CLIENT_SECRET, SF_REFRESH_TOKEN, and optionally SF_LOGIN_URL.
ATTACH 'salesforce://myorg' AS sf (TYPE salesforce, auth_source 'env');
SELECT Id, Name FROM sf.Account WHERE Name = 'Acme' LIMIT 10;
About salesforce
duckdb-salesforce attaches a Salesforce org as a read-only DuckDB catalog. Tables map to sObjects; SELECT runs over REST /query (or /queryAll for archived + soft-deleted rows), Bulk API 2.0 (lazy-streamed, optional parallel PK chunking), or an auto-selected transport, with SOQL projection + predicate pushdown and COUNT pushdown. salesforce_aggregate() runs explicit server-side SOQL aggregates (MIN/MAX/SUM/AVG/COUNT, optional filter and GROUP BY) without dragging rows down, and salesforce_relationships() reports parent/grandparent expansion. Read-only metadata helpers are available too: salesforce_refresh_metadata() (manual per-ATTACH cache refresh), salesforce_picklist_values(), and salesforce_record_types(). A Bulk blob/base64 compatibility guard keeps incompatible scans off Bulk, and blob bodies / epoch datetimes have clear documented limitations. Opt-in parent- and grandparent-relationship STRUCT columns, a per-job API quota governor, and Tooling-API fast schema discovery round it out. Authentication is OAuth 2.0 refresh-token or JWT bearer, with credentials from inline options, environment variables, or an SFDX auth URL; credentials stay in memory and are never logged; TLS server-certificate verification is always on. Read-only: all mutating catalog operations throw. Since v0.15.1 every function self-documents through duckdb_functions() (description, runnable example, real parameter names), so SQL clients and AI agents can discover the surface from the connection. Since v0.16.0-v0.17.0, no-group COUNT, COUNT(DISTINCT), MIN and MAX queries push down transparently (kill-switch sf_aggregate_pushdown; MIN/MAX need a sortable numeric/temporal/boolean field; SUM/AVG deliberately stay local — the server sums in floating point), salesforce_relationship_graph recurses child relationships up to max_depth with cycle protection, and the Report Bridge resolves a report's base object from the official reportTypeMetadata when the first category is unambiguous. Built and tested against DuckDB v1.5.4, v1.5.5 and v1.5.6 (v1.5.2/v1.5.3 support was dropped in v0.15.0 — see CHANGELOG.md). Full change history in this repo's CHANGELOG.md.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| salesforce_aggregate | table | Runs an explicit server-side SOQL aggregate over an attached catalog - SELECT |
NULL | [SELECT * FROM salesforce_aggregate('sf', 'Account', 'COUNT(Id) n', 'IsDeleted = false', 'Industry');] |
| salesforce_decode | table | Decodes a fields-describe JSON array plus a records JSON array into typed DuckDB rows; utility surface of the scan's JSON-to-vector conversion. | NULL | [SELECT * FROM salesforce_decode('[{"name":"Name","type":"string"}]', '[{"Name":"Acme"}]');] |
| salesforce_describe | table | Fetches the REST describe metadata (fields, types, relationships) for a single Salesforce sObject without an ATTACH; credentials are passed as the named parameters client_id, client_secret, refresh_token, login_url and api_version. | NULL | [SELECT * FROM salesforce_describe('Account', client_id := ' |
| salesforce_describe_calls | table | TEST ONLY: returns the number of sObject describes issued since ATTACH, proving the metadata cache is reused. | NULL | [SELECT * FROM salesforce_describe_calls();] |
| salesforce_global_describe_calls | table | TEST ONLY: returns the number of global describe (GET /sobjects) calls issued since ATTACH, proving object-list discovery caching. | NULL | [SELECT * FROM salesforce_global_describe_calls();] |
| salesforce_last_bulk_create_body | table | TEST ONLY: returns the JSON body of the most recent Bulk API 2.0 job-create POST, showing the pushed SOQL. | NULL | [SELECT * FROM salesforce_last_bulk_create_body();] |
| salesforce_last_quota | table | Returns the most recent quota-governor decision for Bulk job starts: limit name, max, remaining, threshold, whether the job was allowed, and why. | NULL | [SELECT * FROM salesforce_last_quota();] |
| salesforce_last_scan_pages | table | TEST ONLY: returns the number of query pages the most recent scan fetched, proving lazy pagination. | NULL | [SELECT * FROM salesforce_last_scan_pages();] |
| salesforce_last_soql | table | Returns the SOQL (projection plus predicate pushdown) generated by the most recent salesforce scan; returns no rows until a scan has run. | NULL | [SELECT * FROM salesforce_last_soql();] |
| salesforce_last_transport | table | Returns the transport (rest or bulk) the most recent scan resolved to, with the row-count estimate and the reason for the choice. | NULL | [SELECT * FROM salesforce_last_transport();] |
| salesforce_metadata_fields | table | Returns per-field schema metadata for an sObject in an attached salesforce catalog (name, type, capability flags, reference target, picklist values) from the shared read-only metadata cache. | NULL | [SELECT field_name, type FROM salesforce_metadata_fields('sf', 'Account');] |
| salesforce_metadata_objects | table | Lists the sObjects visible to the authenticated user in an attached salesforce catalog, with queryable and other capability flags, from the shared read-only metadata cache. | NULL | [SELECT object_name FROM salesforce_metadata_objects('sf') WHERE queryable;] |
| salesforce_picklist_values | table | Returns the picklist entries of a field from the cached REST describe with value, label and active flag; read-only metadata, not the Metadata API. | NULL | [SELECT value, label FROM salesforce_picklist_values('sf', 'Account', 'Industry') WHERE active;] |
| salesforce_query | table | Runs a read-only SOQL query against Salesforce and returns the raw JSON records with lazy pagination; credentials use the same named parameters as salesforce_describe(). | NULL | [SELECT * FROM salesforce_query('SELECT Id, Name FROM Account LIMIT 10', client_id := ' |
| salesforce_query_cost | table | Returns a unified cost view of the most recent scan: SOQL, transport, projection ratio, pushed and residual filter counts, pages, rows delivered, quota decision and Bulk poll count. | NULL | [SELECT * FROM salesforce_query_cost();] |
| salesforce_query_explain | table | Returns a field-by-field explanation of the most recent scan: which filters were pushed to SOQL versus evaluated as residuals, plus projection, relationship, count and transport decisions; diagnostic only, never changes scan behavior. | NULL | [SELECT * FROM salesforce_query_explain();] |
| salesforce_record_types | table | Returns the record types of an sObject from the cached REST describe: developer_name, label, record_type_id, active and is_default. | NULL | [SELECT developer_name, label, is_default FROM salesforce_record_types('sf', 'Account');] |
| salesforce_refresh_metadata | table | Invalidates the in-memory metadata cache of an attached catalog - the whole catalog when no object is given, only the named object otherwise - so the next reference re-describes. | NULL | [SELECT * FROM salesforce_refresh_metadata('sf', 'Account');] |
| salesforce_relationship_graph | table | Enumerates relationship edges reachable from an sObject with an explicit status per edge (resolved, polymorphic, self_reference, cyclic, …); accepts an optional positional max_depth bounding both directions plus the named parameters include_children (default false) and direction ('parent', 'child' or 'both', default 'parent'); child traversal recurses through resolved children up to max_depth with cycle protection. | NULL | [SELECT * FROM salesforce_relationship_graph('sf', 'Contact');] |
| salesforce_relationships | table | Explains the most recent scan's relationship resolution: one config row (mode, effective depth, expanded/skipped counts) plus one row per reference field, expanded with field counts or skipped with a reason. | NULL | [SELECT * FROM salesforce_relationships();] |
| salesforce_report | table | Runs a tabular Salesforce report synchronously and returns its fact rows plus run diagnostics; capped at 2,000 rows, for discovery and validation rather than large extraction. | NULL | [SELECT * FROM salesforce_report('sf', '00O…');] |
| salesforce_report_soql | table | Returns a best-effort, describe-validated candidate SOQL translation of a report with explainability columns (translatable, translation_status, blocked_by, confidence); reports translatable = false instead of an unverified SOQL. | NULL | [SELECT * FROM salesforce_report_soql('sf', '00O…');] |
| salesforce_reports | table | Lists the Salesforce report definitions visible to the authenticated user, for Report Bridge discovery and validation. | NULL | [SELECT * FROM salesforce_reports('sf');] |
| salesforce_tooling_calls | table | TEST ONLY: returns the number of Tooling API schema queries issued since ATTACH, proving fast-schema batching (sf_schema_source = 'tooling'). | NULL | [SELECT * FROM salesforce_tooling_calls();] |
| sf_url_encode | scalar | Percent-encodes a string for safe use as a literal inside a SOQL query or a Salesforce REST URL. | NULL | [sf_url_encode('Acme & Sons')] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| sf_aggregate_pushdown | Transparent no-group aggregate pushdown: when COUNT, COUNT_DISTINCT, MIN or MAX runs directly on an attached sObject column with every filter already pushed to SOQL, run one server-side aggregate SOQL query instead of fetching rows (default true). MIN/MAX require a sortable numeric, temporal or boolean field. false keeps the row-scan fallback. | BOOLEAN | GLOBAL | [] |
| sf_auto_bulk_threshold | For sf_force_transport='auto': estimated row count above which Bulk is chosen over REST (default 50000). | BIGINT | GLOBAL | [] |
| sf_auto_probe | For sf_force_transport='auto': run the COUNT() row-count probe (default true). When false, 'auto' always resolves to REST. | BOOLEAN | GLOBAL | [] |
| sf_bulk_chunks | Bulk PK chunking: split a Bulk scan into N disjoint Id ranges (1 = off, default; capped at 8). Sequential in this cut. Bulk transport only. | BIGINT | GLOBAL | [] |
| sf_bulk_poll_budget | Max Bulk job-status polls before failing fast (default 600, ~250ms each). Raise for a large backfill; surfaced as salesforce_query_cost().bulk_polls. | BIGINT | GLOBAL | [] |
| sf_bulk_require_predicate | When true, a Bulk read with no predicate pushed to SOQL (full-object extraction) fails fast instead of running. Default false (guidance only). Use for planned large backfills to force a CreatedDate/SystemModstamp window. | BOOLEAN | GLOBAL | [] |
| sf_force_transport | Scan transport: 'rest' (default), 'bulk' (Bulk API 2.0), or 'auto' (choose by row-count probe). 'bulk' is for large extractions / CREATE TABLE AS / COPY; same SOQL (projection + predicate pushdown) either way. | VARCHAR | GLOBAL | [] |
| sf_mock_bulk_create_body | TEST ONLY. Bulk job-create response body. | VARCHAR | GLOBAL | [] |
| sf_mock_bulk_create_status | TEST ONLY. Bulk job-create HTTP status. | BIGINT | GLOBAL | [] |
| sf_mock_bulk_results_body | TEST ONLY. Bulk results CSV page(s) ('|~|' per page). | VARCHAR | GLOBAL | [] |
| sf_mock_bulk_results_locator | TEST ONLY. Sforce-Locator per results page (comma-separated; empty = last). | VARCHAR | GLOBAL | [] |
| sf_mock_bulk_results_status | TEST ONLY. Bulk results HTTP status(es), CSV. | VARCHAR | GLOBAL | [] |
| sf_mock_bulk_status_body | TEST ONLY. Bulk status body/bodies ('|~|' per poll). | VARCHAR | GLOBAL | [] |
| sf_mock_bulk_status_code | TEST ONLY. Bulk status HTTP status(es), CSV. | VARCHAR | GLOBAL | [] |
| sf_mock_count_body | TEST ONLY. Body for the mocked COUNT() probe (reads totalSize). | VARCHAR | GLOBAL | [] |
| sf_mock_count_status | TEST ONLY. Statuses for the mocked COUNT() probe GET. | VARCHAR | GLOBAL | [] |
| sf_mock_describe_body | TEST ONLY. Bodies for mocked describe GETs ('|~|'-separated). | VARCHAR | GLOBAL | [] |
| sf_mock_describe_status | TEST ONLY. Statuses for mocked describe GETs (e.g. '200', '401,200'). | VARCHAR | GLOBAL | [] |
| sf_mock_env | TEST ONLY. Override environment-variable lookup for auth_source env/sfdx_url ('NAME=value;…'). Empty uses the real OS environment. | VARCHAR | GLOBAL | [] |
| sf_mock_limits_body | TEST ONLY. Body for the mocked GET /limits. | VARCHAR | GLOBAL | [] |
| sf_mock_limits_status | TEST ONLY. Statuses for the mocked GET /limits. | VARCHAR | GLOBAL | [] |
| sf_mock_query_body | TEST ONLY. Bodies for mocked query GET pages ('|~|'-separated). | VARCHAR | GLOBAL | [] |
| sf_mock_query_status | TEST ONLY. Statuses for mocked query GETs (e.g. '200,200'). | VARCHAR | GLOBAL | [] |
| sf_mock_queryall_body | TEST ONLY. Body/pages for the mocked GET /queryAll ('|~|'). | VARCHAR | GLOBAL | [] |
| sf_mock_queryall_status | TEST ONLY. Statuses for the mocked GET /queryAll. | VARCHAR | GLOBAL | [] |
| sf_mock_report_body | TEST ONLY. Analytics report-run JSON body/pages ('|~|'). | VARCHAR | GLOBAL | [] |
| sf_mock_report_describe_body | TEST ONLY. Analytics report /describe JSON body ('|~|'). | VARCHAR | GLOBAL | [] |
| sf_mock_report_describe_status | TEST ONLY. Analytics report /describe HTTP status(es), CSV. | VARCHAR | GLOBAL | [] |
| sf_mock_report_status | TEST ONLY. Analytics report-run HTTP status(es), CSV. | VARCHAR | GLOBAL | [] |
| sf_mock_report_token_map | TEST ONLY. Inject report compound/address token mappings for the Report Bridge normalizer: 'Object:TOKEN=Field;Object:TOKEN2=Field2'. The real map is empty; this drives the mechanism in tests without hardcoded entries. | VARCHAR | GLOBAL | [] |
| sf_mock_sobjects_body | TEST ONLY. Body for mocked global describe (GET /sobjects). | VARCHAR | GLOBAL | [] |
| sf_mock_sobjects_status | TEST ONLY. Statuses for mocked global describe (GET /sobjects). | VARCHAR | GLOBAL | [] |
| sf_mock_token_body | TEST ONLY. Response body paired with sf_mock_token_status. | VARCHAR | GLOBAL | [] |
| sf_mock_token_status | TEST ONLY. HTTP status for a mocked Salesforce token-endpoint response. 0 disables the mock and uses the live transport (default). | BIGINT | GLOBAL | [] |
| sf_mock_tooling_body | TEST ONLY. Body/pages for the mocked GET /tooling/query ('|~|'). | VARCHAR | GLOBAL | [] |
| sf_mock_tooling_status | TEST ONLY. Statuses for the mocked GET /tooling/query. | VARCHAR | GLOBAL | [] |
| sf_query_mode | Read mode: 'query' (default) or 'queryAll' (also returns archived + soft-deleted records). Affects the scan (REST + Bulk) and its probes. | VARCHAR | GLOBAL | [] |
| sf_quota_cache_seconds | Quota governor: in-memory TTL for a cached /limits snapshot, per instance_url (default 60; 0 disables caching). | BIGINT | GLOBAL | [] |
| sf_quota_enabled | Quota governor: gate Bulk job starts on the org's API quota (default true). false skips /limits and never blocks. | BOOLEAN | GLOBAL | [] |
| sf_quota_enforce | Quota governor: block when below reserve (default true). false = consult /limits and report, but proceed (warn-only). | BOOLEAN | GLOBAL | [] |
| sf_quota_fail_open | Quota governor: when /limits is unavailable, allow the Bulk job (default true). false blocks with a clear error. | BOOLEAN | GLOBAL | [] |
| sf_quota_min_remaining | Quota governor: absolute floor of remaining DailyApiRequests below which Bulk is refused (default 1000). | BIGINT | GLOBAL | [] |
| sf_quota_reserve_pct | Quota governor: keep this %% of DailyApiRequests.Max in reserve (default 10). | BIGINT | GLOBAL | [] |
| sf_relationship_depth | Parent traversal depth when sf_relationships='parent': 1 (default, parent only) or 2 (also grandparent, nested STRUCT). Capped at 2. | BIGINT | GLOBAL | [] |
| sf_relationships | Parent relationship traversal: 'off' (default) or 'parent' (expose each single-target parent as a STRUCT column, e.g. SELECT Account.Name FROM sf.Contact). Polymorphic/child relationships not expanded. | VARCHAR | GLOBAL | [] |
| sf_schema_source | Schema discovery: 'describe' (default, REST, authoritative) or 'tooling' (fast batched Tooling API FieldDefinition; falls back to REST describe per object on error/absent/ambiguous type; coarser types; fields default non-filterable unless Tooling marks them filterable). | VARCHAR | GLOBAL | [] |