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

> Set up the SQL Workspace with PostGIS (PGlite) for browser spatial SQL. Query GeoJSON layers directly in your browser without a backend server using this powerful tool.

- Repository: [Open Geospatial Solutions/GeoLibre](https://github.com/opengeos/GeoLibre)
- Tags: how-to-guide
- Published: 2026-08-02

---

**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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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.

```typescript
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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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.

```typescript
// 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`](https://github.com/opengeos/GeoLibre/blob/main/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)`.

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

```bash

# Force offline bundle in CI or desktop builds

export GEOLIBRE_PGLITE_CDN=0
npm run build

```

The [`pglite-loader.cdn.ts`](https://github.com/opengeos/GeoLibre/blob/main/pglite-loader.cdn.ts) file provides the CDN implementation, while [`pglite-loader.ts`](https://github.com/opengeos/GeoLibre/blob/main/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:

```typescript
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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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`](https://github.com/opengeos/GeoLibre/blob/main/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.