pg-ws

Run the tests

A PostgreSQL driver for the browser. It speaks PostgreSQL's frontend/backend protocol over a WebSocket to db-ws-relay, which pipes the bytes to a PostgreSQL server. The relay has a fixed target, so the client only chooses which relay to connect to. PostgreSQL's own authentication applies as usual, and nothing between the browser and PostgreSQL parses the protocol.

The API is node-postgres's client, with callbacks only, ported from it. It is the PostgreSQL counterpart of mysql-ws, and works the same way.

Status: new; tested against a fake server, not yet against PostgreSQL itself.

Running locally

1. PostgreSQL, for example with Homebrew:

   brew install postgresql@16
   brew services start postgresql@16
   

2. The relay, on the same machine, forwarding to PostgreSQL's port:

   git clone https://codeberg.org/joexo/db-ws-relay.git
   cd db-ws-relay
   npm install
   npm start -- --db-port 5432
   

3. The tests: open test.html; they need neither PostgreSQL nor the relay. They load triad.js and testrunner from folders beside this one, as online, so clone those next to it and serve the folder that holds all three.

Getting started

Load triad.js first, then pg-ws.js with withFiles. It defines the global pg, and loads nothing else: each call loads the files it needs the first time it's made, then starts. Every call reports through its callback, and the next one starts from it:

<script src="https://spa-mvc-pages.duckdns.org/triad.js/triad.js"></script>
<script>
  triad.withFiles(['https://spa-mvc-pages.duckdns.org/pg-ws/pg-ws.js'], function () {
    pg.createClient(
      {
        relay: 'ws://localhost:8080',
        user: 'alice',
        password: 'secret',
        database: 'app'
      },
      function (err, client) {
        client.connect(function (err) {
          if (err && err.code === '28P01') {
            return showLogin();
          }
          client.query('SELECT $1::int + $2::int AS sum', [1, 2], function (err, result) {
            console.log(err || result.rows[0].sum);
            client.end();
          });
        });
      }
    );
  });
</script>

Calls can be made at once, and they all run: the server takes one command at a time, so the others wait for it, in no promised order. When order matters, as in a transaction, start each command from the callback of the one before. A call made before connect connects first; call connect to learn whether the login worked. A query without values loads the query and the type parsers; one with values loads the extended protocol too; an md5 login loads MD5 only when the server asks for it.

How it works

sequenceDiagram
  participant App as App, in the browser
  participant WS as pg-ws
  participant Relay as db-ws-relay
  participant DB as PostgreSQL
  App->>WS: createClient(relay, user, password)
  App->>WS: connect()
  WS->>Relay: Opens a WebSocket
  Relay->>DB: Opens a TCP connection
  Relay-->>WS: Text frame, event connect
  WS->>DB: StartupMessage, with the user and database
  DB-->>WS: SCRAM-SHA-256, md5 or cleartext password request
  WS->>DB: The proof, or the password's hash
  DB-->>WS: AuthenticationOk, parameters, ReadyForQuery
  App->>WS: query(text, values)
  WS->>DB: Parse, Bind, Describe, Execute, Sync (or Query, without values)
  DB-->>WS: RowDescription, DataRows, CommandComplete, ReadyForQuery
  WS-->>App: callback(err, result)
  App->>WS: end()
  WS->>DB: Terminate

Binary frames carry the protocol's bytes both ways, unchanged. The relay's text frames report its own TCP connection, {"event":"connect"}, or an error, which the driver turns back into an Error with its code.

Calls

Call
pg.createClient(options, callback(err, client))	A client, through the relay in options.relay, not yet connected
pg.escapeIdentifier(text, callback(err, escaped)), pg.escapeLiteral(text, callback(err, escaped))	As in node-postgres
connect(callback(err))	Checks the options, opens the client and logs in; calls back once logged in, or with the error
query(text, values, callback(err, result))	text may also be { text, values, rowMode }. result is { command, rowCount, oid, fields, rows }, an array of them for a query of several statements; rowMode: 'array' gives rows as arrays
escapeIdentifier(text, callback(err, escaped)), escapeLiteral(text, callback(err, escaped))
end(callback())	Terminates once the calls made before it have started; commands made after it fail
close()	Closes the WebSocket at once; the running and waiting commands never finish, so they don't call back
on('error', handler), on('end', handler), on('notice', handler), on('notification', handler)	A fatal error with no command to report it to, the end of the connection, the server's notices, and NOTIFY's notifications

A client also has processID, secretKey, parameters (what the server reports, such as server_version) and transactionStatus: 'I' idle, 'T' in a transaction, 'E' in a failed one.

Options

relay is pg-ws's own; the others are node-postgres's.

Option
relay	The relay's URL, ws:// or wss://; ws://127.0.0.1:8080 by default
user, password, database	The login, and the database, the user's name by default
connectionTimeoutMillis	Milliseconds to log in in; no limit by default
application_name, options, statement_timeout, lock_timeout, idle_in_transaction_session_timeout	Sent to the server at startup
scramMaxIterations	The most SCRAM iterations to accept, 100000 by default, 0 for any

An unknown option, or one of node-postgres's left out below, fails connect with a TypeError.

Values

Values come back as node-postgres gives them: integers and floats as numbers, int8 and numeric as strings (they may not fit a number), booleans, json and jsonb parsed, dates and timestamps as Dates, interval as { years, months, days, hours, minutes, seconds, milliseconds }, bytea as a Uint8Array, and arrays of these as arrays. Other types stay strings.

Values go as node-postgres sends them: null and undefined as NULL, a Uint8Array as binary, a Date in local time with its offset, an array as an array literal, an object with toPostgres() as what it gives, and other objects as JSON.

Errors

A server error has PostgreSQL's fields: code, its SQLSTATE such as 23505 (unique violation) or 28P01 (wrong password), severity, detail, hint, position, schema, table, column, constraint and the rest, as in node-postgres. The driver adds RELAY_UNREACHABLE when the WebSocket can't open, CONNECTION_LOST when the connection ends unexpectedly, ETIMEDOUT for connectionTimeoutMillis and CLIENT_CLOSED for a command after end, and passes on the relay's, such as ECONNREFUSED when PostgreSQL isn't running. A fatal error sets err.fatal, and the client can't be used again. A file of the driver's that fails to load fails the call that needed it, with an Error that names the file.

Supported

• Simple queries, several statements in one, and queries with values by the extended protocol
• SCRAM-SHA-256, md5 and cleartext logins
• Notices, LISTEN/NOTIFY notifications, transactions

Not supported

Connection strings, host, port and ssl (the relay decides the target; use wss:// to the relay), pools, COPY (a COPY … FROM STDIN fails), cursors and named prepared statements, binary results, custom type parsers, cancelling a query, and SCRAM channel binding.

Tests

test.html runs tests ported from node-postgres's, pg-protocol's and pg-types', and pg-ws's own, against a fake server behind a fake WebSocket, so no database is needed. Open it locally or online.