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.
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
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. Since v0.19.2, salesforce_deleted_ids returns latest_date_covered (the getDeleted coverage frontier, for wide-window sweep checkpoints), the read_only ATTACH option is accepted as a documented no-op (the catalog is read-only by design), Id literals in =/!=/IN filters are validated at plan time (15/18 alphanumeric chars; clear error instead of Salesforce's generic INVALID_QUERY_FILTER_OPERATOR), and the per-attempt HTTP read timeout is tunable via sf_timeout_read_s (default 60s, cap 3600s). Since v0.19.1, salesforce_deleted_ids paginates its queryAll sweep (v0.19.0 truncated at exactly 2,000 ids) and uses the Replication API getDeleted() as the default source for ANY window width (the 15-minute cap was an incorrect assumption; latestDateCovered resume + boundary dedup), covering objects with no IsDeleted field (e.g. User). Since v0.19.0, salesforce_deleted_ids(catalog, object, since, until) surfaces the ids deleted in the org within a window (Replication API getDeleted for exact deletion timestamps; a queryAll IsDeleted scan as fallback for wider windows), salesforce_bulk_resume(catalog, job_id) re-streams the results of an existing Bulk query job (recovery without re-running it), salesforce_scan(catalog, object, filter, …) scans an sObject directly with per-call overrides, transient HTTP failures retry with tunable sf_retry_max/sf_retry_backoff_ms, and well-known Salesforce errorCodes surface with embedded remedy hints.
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_bulk_resume | table | Re-streams the result rows of an existing Bulk API 2.0 query job by id, so a load that died mid-stream can resume without re-running the query. Columns come from the job's CSV header (VARCHAR); rows stream page-by-page following the Sforce-Locator. Read-only; invalid or expired job ids fail fast. | NULL | [SELECT * FROM salesforce_bulk_resume('sf', '7504x00000ABCDef');] |
| 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_deleted_ids | table | Returns the ids deleted in the org for an sObject within a time window, so a watermark-based incremental load can sweep deletes. Uses the Replication API getDeleted() for any window width (exact deleted_date, latestDateCovered resume with boundary dedup) and covers objects with no IsDeleted field (e.g. User); opt-in source := 'queryAll' is a paginated IsDeleted sweep (deleted_date NULL, ~15-day recycle bin). | NULL | [SELECT * FROM salesforce_deleted_ids('sf', 'Lead', since := '2026-09-30T00:00:00');] |
| 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_scan | table | Scans an sObject directly using the credentials of an attached catalog, without the catalog table layer. The optional where argument is raw SOQL appended to the query (no ';', no nested SELECT); projection and plan predicates push down as on catalog tables. Named parameters query_mode ('query'/'queryAll'), transport ('rest'/'bulk'/'auto') and chunks (1-8) override per call without touching session settings. | NULL | [SELECT * FROM salesforce_scan('sf', 'Lead', where := 'Status 'Open'') LIMIT 10;] |
| 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_retry_backoff_ms | Linear backoff base between transient HTTP retries: attempt N sleeps backoff_ms * N before retrying. Clamped to [0,60000]. Default 200. | BIGINT | GLOBAL | [] |
| sf_retry_max | Maximum attempts for transient HTTP failures (429, 5xx, connection errors) before failing a call. Clamped to [1,10]. Default 3. Raise for runs over flaky links; the 401 refresh path is separate and always applies. | BIGINT | 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 | [] |
| sf_timeout_read_s | HTTP read timeout in seconds per attempt (default 60). Raise it when a server-side COUNT() or scan on a very large object exceeds the default instead of failing; each transient retry waits up to this timeout, so budget attempts x timeout when tuning with sf_retry_max. | BIGINT | GLOBAL | [] |