Build a Spreadsheet Data Editor for PGlite
PGlite is Postgres itself, compiled to WebAssembly and published as a TypeScript package. It starts inside the browser tab with no database server behind it. It parses SQL, enforces constraints, runs transactions, and can keep its files in IndexedDB, so the rows are still there on the next visit. It draws nothing.
A beekeeper with fourteen hives, four corrections to make and two inspections to add needs a screen, and the database has no opinion about that screen. The rows come out of a PGlite table, a person edits them as a spreadsheet, and the edits go back as INSERT, UPDATE and DELETE inside one transaction. No file goes near any of it.
The boot line settles where the rows live and what shape they arrive in
PGlite's documentation describes it as running in the browser, Node.js and Bun with no other dependencies. On version 0.5.8, SELECT version() answers a string that opens PostgreSQL 18.3 (PGlite 0.5.8) on wasm32-unknown-emscripten and goes on to name the emcc build. How to import CSV into DuckDB covers the other database that runs inside the tab.
import { PGlite, types } from "@electric-sql/pglite";
export const db = await PGlite.create("idb://apiary", { parsers: { [types.DATE]: (value) => value, [types.BOOL]: (value) => (value === "t" ? "Yes" : "No"), },});PGlite.create() is the static method the documentation prefers, because it resolves once the database has finished starting. The idb:// prefix puts the data files in IndexedDB, so the table survives a page reload. Browsers treat that storage as evictable, so an app that has to survive a gap between visits calls navigator.storage.persist() first and reads the answer, because the browser can refuse. PGlite recommends IndexedDB for the browser today, because its OPFS filesystem does not work in Safari. The documentation names a Safari limit of 252 open sync access handles against a Postgres install of more than 300 files.
The two parsers entries decide the JavaScript value each column carries by the time it reaches the grid.
PGlite is single connection only, so the page shares one instance. An application that wants several tabs on one database runs PGlite inside PGliteWorker and lets the tabs elect a leader.
The table decides what a row is
An apiary log records one inspection per hive per day.
CREATE TABLE IF NOT EXISTS inspections ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, hive_ref TEXT NOT NULL, inspected_on DATE NOT NULL, queen_seen BOOLEAN NOT NULL DEFAULT false, brood_frames INTEGER NOT NULL CHECK (brood_frames BETWEEN 0 AND 20), varroa_count INTEGER NOT NULL CHECK (varroa_count >= 0), action TEXT, UNIQUE (hive_ref, inspected_on));The identity column generates id, so a plain INSERT that supplies a value of its own is refused. CHECK holds brood_frames between 0 and 20 and keeps varroa_count off negative numbers. NOT NULL stands on every column but action. UNIQUE (hive_ref, inspected_on) refuses a second row with the same hive reference and the same date.
Postgres enforces every one of those rules at the moment of the write. The person making the edit is somewhere else by then, looking at a screen that has already accepted their typing. Two of those rules move forward, to the cell the person is filling, and that is most of what the editor is for.
A database row is not yet a spreadsheet row
A grid paints text. Postgres holds types, and the client turns them into JavaScript. On PGlite 0.5.8 through Node 24.19.0, a SELECT over this table with no parser registered answers with four JavaScript shapes, and null wherever action is empty.
brood_frames integer 7 numbervarroa_count integer 4 numberaction text "Added super" stringqueen_seen boolean true booleaninspected_on date Date object at UTC midnightThe date is the one that breaks. Updog Data Editor paints a cell by taking its value as a string and running the date column's display codec over it, so a canonical 2026-08-17 reaches the screen in the browser locale's date order, always Gregorian and always in Latin digits. The codec hands back any value it cannot read as an ISO date, so a Date object reaches the canvas as the string JavaScript makes of it.
TZ=UTCMon Aug 17 2026 00:00:00 GMT+0000 (Coordinated Universal Time)
TZ=America/New_YorkSun Aug 16 2026 20:00:00 GMT-0400 (Eastern Daylight Time)In New York the table says the seventeenth of August and the cell says the sixteenth. A Date object is not an ISO date, so the date rule flags every date cell Invalid date, and this screen refuses to submit. The flag names the shape and never mentions the day.
PGlite documents parsers, an object mapping a Postgres type id to a function that receives the raw value as text. The two lines in the boot snippet use it. The parser registered under types.DATE hands back the wire text 2026-08-17 untouched, which is exactly the form the date column canonicalises. The one under types.BOOL receives t or f and answers Yes or No.
A Postgres boolean travels through the grid as a select. As of August 2026 a column's editor is text, date, time, select, multiselect or number, and a column with no editor behaves as text, so the parser turns t and f into the two words the dropdown holds, and the write turns the word back.
A NUMERIC column needs nothing at all, and this table carries none. PGlite hands one over as a string, and a number column keeps its cells as strings, so 59.90 makes the round trip with no float in the middle of it.
Shape the values once, at the client, and every query in the application gets the same shapes. The same overrides can travel per query through QueryOptions when only one screen wants them.
One SELECT fills the grid
loadData receives an onChunk callback and can call it several times to stream a large table in batches. A table this size arrives in one call.
const SELECT_INSPECTIONS = ` SELECT id, hive_ref AS "hiveRef", inspected_on AS "inspectedOn", queen_seen AS "queenSeen", brood_frames AS "broodFrames", varroa_count AS "varroaCount", action FROM inspections ORDER BY hive_ref, inspected_on`;
const loadInspections = useCallback( async (onChunk: (rows: Inspection[]) => void) => { const result = await db.query<Inspection>(SELECT_INSPECTIONS); onChunk(result.rows); }, [version],);AS "hiveRef" renames each column to the id the editor addresses it by. The table is written in snake_case, and quoting the alias keeps the case that Postgres would otherwise fold to lowercase.
loadInspections is wrapped in useCallback on purpose. The editor loads again whenever it receives a different loadData function, and it clears the grid first, so a loader rebuilt on each render of your component would throw away an unsaved edit. The version dependency is what fires that reload here, and a commit is what moves it.
Identity does not have to be a spreadsheet column
columns names six fields and id is not one of them. The rows still carry it.
what the SELECT returns { id: 3, hiveRef: "WM-03", inspectedOn: "2026-08-19", … }what the grid draws Hive · Inspected on · Queen seen · Brood frames · Varroa · Actionwhat onComplete returns { id: 3, hiveRef: "WM-03", inspectedOn: "2026-08-19", … }A row keeps every field you send it, whether or not a column claims it. The editor fills in the columns a row is missing and leaves the rest alone, so the primary key goes out with the row and comes back on it, and no cell edit can reach it.
primaryKey="id" belongs to import matching. The prop points at a field no column declares, so the editor drops it and warns once when it mounts, [updog] primaryKey names "id", which no column declares. Imported rows are appended instead of merged. This screen takes no file yet, and when one arrives it carries no id, so appending is what it wants.
The editor opens with no import surface
Updog Data Editor is a spreadsheet over an in-memory dataset. A file is one way to fill that dataset. A SELECT is another.
<DataEditor<Inspection> apiKey={import.meta.env.VITE_UPDOG_KEY} mode="inline" variant="editor" columns={columns} primaryKey="id" loadData={loadInspections} onComplete={saveInspections} importFormats={false} enableAddSource={false} enableCreateColumn={false} enableAddRow enableDeleteRow="all" blockSubmitOnError/>variant="editor" opens the grid directly, which is the default. importFormats={false} turns file import off, which closes both routes into the wizard, the add-source button and the drop target over the grid. enableAddSource={false} hides that button on its own, and a screen that keeps import uses it alone. enableCreateColumn={false} matters once the import surface comes back, and it stops an unmatched header from becoming a column the table does not have. What remains is a grid over the rows the SELECT returned.
enableAddRow puts Insert row above and Insert row below in the right-click menu on any cell. Right-click the row number and the same menu also holds Duplicate row. enableDeleteRow="all" adds Delete row to the row-number menu, so the person can remove a row the database already holds, the way uniting two hives removes one of them. blockSubmitOnError keeps the submit button disabled while any rule fails or a remote check is still running.
For a screen that only shows the table, variant="viewer" renders the grid alone. It drops the sidebar, the footer and the AI assistant, and it stays read-only whatever readonly says.
This example uses the React component. The web component build takes the same props through configure() for Vue, Angular and Svelte, with onClose replaced by a close event.
The editor checks one cell, Postgres checks the rest
The column definitions carry the table's per-cell rules into the place where a person can still act on them, and add one the table never had.
import type { DataEditorColumn } from "@updog/data-editor";
export const columns: DataEditorColumn[] = [ { id: "hiveRef", title: "Hive", size: 110, validators: [ { type: "required" }, { type: "regex", pattern: "^WM-\\d{2}$" }, ], }, { id: "inspectedOn", title: "Inspected on", size: 150, editor: { type: "date" }, validators: [{ type: "required" }, { type: "date" }], }, { id: "queenSeen", title: "Queen seen", size: 130, editor: { type: "select", options: ["Yes", "No"], enableCustomValue: false, }, validators: [{ type: "required" }], }, { id: "broodFrames", title: "Brood frames", size: 140, editor: { type: "number" }, validators: [ { type: "required" }, { type: "number", min: 0, max: 20, decimalPlaces: 0 }, ], }, { id: "varroaCount", title: "Varroa", size: 110, editor: { type: "number" }, validators: [ { type: "required" }, { type: "number", min: 0, decimalPlaces: 0 }, ], }, { id: "action", title: "Action", size: 220 },];min: 0, max: 20 is the CHECK (brood_frames BETWEEN 0 AND 20) constraint, moved forward by one screen. decimalPlaces: 0 stands for the INTEGER, and it refuses 8.0 in the cell as flatly as it refuses 21. { type: "required" } stands where NOT NULL does and reaches further, because it flags an empty string too. Nothing in the table constrains a hive reference, and ^WM-\d{2}$ is the editor's own rule. The dropdown holds queenSeen to the two words the write understands, and enableCustomValue: false takes away the entry that would let a person add a third.
24Uniqueness across two columns stays behind in the database. A built-in rule reads one column, one cell at a time or the whole column for { type: "unique" }, and never two columns at once. That rule can hold a hive reference or a date, and nothing holds the pair. UNIQUE (hive_ref, inspected_on) is left to Postgres, which raises 23505 when the pair repeats.
The blank cell has no rule on either side. Every row of a submitted result carries every column the editor declares. An untouched cell in a row the person added arrives as an empty string, and one in a row the SELECT filled keeps what Postgres handed over, which for an empty action is null. Postgres refuses "" for an integer with invalid input syntax for type integer: "", code 22P02, and accepts "" for a NOT NULL text column without complaint. The database rejects a missing count and stores a blank hive reference, which is why { type: "required" } sits on both.
Submit returns the changes and what the person meant by them
onComplete fires once per confirmed submit, from the confirmation the submit button opens, and receives the rows that moved, grouped by source. Each row carries isNew, isChanged, isDeleted and isValid. A row that came from loadData and nobody changed never appears. Import, edit and delete rows through your REST API sets out the four flags and the request each one calls for.
Rows the person adds arrive under their own source, named Manually added, and a new row holds every declared column as an empty string and no id at all. That is what the identity column wants. A plain INSERT that supplied an id of its own would be refused with cannot insert a non-DEFAULT value into column "id", code 428C9, so the statement lists the six columns a person can fill and lets the table name the row.
Duplicate row copies the row as the editor holds it, including the fields no column claims. The copy is new to the editor, and it carries the same id, the same hive and the same date as the row it copies. New to the editor says nothing about the database. Build each statement from the fields the table accepts, and a stale id riding along in the JavaScript object costs nothing. The hive and the date are a different matter, and they surface at the write.
The whole edit session commits or none of it does
Three statements cover the delta.
const INSERT_INSPECTION = ` INSERT INTO inspections (hive_ref, inspected_on, queen_seen, brood_frames, varroa_count, action) VALUES ($1, $2, $3 = 'Yes', $4, $5, nullif($6, ''))`;
const UPDATE_INSPECTION = ` UPDATE inspections SET hive_ref = $2, inspected_on = $3, queen_seen = $4 = 'Yes', brood_frames = $5, varroa_count = $6, action = nullif($7, '') WHERE id = $1`;
const DELETE_INSPECTION = `DELETE FROM inspections WHERE id = $1`;$3 = 'Yes' turns the dropdown word back into a boolean. nullif($6, '') writes NULL when the cell is empty. Without it the column stores an empty string, and an application reading that column as absent never finds it. Postgres parses each parameter for the column it lands in, so the values need no cast in JavaScript, and "9" reaches an integer column as 9.
The handler walks the delta and hands each row to one statement.
const saveInspections = useCallback( async (result: DataEditorResult<Inspection>) => { await db.transaction(async (tx) => { for (const source of result.sources) { for (const change of source.rows) { const row = change.row;
if (change.isDeleted) { if (!change.isNew) { await tx.query(DELETE_INSPECTION, [row.id]); } continue; }
const values = [ row.hiveRef, row.inspectedOn, row.queenSeen, row.broodFrames, row.varroaCount, row.action, ];
if (change.isNew) { await tx.query(INSERT_INSPECTION, values); continue; }
await tx.query(UPDATE_INSPECTION, [row.id, ...values]); } } });
setVersion((current) => current + 1); }, [],);db.transaction() rolls back when your callback rejects, and commits when it resolves with every statement inside it having succeeded. A statement that raised aborts the block, so a callback that swallows the error resolves onto a rollback. Four corrected rows, two new inspections and one deletion are one unit of work. There is no endpoint and no chunk size, because the database is in the same tab. There is no half-applied batch to reconcile afterwards, because the whole delta goes through one db.transaction().
The duplicated row lands here, in a submit after the one that went through.
duplicate key value violates unique constraint"inspections_hive_ref_inspected_on_key" code 23505Key (hive_ref, inspected_on)=(WM-03, 2026-08-21) already exists.
rows in inspections after the failed submit 7the valid new inspection that travelled with it 0One statement raised, the transaction rolled back, and the good row in the same delta was never written. Atomicity gives up partial success, and the person keeps a screen where every correction is still visible.
They keep it because the handler lets the error out. Updog Data Editor awaits the promise and clears the grid the moment it resolves. A handler that catches the failure and returns normally reads as a clean save, and the session is gone with the work unsaved, including the statements that had already succeeded. Let it reject and the editor keeps every row, every value and the undo history behind them.
After the commit the grid reads the table again
The editor empties itself once the handler resolves. Nothing refills it on its own, so without the last line of the handler the screen stays empty where a table full of rows should be.
setVersion gives loadInspections a new identity, and the editor's load effect is keyed on that function, so a new one sends it back to the database. The read that follows is the same plain SELECT, against a table that now holds the committed rows.
id hiveRef inspectedOn queenSeen broodFrames varroaCount action1 WM-01 2026-08-17 Yes 8 4 Added super7 WM-01 2026-08-21 Yes 9 32 WM-02 2026-08-17 Yes 5 9 Treated3 WM-03 2026-08-19 Yes 9 28 WM-03 2026-08-21 No 7 5 Requeen5 WM-05 2026-08-19 Yes 6 26 WM-06 2026-08-19 No 3 17 RequeenWM-04 was deleted, WM-02 carries its corrected mite count and its note, and the two rows dated the twenty-first are the ones the person added, wearing the ids the table gave them. WM-01 and WM-03 each sit there twice on two dates, which is what a key built from the pair allows. Reload the page and the seven rows are still in the table, because IndexedDB kept the files PGlite writes.
The second read replaces whatever the grid was holding. The editor clears its rows before the new ones arrive, so an unsaved correction goes with them. Bump the version after a commit, and leave it alone while a person is typing.
The editor holds a snapshot of the table
loadData runs when the editor opens and when its identity changes. The rows it hands over pass into the editor's keeping for the length of the session, and they come back to the application at onComplete. A write from somewhere else in the application while the grid is open leaves the screen as the person left it.
PGlite has the machinery for a screen that follows the table instead. Its live extension offers live.query, live.incrementalQuery and live.changes, and the last of those emits insert, update and delete objects keyed on a column. That fits a dashboard, where the newest number wins. Wire live.changes to the version counter here and every write to inspections, the grid's own commit included, reloads the grid and drops unsaved edits and the undo history with them.
Treat the grid as an edit session over a snapshot. Open it on the slice a person is working on, let them finish, commit, and read again.
Updog Data Editor validates its licence key against https://api.updog.tech/v1/validate when the editor mounts, from a dev server as readily as from a production build, so the page does make one request, and the rows are not in it. And Updog ships no PGlite connector, no destination list and no adapter. loadData and onComplete are the only two props that touch the database, and the SQL on both sides is yours.
A migration nobody reviews, run once against a table the application owns, belongs in SQL and needs no screen at all.
A file can still arrive on the same screen
The inspector who walks the apiary keeps notes in a phone app, and the phone app exports CSV.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Hive | Date | Queen | Brood | Varroa | Notes |
| 2 | WM-06 | 24/08/2026 | Y | 8 | 1 | |
| 3 | WM-07 | 24/08/2026 | N | 4 | 17 | Requeen |
| 4 | WM-08 | 24/08/2026 | Y | 11 | 3 | Added super |
| 5 | WM-09 | 25/08/2026 | Y | 7 | 0 |
1Hive,Date,Queen,Brood,Varroa,Notes2WM-06,24/08/2026,Y,8,1,3WM-07,24/08/2026,N,4,17,Requeen4WM-08,24/08/2026,Y,11,3,Added super5WM-09,25/08/2026,Y,7,0,Two of the props the mount switched off come back on, and synonyms joins them. enableCreateColumn stays off, so a header the schema does not know stays out of the grid instead of becoming a column the table has no room for.
<DataEditor<Inspection> /* everything above stays */ importFormats={["csv", "xlsx"]} enableAddSource synonyms={{ columns: { inspectedOn: ["date"], action: ["notes"], }, }}/>The file lands as a second source beside the rows from the table, and the person works on both in one grid. Drop the two synonym lines and four of the six headers still reach a column, each header measured against the column id and the column title. Hive and Varroa match a title outright, and Queen and Brood are contained by one.
Hive hiveRef 100Varroa varroaCount 100Queen queenSeen 80Brood broodFrames 80Date inspectedOn 0Notes action 0A header needs 60 to be accepted. Neither Date nor inspectedOn contains the other, and nor do Notes and action, so both score zero and the two synonym entries name the pairs by hand. The values need nothing. Y and N reach Yes and No through the built-in synonym table, and so does TRUE. How to build CSV column mapping in React runs the same matcher over a different file, and builds the screen a person fixes it on.
The file carries no id, so every line in it is a new row, and the same handler inserts it. The import is one more way to fill the same dataset.
Each side keeps the rules it can explain
The integration is one table, six editor columns, two type parsers, one SELECT, three write statements, two callbacks and a version counter. You wrote the mapping between them, in the column aliases, the two parsers and one = 'Yes' on each write. Neither side ships a connector for the other.
PGlite owns the identity, the transaction, the files in IndexedDB and every rule that needs the whole table. The editor holds the rules it can explain in one cell, the hive-reference pattern the table never checks included. Updog Data Editor owns the screen where a person reads the rows, sorts them, filters them, fixes four of them and adds two more.
The boundary is loadData in one direction and onComplete in the other. One runs a SELECT and hands the rows over. The other reads two flags off each changed row and runs the statement they call for. The SQL on both sides is yours.
The same grid ships through the React CSV importer component or the web component build. The beekeeper walks the apiary again, and the table is where they left it.