PGlite

You author plain PostgreSQL inside one or more .sql files. The platform runs an in-browser PostgreSQL instance via @electric-sql/pglite — every non-test .sql file gets executed automatically on save against the same in-memory database, in lexicographic order.

The seed ships a script.sql that creates a greetings table, inserts the row (1, 'Hello, World!'), and selects it back — extend it by adding more schema, seed data, and queries.

Entry file

Solution files live at the project root:

• script.sql — editable solution file. The starter contains a TODO walkthrough for creating the greetings table and selecting the message.
• You can rename script.sql to anything, and you can create additional .sql files (organize them under subfolders if you like). The auto-runner discovers every non-test .sql file in the tree.

Files run in lexicographic order by full path. Prefix file names (01-schema.sql, 02-data.sql, 03-queries.sql) when execution order matters.

Database playground

The right panel is a live Database tab that talks to the same in-memory PGlite instance:

• Auto-run result — every SELECT (or other row-producing statement) from your .sql files renders here, grouped by source file, on every save.
• Tables — schema sidebar listing every public table plus its columns + types. Click Preview on a table to instantly SELECT * FROM <table> LIMIT 20 in the ad-hoc tab.
• Ad-hoc query — Monaco-powered SQL editor for one-off queries against the live database. Ctrl/⌘ + Enter runs the query without touching your source files.

The database is in-memory: refreshing the preview wipes it. To clean up without reloading, run DROP TABLE … in any source file or in the ad-hoc tab.

Version

Running PGlite v0.2.17 (PostgreSQL WASM) with Vitest v3.2.4 on Node v22.

Supported languages

SQL (PostgreSQL dialect)

Testing framework

Vitest with the @dojocode/sql-test-helpers helper.

Special reminders and implementation details

• Standard PostgreSQL dialect. Use SERIAL / BIGSERIAL for auto-increment IDs, ON CONFLICT for upserts, RETURNING to fetch inserted rows.
• Cast COUNT(*) to ::int in tests — PGlite returns bigint (as a string) by default, which compares awkwardly with JS numbers.
• Files matching *.test.sql are skipped by auto-run — reserved for a future raw-SQL test runner.
• Tests are TypeScript (*.test.ts) using the shared createPgliteTestDb(import.meta.url) helper. The helper boots a fresh PGlite instance and auto-loads every non-test .sql file alongside the test before assertions run.
• PGlite supports CTEs, window functions, JSON, full-text search, and most PostgreSQL features.

Example with Vitest:

import { describe, it, expect, beforeAll } from 'vitest';
import { createPgliteTestDb, type PgliteTestDb } from '@dojocode/sql-test-helpers/pglite';

let db: PgliteTestDb;

beforeAll(async () => {
  db = await createPgliteTestDb(import.meta.url);
});

describe('greetings', () => {
  it("contains the row with message 'Hello, World!'", async () => {
    const rows = await db.query<{ message: string }>(
      'SELECT message FROM greetings WHERE id = 1'
    );
    expect(rows[0]?.message).toBe('Hello, World!');
  });

  it('has exactly one row', async () => {
    const rows = await db.query<{ n: number }>('SELECT COUNT(*)::int AS n FROM greetings');
    expect(rows[0].n).toBe(1);
  });
});

The helper returns { query<T>(sql, params?): Promise<T[]>, exec(sql): Promise<void>, raw: PGlite }. query is async and rows come back already unwrapped (no .rows indirection). Use raw only for advanced cases (transactions, live queries, listen/notify).

Included libraries

• @electric-sql/pglite
• @dojocode/sql-test-helpers
• hono (in-WC server runtime)
• @hono/node-server
• vitest

How to debug

Three ways to inspect what your SQL is doing:

1. The Auto-run result tab

Every SELECT result is rendered there with its source file label. If a query returns nothing, the section is omitted — that itself is a useful signal that a row didn't get inserted or a WHERE filter is too tight.

2. The Tables tab

Confirms which tables exist and their column types right now. If you're hitting "relation does not exist" errors, the table didn't get created — usually a syntax error in a CREATE statement above it.

3. The Ad-hoc query tab

Run targeted SELECTs against the live database without modifying your source files:

SELECT * FROM information_schema.tables WHERE table_schema = 'public';
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'greetings';
SELECT * FROM greetings WHERE id = 1;

Common pitfalls

• Forgetting ON CONFLICT DO NOTHING — re-running your script can fail with unique-constraint violations if a previous run already inserted the same rows.
• bigint vs int in tests — SELECT COUNT(*) FROM users returns a string; cast with ::int to compare against expect(n).toBe(2).
• Cross-folder execution order — files are sorted by their full path, so a/02-init.sql runs BEFORE b/01-init.sql. Prefix folders too when order matters across directories.