External databases

Overview

A DMS project can show pages over a database it does not own, such as a client's PostgreSQL, MongoDB or RethinkDB database. The engine's module (@antelopejs/postgres, @antelopejs/mongodb or @antelopejs/rethinkdb) reads and writes it live, in external mode. The builder takes such a database in four operations:

1. ConfigureProject wires the database into antelope.config.ts. It adds the provider module, a local module for the tables, the builder's catalog connection, and the DMS module's pin to the project's own database.
2. AnalyseDatabase reads the catalog and proposes a mapping by fixed rules. It writes nothing.
3. A person in the builder's Database workspace, or an assistant through these operations, settles the proposal.
4. MapResources writes table classes, controllers and TableView pages into the module bound to the database.

The builder reaches the database through @antelopejs/interface-database-catalog, a connection declared on its own module entry. The builder never connects with credentials of its own.

Wire the project

ConfigureProject(steps, opts?) applies its steps to antelope.config.ts in order, as one edit. It validates every step before it writes anything, and a refused step fails the call with invalid_config and writes nothing. With { dryRun: true }, it answers the changes it would make as OpResult.changes, so a caller can show the diff before the write. The configuration must be a defineConfig({ … }) or plain object literal with a literal modules.

env maps a config path to a variable name, never to a value. Each variable is added to envOverrides, and to .env.example when the project has one. A variable that already supplies another path is refused.

The provider's entry depends on its engine:

disabledExports stops core from offering the external instance's raw client. A module that needs the DMS's own MongoDB or RethinkDB client never gets the client's database instead.

Replace <version> in the example with the range of the provider package to install. The Database workspace fills in a default range under Advanced settings, in the Package version field.

import { ConfigureProject } from "@antelopejs/interface-dms-builder";

const catalog = "@antelopejs/interface-database-catalog";

const result = await ConfigureProject([
  {
    op: "AddModule",
    id: "postgres-erp",
    entry: {
      source: { type: "package", package: "@antelopejs/postgres", version: "<version>" },
      config: { mode: "external", statementTimeoutMs: 30000 },
      env: { url: "ERP_DATABASE_URL" },
      exportPriority: { "@antelopejs/interface-database": -1 },
    },
  },
  {
    op: "CreateLocalModule",
    id: "erp-tables",
    path: "modules/erp-tables",
    connection: "erp",
    entry: {
      importOverrides: [{ interface: "@antelopejs/interface-database", source: "postgres-erp" }],
    },
  },
  {
    op: "ConfigureModule",
    id: "dms",
    entry: { importOverrides: [{ interface: "@antelopejs/interface-database", source: "mongodb" }] },
  },
  {
    op: "ConfigureModule",
    id: "dms-builder",
    entry: { importOverrides: [{ interface: catalog, source: "postgres-erp", id: "erp" }] },
  },
]);
if (!result.ok) console.error(result.error);

• The provider's exportPriority of -1 keeps the project's own database the default for every module that does not pin interface-database.
• The tables module pins interface-database to the provider, and it uses the DMS. Core passes that pin on to the DMS, which every other module reaches with the default. The DMS therefore pins the project's own database to settle it. A DMS that already pins interface-database needs nothing more.
• When the project loads dms-ai, the Database workspace gives it the same catalog connection as dms-builder.

The connection exists once the project restarts with the variable set.

Remove a connection

In the Database workspace, the Remove <connection> button takes a connection out of the configuration with RemoveConnection for its catalog connections and RemoveModule for its tables module. The tables module's folder is deleted with it. The database itself is untouched.

The tables module is the one whose dmsBuilder.database record names the connection. CreateLocalModule writes that record from connection. The single-operation CreateLocalModule takes it in its options. A module that reads through the provider without such a record is never removed or deleted.

The provider is removed too, with the variables that supply its configuration in envOverrides, .env and .env.example, only when it was added for this connection alone. That holds when its exportPriority for @antelopejs/interface-database is below the project's own database, and no module or connection that the removal leaves names it. Otherwise the provider stays, since it may be the project's own database.

A removal is all or nothing. The workspace moves the folders aside and edits .env first, and puts both back when the configuration cannot be written.

Analyse a database

AnalyseDatabase({ connection, module, schemas? }) starts a job and answers it at once, because profiling a database takes a while. GetAnalysis(id) answers the job's progress while it runs, then its proposal or its error.

import { AnalyseDatabase, GetAnalysis } from "@antelopejs/interface-dms-builder";

const started = await AnalyseDatabase({ connection: "erp", module: "erp-tables" });
if (started.ok) {
  const state = await GetAnalysis(started.data.id);
  if (state.ok) {
    console.log(state.data.status); // "running", "done" or "failed"
  }
}

• connection must be a catalog connection on the dms-builder entry, and module a local module other than the project's own. Otherwise the call fails with invalid_config.
• One job runs per connection. Starting another while one runs answers the running one.
• A finished job is kept until the next one on its connection, or until a restart.
• Relations that the module has mapped, or recorded as excluded, are not proposed again.
• When more than 200 relations are left to profile and the call passed no schemas, the proposal holds a single schemas question instead. Run the analysis again with schemas.

Read a proposal

A MappingProposal is a MapResources input in which every choice says where it came from:

interface Proposed<T> {
  value: T;
  rule: string;            // "R6", "L1", "F5"…
  kind: "fact" | "default";
}

A fact follows from the catalog with no judgement. A default is a judgement to check.

The relation rules apply in the order R1, R4, R3, R2, then R5 to R9. The first rule that applies decides.

A badge's tone comes from its value. paid, active, done, completed, shipped, delivered and approved are success. pending, draft, processing, waiting and review are warning. failed, cancelled, canceled, refunded, rejected, error and blocked are error. new and open are info. Other values take primary, secondary, info and neutral in turn. A price takes the currency the database states, from an ISO 4217 code in the column's comment, else from a sibling currency column that holds one code in every row. When the database states none, it uses the DataType's default currency.

Each field also carries the declarations its column accepts, and its profile's aggregates: traits, nulls, distinct values, range and categories. A profile never holds a row.

Each resource also carries its rows, counted by the profile or estimated, when either is known.

The open list holds what the rules could not decide. Each item holds its options and what the proposal holds meanwhile:

• A label item offers every column that a field reads, the key among them, except columns named like a secret.
• A reference item offers a plain column, the target its values were found in, and every TableView whose single-column key accepts the column.
• A purpose item carries the resource it would map if the answer is to map it. Its own open holds the questions that resource leaves, which apply only once the answer maps it.
• A field-type item asks which declaration reads a field of several native types.

A document database declares no field types, so its catalog reads them from a sample. When no type covers enough of a field's values (int 60% | string 40%), the field is left out and a field-type item asks which declaration reads it. Its options are a declaration per type seen, and leaving the field out. The _id of a collection without a key is never declared, so it is left out and asks nothing. MapResources accepts the declaration the answer names. MongoDB and RethinkDB declare no references either, so F5 finds them all.

GetMapping(connection, module) reads the relations a module maps back into the proposal's shape, with every choice citing the rule mapped, so a caller can change them and map again.

Map relations

MapResources(input) reads the descriptions of the relations through the builder's connection, checks every choice against them, and writes in one typechecked transaction. It then creates the connection's category, pages.<connection>, and the TableView pages.

import { MapResources } from "@antelopejs/interface-dms-builder";

const result = await MapResources({
  module: "erp-tables",
  connection: "erp",
  resources: [
    {
      relation: "erp.customers",
      label: "name",
      title: "Customers",
      tableView: true,
      writable: false,
      fields: [{ column: "name", searchable: true }, { column: "segment", filterable: true }],
    },
  ],
  excluded: [{ relation: "erp.schema_migrations", reason: "a framework's or a migration tool's table" }],
});
if (!result.ok) console.error(result.error);

• Names are the database's own. _id is declared only where the relation has a key, and nothing creates or alters a table. A MongoDB collection's key is _id itself, which is declared once. A relation without a key gets no TableView, whatever tableView says.
• Relation columns point at a relation of the batch, or at one the module mapped before, by its single-column key.
• A relation column's label is written as sent. When it is left out, the column keeps the label its controller holds, if its relation was mapped before and held the column. Otherwise it takes D3, the title of the relation it points at in the singular ("Customer", not "Customer id"). That title comes from this call, else from the target's page if it was mapped before, else from its humanized name. A relation column that the database commented with a short first sentence takes that sentence instead (L2). A column that is not a relation column takes its humanized name on every mapping, even when the database commented it, so it does not take the analysis's title. A call that never sends a label therefore gets D3 or L2 titles for its relation columns on a first mapping and keeps them on a remap, and gets humanized names for its other columns.
• A column's display is { kind: "badge", items: [{ value, label, tone }] } on a string column, or { kind: "price" } on a number one. A relation column takes none. When display is left out, the column keeps the display it has if its relation was mapped before, read back from its controller as GetMapping reads it. A new relation takes the display that D1 or D2 proposes. null writes the column plain, over any display it had. A call that never sends a display therefore gets the proposal on a first mapping and loses nothing on a remap.
• Where a proposal comes from. MapResources reads the profile of a new relation through the catalog connection at map time, because D1 needs it. When it cannot read the profile, it takes the proposal from the connection's last AnalyseDatabase. When there is none either, D2 still applies by the column's name, no column becomes a badge, and the result warns display_not_proposed. A display that does not suit the column's declaration, such as a badge on a column declared number, or one on the primary key, is dropped and the column is written plain. When the display was sent, the result warns display_dropped.
• A TableView's tabs. tabs: { column } writes an "All" tab and one tab per value of that column's badge to the TableView, over the tabs it holds. The column must be drawn as a badge, or the tabs are left out with a display_dropped warning. When the column is not filterable, the builder marks it filterable and warns field_flag_set. When tabs is left out, a page already mapped keeps its tabs, and a new relation takes the tabs that T1 proposes among its filterable badge columns only. null writes none. It adds no tabs to a new relation, and it removes from a page already mapped the tabs that a mapping wrote from a badge, as display: null removes the badge. Tabs edited by hand are kept.
• Mapping a relation again rewrites it from the new choices. Its page is created, kept or removed as tableView says, and takes the new title. The module's relation columns that point at it take its new label.
• Before a remap that removes a page, PreviewMapResources(input) answers pagesToRemove for the same input and writes nothing, so a caller can ask a person first. It refuses what MapResources refuses.
• The module's package.json records the connection and the excluded tables with their reasons, so the next analysis does not propose those tables. Mapping one later removes it from the record:
  json

  Copy

  "dmsBuilder": {
    "database": {
      "connection": "erp",
      "excludedTables": { "erp.schema_migrations": "a framework's or a migration tool's table" }
    }
  }
  

• In a mapped module, CreateResource, AddField and a ConfigureField that changes dbField fail with invalid_config, since the module's tables belong to the database. DeleteResource keeps the client's rows and warns data_kept.

Next steps

• Resources describes the resources a mapping writes. They are ordinary resources, read the same way.
• Queries shows how stats over a mapped relation name it by its ref.
• Results and errors shows how to branch on what every operation answers.