Show HN: PGlite – in-browser WASM Postgres with pgvector and live sync

Getting PGlite working with other languages is very high on my list. We are working on two approaches:

- a WASI build with a lower level api that users can wrap with a higher level api.

- a "libpglite" that can be linked to by any native language. The WASI build is likely a WASM build of this.

Most languages already have a Postgres wire protocol implementation and so wrapping a low level API that reads/emits this is relatively easy - it's what the JS side of PGlite does.

As a workaround, maybe something like this could work:

db.js:

    const { PGlite } = require('@electric-sql/pglite');
    const db = new PGlite();

    async function execSQL(sql) {
        await db.exec(sql);
        return { success: true };
    }

    async function querySQL(sql, params) {
        const ret = await db.query(sql, params);
        return ret.rows;
    }

    // Command-line interface logic
    const action = process.argv[2];
    const sql = process.argv[3];
    const params = process.argv.slice(4);

    if (action === 'exec') {
        execSQL(sql).then(result => {
            console.log(JSON.stringify(result));
            process.exit();
        });
    } else if (action === 'query') {
        querySQL(sql, params).then(result => {
            console.log(JSON.stringify(result));
            process.exit();
        });
    }

    import subprocess
    import json

    def execute_sql(sql: str) -> dict:
        result = subprocess.run(
            ['node', 'db.js', 'exec', sql],
            stdout=subprocess.PIPE,
            text=True
        )
        return json.loads(result.stdout)

    def query_sql(sql: str, params: list) -> list:
        result = subprocess.run(
            ['node', 'db.js', 'query', sql] + params,
            stdout=subprocess.PIPE,
            text=True
        )
        return json.loads(result.stdout)

    # Example Usage
    create_table_sql = """
    CREATE TABLE IF NOT EXISTS todo (
        id SERIAL PRIMARY KEY,
        task TEXT,
        done BOOLEAN DEFAULT false
    );
    """
    execute_sql(create_table_sql)

    query_result = query_sql("SELECT * FROM todo WHERE id = $1", ["1"])
    print(query_result)

Definitely interesting... maybe someone more familiar with the licenses involved can chime in on how this might impact a project/solution and compare/contrast to something like embedded FirebirdSQL.

For example, can you use this in/with a commercial project without releasing source? If you embed this, will you need to change your license to match pg/fb?

aside: Mostly asking out of curiosity if anyone knows already, if I actually had an immediate need, would be reviewing this of my own accord. I often dislike a lot of the simplicity behind SQLite in very similar ways as to things I don't care for with MySQL/MariaDB.

I'd also love to try PGlite in Python.

While reading this thread, I realized that you could already access PGlite in Pyodide. Pyodide is a Wasm port of CPython. It can work with JavaScript objects through proxies.

Here is a demo. I have run the code in current Node.js 18, Deno 1, and Bun 1.

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

  globalThis.db = new PGlite();
  let pyodide = await loadPyodide();

  console.log(
    await pyodide.runPythonAsync(`
      import js

      ret = await js.db.query("""
          SELECT 'Hello, world!'
          AS "greeting";
      """)

      ret.rows[0].greeting
    `),
  );

  > npm init -y
  > npm install '@electric-sql/pglite@==0.2.0'
  > npm install 'pyodide@==0.26.2'
  > node index.mjs
  prerun(C-node) worker= false
  Running in main thread, faking onCustomMessage
  Hello, world!

Weird question: Since you mention 'bare-metal' and this is "enables you to run Postgres in the browser"

UH, could you do:

bare_metal --> HeadlessGPU-Chrome --> PGLite with whatever orchesrtration and you have a an automatable 'browser' that can scrape/store/logic/log/be-config'd from/lookup directly to your browserDB

and maybe run a fastAPI ontop of such that you have a full stack, baremetal, headless, browserDB/fastAPI/endpoint processing doohicky?

Or does this sound dumb?

Because this is exactly what I actually need and am attempting to create - but its beyond my capabilities in any functional/elegant implementation...

https://www.browserless.io/blog/browserless-gpu-instances

https://github.com/dhamaniasad/HeadlessBrowsers

And you can't have your own docker on bare metal?

Corpo beancounters want us to move off docker on EC2 onto bare metal

There is a very good reason for it. EC2 is way way overpriced.

Definitely interesting, though I often just use docker compose for dev dependencies, this is definitely a cool alternative.

I would also love to use it this way in Go. Currently we dump SQLite compatible schema to use for SQLite powered integration tests even though we use postgres when live. It allows us to have zero dependency sub-second integration tests using a real database. Being able to use postgres would be even better, allowing us to test functionality requiring PG specific functionality like full text search.

I see unit and integration testing as a massive opportunity for PGlite. I know of projects (such as Drizzle ORM) that are already making good use of it.

The team at Supabase have built pg-gateway (https://github.com/supabase-community/pg-gateway) that lets you connect to PGlite from any Postgres client. That's absolutely a way you could use it for CI.

One thing I would love to explore (or maybe commenting on it here will inspire someone else ;-) ) is a copy on write page level VFS for PGLite. I could imagine starting and loading a PGlite with a dataset and then instantly forking a database for each test. Not complexities with devcontainers or using Postgres template databases. Sub ms forks of a database for each test.

Huh, can you do that? I can't google anything just now, got a link to some docs about js window functions in sqlite?! Sounds very powerful

Yes, they should all work.

We save the query as a view and then introspect it to see what tables it depends on. It then uses pg_notify to watch for changes.

The plan is to integrate pg_ivm (https://github.com/sraoss/pg_ivm) to make it even more efficient.

IIRC Sqlite can exist as flat file and can be backed up. Will this work the same? And will it allow multiple writers?

Exactly, all your favourite PG types, plus any that come with extensions such as vectors with pgvector.

We are working on PostGIS to, which will bring the geo types to PGlite.

WASM is inherently slower than native, but it does very well!

We have some micro-benchmarks here that have some baseline native numbers to compare to: https://pglite.dev/benchmarks

Much of the complexity with WASM in the browser actually comes down to the performance of the underlying VFS for storage. When running in-memory or using the OPFS VFSs both PGlite and the WASM SQLite builds are very performant, absolutely capable of handing the embedded use case.

That means getting those 2 million patients on every device... Really bad choice for medical software.

pg-gateway from Supabase does exactly this, you can start a PGlite and then connect to it from any standard PG client: https://github.com/supabase-community/pg-gateway

Curious, what's the benefit vs running real PG in a container for this specific use case?

I confirm it works perfectly in CI and locally. I'm using it in CI and locally.

Not sure about Prisma but it works with Drizzle ORM.

Yours and Roys work on WASM SQLite VFSs has directly influenced my work on this. Thank you!

It's planned.

We want to extract the core changes to Postgres that we had to make and create a "libpglite" C lib that can be linked to from any language/platform.

Support important for React Native, as it doesn't have WASM support.

Hey, yes, PGlite and Electric sync are separate projects, but we are very much building PGlite as a sync target for Electric.

The changes we are making with Electric is towards a more loosely coupled stack, rather than the tightly integrated stack we had before. PGlite is one possible client store for Electric sync, and fulfils the use case where you want to have a full SQL database on the client along with (potentially) the same schema on client and server.

The name is (obviously) a nod to SQLite, which goes without saying is an incredible database. We went with the small "l" in the name to add a slight differentiator and a nod towards "light", and we are a light weight Postgres.

Ah, screen shot is out of date. We are on v16.3.

I'd also love to know this!

Well, Sqlite is battle-tested to hell and back... and with PGlite I managed to get a `memory access out of bounds` in less than 3min of playing around. So perhaps maybe not prod-ready yet, even if it's bigger brother is one of the most solid DBs. (To be fair I've also seen & experienced issues with SQLite - no software is bug-free.)

Posting the issue to GitHub would be great!

It runs on PGlite. https://supabase.com/blog/postgres-new#how-it-works

Thanks, fixed!

Ah, you beat me to it, yes those benchmarks give you a good overview of baseline performance. But it'a a little nuanced as the underlying VFS performance is very significant.

Both PGlite and SQLite in the browser is incredible. The latter is a little lighter weight and more mature, but PGlite brings with it all the type support and extensions that you love with Postgres. It comes down to what works best for your project.

Answered here https://news.ycombinator.com/item?id=41224295

You might remember an earlier WASM build[1] that was around ~30MB. The Electric team [2] have gone one step further and created a complete build of Postgres that’s under 3MB.

Their implementation is technically interesting. Postgres is normally multi-process - each client connection is handed to a child process by the postmaster process. In WASM there’s limited/no support for process forking and threads. Fortunately, Postgres has a relatively unknown built-in “single user mode” [3] primarily designed for bootstrapping a new database and disaster recovery. Single-user mode only supports a minimal cancel REPL, so PGlite adds wire-protocol support which enables parametrised queries etc.

You can use pg-gateway from supabase (https://github.com/supabase-community/pg-gateway) to start a PGlite and connect form any standard PG client.

Support for other languages is high on my list, the plan is to build a "libpglite" as a native C lib and a WASI build that can run in any WASM runtime.

Looks like you configure it with the connection string, so assuming you can just detect whether OPFS is available or swap based on the browser you should be fine.

There are two options here:

- Build a Postgres at-rest encryption extensions as WASM, that should "just work" as long as you can build the extension.

- Create an encrypted VFS for PGlite. This is doable now, but we haven't yet documents the VFS API as we want to make it a little cleaner.

But yes, it's 100% possible to build an at-rest encryption scheme for PGlite.

Yes, we can persistent to the normal filesystem with Node and Bun, and to IndexedDB and OPFS in the browser: https://pglite.dev/docs/filesystems