Thanks to WebAssembly we can now enjoy PostgreSQL inside the browser so that we can build reactive, realtime, local-first apps directly on Postgres. PGLite is about to make this even easier.

Until recently, the only way to run PostgreSQL in a browser was through spanning a VM, as we examined in "Running PostgreSQL Inside Your Browser":

Crunchy Data, by building a v86 virtual machine based on an Alpine Linux image and installing Postgres on it, they could run it inside the browser because v86 emulates an x86-compatible CPU and hardware by translating machine code to WebAssembly at runtime.

However there's two limitations with that approach; there's no Internet access from inside the VM and there's no data persistence, so changes are lost when leaving or refreshing the page.

That latter disadvantage is one Cruncy Data actually took advantage of, building on it to create ephemeral playgrounds that let you practice your SQL inside your browser and then just let go when finished.

But this is about to change thanks to PGlite. PGlite is a WASM Postgres build packaged into a TypeScript client library that enables you to run Postgres in the browser with no need to install any other dependencies. That is, Postgres stepping into SQLite's shoes, acting as a library that can be called by code with the added bonus of being able to persist to storage in the file system (Node/Bun) or indexedDB (Browser).

Also since PGLite is based on ElectricSQL, it can sync with a server on demand, as such oferring the ability to making truly reactive and local first applications too.That sole property makes PGLite part of the data sovereignty movement, which goes something like this: "Unless you own the data or it doesn't reside on your own devices then it's not yours"

To clearly get used to the idea of Postgres-as-a-library that we can invoke and use from Javascript code, we can do:

import { PGlite } from "@electric-sql/pglite";

const db = new PGlite();
await db.query("select 'Hello world' as message;");

You can now run your SQL queries on Postgres like you used to in SQLite.

console.log("Creating table...");
await pg.query(`
CREATE TABLE IF NOT EXISTS test (
id SERIAL PRIMARY KEY,
name TEXT
); `);

console.log("Inserting data...");
await pg.query("INSERT INTO test (name) VALUES ('test');");

console.log("Selecting data...");
const res = await pg.query(`
SELECT * FROM test;`);

This makes PGLite useful in a range of different scenarios:

• It can be used when offline and syncing to a remote service when going online, or when continuous data connectivity is not guaranteed - don't assume that there's a Cloud always on the backend
• It can be used as a in-memory database to test code in place of SQLite. This also forgoes the incompatibilities when doing the testing using SQLite and then trying to load the SQLite database into PostgreSQL.These incompatibilities are summed up as:
• Typing Differences
  SQLite and Postgres use different types.
• Identifier Quoting
  Mismatches in some cases between how PostgresSQL and Sqlite quote their objects.
• Forgiving SQL Parsing
  SQLite is more lax with syntax errors in some SQL statements, while Postgres is not.

The other advantage of PGLite in contrast to spawning a VM is the small size; it's only 3.7mb gzipped, where the VM version 30+ MB.

All good but there's some limitations too in contrast to a fully fledged PostgreSQL instance. As Postgres typically operates using a process forking model, whenever a client initiates a connection, a new process is forked to manage that connection.

However, programs compiled with Emscripten - a C to WebAssembly (WASM) compiler - cannot fork new processes, and as such operate strictly in a single-process mode. As a result, PostgreSQL cannot be directly compiled to WASM for conventional operation.

Fortunately, Postgres includes a "single user mode" primarily intended for command-line usage during bootstrapping and recovery procedures. Building upon this capability, PGlite introduces a input/output pathway that facilitates interaction with PostgreSQL when it is compiled to WASM within a JavaScript environment.

Plus PGLite can't run extensions, yet(?). Maybe a bit too much to ask, but imagine running PostGIS on your portable web based Postgres and your own data.

In summary, PGLite is the latest addition to the Postgres-for-everything movement and is about bringing PostgresSQL to the client side!

More Information

PGLite

Related Articles

Running PostgreSQL Inside Your Browser

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info