webway

Run the tests

Flyway-style migrations for triad.js apps that bring their own database. The app connects from the browser to a MySQL or PostgreSQL database the user picks, through db-connector, with no server of ours in between; webway sets up and migrates that database. Connecting, logging in and when to migrate stay the app's own.

Each database is migrated on its own, with its own history: the app's, and one for each library that keeps a database of its own, such as db-auth. MyDay is the first app on it.

Status: an earlier version has migrated MyDay's database on MySQL 8; this one has only run against the tests' fakes.

Running locally

1. MySQL 8 or later, for example with Homebrew. Its root is the admin who migrates.

   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.

Getting started

1. Run MySQL and db-ws-relay on one host.

2. Describe the app's database: its name, readable and unique, such as yourapp_ and 16 random hex digits, and its migration files:

   var database = {
     database: 'yourapp_1a7d2145c1800e78',
     migrations: { mysql: ['migrations/V1__hello_world.js'] },
     params: { appUser: 'yourapp_1a7d2145c1800e78' }
   };
   

3. Write the app's first migration, migrations/V1__hello_world.js. It lets the account the app's pages connect with read the database's versions:

   webway.migrations.push(`
     SELECT 'Hello World!';
     GRANT EXECUTE ON PROCEDURE schema_history_versions TO :appUser@'%';
   `);
   

4. Check the database when a page loads, after loading webway, after triad.js:

   withFiles(['https://spa-mvc-pages.duckdns.org/webway/webway.js'], function () {
     webway.check(connection, database, function (err, state) {
       if (state === 'behind') askForAnAdmin();
     });
   });
   

5. Migrate as a database admin, with a connection that has multipleStatements: true:

   var connection = mysql.createConnection({ relay: 'ws://localhost:8080', user: 'root', password: password, multipleStatements: true });
   webway.migrate(connection, database, function (err, version) {
     connection.end();
   });
   

Calls

Each takes a db-connector connection and a database, { database, migrations, params }: its name (a schema's, on PostgreSQL), the paths of its migration files, relative to the page or absolute, by the server they're for, { mysql: [...] } or { postgresql: [...] }, or both, and optionally the values its migrations take. An app picks its server by the migrations it lists. A database with no migrations for the connection's server, such as MySQL's alone on a PostgreSQL server, is refused with an error before anything is sent, as is one whose migrations name no server.

Call
check(connection, database, callback(err, state, version))	When a page loads: how the database compares with these migrations, from their file names alone, without loading any of them
migrate(connection, database, callback(err, version))	As a database admin: creates the database and its history if they're missing, and runs every migration it doesn't have yet. Loads the runner, lib/migrate.js, on its first call

The connection is the app's own; webway doesn't end it, and names the database in every call, so the connection needs no default database. A database in the wrong shape is refused before anything is sent.

An app with several databases checks and migrates each, and when one database's migrations use another's objects, that one is migrated first. A library's database may be migrated elsewhere: db-auth's is migrated on its user admin page, so an app on db-auth checks it, and migrates only its own.

Checking

The page and its migrations are published together, so every version of a page knows the database it needs. check compares the database's schema history with the latest version among the migrations' file names:

state	The database	The page
current	Has every migration, and none newer	Runs
behind	Lacks some, doesn't exist yet, or can't be checked by this account	Asks for a database admin to migrate
ahead	Has migrations newer than these	Is out of date, and reloads
failed	Has a failed migration	Needs the database fixed by hand

version is the database's. A database that doesn't exist, has no history, or doesn't grant this account its versions counts as behind, since migrating fixes each.

Migrations

As in Flyway, a migration's version and description come from its file name, V<version>__<description>.js, with the V in either case. Each file pushes its SQL onto webway.migrations, as one template literal of statements that runs as a single multi-statement query, inside its database, so it never needs to name it.

sequenceDiagram
  participant App as App, in the browser
  participant W as webway
  participant DB as MySQL, through the relay
  App->>W: migrate(connection, database)
  W->>W: Loads lib/migrate.js, the first time
  W->>DB: CALL schema_history_read()
  DB-->>W: The applied migrations, or no database yet
  W->>DB: If new: CREATE DATABASE, the history, and USE it
  W->>W: Loads every pending file in one go
  loop In version order
    W->>DB: The migration's SQL
    W->>DB: CALL schema_history_add(…, success)
    W->>W: Removes the file from the page
  end
  W-->>App: version, or the first error

• A failure stops the run, and its error names the migration, such as V2 (add due dates): …. MySQL can't roll back DDL, so as in Flyway the failure is recorded, and it blocks further migrations until the database is fixed by hand.
• Nothing stays in memory: each file is removed from the page once it has run.

Params

A database's params are values its migrations take, which the driver binds and escapes, so migrations can use the app's values without building SQL from them: :name for a value, and ::name for a name, such as another database's. In migrations with params, : and ? outside quotes mark placeholders.

An app on db-auth grants its version check and its procedures to db-auth's users this way, through db-auth's database, as its pages check the database as the logged-in user:

var database = {
  database: 'yourapp_1a7d2145c1800e78',
  migrations: { mysql: ['migrations/V1__hello_world.js', 'migrations/V2__create_item_procedures.js'] },
  params: { authDatabase: dbAuth.schema.database }
};

webway.migrations.push(`
  CALL ::authDatabase.auth_grant_to_users(DATABASE(), 'schema_history_versions');
`);

webway.migrations.push(`
  CALL ::authDatabase.auth_grant_to_users(DATABASE(), 'items_list');
`);

Tests

test.html covers loading, the database's shape, creating the database and its history, ordering, failures, params, cleaning up and checking, with a fake MySQL in place of the database. Open it locally or online.

Stored procedures

The history is webway's, not the migrations': as Flyway does with its table, webway creates it in a database that has none, before the first migration, with three procedures. The runner's run with the caller's rights, so installed_by is the admin; the one pages call runs with the rights of the admin who migrated, so a caller needs no rights on the table, only EXECUTE on it, which the database's migrations grant.

Procedure	Called by
schema_history_read()	The runner, as the admin	version and success of each migration, in the order applied
schema_history_add(version, description, execution_time, success)	The runner, as the admin	Records a migration, with the next installed_rank
schema_history_versions()	Pages, through check, as the account the migrations grant it to	The same as schema_history_read()

Schema

erDiagram
  schema_history {
    int installed_rank PK
    int version UK
    varchar description
    varchar installed_by "the admin account"
    timestamp installed_on
    int execution_time "milliseconds"
    bool success
  }