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

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 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. 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

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 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) 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:

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 and 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. 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:

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.

---

Key Source Files

Purpose	File Path
DuckDB + PostGIS workspace core	apps/geolibre-desktop/src/lib/sql-workspace.ts
PGlite + PostGIS workspace	apps/geolibre-desktop/src/lib/pglite-workspace.ts
PGlite WASM loader	apps/geolibre-desktop/src/lib/pglite-loader.ts
Apache Sedona sidecar client	packages/processing/src/sidecar-client.ts
CereusDB (Sedona) WASM loader	apps/geolibre-desktop/src/lib/cereus-loader.ts
Shared result types	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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}