Database schema to diagram, from the command line

A developer tool wants to show a project's database schema where the team can discuss it. mfschema login connects the developer's own MockFlow account in the browser; mfschema push schema.sql reads the CREATE TABLE statements and draws the tables, keys and relations as a database diagram the team can annotate and comment on.

A command line tool runs on people's computers, so it cannot keep a client secret. It is registered as a public client and proves each login with PKCE instead.

Download the code (Node.js 18 or later, no packages)

What it does

• login: opens MockFlow's consent page in the browser and receives the code, then stores the tokens in ~/.config/mfschema/tokens.json, readable by the developer only.

• push: parses the SQL file and creates a board with a database diagram: each table with its columns, primary and foreign keys, and a line for each relation.

• logout: revokes the tokens and deletes the file.

How it works

1. Register the app with This app cannot keep a secret ticked. It gets a client ID and no secret.

2. On login, make a code_verifier and send its SHA-256 as the code_challenge. Without a secret, MockFlow refuses a request that has no challenge.

3. Receive the code: on a loopback address while developing, or by paste once the app is published (below).

4. Trade the code for tokens with the code_verifier instead of a secret. Refreshing later also needs only the client ID.

5. Create the board and the item with the developer's access token.

Receiving the code, in development and once published

While the app is in development, its redirect can be a loopback address such as http://127.0.0.1:8765/callback; the CLI listens on that port and catches the code.

A published app's redirect must be https on your own domain, not localhost. The download includes callback.html: host it at your redirect URI, and it shows the person a code to paste into the terminal. The code is useless to anyone else, because the verifier that redeems it never leaves the person's computer.

Run the example

Register an app in the OAuth apps tab of the developer console: tick This app cannot keep a secret, use the redirect URI http://127.0.0.1:8765/callback, and the scopes user:read and boards:write. Then:

cp .env.example .env        # paste the client ID into .env
node mfschema.js login
node mfschema.js push schema.sql "Shop database"

The key code

const auth = newAuthRequest();   // state, verifier, challenge
openBrowser(authorizeUrl({ clientId, redirectUri, scope: 'user:read boards:write', state: auth.state, challenge: auth.challenge }));

// ...the code arrives on the loopback port, or is pasted
const tokens = await exchangeCode({ clientId, redirectUri, code, verifier: auth.verifier });   // no secret

await mf.createItem(board.id, {
  type: 'database_diagram',
  position: { x: 0, y: 0 },
  data: { title, class: 'GraphLinksModel', category: 'database', nodeDataArray: tables, linkDataArray: relations },
});

Adapt it

• Infrastructure: read Terraform or a cloud account and draw a cloud architecture diagram.

• APIs: read an OpenAPI file and draw its resources and how they relate.

• Desktop and mobile apps: the same public client and PKCE flow, with the platform's own way of receiving the redirect.

What you need

An OAuth app registered as one that cannot keep a secret, with the scopes user:read and boards:write.