SQL Workbench Embedded

Init Queries Feature - DuckDB Extension Management

About Init Queries

The initQueries configuration allows you to execute initialization SQL queries once per page before any user query runs. This is perfect for:

Key behaviors:

⚠️ Important

In this example, the INSTALL and LOAD commands for the spatial extension and the community a5 extension are configured as init queries. This allows all embeds below to use geospatial functions and additional utility functions without needing to install the extensions in each query.

Open your browser console to see the init queries being executed when you click Run on any embed for the first time.

Configuration

Add this before your embeds initialize:

<script>
  // Configure init queries globally
  window.SQLWorkbench.config({
    initQueries: [
      "INSTALL spatial",
      "LOAD spatial",
      "INSTALL a5 FROM community",
      "LOAD a5"
    ]
  });
</script>

Example 1: Spatial Distance Calculations

This query uses the spatial extension to calculate distances between points using geospatial functions that were installed via init queries. 

-- Calculate distances between cities using spatial extension
WITH cities AS (
  SELECT 'New York' as city, 40.7128 as lat, -74.0060 as lon
  UNION ALL
  SELECT 'Los Angeles', 34.0522, -118.2437
  UNION ALL
  SELECT 'Chicago', 41.8781, -87.6298
  UNION ALL
  SELECT 'Houston', 29.7604, -95.3698
)
SELECT
  c1.city as city1,
  c2.city as city2,
  ROUND(
    ST_Distance(
      ST_Point(c1.lon, c1.lat),
      ST_Point(c2.lon, c2.lat)
    ) / 1000, 2
  ) as distance_km
FROM cities c1
CROSS JOIN cities c2
WHERE c1.city > c2.city
ORDER BY distance_km DESC;

Example 2: Geospatial Point Creation

Create and manipulate geospatial points using the spatial extension functions. The extension is already loaded from the first query execution. 

-- Create points and extract coordinates
SELECT
  'Office' as location,
  ST_Point(-122.4194, 37.7749) as point,
  ST_X(ST_Point(-122.4194, 37.7749)) as longitude,
  ST_Y(ST_Point(-122.4194, 37.7749)) as latitude,
  ST_AsText(ST_Point(-122.4194, 37.7749)) as wkt_format
UNION ALL
SELECT
  'Home',
  ST_Point(-122.4089, 37.7833),
  ST_X(ST_Point(-122.4089, 37.7833)),
  ST_Y(ST_Point(-122.4089, 37.7833)),
  ST_AsText(ST_Point(-122.4089, 37.7833));

Example 3: A5 Extension - GeoJSON Generation

Use the community a5 extension to convert geographic coordinates into A5 cells and generate GeoJSON polygons. This demonstrates that community extensions work seamlessly with the spatial extension. 

-- Generate GeoJSON for A5 cell (Madrid coordinates)
SELECT
  ST_AsGeoJSON(
    ST_MakePolygon(
      ST_MakeLine(
        list_transform(
          a5_cell_to_boundary(
            a5_lonlat_to_cell(-3.7037, 40.41677, 10)
          ),
          x -> ST_Point(x[1], x[2])
        )
      )
    )
  ) as geojson;

Example 4: Attaching External DuckDB Databases

The ATTACH statement lets you connect to external DuckDB database files hosted remotely. This is perfect for sharing pre-computed datasets without loading them into the main database.

Note: Uncomment the ATTACH query in the configuration below to try this example. 

-- Query the attached gtfs database
-- Requires httpfs extension and ATTACH statement in init queries
-- e.g ATTACH 'https://data.openrailway.dev/providers/gtfs-de/full/database.duckdb' AS gtfs_de (READ_ONLY)
SELECT count(*) FROM gtfs_de.stops;

More Init Query Examples

Here are other common use cases for init queries:

// Install official extension
window.SQLWorkbench.config({
  initQueries: [
    "INSTALL spatial",
    "LOAD spatial",
  ]
});

// Attach external DuckDB database
window.SQLWorkbench.config({
  initQueries: [
    "INSTALL httpfs",
    "LOAD httpfs",
    "ATTACH 'https://data.openrailway.dev/providers/gtfs-de/full/database.duckdb' AS gtfs_de (READ_ONLY)"
  ]
});

// Create user-defined functions
window.SQLWorkbench.config({
  initQueries: [
    "CREATE MACRO add_tax(price, rate) AS price * (1 + rate)",
    "CREATE MACRO full_name(first, last) AS first || ' ' || last",
    "CREATE MACRO distance_km(lat1, lon1, lat2, lon2) AS ST_Distance(ST_Point(lon1, lat1), ST_Point(lon2, lat2)) / 1000"
  ]
});

📚 Learn More