Show HN: PGlite – in-browser WASM Postgres with pgvector and live sync (pglite.dev)

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.

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

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.

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)

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!

Not sure about Prisma but it works with Drizzle ORM.

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

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.

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.

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

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

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

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.

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.

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.