English

Română

English

Română

Appearance

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: ​

typescript
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 ​

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:

sql
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.