fire_duck_ext

Downloads

999

GitHub stars

Extension repository on GitHub

Query Google Cloud Firestore directly from DuckDB using SQL

Maintainer(s): BorisBesky

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. Scan collections with firestore_scan(), including 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 and SQL ORDER BY / LIMIT are pushed down to Firestore where possible to reduce data transferred.

Firestore’s schemaless documents are mapped to typed DuckDB columns automatically, with configurable handling for nested maps (map_encoding) and fields that only appear in some documents (schema_sample_size, unmapped_column, columns).

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 (https://github.com/BorisBesky/fire_duck_ext) for the full parameter 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_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	[]