How to Set Up the SQL Workspace with PostGIS (PGlite) for Browser Spatial SQL

GeoLibre provides an in-browser SQL Workspace powered by PGlite (WebAssembly PostgreSQL) and PostGIS, enabling spatial SQL queries against GeoJSON layers without a backend server.

The GeoLibre repository ships a lightweight, browser-based SQL engine that combines PGlite (a WebAssembly implementation of PostgreSQL) with the PostGIS extension. This setup allows you to run complex spatial SQL queries directly against in-memory GeoJSON FeatureCollections, eliminating the need for a dedicated database server.

How the Browser-Based SQL Engine Works

The SQL Workspace in GeoLibre follows a six-stage pipeline to transform GeoJSON layers into queryable PostGIS tables. According to the source code in apps/geolibre-desktop/src/lib/pglite-workspace.ts, the engine uses a singleton pattern to manage the PGlite instance, ensuring that the ~25 MB of WebAssembly files are loaded only once and reused across multiple queries.

The architecture handles schema isolation, automatic column type inference, and batch data insertion while respecting PostgreSQL's bind parameter limits. All spatial data is automatically projected to EPSG:4326 during import, allowing consistent coordinate reference system handling across different layer sources.

Step-by-Step Implementation

1. Lazy Load the PGlite Engine

To avoid blocking the initial page load, the engine implements lazy loading via loadPgliteModules in apps/geolibre-desktop/src/lib/pglite-loader.ts. This function dynamically imports the PGlite core and PostGIS extension only when the user first opens the SQL Workspace.

import { loadPgliteModules } from '@/lib/pglite-loader';

// First call downloads ~25MB of WASM files; subsequent calls return cached modules
const modules = await loadPgliteModules();

The Vite configuration in vite.config.ts creates a separate chunk for @electric-sql/pglite and supports a CDN loader for embedded builds, controlled via the GEOLIBRE_PGLITE_CDN environment variable.

2. Initialize the Singleton Instance

The getState function in pglite-workspace.ts maintains a memoized statePromise that holds a single PGlite instance. Upon first execution, it initializes the database with the postgis extension enabled.

// Internal implementation detail from pglite-workspace.ts
const pglite = new PGlite({
  extensions: { postgis },
});
await pglite.exec('CREATE EXTENSION IF NOT EXISTS postgis');

This singleton pattern ensures that multiple queries share the same database connection without reinitializing the WebAssembly runtime.

3. Configure Schema Isolation

All layers are registered in a dedicated temporary schema named geolibre. The registerLayerTables function drops and recreates this schema on every query execution, ensuring that table definitions always match the current map state and preventing stale data from previous sessions.

4. Infer Column Types and Create Tables

The inferPropertyColumns function in apps/geolibre-desktop/src/lib/pglite-sql.ts analyzes every feature in the GeoJSON to determine column types. It maps JavaScript types to PostgreSQL equivalents:

• Numbers → double precision
• Booleans → boolean
• Objects/Arrays → jsonb
• Strings/Others → text

The function also selects a geometry column name (geom, geometry, geom_2, etc.) that avoids collisions with existing property names.

5. Execute Batch Inserts

Because PostgreSQL caps bind parameters at 65,535, the engine inserts data in chunks defined by INSERT_CHUNK_ROWS. The buildInsertChunk function constructs parameterized INSERT statements that convert GeoJSON geometries to PostGIS format using ST_SetSRID(ST_GeomFromGeoJSON(...), 4326).

// From pglite-workspace.ts - handles wide tables by adjusting chunk size
const INSERT_CHUNK_ROWS = calculateChunkSize(columnCount);

6. Run Spatial Queries

The runPostgisQuery function orchestrates the execution flow:

1. Registers all current layers as temporary tables
2. Describes the query to detect geometry columns using describeQuery
3. If geometry is present, projects columns and renders geometry as WKT for the results grid while adding a hidden ST_AsGeoJSON column for export
4. Returns a SqlQueryResult object compatible with the DuckDB engine interface

Build Configuration for CDN vs. Offline Bundles

The Vite configuration supports two distribution modes via environment variables:

• CDN Mode (GEOLIBRE_PGLITE_CDN=1): Loads WASM files from a CDN, reducing the initial bundle size to near zero
• Offline Mode (GEOLIBRE_PGLITE_CDN=0): Bundles all ~25MB of assets locally for air-gapped environments

# Force offline bundle in CI or desktop builds

export GEOLIBRE_PGLITE_CDN=0
npm run build

The pglite-loader.cdn.ts file provides the CDN implementation, while pglite-loader.ts handles the standard bundled approach.

Practical Example: Querying Layers with PostGIS

Here is a complete workflow for executing spatial SQL against GeoLibre layers:

import { loadPgliteModules } from '@/lib/pglite-loader';
import { runPostgisQuery } from '@/lib/pglite-workspace';
import { useLayers } from '@geolibre/core';

async function executeSpatialQuery(sql: string) {
  // 1. Ensure engine is ready (lazy load)
  await loadPgliteModules();
  
  // 2. Get current layers from the map store
  const { layers } = useLayers();
  
  // 3. Execute query
  const result = await runPostgisQuery(sql, layers);
  
  console.log('Columns:', result.columns);
  console.log('Rows:', result.rows);
  
  // 4. Access GeoJSON for re-importing as a new layer
  if (result.geojson) {
    console.log('FeatureCollection ready:', result.geojson);
  }
}

// Example: Create 100-meter buffers around roads within a bounding box
const bufferQuery = `
  SELECT
    id,
    name,
    ST_AsGeoJSON(ST_Buffer(geom, 100)) AS geom
  FROM roads
  WHERE ST_Intersects(
    geom, 
    ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326)
  );
`;

executeSpatialQuery(bufferQuery);

Summary

• GeoLibre combines PGlite and PostGIS to provide a fully client-side spatial SQL engine running in WebAssembly
• The singleton pattern in pglite-workspace.ts ensures efficient memory usage by sharing one PGlite instance across queries
• Schema isolation via the geolibre temporary schema prevents data collisions between query sessions
• Automatic type inference converts GeoJSON properties to appropriate PostgreSQL types (double precision, jsonb, text)
• Batch insertion respects PostgreSQL's 65,535 parameter limit through chunked INSERT operations
• CDN support allows for minimal initial bundle sizes while maintaining offline capability via environment variables

Frequently Asked Questions

What is PGlite and how does it work in the browser?

PGlite is a WebAssembly build of PostgreSQL that runs entirely in the browser without a server. In GeoLibre, it executes as a singleton instance initialized via getState in pglite-workspace.ts, loading the ~25MB WebAssembly files on first use and persisting the database in memory for the session duration.

How does GeoLibre handle geometry columns during SQL execution?

The describeQuery function detects geometry columns automatically. When found, the engine projects all result columns and converts geometries to WKT format for display, while maintaining a hidden ST_AsGeoJSON column in pglite-workspace.ts to facilitate exporting results back to GeoJSON for map visualization.

Can I use this SQL Workspace without an internet connection?

Yes. By setting GEOLIBRE_PGLITE_CDN=0 before building, the Vite configuration bundles all PGlite and PostGIS WebAssembly files locally. This creates a fully offline-capable spatial SQL environment suitable for desktop applications or air-gapped networks, though the initial download size increases by approximately 25 MB.

What are the performance limits for inserting data into the SQL Workspace?

The engine batches insertions to respect PostgreSQL's 65,535 bind parameter limit, automatically calculating chunk sizes based on table width. For tables with many columns, the INSERT_CHUNK_ROWS constant decreases proportionally to prevent parameter overflow errors while maximizing throughput.

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