Query Google Cloud Firestore directly from DuckDB using SQL
Installing and Loading
INSTALL fire_duck_ext FROM community;
LOAD fire_duck_ext;
Example
LOAD fire_duck_ext;
-- Authenticate with a service account key file (see the README for API-key
-- and Firebase-user auth, including browser-safe authentication)
CREATE SECRET my_firestore (
TYPE firestore,
PROJECT_ID 'my-gcp-project',
SERVICE_ACCOUNT_JSON '/path/to/service-account.json'
);
-- Read documents
SELECT * FROM firestore_scan('users');
-- Filter with SQL
SELECT __document_id, name, email
FROM firestore_scan('users')
WHERE status = 'active';
-- Update a document
CALL firestore_update('users', 'user123', 'status', 'verified');
-- Insert documents from a subquery
CALL firestore_insert('users', (SELECT name, age FROM read_csv('new_users.csv')));
About fire_duck_ext
fire_duck_ext lets you work with Google Cloud Firestore directly from DuckDB using SQL. Read with firestore_scan(), which also covers collection-group queries (firestore_scan('~collection')) and subcollection-ID discovery (firestore_scan('collection/doc_id')). Write with firestore_insert(), firestore_update(), firestore_delete(), their batch counterparts (firestore_update_batch(), firestore_delete_batch()), and the array-transform functions firestore_array_union(), firestore_array_remove(), and firestore_array_append().
WHERE filters, SQL ORDER BY / LIMIT, the column projection, and a bare COUNT(*) are pushed down to Firestore where possible. A query then transfers only what it uses: matching rows, selected fields, and for a count, no documents at all. Firestore orders differently from SQL, so an ORDER BY is only sent when the sampled documents say the two agree; otherwise DuckDB sorts and the reason is logged.
Scans stream a page at a time, so a collection of any size can be read without holding it in memory, and the page size adapts when documents turn out to be unusually large. Settings cover paging, schema sampling, ORDER BY pushdown, and optional parallel scanning.
Firestore's schemaless documents are mapped to typed DuckDB columns automatically, with configurable handling for nested maps and for fields that appear in only some documents.
Secrets require PROJECT_ID and one of: a service-account key file path (SERVICE_ACCOUNT_JSON, admin access, bypasses Security Rules via IAM, native-only) – never embed this key in application code or a browser context; an API_KEY (unauthenticated, request.auth is null); or Firebase Auth user credentials (EMAIL/PASSWORD, ANONYMOUS, or a pre-obtained ID_TOKEN) – authenticated, respects Security Rules, and works in the WebAssembly/browser build. GOOGLE_APPLICATION_CREDENTIALS is also read on startup if set.
The extension also builds and runs under DuckDB-WASM using API-key or Firebase-user auth (service-account auth needs OpenSSL and is native-only).
See the project README for the parameter and settings reference, type mapping, pushdown semantics, and platform notes.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| firestore_scan | table | Read all documents from a Firestore collection or collection group. | NULL | [SELECT * FROM firestore_scan('users');] |
| firestore_insert | table | Insert documents into a Firestore collection from a subquery. | NULL | [CALL firestore_insert('users', (SELECT name, age FROM read_csv('new_users.csv')));] |
| firestore_update | table | Update fields on a single Firestore document. | NULL | [CALL firestore_update('users', 'user123', 'status', 'verified');] |
| firestore_delete | table | Delete a single Firestore document. | NULL | [CALL firestore_delete('users', 'user123');] |
| firestore_update_batch | table | Batch update multiple Firestore documents by ID list. | NULL | [CALL firestore_update_batch('users', ['id1', 'id2'], 'status', 'reviewed');] |
| firestore_delete_batch | table | Batch delete multiple Firestore documents by ID list. | NULL | [CALL firestore_delete_batch('users', ['id1', 'id2']);] |
| firestore_array_union | table | Add elements to an array field without duplicates. | NULL | [CALL firestore_array_union('users', 'user123', 'tags', ['vip', 'active']);] |
| firestore_array_remove | table | Remove elements from an array field. | NULL | [CALL firestore_array_remove('users', 'user123', 'tags', ['inactive']);] |
| firestore_array_append | table | Append elements to an array field (allows duplicates). | NULL | [CALL firestore_array_append('users', 'user123', 'log', ['event1']);] |
| firestore_clear_cache | table | Clear the cached schema for all or a specific collection. | NULL | [CALL firestore_clear_cache();] |
| firestore_connect | table | Set the session-scoped Firestore database for subsequent queries. | NULL | [CALL firestore_connect('analytics-db');] |
| firestore_disconnect | table | Clear the session-scoped Firestore database override. | NULL | [CALL firestore_disconnect();] |
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 |
|---|---|---|---|---|
| firestore_max_threads | Maximum threads one scan may split across, reading separate document-key ranges. 1 (the default) disables parallel scanning; raise it for collections whose document ids spread over the key space, such as Firestore auto-ids | BIGINT | GLOBAL | [] |
| firestore_orderby_pushdown | Whether a SQL ORDER BY may be sent to Firestore. Off by default, because Firestore's ordering is not SQL's: it omits documents that lack the ordering field, sorts nulls first, and orders across types by its own precedence, so pushing the sort down changes which rows a query returns. The order_by:= parameter is unaffected | BOOLEAN | GLOBAL | [] |
| firestore_page_byte_budget | Uncompressed bytes one page may weigh before the scan requests fewer documents per round trip (0 disables the guard) | BIGINT | GLOBAL | [] |
| firestore_page_size | Documents requested per Firestore round trip (1-1000; lower it for collections of large documents) | BIGINT | GLOBAL | [] |
| firestore_schema_cache_ttl | Schema cache TTL in seconds (0 to disable caching) | BIGINT | GLOBAL | [] |
| firestore_schema_sample_size | Documents sampled to infer a collection's schema (-1 samples every document) | BIGINT | GLOBAL | [] |