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

> Explore the GeoLibre SQL Workspace architecture. Learn how its unified spatial SQL interface supports DuckDB, PostGIS, and Apache Sedona backends with WebAssembly for in-browser execution.

- Repository: [Open Geospatial Solutions/GeoLibre](https://github.com/opengeos/GeoLibre)
- Tags: architecture
- Published: 2026-08-15

---

**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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/sql-workspace.ts)**, registration logic inspects each layer's GeoJSON properties and builds appropriate `CREATE TABLE` statements:

```typescript
// 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`](https://github.com/opengeos/GeoLibre/blob/main/duckdb-workspace.ts)** manages the DuckDB-WASM instance with spatial extension:

```typescript
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`](https://github.com/opengeos/GeoLibre/blob/main/pglite-workspace.ts)** bootstraps PGlite with PostGIS extension loaded:

```typescript
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`](https://github.com/opengeos/GeoLibre/blob/main/pglite-loader.ts)** and **[`pglite-loader.cdn.ts`](https://github.com/opengeos/GeoLibre/blob/main/pglite-loader.cdn.ts)** selects between bundled and CDN-hosted WASM binaries. Build-time configuration via [`vite.config.ts`](https://github.com/opengeos/GeoLibre/blob/main/vite.config.ts) toggles this with `GEOLIBRE_PGLITE_CDN=1`.

### Apache Sedona Wrapper

**[`sedona-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/sedona-workspace.ts)** interfaces with CereusDB, falling back to a native side-car on desktop when the `sedona` extra is installed:

```typescript
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:

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

```

This structure lets [`SqlWorkspacePanel.tsx`](https://github.com/opengeos/GeoLibre/blob/main/SqlWorkspacePanel.tsx) render results without checking which engine produced them.

## Consuming Query Results

Results flow back into the map through standard layer APIs:

```typescript
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`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/sql-workspace.ts) | Shared utilities, schema handling, result normalization |
| [`apps/geolibre-desktop/src/lib/duckdb-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/duckdb-workspace.ts) | DuckDB-WASM wrapper |
| [`apps/geolibre-desktop/src/lib/pglite-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/pglite-workspace.ts) | PGlite + PostGIS wrapper |
| [`apps/geolibre-desktop/src/lib/sedona-workspace.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/sedona-workspace.ts) | CereusDB/Sedona wrapper with side-car fallback |
| [`apps/geolibre-desktop/src/lib/pglite-loader.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/pglite-loader.ts) | Dynamic PGlite WASM import (bundled) |
| [`apps/geolibre-desktop/src/lib/pglite-loader.cdn.ts`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/lib/pglite-loader.cdn.ts) | Dynamic PGlite WASM import (CDN fallback) |
| [`apps/geolibre-desktop/src/components/panels/SqlWorkspacePanel.tsx`](https://github.com/opengeos/GeoLibre/blob/main/apps/geolibre-desktop/src/components/panels/SqlWorkspacePanel.tsx) | UI shell with engine selector |
| [`apps/geolibre-desktop/vite.config.ts`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/pglite-loader.ts) and [`pglite-loader.cdn.ts`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/vite.config.ts).