# How GeoLibre's SQL Workspace Supports DuckDB: In-Browser Spatial SQL Processing

> Discover how GeoLibre's SQL Workspace empowers in-browser spatial SQL with DuckDB. It processes queries client-side, handles cloud URLs, maps layers as tables, and converts geometries for visualization.

- Repository: [Open Geospatial Solutions/GeoLibre](https://github.com/opengeos/GeoLibre)
- Tags: how-to-guide
- Published: 2026-07-31

---

**GeoLibre's SQL Workspace leverages DuckDB-WASM with the Spatial extension to execute client-side spatial SQL queries, automatically rewriting cloud URLs, registering map layers as temporary tables, and converting geometries to GeoJSON for visualization.**

GeoLibre is an open-source geospatial platform that brings advanced spatial analysis to both browser and desktop environments. The **GeoLibre SQL Workspace** provides a sandboxed DuckDB environment where users can query vector layers, remote Parquet files, and cloud storage without installing database software. This article examines how the `opengeos/GeoLibre` repository implements this architecture, referencing the actual source code in [`apps/geolibre-desktop/src/lib/sql-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/sql-workspace.ts).

## Architecture Overview

The SQL Workspace builds a **sandboxed DuckDB instance** per query session, loading the **Spatial extension** to handle geometry operations. This design enables zero-install data access while maintaining cross-platform parity between the web build and the Tauri desktop application.

The system processes queries through a nine-stage pipeline that handles everything from input sanitization to geometry conversion. Each stage is implemented as a discrete function in the core workspace file, ensuring robust error handling and recovery from known DuckDB-WASM limitations.

## Query Processing Pipeline

### Statement Cleaning and Safety Checks

Before execution, user-provided SQL undergoes strict validation. The `cleanStatement` function strips trailing semicolons and comments, while `containsMultipleStatements` verifies that only a single statement is submitted. This prevents batch injection attacks and ensures predictable behavior in the single-query execution model.

```typescript
// Located in apps/geolibre-desktop/src/lib/sql-workspace.ts
cleanStatement(sql: string): string
containsMultipleStatements(sql: string): boolean

```

### Cloud URL Normalization

Public cloud URLs using `s3://`, `gs://`, or `az://` protocols are automatically rewritten to HTTPS equivalents. The `rewriteCloudUrls` function, assisted by `cloudUrlToHttps`, transforms these schemes so DuckDB can stream data via HTTP range requests rather than relying on native cloud protocol support.

```typescript
// Lines 48-66 in sql-workspace.ts
rewriteCloudUrls(sql: string): string
cloudUrlToHttps(url: string): string

```

### Bare Source Wrapping

Bare file paths or URLs following `FROM` or `JOIN` clauses are automatically wrapped in appropriate DuckDB table functions. The `rewriteBareSources` function uses the `DATA_SOURCE_READERS` registry to determine whether to apply `read_parquet`, `read_csv_auto`, `read_json_auto`, or `ST_Read` based on file extensions.

```typescript
// Lines 29-38 and 6-12 in sql-workspace.ts
rewriteBareSources(sql: string): string
// DATA_SOURCE_READERS maps extensions to reader functions

```

### Remote File Registration

For every remote reader call (e.g., `read_parquet('https://...')`), the workspace registers a Virtual File System (VFS) handle using `db.registerFileURL`. This implementation in `registerRemoteSources` enables byte-range streaming without downloading entire files, bypassing the unreliable in-WASM `httpfs` path that can trigger memory issues.

### Spatial Extension Warm-Up

The `ensureSpatialExtension` function loads DuckDB's spatial capabilities before query execution. To prevent the "stoi: no conversion" bug that can poison a DuckDB-WASM instance, the workspace includes warm-up logic in `runSqlStatementOnce` that reads a tiny default Parquet file (`SAMPLE_DATASET_URL`) if the user's query does not already trigger a remote read.

### Layer Table Registration

Every loaded vector layer containing an in-memory GeoJSON collection is registered as a **temporary DuckDB table** via `ST_Read`. The `registerLayerTables` function derives table names from layer names using `assignTableNames` and `sanitizeTableName`, making map data queryable as standard SQL tables.

```sql
-- Query a loaded layer automatically exposed as table 'countries'
SELECT name, continent, pop_est
FROM countries
ORDER BY pop_est DESC
LIMIT 10;

```

### Query Description and Schema Detection

Before final execution, `describeQuery` runs a lightweight `DESCRIBE SELECT * FROM (<statement>)` probe to determine column order and detect geometry columns using `isGeometryColumnType`. This metadata gathering ensures proper handling of spatial data in subsequent processing stages.

### Geometry Handling and Conversion

When geometry columns are detected, the workspace re-runs the query with specialized formatting functions. **ST_AsText** generates human-readable WKT for the UI grid, while **ST_AsGeoJSON** produces machine-readable geometry for the "Add as layer" functionality. The system uses a hidden `GEOMETRY_JSON_COLUMN` constant to manage this dual representation, filtering the raw GeoJSON from the grid display while using it to build valid GeoJSON FeatureCollections.

```typescript
// Geometry wrapping logic in runSqlStatementOnce (lines 84-100)
ST_AsText(geom) // For display
ST_AsGeoJSON(geom) // For map integration

```

### Result Normalization and Export

Query results undergo normalization via `normalizeValue` and `normalizeRow` to ensure JSON and CSV safety. The workspace provides `resultToCsv` for spreadsheet export and `rowsToFeatureCollection` for converting tabular results with geometries into standard GeoJSON FeatureCollections suitable for map visualization.

## Error Recovery and Robustness

If a remote-read query triggers the "stoi: no conversion" error—a known DuckDB-WASM bug that renders the instance unusable—the workspace implements automatic recovery. The `runSqlQuery` function catches this specific error, invokes `resetSqlDatabase` to instantiate a fresh DuckDB connection, and retries the query exactly once. This ensures users experience seamless query execution even when underlying WASM limitations occur.

## Practical SQL Examples

The SQL Workspace supports querying remote files directly without download:

```sql
-- Query remote Parquet with automatic streaming
SELECT name, population, geom
FROM https://data.source.coop/giswqs/opengeos/countries.parquet
LIMIT 100;

```

Cloud storage URLs are rewritten automatically for HTTPFS compatibility:

```sql
-- S3 URLs are transparently converted to HTTPS
SELECT *
FROM read_parquet('s3://open-data-bucket/sample.parquet')
WHERE population > 1_000_000;

```

Local files and map layers coexist in the same query namespace:

```sql
-- Join a map layer with a remote CSV
SELECT a.name, b.elevation
FROM map_layer a
JOIN read_csv_auto('https://example.com/data.csv') b
ON a.id = b.site_id;

```

## Summary

- **Zero-install architecture**: GeoLibre's SQL Workspace uses DuckDB-WASM to execute spatial SQL entirely in the browser or desktop client without external dependencies.
- **Automatic URL handling**: Cloud URLs (`s3://`, `gs://`, `az://`) are rewritten to HTTPS, and bare sources are wrapped in appropriate reader functions (`read_parquet`, `ST_Read`, etc.).
- **Layer integration**: Map layers are registered as temporary tables via `registerLayerTables`, enabling seamless SQL queries against active visualization data.
- **Robust geometry processing**: The workspace automatically detects geometry columns and dual-formats them as WKT for display and GeoJSON for map integration.
- **Error resilience**: Automatic detection and recovery from the "stoi: no conversion" DuckDB-WASM bug ensures query reliability through instance reset and retry logic.

## Frequently Asked Questions

### What file formats does GeoLibre SQL Workspace support via DuckDB?

GeoLibre supports **Parquet**, **CSV**, **JSON**, and **GeoJSON** files through automatic reader detection. The `rewriteBareSources` function in [`sql-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/sql-workspace.ts) maps file extensions to DuckDB functions including `read_parquet`, `read_csv_auto`, `read_json_auto`, and `ST_Read` for spatial formats.

### How does GeoLibre handle cloud storage URLs in SQL queries?

The workspace automatically rewrites cloud-specific protocols (`s3://`, `gs://`, `az://`) to HTTPS equivalents using `rewriteCloudUrls` and `cloudUrlToHttps`. This enables DuckDB's HTTPFS extension to stream data via byte-range requests without requiring cloud SDK credentials in the client.

### What happens if a DuckDB query fails in GeoLibre's SQL Workspace?

The system implements specific error recovery for the "stoi: no conversion" DuckDB-WASM bug. When detected in `runSqlQuery`, the workspace calls `resetSqlDatabase` to create a fresh DuckDB instance and retries the query once. Other SQL errors are returned to the user with standard DuckDB error messages.

### Can I query existing map layers using SQL in GeoLibre?

Yes. The `registerLayerTables` function automatically exposes every loaded vector layer as a temporary DuckDB table using `ST_Read`. Table names are derived from layer names via `sanitizeTableName`, allowing you to reference active map data directly in SQL statements alongside remote files.