mysql-ws

Run the tests

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

The API is a callback-only subset of mysql2, ported from it. webway and db-auth run on it, and MyDay is the first app.

Status: used by MyDay and db-auth's user admin page against MySQL 8.

Running locally

1. MySQL 8 or later, for example with Homebrew:

   brew install mysql
   brew services start mysql
   mysql_secure_installation
   

2. The relay, on the same machine. It listens on ws://localhost:8080 and forwards to MySQL on 127.0.0.1:3306:

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

3. The tests: open test.html; they need neither MySQL 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 mysql-ws.js with withFiles. It defines the global mysql, 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/mysql-ws/mysql-ws.js'], function () {
    mysql.createConnection(
      {
        relay: 'ws://localhost:8080',
        user: 'alice',
        password: 'secret',
        database: 'app'
      },
      function (err, connection) {
        connection.connect(function (err) {
          if (err && err.code === 'ER_ACCESS_DENIED_ERROR') {
            return showLogin();
          }
          connection.query('SELECT ? + ? AS sum', [1, 2], function (err, rows) {
            console.log(err || rows[0].sum);
            connection.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. An app that connects and runs queries loads about 60% of the driver's code; prepared statements, changeUser, ping, reset, the rarer auth plugins and the names of errors load only when used.

How it works

sequenceDiagram
  participant App as App, in the browser
  participant WS as mysql-ws
  participant Relay as db-ws-relay
  participant DB as MySQL
  App->>WS: createConnection(relay, user, password)
  App->>WS: connect()
  WS->>Relay: Opens a WebSocket
  Relay->>DB: Opens a TCP connection
  Relay-->>WS: Text frame, event connect
  DB-->>WS: Handshake, with the server's scramble
  WS->>DB: Handshake response, with the user and a password token
  DB-->>WS: Auth switch or more auth data, if the plugin needs it
  DB-->>WS: OK, or access denied
  App->>WS: query(sql, values)
  WS->>DB: COM_QUERY
  DB-->>WS: Result sets
  WS-->>App: callback(err, rows, fields)
  App->>WS: end()
  WS->>DB: COM_QUIT

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
mysql.createConnection(options, callback(err, connection))	A connection, through the relay in options.relay, not yet connected
mysql.escape, escapeId, format, raw	As in mysql2, with callback(err, result) last
connect(callback(err))	Checks the options, opens the connection and logs in; calls back once logged in, or with the error
query(sql, values, callback(err, rows, fields))	The text protocol. sql may also be { sql, namedPlaceholders, rowsAsArray, typeCast }
execute(sql, values, callback(err, rows, fields))	A prepared statement, cached per connection
prepare(sql, callback(err, statement)), unprepare(sql, callback(err))	A statement to run with statement.execute(values, callback) and end with statement.close(callback(err)); unprepare closes the cached one for sql
changeUser({ user, password, database }, callback(err))	Logs in again as another user, on the same connection
beginTransaction, commit, rollback	Each with callback(err)
ping(callback(err)), reset(callback(err))
format(sql, values, callback(err, sql)), escape(value, callback(err, escaped)), escapeId(value, callback(err, escaped))	Under the connection's options
end(callback(err))	Quits 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)	A fatal error with no command to report it to, and the end of the connection

Options

relay is mysql-ws's own; the others are mysql2's.

Option
relay	The relay's URL, ws:// or wss://; ws://127.0.0.1:8080 by default
user, password, database	The MySQL login, and the database to start in
multipleStatements	Lets one query hold several statements, as migrations need
namedPlaceholders	:name placeholders with an object of values, for every query
connectTimeout	Milliseconds to log in in, 10000 by default
charset, timezone, dateStrings, typeCast, supportBigNumbers, bigNumberStrings, decimalNumbers, jsonStrings, rowsAsArray, nestTables	How values come back
authPlugins, enableCleartextPlugin, password2, password3	Authentication

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

Examples

Named placeholders, for one query:

connection.query({ sql: 'SELECT * FROM items WHERE owner = :owner', namedPlaceholders: true }, { owner: 'SleepyOtter' }, function (err, rows) {});

A stored procedure, whose result sets come first and a header last:

connection.query('CALL register()', function (err, results) {
  var rows = results[0];
});

A prepared statement:

connection.execute('SELECT * FROM items WHERE id = ?', [7], function (err, rows) {});

Errors carry MySQL's code and number, such as ER_ACCESS_DENIED_ERROR (1045), ER_DBACCESS_DENIED_ERROR (1044) or ER_BAD_DB_ERROR (1049). The driver adds RELAY_UNREACHABLE when the WebSocket can't open, and passes on the relay's, such as ECONNREFUSED when MySQL isn't running. A fatal error sets err.fatal, and the connection can't be used again.

The names come in groups, loaded before the callback the first time an error from a group arrives; if that file can't load, err.code is UNKNOWN_ERROR, and err.errno and the message are still there. A file of the driver's that fails to load fails the call that needed it, with an Error that names the file.

Supported

• Text protocol (query) and prepared statements (execute, prepare)
• Multiple result sets, so CALL on stored procedures works
• Auth plugins: caching_sha2_password, sha256_password, mysql_native_password and mysql_clear_password, with auth switches, also on servers with multi-factor authentication
• changeUser, reset, ping, transactions, named placeholders

Not supported

Connection URL strings, host, port and uri (the relay decides the target), TLS options (ssl, use wss:// to the relay), pools, compression and LOCAL INFILE.

Tests

test.html runs tests ported from mysql2's unit tests, and mysql-ws's own, against a fake server behind a fake WebSocket, so no database is needed. Open it locally or online.