# How GeoLibre's SQL Workspace Supports DuckDB, PostGIS/PGlite, and Apache Sedona

> Discover how GeoLibre's SQL Workspace unifies DuckDB, PostGIS/PGlite, and Apache Sedona with a single interface. Query spatial data seamlessly across engines.

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

---

**GeoLibre's SQL Workspace provides a unified, engine-agnostic interface for spatial SQL by abstracting three execution backends—DuckDB with PostGIS, PGlite with PostGIS, and Apache Sedona—behind a single `SqlQueryResult` type used by the entire UI.**

The SQL Workspace is the central component in [opengeos/GeoLibre](https://github.com/opengeos/GeoLibre) that lets users run spatial SQL queries against map data without worrying about which underlying engine processes them. This article explains how each backend integrates into the workspace architecture, what makes them distinct, and how the codebase maintains a consistent developer experience across all three.

---

## DuckDB + PostGIS Integration

The **DuckDB + PostGIS** implementation lives in [`apps/geolibre-desktop/src/lib/sql-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/sql-workspace.ts). It runs entirely in the browser via **DuckDB-WASM** and handles the most common spatial SQL workloads.

### Setting Up the Database Instance

The workspace creates and manages a dedicated DuckDB-WASM instance through two key functions:

- `acquireSqlDatabase()` – initializes or returns an existing DuckDB connection
- `resetSqlDatabase()` – tears down and recreates the instance when schemas change

```typescript
import { runSqlQuery } from '@geolibre/desktop/src/lib/sql-workspace';

const result = await runSqlQuery(
  "SELECT name, ST_Area(geom) AS area FROM countries WHERE POP_EST > 10000000",
  appLayers,
);

```

### Layer Registration as Temporary Tables

All map layers containing GeoJSON `FeatureCollection` data are materialized as temporary tables via `registerLayerTables()`. This makes layer data directly queryable as SQL tables.

### URL Rewriting for Cloud Storage

The workspace automatically transforms URLs to work with DuckDB's reader functions:

- **Bare URLs** (`SELECT * FROM https://…/data.parquet`) → wrapped with `read_parquet()`
- **Cloud object stores** (`s3://`, `gs://`, `az://`) → converted to public HTTPS URLs via `rewriteCloudUrls()`

### Geometry Detection and Output

DuckDB's `DESCRIBE SELECT * FROM (…)` command identifies the first column matching a PostGIS geometry type through `isGeometryColumnType()`. Results are returned as **WKT** for the data grid and as a **GeoJSON FeatureCollection** via `rowsToFeatureCollection()`.

---

## PGlite + PostGIS Integration

The **PGlite + PostGIS** backend in [`apps/geolibre-desktop/src/lib/pglite-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/pglite-workspace.ts) provides full PostgreSQL compatibility in a WASM bundle, complete with the PostGIS spatial extension.

### Lazy Loading and Instance Management

The PGlite loader ([`pglite-loader.ts`](https://github.com/opengeos/GeoLibre/blob/main/pglite-loader.ts)) handles dynamic imports:

- `loadPgliteModules()` – fetches the PGlite WASM bundle and PostGIS extension on demand
- `statePromise` – caches a singleton `Pglite` instance
- `runExclusive()` – guards concurrent queries with a promise queue to prevent schema reset conflicts

### Schema and Table Creation

Layer tables are created within a dedicated `geolibre` schema using standard PostgreSQL DDL:

```typescript
import { runPostgisQuery } from '@geolibre/desktop/src/lib/pglite-workspace';

const result = await runPostgisQuery(
  "SELECT name, ST_Length(geom) AS perimeter FROM rivers WHERE LENGTH_KM > 500",
  appLayers,
);

```

The `buildCreateTableStatement()` function generates appropriate `CREATE TABLE` statements, while `insertChunkRows()` batches row inserts respecting PostgreSQL's 65,535 bind-parameter limit (`INSERT_CHUNK_ROWS`).

### Catalog-Based Geometry Detection

Unlike DuckDB's `DESCRIBE` approach, PGlite queries the **PostgreSQL system catalog** to find the PostGIS `geometry` type OID, then scans result fields in `describeQuery()` to locate spatial columns.

Output formatting mirrors DuckDB: WKT for display, GeoJSON via `rowsToFeatureCollection()` for visualization.

---

## Apache Sedona (CereusDB) Integration

The **Apache Sedona** support leverages the CereusDB WASM build and optional Python sidecar, implemented across [`packages/processing/src/sidecar-client.ts`](https://github.com/opengeos/GeoLibre/blob/main/packages/processing/src/sidecar-client.ts) and [`apps/geolibre-desktop/src/lib/cereus-loader.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/cereus-loader.ts).

### WASM Module and Sidecar Architecture

CereusDB ships as a **~40 MB WASM module** loaded by [`cereus-loader.ts`](https://github.com/opengeos/GeoLibre/blob/main/cereus-loader.ts). The workspace can execute Sedona SQL in two modes:

1. **Pure browser execution** – runs entirely within the WASM runtime
2. **Sidecar service** – delegates to a Python backend for heavier workloads

### Request/Response Flow

The `runSedonaSql()` function sends a `SedonaSqlRequest` containing the SQL string and optional layer metadata:

```typescript
import { runSedonaSql } from '@geolibre/processing';

const result = await runSedonaSql({
  sql: "SELECT name, ST_Intersection(geom, ST_Buffer(geom, 1000)) AS clipped FROM parcels",
  layers: [{ name: "parcels", geometryColumn: "geom" }],
});

```

The sidecar responds with a `SedonaSqlResult` that gets normalized to the standard `SqlQueryResult` shape.

### Geometry Column Handling

Sedona's SQL dialect uses `geometry` as the default geometry column name, unlike DuckDB's `geom`. The workspace passes the engine-appropriate name through `previewLayerColumns` and `runSedonaQuery()` to ensure compatibility.

---

## Common Workflow Across All Engines

Despite their different implementations, all three backends follow an identical seven-step pipeline defined in the SQL Workspace architecture:

1. **`cleanStatement()`** – strips trailing semicolons and comments
2. **`containsMultipleStatements()`** – rejects multi-statement queries for security
3. **URL rewriting** – `rewriteCloudUrls()` and `rewriteBareSources()` normalize data sources
4. **Layer table registration** – each engine's `registerLayerTables` equivalent makes layers queryable
5. **`describeQuery()`** – discovers column order and detects geometry columns
6. **Query execution** – geometry rendered as WKT with `__geolibre_sql_geometry_geojson` holding GeoJSON
7. **Unified `SqlQueryResult` return** – contains `columns`, `rows`, `rowCount`, optional `geometryColumn`, and optional `geojson`

This common workflow means the **UI layer requires zero engine-specific code**. The SQL dialog, "Add as layer" functionality, export features, and autocompletion all consume the same `SqlQueryResult` type defined in [`packages/core/src/types.ts`](https://github.com/opengeos/GeoLibre/blob/main/packages/core/src/types.ts).

---

## Key Source Files

| Purpose | File Path |
|---------|-----------|
| DuckDB + PostGIS workspace core | [`apps/geolibre-desktop/src/lib/sql-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/sql-workspace.ts) |
| PGlite + PostGIS workspace | [`apps/geolibre-desktop/src/lib/pglite-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/pglite-workspace.ts) |
| PGlite WASM loader | [`apps/geolibre-desktop/src/lib/pglite-loader.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/pglite-loader.ts) |
| Apache Sedona sidecar client | [`packages/processing/src/sidecar-client.ts`](https://github.com/opengeos/GeoLibre/blob/main/packages/processing/src/sidecar-client.ts) |
| CereusDB (Sedona) WASM loader | [`apps/geolibre-desktop/src/lib/cereus-loader.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/cereus-loader.ts) |
| Shared result types | [`packages/core/src/types.ts`](https://github.com/opengeos/GeoLibre/blob/main/packages/core/src/types.ts) |

---

## Summary

- **DuckDB + PostGIS** provides lightweight, fast spatial SQL via DuckDB-WASM with automatic URL rewriting for cloud storage and parquet files.
- **PGlite + PostGIS** offers full PostgreSQL compatibility with proper schema isolation, batch inserts, and catalog-based geometry detection.
- **Apache Sedona** (CereusDB) brings distributed spatial analytics to the browser via WASM or an optional Python sidecar, using the `geometry` column naming convention.
- All three engines return a **unified `SqlQueryResult`** type, making the SQL Workspace truly engine-agnostic for downstream UI components.

---

## Frequently Asked Questions

### How does GeoLibre choose which SQL engine to use?

The application typically defaults to **DuckDB + PostGIS** for standard queries due to its smaller bundle size and faster startup. **PGlite** is available when full PostgreSQL compatibility is needed, and **Apache Sedona** is invoked explicitly for complex spatial analytics or when the CereusDB sidecar is configured. The workspace API abstracts this choice—developers call engine-specific functions like `runSqlQuery()` or `runPostgisQuery()` based on their requirements.

### What is the performance difference between the three backends?

According to the GeoLibre source code, **DuckDB-WASM** starts fastest and handles most GIS workloads efficiently. **PGlite** incurs a larger initial load (~additional WASM bundle) but benefits from mature PostgreSQL query optimization. **Apache Sedona via CereusDB** carries the heaviest footprint (~40 MB WASM) but provides distributed spatial operations unmatched by the other two; it can also offload to a Python sidecar for server-grade performance.

### Can I mix different SQL dialects in the same GeoLibre session?

No—the SQL Workspace enforces single-statement queries through `containsMultipleStatements()` and does not allow cross-engine queries in a single execution. However, you can **chain operations sequentially**: query with DuckDB, export results as a layer, then run a follow-up Sedona analysis on that output. Each workspace maintains its own connection state.

### Why does Sedona use `geometry` instead of `geom` for geometry columns?

Apache Sedona's SQL dialect follows its own convention where spatial columns are named `geometry` by default. The GeoLibre workspace handles this transparently through `previewLayerColumns` and `runSedonaQuery()`, which pass the appropriate column name to each engine. Users rarely need to account for this difference unless writing dialect-specific SQL.