--{{0}}--
PGlite is a lightweight WASM Postgres build packaged into a TypeScript library that runs entirely in the browser. This LiaScript template allows you to embed interactive PostgreSQL queries directly in your educational materials, enabling students to experiment with SQL and database operations in the browser using PGlite.
For more information about PGlite, visit the PGlite website.
Try it on LiaScript:
See the project on Github:
https://github.com/LiaTemplates/PGlite
Experiment in the LiveEditor:
--{{1}}--
Like with other LiaScript templates, there are three ways to integrate PGlite, but the easiest way is to copy the import statement into your project.
{{1}}
-
Load the latest macros via (this might cause breaking changes):
import: https://raw.githubusercontent.com/LiaTemplates/PGlite/main/README.mdor the current version 0.0.4 via:
import: https://raw.githubusercontent.com/LiaTemplates/PGlite/0.0.4/README.md -
Copy the definitions into your Project
-
Clone this repository on GitHub
--{{0}}--
This is the most common way to run PostgreSQL queries in LiaScript. It executes SQL queries and displays the results in a nicely formatted table. The macro requires a database name parameter (which allows you to have multiple independent databases) and executes the SQL code block.
SELECT 'Hello, PGlite!' AS greeting, 42 AS answer;@PGlite.eval(demo)
--{{1}}--
You can create tables, insert data, and perform complex queries:
{{1}}
-- Create a table with sample data
CREATE TABLE weather (
city VARCHAR(80),
temp_lo INT,
temp_hi INT,
prcp REAL,
date DATE
);
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO weather VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT INTO weather VALUES ('Hayward', 37, 54, NULL, '1994-11-29');
-- Query the data
SELECT city, (temp_hi + temp_lo) / 2 AS temp_avg, date
FROM weather
ORDER BY temp_avg DESC;@PGlite.eval(demo)
--{{2}}--
PGlite supports full PostgreSQL functionality including aggregations, joins, and window functions:
{{2}}
SELECT
city,
COUNT(*) AS num_readings,
AVG(temp_hi) AS avg_high,
MAX(temp_hi) AS max_high,
MIN(temp_lo) AS min_low
FROM weather
GROUP BY city
ORDER BY city;@PGlite.eval(demo)
--{{0}}--
This macro creates an interactive terminal where users can execute multiple
queries in succession. Unlike @PGlite.eval, which executes once, the terminal
mode allows continuous interaction with the database. Users can type queries and
see results immediately.
-- Initial query to set up the database
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
category VARCHAR(50)
);
INSERT INTO products (name, price, category) VALUES
('Laptop', 999.99, 'Electronics'),
('Mouse', 29.99, 'Electronics'),
('Desk', 299.99, 'Furniture'),
('Chair', 199.99, 'Furniture');
-- Try running these queries in the terminal below:
-- SELECT * FROM products;
-- SELECT category, AVG(price) AS avg_price FROM products GROUP BY category;
-- SELECT * FROM products WHERE price > 100 ORDER BY price;@PGlite.terminal(shop)
--{{1}}--
The terminal maintains the database state, so you can build upon previous queries. This is excellent for teaching incremental database operations and allowing students to explore data interactively.
--{{0}}--
For advanced use cases, this macro allows you to write custom JavaScript code that interacts with the PGlite database object. This is useful when you need more control over query execution, want to process results programmatically, or integrate with other JavaScript libraries.
// Create a table with sample data
await db.exec(`
CREATE TABLE sales (
month VARCHAR(10),
product VARCHAR(20),
revenue INTEGER
);
INSERT INTO sales VALUES
('2024-01', 'Product A', 1500),
('2024-01', 'Product B', 2300),
('2024-02', 'Product A', 1800),
('2024-02', 'Product B', 2100),
('2024-03', 'Product A', 2200),
('2024-03', 'Product B', 2500);
`);
// Query and process results
const result = await db.query(`
SELECT
product,
SUM(revenue) AS total_revenue,
AVG(revenue)::INTEGER AS avg_revenue,
COUNT(*) AS num_months
FROM sales
GROUP BY product
ORDER BY total_revenue DESC
`);
// Access the results
const data = result.rows;
console.log("Sales Analysis:");
// Display results in a custom format
let output = '<div style="padding: 10px; background: #1e1e1e; color: #eee; font-family: monospace;">';
output += '<h3 style="color: #fff; margin-top: 0;">Sales Summary</h3>';
for (const row of data) {
output += `<div style="margin: 8px 0; padding: 8px; background: #2b2b2b; border-left: 3px solid #4a9eff;">`;
output += `<strong style="color: #4a9eff;">${row.product}</strong><br/>`;
output += `Total Revenue: $${row.total_revenue.toLocaleString()}<br/>`;
output += `Average: $${row.avg_revenue.toLocaleString()}<br/>`;
output += `Months: ${row.num_months}`;
output += `</div>`;
}
output += '</div>';
console.html(output);@PGlite.js(analytics)
--{{1}}--
The database object (db) is automatically provided and connected. You have
full access to the PGlite JavaScript API, allowing you to execute complex
workflows, handle errors, and integrate with other browser APIs.
{{1}}
Note on numeric values: PostgreSQL often returns numeric aggregations with high precision. To avoid display issues, cast large numbers to INTEGER using
::INTEGERin your SQL queries, or handle numeric values explicitly in your JavaScript code using.toString()orNumber()conversion.
--{{0}}--
PGlite supports the full PostgreSQL feature set including window functions, CTEs (Common Table Expressions), and complex aggregations:
-- Create sample time-series data
CREATE TABLE daily_sales (
sale_date DATE,
sales INTEGER,
store VARCHAR(20)
);
INSERT INTO daily_sales
SELECT
DATE '2024-01-01' + (day || ' days')::INTERVAL AS sale_date,
50 + FLOOR(RANDOM() * 50) AS sales,
'Store ' || ((day % 3) + 1) AS store
FROM generate_series(0, 29) AS day;
-- Calculate moving averages with window functions
SELECT
sale_date,
store,
sales,
AVG(sales) OVER (
PARTITION BY store
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3day
FROM daily_sales
ORDER BY store, sale_date
LIMIT 15;@PGlite.eval(advanced)
--{{1}}--
You can also use CTEs for complex queries:
{{1}}
-- Using Common Table Expressions (CTEs)
WITH weekly_stats AS (
SELECT
store,
DATE_TRUNC('week', sale_date) AS week,
SUM(sales) AS weekly_total,
AVG(sales) AS weekly_avg
FROM daily_sales
GROUP BY store, DATE_TRUNC('week', sale_date)
)
SELECT
store,
COUNT(*) AS num_weeks,
AVG(weekly_total)::INTEGER AS avg_weekly_sales,
MAX(weekly_total) AS best_week,
MIN(weekly_total) AS worst_week
FROM weekly_stats
GROUP BY store
ORDER BY avg_weekly_sales DESC;@PGlite.eval(advanced)
--{{0}}--
PGlite includes a special feature to visualize your database schema using
dbdiagram.io. Simply run the erdiagram command to generate an interactive
entity-relationship diagram of all your tables.
-- Create multiple related tables
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
order_date DATE,
total DECIMAL(10,2)
);
CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(order_id),
product_name VARCHAR(100),
quantity INTEGER,
price DECIMAL(10,2)
);
ERDIAGRAM; -- Visualize the schema@PGlite.eval(visualization)
--{{0}}--
Each macro call can use a different database name (the parameter in parentheses). This allows you to have multiple independent databases in the same document:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');
SELECT * FROM users;@PGlite.eval(db1)
-- This is a completely separate database
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
description VARCHAR(200),
amount DECIMAL(10,2)
);
INSERT INTO orders (description, amount) VALUES
('Order A', 150.00),
('Order B', 200.00);
SELECT * FROM orders;@PGlite.eval(db2)
--{{1}}--
The database name parameter ensures isolation between different examples and exercises in your course material.
--{{0}}--
PGlite includes support for pgTAP, a unit testing framework for PostgreSQL. This allows you to write and run database tests directly in your educational materials.
-- Create a test suite
CREATE EXTENSION IF NOT EXISTS pgtap;
-- Test table creation
SELECT plan(3);
CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
value INTEGER
);
SELECT has_table('test_table', 'test_table should exist');
SELECT has_column('test_table', 'id', 'test_table should have id column');
SELECT has_column('test_table', 'value', 'test_table should have value column');
SELECT * FROM finish();@PGlite.eval(testing)
--{{0}}--
This template is perfect for:
- Teaching SQL: Interactive SQL tutorials where students can modify and run queries
- Database Design Courses: Demonstrate table creation, constraints, and relationships
- Database Concepts: Show transactions, indexes, and query optimization
- Application Development: Teach how to interact with databases from code
- Data Analysis: Pre-process and explore datasets with SQL
- Testing Practices: Demonstrate database testing with pgTAP
- Interactive Examples: Allow readers to experiment with queries in documentation
--{{0}}--
The LiaScript implementation of PGlite is based on @electric-sql/pglite, which runs entirely in the browser using WebAssembly. The implementation includes custom table rendering with a dark theme and support for multiple concurrent database instances.
- PGlite WASM: Uses the official
@electric-sql/pglitepackage - PostgreSQL Compatible: Full PostgreSQL 16 feature set
- Table Rendering: Custom HTML table renderer with dark theme styling
- Multiple Databases: Supports concurrent isolated database instances
- Result Display: Automatic formatting of query results with row limiting
- Extensions: Includes pgTAP extension for database testing
- Schema Visualization: Integration with dbdiagram.io for ER diagrams
--{{0}}--
For more examples and detailed PostgreSQL usage, see the PostgreSQL documentation
--{{0}}--
Here's a complete example combining multiple features:
-- Complete example: Student grade tracking system
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE courses (
course_id SERIAL PRIMARY KEY,
course_name VARCHAR(100),
credits INTEGER
);
CREATE TABLE enrollments (
enrollment_id SERIAL PRIMARY KEY,
student_id INTEGER REFERENCES students(student_id),
course_id INTEGER REFERENCES courses(course_id),
grade DECIMAL(3,2),
semester VARCHAR(20)
);
-- Insert sample data
INSERT INTO students (name, email) VALUES
('Alice Johnson', 'alice@university.edu'),
('Bob Smith', 'bob@university.edu'),
('Carol White', 'carol@university.edu');
INSERT INTO courses (course_name, credits) VALUES
('Database Systems', 4),
('Web Development', 3),
('Data Structures', 4);
INSERT INTO enrollments (student_id, course_id, grade, semester) VALUES
(1, 1, 3.7, 'Fall 2024'),
(1, 2, 4.0, 'Fall 2024'),
(2, 1, 3.3, 'Fall 2024'),
(2, 3, 3.8, 'Fall 2024'),
(3, 2, 3.9, 'Fall 2024'),
(3, 3, 4.0, 'Fall 2024');
-- Complex query with joins and aggregations
SELECT
s.name,
COUNT(e.course_id) AS courses_taken,
AVG(e.grade)::DECIMAL(3,2) AS gpa,
SUM(c.credits) AS total_credits
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
GROUP BY s.student_id, s.name
ORDER BY gpa DESC;@PGlite.eval(complete_example)
--{{0}}--
This template is released under the CC0-1.0 license, meaning you can use it freely in your projects without restrictions.