How to Use PostGIS in GeoLibre's SQL Workspace: PGlite Integration Guide

Yes, GeoLibre's SQL Workspace supports PostGIS through an embedded PGlite instance that runs entirely in the browser, enabling full PostGIS-compatible spatial SQL without external database servers.

GeoLibre is an open-source geospatial platform that provides a versatile SQL Workspace for executing spatial queries. According to the opengeos/GeoLibre source code, the application integrates both DuckDB Spatial and PGlite with PostGIS extensions, allowing users to execute familiar spatial SQL directly within the desktop or browser environment.

How PostGIS Integration Works in GeoLibre

The SQL Workspace in GeoLibre is designed to support multiple SQL engines. While DuckDB Spatial handles fast analytic queries on vector and raster data, the PGlite integration provides a full PostgreSQL-compatible environment with the complete PostGIS extension loaded.

The PGlite Workspace Architecture

The core implementation resides in apps/geolibre-desktop/src/lib/pglite-workspace.ts. This module orchestrates the instantiation of an in-browser PostgreSQL database using the @electric-sql/pglite library, specifically configured with the PostGIS extension from @electric-sql/pglite-postgis.

When the SQL Workspace initializes PostGIS mode, it executes CREATE EXTENSION IF NOT EXISTS postgis; against the PGlite instance. This makes all standard PostGIS functions—such as ST_Intersects, ST_Buffer, and ST_AsGeoJSON—available for immediate use.

Dynamic Module Loading Strategy

To optimize bundle sizes, GeoLibre uses a dynamic loader pattern implemented in apps/geolibre-desktop/src/lib/pglite-loader.ts. This module handles the asynchronous import of PGlite and its PostGIS extension, with a fallback to pglite-loader.cdn.ts for CDN-based loading when offline builds are required.

// apps/geolibre-desktop/src/lib/pglite-workspace.ts
import { loadPgliteModules } from "./pglite-loader";

export async function initPgliteWorkspace() {
  const { PGlite, postgis } = await loadPgliteModules();
  const pg = new PGlite({ extensions: { postgis } });
  await pg.exec("CREATE EXTENSION IF NOT EXISTS postgis;");
  return pg; // ready for PostGIS queries
}

Executing PostGIS Queries in the SQL Workspace

Once initialized, users can execute arbitrary PostGIS SQL through the runPostgisQuery function exported from the workspace module.

Using runPostgisQuery

The primary API for running queries accepts a SQL string and returns a JavaScript object containing the results. The UI panel invokes this function when users click the run button in the SQL editor.

// apps/geolibre-desktop/src/components/panels/SqlWorkspacePanel.tsx
import { runPostgisQuery } from "../../lib/pglite-workspace";

async function onRunClick(sql: string) {
  try {
    const result = await runPostgisQuery(sql);
    console.log("Query result:", result);
  } catch (e) {
    console.error("SQL error:", e);
  }
}

Practical Query Example

The following SQL demonstrates a typical spatial filter using PostGIS functions within GeoLibre's workspace:

SELECT
  id,
  ST_AsGeoJSON(geom) AS geom
FROM my_layer
WHERE ST_Intersects(geom, ST_MakeEnvelope(-122.5, 37.7, -122.3, 37.9, 4326));

This query selects features that intersect with a bounding box, converting the geometry to GeoJSON format for consumption by the application's visualization layer.

Key Implementation Files

Understanding the file structure helps developers extend or debug the PostGIS integration:

• apps/geolibre-desktop/src/lib/pglite-workspace.ts: Boots the PGlite database, loads the PostGIS extension, and exposes runPostgisQuery.
• apps/geolibre-desktop/src/lib/pglite-loader.ts: Dynamically imports @electric-sql/pglite and @electric-sql/pglite-postgis to minimize initial load times.
• apps/geolibre-desktop/src/lib/pglite-loader.cdn.ts: Provides CDN-based module loading for offline-only builds.
• apps/geolibre-desktop/src/components/panels/SqlWorkspacePanel.tsx: React component that renders the SQL editor and triggers query execution.
• tests/pglite-workspace.test.ts: Contains the test suite validating PostGIS query execution within the workspace.

DuckDB Spatial vs. PostGIS in GeoLibre

GeoLibre's SQL Workspace offers two distinct engines for different use cases:

• DuckDB Spatial: Optimized for fast analytical queries on GeoParquet, Shapefiles, and other vector formats. This is the default engine for the workspace.
• PGlite with PostGIS: Provides full PostgreSQL compatibility with the complete PostGIS extension, ideal for users requiring specific PostGIS functions or migrating existing PostGIS workflows.

Users can switch between engines based on their specific spatial SQL requirements, with PostGIS providing the familiar dialect used in enterprise geospatial databases.

Summary

• GeoLibre supports PostGIS through an in-browser PGlite instance that runs without external servers.
• The integration is implemented in apps/geolibre-desktop/src/lib/pglite-workspace.ts, which exposes the runPostgisQuery function.
• The pglite-loader.ts module handles dynamic imports of PGlite and the PostGIS extension to optimize performance.
• Users can execute standard PostGIS functions like ST_Intersects and ST_Buffer directly in the SQL Workspace panel.
• The workspace creates a temporary in-memory database per session, requiring CREATE EXTENSION IF NOT EXISTS postgis; on initialization.

Frequently Asked Questions

What is the difference between DuckDB Spatial and PostGIS in GeoLibre's SQL Workspace?

DuckDB Spatial is optimized for high-performance analytics on modern geospatial formats like GeoParquet, while PGlite with PostGIS provides full PostgreSQL compatibility with the complete PostGIS extension. Choose DuckDB for fast data processing tasks and PostGIS when you need specific PostGIS functions or compatibility with existing PostgreSQL workflows.

Do I need an internet connection to use PostGIS in GeoLibre?

No, GeoLibre bundles the PGlite and PostGIS modules locally through the dynamic loader system. The pglite-loader.ts imports dependencies from the local node_modules, and pglite-loader.cdn.ts serves as a fallback for specific build configurations. Once the application is loaded, all PostGIS operations execute entirely within the browser or desktop environment without external database servers.

Which PostGIS functions are available in GeoLibre's SQL Workspace?

The workspace loads the full PostGIS extension, making all standard spatial functions available. This includes spatial predicates like ST_Intersects and ST_Contains, geometry constructors like ST_MakeEnvelope and ST_Buffer, and format conversions like ST_AsGeoJSON and ST_AsText. The extension initialization runs CREATE EXTENSION IF NOT EXISTS postgis;, loading the complete function set supported by the @electric-sql/pglite-postgis package version bundled with GeoLibre.

Is data persisted when using PostGIS in the SQL Workspace?

No, the PGlite instance in GeoLibre's SQL Workspace operates as a temporary in-memory database for the current session. When you close the application or switch workspaces, the PostGIS database is destroyed. For persistent storage, you must export your results to GeoJSON, GeoParquet, or other supported file formats before closing the workspace.

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