PGlite - run Postgres everywhere

I recently gave a quick intro to PGlite at a local meetup (Cologne.js - come around, I'm a regular there). PGlite is a WASM-based Postgres database that you can use in different runtimes. It's based on a single user mode that not many people know about, but it's ideal for having a database right where the user is. What's really great is that it even supports popular Postgres extensions like PostGIS or pgvector.

I'd like to give you a similar introduction to show you what a great tool PGlite is.

How do you get it to work?

You can use it in any major JavaScript runtime, such as NodeJS, BUN or Deno. But you can even use PGlite in your browser. To do this, it uses the IndexedDB of the browser, which is a great client-side database for storing the application's data.

You can get the database package as a npm module called @electric-sql/pglite. ElectricSQL is the company behind PGlite, which builds on the work of Stas Kelvich. Their product is a sync tool for PGlite that can back up data in a remote database.

The package is just a npm module, so we can install it in our project with a simple npm install @electric-sql/pglite or deno install @electric-sql/pglite depending on your used runtime. The gzipped module is only 2.6 MB in size.

Once we've imported it into our code, we can use regular SQL to interact with the database. Let's take a look at an example from the PGlite docs.

import { PGlite } from '@electric-sql/pglite'

// this starts an in-memory database
const db = new PGlite()

await db.exec(`
  CREATE TABLE IF NOT EXISTS todo (
    id SERIAL PRIMARY KEY,
    task TEXT,
    done BOOLEAN DEFAULT false
  );
  INSERT INTO todo (task, done) VALUES ('Load PGlite', true);
  INSERT INTO todo (task, done) VALUES ('Create a table', true);
  INSERT INTO todo (task, done) VALUES ('Insert some data', true);
  INSERT INTO todo (task) VALUES ('Update a task');
`)

const ret = await db.query(`
  SELECT * from todo WHERE id = 1;
`)
console.log(ret.rows)
// [
//  {
//    id: 1,
//    task: 'Install PGlite from NPM',
//    done: false,
//  },
// ]

Another great feature is that we can subscribe to a query. With "live queries", we can get data every time the underlying and monitored table changes. To use it, we can import the live extension:

import { PGlite } from '@electric-sql/pglite'
import { live } from '@electric-sql/pglite/live'

const pg = await PGlite.create({
  extensions: {
    live,
  },
})

What use cases could this be put to?

This type of database setting could be useful for local first applications. Local First apps are great for building apps with excellent offline support and super-fast frontend reaction times. Since the data is stored locally, we have a lot less latency. The data can be synced in the background to make it available on more devices. To support such an app, we can use PGlite to host the app's data in the user's browser.

Another use case I can think of is having a database for a proof of concept. We can easily build a Typescript prototype without needing an external database. Just install the module, import it, and we're good to go. Since it's compatible with a regular Postgres instance, the change should be plug-and-play.

In data-heavy apps, we could use PGlite as a local data cache. That way, we don't have to fetch data from remote APIs every time we want to work with it.

Finally, we could host it in test suites or CI pipelines. That way, we wouldn't need a containerised database running in parallel. Instead, we could just host it inside our application while running tests to see them pass.

Conclusion

I hope that this introduction shows you what PGlite is capable of. I can really recommend playing around with it. An easy way is using Postgres.new which is built by Supabase. The site hosts a PGlite database in your browser and connects it to an AI to create, manipulate and visualize a browser based Postgres database.

Thank you for reading,
Niklas