GeoLibre SQL Workspace Architecture: How It Supports DuckDB, PostGIS, and Apache Sedona Backends

The GeoLibre SQL Workspace is a unified spatial SQL interface that abstracts three distinct database engines—DuckDB, PostGIS (via PGlite), and Apache Sedona—behind a common API, with each backend compiled to WebAssembly for in-browser execution.

The SQL Workspace in opengeos/GeoLibre lets analysts run spatial queries directly on map layers without leaving the application. Its architecture centers on engine-agnostic design: three WASM-powered backends share a single schema registration system and return identical result shapes, letting the UI remain completely decoupled from query execution details.

Supported Database Backends

GeoLibre ships with three interchangeable SQL engines. The engine selector lives in SqlWorkspacePanel.tsx and swaps implementations without page reloads.

Engine	Package	Runtime	Best For
DuckDB	@duckdb/duckdb-wasm	Browser WASM (or Tauri desktop)	Default choice; offline-capable after first load
PostGIS	@electric-sql/pglite + @electric-sql/pglite-postgis	Browser WASM; desktop side-car with sedona extra	Full PostGIS SQL dialect compatibility
Apache Sedona	CereusDB (WASM build of SedonaDB)	Browser WASM; native SedonaDB side-car with sedona extra	Large layer performance; Sedona-specific spatial functions

Lazy loading keeps initial bundle size small. The ~19 MB PostGIS WASM payload, for example, downloads only on first engine selection.

Core Workflow: Four Shared Stages

Every backend follows an identical four-stage pipeline defined in sql-workspace.ts:

1. Schema Preparation — Creates a dedicated geolibre schema for temporary tables
2. Layer Registration — Converts in-memory GeoJSON FeatureCollections to SQL tables
3. Query Execution — Runs user SQL through the selected engine's wrapper
4. Result Normalization — Returns standardized SqlQueryResult with WKT for display and GeoJSON for mapping

This uniformity means the grid component, "Add as layer" buttons, and export dialogs require zero engine-specific code.

Layer Registration and Table Mapping

When layers load into the map, the workspace mirrors them as temporary tables. In sql-workspace.ts, registration logic inspects each layer's GeoJSON properties and builds appropriate CREATE TABLE statements:

// Pseudocode from sql-workspace.ts utility functions
function registerLayerAsTable(
  engine: SqlEngine,
  layer: MapLayer,
  schema: string = 'geolibre'
): Promise<void> {
  const tableName = sanitizeIdentifier(layer.id);
  const columns = inferSchemaFromGeoJSON(layer.features);
  // Engine-specific CREATE TABLE + INSERT execution
  return engine.createTable(schema, tableName, columns, layer.features);
}

All geometry columns store as native spatial types. Each backend handles coordinate system assumptions differently, but the registration API hides these details.

Backend Implementation Wrappers

Each engine implements a thin wrapper satisfying the same run*Query contract.

DuckDB Wrapper

duckdb-workspace.ts manages the DuckDB-WASM instance with spatial extension:

import { runDuckdbQuery } from "./duckdb-workspace";
import { getLayers } from "@geolibre/core/store";

const sql = `SELECT name, population FROM countries WHERE population > 1e7`;
const layers = getLayers();               // current layers in the map
const result = await runDuckdbQuery(sql, layers);

// result.columns → ["name","population"]
// result.rows   → array of rows for the UI grid
// result.geojson → FeatureCollection (if a geometry column was present)

The wrapper automatically registers ST_AsGeoJSON and ST_AsText functions for geometry serialization.

PostGIS Wrapper

pglite-workspace.ts bootstraps PGlite with PostGIS extension loaded:

import { runPostgisQuery } from "./pglite-workspace";

const sql = `SELECT name, ST_Area(geometry) AS area FROM countries`;
const result = await runPostgisQuery(sql, getLayers());
// Same `result` shape as DuckDB – the UI displays the area column

Dynamic import logic in pglite-loader.ts and pglite-loader.cdn.ts selects between bundled and CDN-hosted WASM binaries. Build-time configuration via vite.config.ts toggles this with GEOLIBRE_PGLITE_CDN=1.

Apache Sedona Wrapper

sedona-workspace.ts interfaces with CereusDB, falling back to a native side-car on desktop when the sedona extra is installed:

import { runSedonaQuery } from "./sedona-workspace";

const sql = `SELECT name, ST_Buffer(geometry, 0.5) AS buffer FROM countries`;
const result = await runSedonaQuery(sql, getLayers());
// Geometry handling works identically to the other engines

Sedona's optimizer particularly benefits queries against layers with millions of features.

Geometry Handling Across Engines

All three backends serialize geometry consistently for downstream consumption:

• Grid display: WKT via ST_AsText or equivalent
• Map rendering: GeoJSON via ST_AsGeoJSON or native conversion

The SqlQueryResult interface guarantees these fields:

interface SqlQueryResult {
  columns: string[];
  rows: Record<string, unknown>[];
  geojson?: FeatureCollection;  // Present when query returns geometry
  rowCount: number;
  executionTimeMs: number;
}

This structure lets SqlWorkspacePanel.tsx render results without checking which engine produced them.

Consuming Query Results

Results flow back into the map through standard layer APIs:

import { addGeoJsonLayer } from "@geolibre/map";

if (result.geojson) {
  addGeoJsonLayer({
    name: "High‑pop countries",
    geojson: result.geojson,
  });
}

Export handlers for CSV and GeoParquet similarly operate on the normalized rows and geojson properties.

File Structure and Source Locations

File	Responsibility
apps/geolibre-desktop/src/lib/sql-workspace.ts	Shared utilities, schema handling, result normalization
apps/geolibre-desktop/src/lib/duckdb-workspace.ts	DuckDB-WASM wrapper
apps/geolibre-desktop/src/lib/pglite-workspace.ts	PGlite + PostGIS wrapper
apps/geolibre-desktop/src/lib/sedona-workspace.ts	CereusDB/Sedona wrapper with side-car fallback
apps/geolibre-desktop/src/lib/pglite-loader.ts	Dynamic PGlite WASM import (bundled)
apps/geolibre-desktop/src/lib/pglite-loader.cdn.ts	Dynamic PGlite WASM import (CDN fallback)
apps/geolibre-desktop/src/components/panels/SqlWorkspacePanel.tsx	UI shell with engine selector
apps/geolibre-desktop/vite.config.ts	Build-time engine configuration

Adding a New Backend

The architecture minimizes extension cost. A new engine requires only:

1. A wrapper module implementing run*Query(sql: string, layers: MapLayer[]): Promise<SqlQueryResult>
2. Registration in the engine selector dropdown
3. Lazy loader for any large WASM assets

All UI components remain unchanged because they depend on the standardized result shape, not engine internals.

Summary

• GeoLibre's SQL Workspace unifies DuckDB, PostGIS, and Apache Sedona behind a single interface
• Three WASM implementations run serverlessly in-browser, with optional native side-cars on desktop
• sql-workspace.ts centralizes layer-to-table registration and result normalization
• Engine wrappers in *-workspace.ts files isolate backend-specific logic
• Standard SqlQueryResult decouples query execution from UI rendering
• Lazy loading keeps initial bundle small while supporting heavyweight engines like PostGIS

Frequently Asked Questions

How does the SQL Workspace switch between database backends without reloading?

The engine selector in SqlWorkspacePanel.tsx triggers a state change that swaps the active runner function. Each engine's loader is dynamically imported on first use, so switching happens in-memory without navigation. The shared SqlQueryResult interface ensures the UI needs no reconfiguration.

What happens to map layers when I run a query?

Layers with in-memory GeoJSON FeatureCollections are automatically registered as temporary tables in the geolibre schema. This registration runs through sql-workspace.ts before any query executes, making all vector data queryable regardless of original format.

Can I use the SQL Workspace offline?

Yes, for DuckDB and PostGIS engines. After the initial WASM download, both @duckdb/duckdb-wasm and @electric-sql/pglite run entirely in the browser without network access. The Sedona engine requires a desktop side-car for full functionality when the sedona extra is installed.

Why does PostGIS have a separate CDN loader?

The PostGIS WASM bundle exceeds 19 MB. pglite-loader.ts and pglite-loader.cdn.ts provide dual loading strategies: bundled for reliable offline use, CDN for faster updates and reduced install size. The GEOLIBRE_PGLITE_CDN environment variable selects the appropriate loader at build time in vite.config.ts.

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