bun.sh/test/js/third_party/pg-gateway/pglite.test.ts

import { PGlite } from "@electric-sql/pglite";
import { SQL, randomUUIDv7 } from "bun";
import { expect, test } from "bun:test";
import { once } from "events";
import net, { AddressInfo } from "node:net";
import { fromNodeSocket } from "pg-gateway/node";

test("pglite should be able to query using pg-gateway and Bun.SQL", async () => {
  const name = "test_" + randomUUIDv7("hex").replaceAll("-", "");
  const dataDir = `memory://${name}`;
  const db = new PGlite(dataDir);

  // Wait for the database to initialize
  await db.waitReady;

  // Create a simple test table
  await db.exec(`
    CREATE TABLE IF NOT EXISTS test_table (
      id SERIAL PRIMARY KEY,
      name TEXT NOT NULL
    );
    
    INSERT INTO test_table (name) VALUES ('Test 1'), ('Test 2'), ('Test 3');
  `);

  // Create a simple server using pg-gateway
  const server = net.createServer(async socket => {
    await fromNodeSocket(socket, {
      serverVersion: "16.3",
      auth: {
        method: "trust",
      },
      async onStartup() {
        // Wait for PGlite to be ready before further processing
        await db.waitReady;
      },
      async onMessage(data, { isAuthenticated }: { isAuthenticated: boolean }) {
        // Only forward messages to PGlite after authentication
        if (!isAuthenticated) {
          return;
        }

        return await db.execProtocolRaw(data);
      },
    });
  });

  // Start listening
  await once(server.listen(0), "listening");

  const port = (server.address() as AddressInfo).port;

  await using sql = new SQL({
    hostname: "localhost",
    port: port,
    database: name,
    max: 1,
  });

  expect(sql.options.hostname).toBe("localhost");
  expect(sql.options.port).toBe(port);
  expect(sql.options.database).toBe(name);
  expect(sql.options.max).toBe(1);

  {
    // prepared statement without parameters
    const result = await sql`SELECT * FROM test_table WHERE id = 1`;
    expect(result).toBeDefined();
    expect(result.length).toBe(1);
    expect(result[0]).toEqual({ id: 1, name: "Test 1" });
  }

  {
    // using prepared statement
    const result = await sql`SELECT * FROM test_table WHERE id = ${1}`;
    expect(result).toBeDefined();
    expect(result.length).toBe(1);
    expect(result[0]).toEqual({ id: 1, name: "Test 1" });
  }

  {
    // using simple query
    const result = await sql`SELECT * FROM test_table WHERE id = 1`.simple();
    expect(result).toBeDefined();
    expect(result.length).toBe(1);
    expect(result[0]).toEqual({ id: 1, name: "Test 1" });
  }

  {
    // using unsafe with parameters
    const result = await sql.unsafe("SELECT * FROM test_table WHERE id = $1", [1]);
    expect(result).toBeDefined();
    expect(result.length).toBe(1);
    expect(result[0]).toEqual({ id: 1, name: "Test 1" });
  }
});