Query Google Cloud Firestore directly from DuckDB using SQL
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 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(). Every function documents itself in duckdb_functions() with a description, parameter names, and an example.
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).
Builds for DuckDB 2.0 and DuckDB 1.5.
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_array_append | table | Append values to an array field of one Firestore document, keeping duplicates. The array is read and written back whole, so a concurrent write to the same field can be lost. Returns a one-row count. | NULL | [CALL firestore_array_append('users', 'user123', 'events', ['login']);] |
| firestore_array_remove | table | Remove every occurrence of the given values from an array field of one Firestore document (Firestore's arrayRemove transform). Returns a one-row count. | NULL | [CALL firestore_array_remove('users', 'user123', 'tags', ['inactive']);] |
| firestore_array_union | table | Add values to an array field of one Firestore document, skipping any the array already holds (Firestore's arrayUnion transform). Returns a one-row count. | NULL | [CALL firestore_array_union('users', 'user123', 'tags', ['vip', 'active']);] |
| firestore_clear_cache | table | Forget every cached Firestore schema, so the next scan of each collection samples its documents again. | NULL | [CALL firestore_clear_cache();] |
| firestore_clear_cache | table | Forget the cached schema of one collection path (e.g. 'users', '~orders' or 'users/u1/orders'), so its next scan samples its documents again. | NULL | [CALL firestore_clear_cache('users');] |
| firestore_connect | table | Make a Firestore database ID the default for later calls in this session that pass no database := parameter. Fails if no firestore secret covers that database. | NULL | [CALL firestore_connect('analytics-db');] |
| firestore_delete | table | Delete one Firestore document by ID. Returns a one-row count. | NULL | [CALL firestore_delete('users', 'user123');] |
| firestore_delete_batch | table | Delete every Firestore document in a list of IDs, sent through batchWrite in groups of up to 500. Batches are not atomic: each delete succeeds or fails on its own. Returns a one-row count. | NULL | [CALL firestore_delete_batch('users', ['user1', 'user2']);] |
| firestore_disconnect | table | Clear the session's default database set by firestore_connect(), so later calls pick their database as they would without it. | NULL | [CALL firestore_disconnect();] |
| firestore_insert | table | Insert one Firestore document per row of a subquery, with one field per column. document_id := names the column that supplies document IDs, which is then left out of the fields; without it Firestore generates the IDs. Returns the number of documents inserted. | NULL | [CALL firestore_insert('users', (SELECT 'alice' AS id, 'Alice' AS name, 30 AS age), document_id := 'id');] |
| firestore_scan | table | Read a Firestore collection as a table: one row per document, with __document_id plus columns inferred from sampled documents. The path may name a nested collection ('users/u1/orders'), every collection with one name ('~orders', a collection-group query), or a document ('users/u1'), which lists that document's subcollection IDs instead. WHERE filters, ORDER BY … LIMIT, the projection and COUNT(*) are pushed down to Firestore where possible. With API-key or Firebase-user auth, pass show_missing := false. | NULL | [SELECT __document_id, name FROM firestore_scan('users', show_missing := false) WHERE status = 'active';] |
| firestore_update | table | Set fields on one Firestore document from the trailing field-name/value pairs, leaving its other fields as they are. Returns a one-row count. | NULL | [CALL firestore_update('users', 'user123', 'status', 'verified', 'age', 31);] |
| firestore_update_batch | table | Set the same field-name/value pairs on every Firestore document in a list of IDs, sent through batchWrite in groups of up to 500. Batches are not atomic: each write succeeds or fails on its own. Returns a one-row count. | NULL | [CALL firestore_update_batch('users', ['user1', 'user2'], 'status', 'reviewed');] |
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 | typed_value |
|---|---|---|---|---|---|
| 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 | [] | 1 |
| firestore_orderby_pushdown | Whether a SQL ORDER BY … LIMIT may be sent to Firestore. Each ordering field is first checked against the sampled documents, because Firestore omits documents that lack the ordering field, sorts nulls first, and orders across types by its own precedence; a field that would change which rows a query returns keeps its sort in DuckDB. The order_by:= parameter is unaffected | BOOLEAN | GLOBAL | [] | true |
| 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 | [] | 67108864 |
| firestore_page_size | Documents requested per Firestore round trip (1-1000; lower it for collections of large documents) | BIGINT | GLOBAL | [] | 1000 |
| firestore_schema_cache_ttl | Schema cache TTL in seconds (0 to disable caching) | BIGINT | GLOBAL | [] | 3600 |
| firestore_schema_sample_size | Documents sampled to infer a collection's schema (-1 samples every document) | BIGINT | GLOBAL | [] | 1000 |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |