Detect relationship inconsistencies in Keystone databases prior to the [Arcade](https://www.keystonejs.com/discussions/new-data-schema) release

detect-dangling-relationships.js

/**
 * NOTE: We use console.error for log output below so that the output can be
 * redirected to a file by the user
 */

const path = require('path');

const entryFile = process.argv[2];

if (!entryFile) {
  const thisScript = path.relative(process.cwd(), process.argv[1]);
  console.error(`
Usage:
  node ${thisScript} <entryFile>

Example:
  node ${thisScript} ./index.js

`);
  process.exit(1);
}

console.error('Loading project');
const { keystone, apps } = require(path.resolve(entryFile));

(async () => {
  console.error('Preparing Keystone');
  await keystone.prepare({
    apps,
    dev: process.env.NODE_ENV !== 'production',
  });
  console.error('Connecting to the database');
  await keystone.connect();
  console.error('Detecting dangling relationships');

  let dangling = [];

  try {
    const rels = consolidateRelationships({ keystone });
    const oneToOneRels = rels.filter(({ right, cardinality }) => right && cardinality === '1:1');
    const oneToManyRels = rels.filter(
      ({ right, cardinality }) => right && (cardinality === '1:N' || cardinality === 'N:1')
    );
    const manyToManyRels = rels.filter(({ right, cardinality }) => right && cardinality === 'N:N');

    dangling = [
      ...await processOneToOne({ keystone, oneToOneRels }),
      ...await processOneToMany({ keystone, oneToManyRels }),
      ...await processManyToMany({ keystone, manyToManyRels }),
    ];

  } catch (error) {
    console.error(error);
    process.exit(1);
  }

  if (dangling.length) {

    const buildSortString = ({ rel, _label_ }) => `${rel.listKey}|${rel.path}|${_label_}`;
    dangling.sort((left, right) => buildSortString(left.dangling).localeCompare(buildSortString(right.dangling)));

    let lastList;
    dangling.forEach(({ dangling, toward }) => {
      if (lastList !== dangling.listKey) {
        console.log(`\n${dangling.listKey}`);
        lastList = dangling.listKey;
      }

      console.log(
        `    ${dangling.id}${dangling.id !== dangling._label_ ? `("${dangling._label_}")` : ''}.${dangling.rel.path} -> ${toward.id}${toward.id !== toward._label_ ? `("${toward._label_}")` : ''}`
      );
    });
    console.error(`${dangling.length} dangling relationships found.`);
  } else {
    console.error('No dangling relationships found 🎉');
  }

  process.exit(0);
})();

function consolidateRelationships({ keystone }) {
  const rels = {};
  const otherSides = {};
  keystone.listsArray.forEach(list => {
    list.fields
      .filter(f => f.isRelationship)
      .forEach(f => {
        const myRef = `${f.listKey}.${f.path}`;
        if (otherSides[myRef]) {
          // I'm already there, go and update rels[otherSides[myRef]] with my info
          rels[otherSides[myRef]].right = f;

          // Make sure I'm actually referencing the thing on the left
          const { left } = rels[otherSides[myRef]];
          if (f.config.ref !== `${left.listKey}.${left.path}`) {
            throw new Error(
              `${myRef} refers to ${f.config.ref}. Expected ${left.listKey}.${left.path}`
            );
          }
        } else {
          // Got us a new relationship!
          rels[myRef] = { left: f };
          if (f.refFieldPath) {
            // Populate otherSides
            otherSides[f.config.ref] = myRef;
          }
        }
      });
  });
  // See if anything failed to link up.
  const badRel = Object.values(rels).find(({ left, right }) => left.refFieldPath && !right);
  if (badRel) {
    const { left } = badRel;
    throw new Error(
      `${left.listKey}.${left.path} refers to a non-existant field, ${left.config.ref}`
    );
  }

  // Ensure that the left/right pattern is always the same no matter what order
  // the lists and fields are defined.
  Object.values(rels).forEach(rel => {
    const { left, right } = rel;
    if (right) {
      const order = left.listKey.localeCompare(right.listKey);
      if (order > 0) {
        // left comes after right, so swap them.
        rel.left = right;
        rel.right = left;
      } else if (order === 0) {
        // self referential list, so check the paths.
        if (left.path.localeCompare(right.path) > 0) {
          rel.left = right;
          rel.right = left;
        }
      }
    }
  });

  Object.values(rels).forEach(rel => {
    const { left, right } = rel;
    let cardinality;
    if (left.config.many) {
      if (right) {
        if (right.config.many) {
          cardinality = 'N:N';
        } else {
          cardinality = '1:N';
        }
      } else {
        // right not specified, have to assume that it's N:N
        cardinality = 'N:N';
      }
    } else {
      if (right) {
        if (right.config.many) {
          cardinality = 'N:1';
        } else {
          cardinality = '1:1';
        }
      } else {
        // right not specified, have to assume that it's N:1
        cardinality = 'N:1';
      }
    }
    rel.cardinality = cardinality;

    let tableName;
    let columnName;
    if (cardinality === 'N:N') {
      tableName = right
        ? `${left.listKey}_${left.path}_${right.listKey}_${right.path}`
        : `${left.listKey}_${left.path}_many`;
      if (right) {
        const leftKey = `${left.listKey}.${left.path}`;
        const rightKey = `${right.listKey}.${right.path}`;
        rel.columnNames = {
          [leftKey]: { near: `${left.listKey}_left_id`, far: `${right.listKey}_right_id` },
          [rightKey]: { near: `${right.listKey}_right_id`, far: `${left.listKey}_left_id` },
        };
      } else {
        const leftKey = `${left.listKey}.${left.path}`;
        const rightKey = `${left.config.ref}`;
        rel.columnNames = {
          [leftKey]: { near: `${left.listKey}_left_id`, far: `${left.config.ref}_right_id` },
          [rightKey]: { near: `${left.config.ref}_right_id`, far: `${left.listKey}_left_id` },
        };
      }
    } else if (cardinality === '1:1') {
      tableName = left.listKey;
      columnName = left.path;
    } else if (cardinality === '1:N') {
      tableName = right.listKey;
      columnName = right.path;
    } else {
      tableName = left.listKey;
      columnName = left.path;
    }
    rel.tableName = tableName;
    rel.columnName = columnName;
  });

  return Object.values(rels);
}

async function processOneToOne({ keystone, oneToOneRels }) {
  const dangling = [];

  for (const rel of oneToOneRels) {
    const query = `
      query {
        leftToRight: ${rel.left.getListByKey(rel.left.listKey).gqlNames.listQueryName} {
          id
          _label_
          ${rel.left.path} {
            id
            _label_
            ${rel.right.path} {
              id
              _label_
            }
          }
        }

        rightToLeft: ${rel.right.getListByKey(rel.right.listKey).gqlNames.listQueryName} {
          id
          _label_
          ${rel.right.path} {
            id
            _label_
            ${rel.left.path} {
              id
              _label_
            }
          }
        }
      }
    `;

    const result = await keystone.executeQuery(query);

    const { data: { leftToRight, rightToLeft } = {}, error } = result;

    if (error) {
      throw error;
    }

    leftToRight.forEach(
      // eg; User.bio
      ({ id, _label_, [rel.left.path]: leftItem }) => {
        if (!leftItem) {
          // There's no Bio associated with this User, so just move on
          return;
        }
        // eg; User.bio.user
        if (!leftItem[rel.right.path] || leftItem[rel.right.path].id !== id) {
          // Dangling reference
          dangling.push({
            dangling: { listKey: rel.left.listKey, rel: rel.left, id, _label_ },
            toward: { listKey: rel.right.listKey, rel: rel.right, id: leftItem.id, _label_: leftItem._label_ },
          });
        }
      }
    );

    rightToLeft.forEach(
      // eg; Bio.user
      ({ id, _label_, [rel.right.path]: rightItem }) => {
        if (!rightItem) {
          // There's no User associated with this Bio, so just move on
          return;
        }
        // eg; Bio.user.bio
        if (!rightItem[rel.left.path] || rightItem[rel.left.path].id !== id) {
          // Dangling reference
          dangling.push({
            dangling: { listKey: rel.right.listKey, rel: rel.right, id, _label_ },
            toward: { listKey: rel.left.listKey, rel: rel.left, id: rightItem.id, _label_: rightItem._label_ },
          });
        }
      }
    );
  }

  return dangling;
}

async function processOneToMany({ keystone, oneToManyRels }) {
  const dangling = [];

  for (const rel of oneToManyRels) {
    let singleSide, manySide;
    if (rel.cardinality === 'N:1') {
      singleSide = rel.left;
      manySide = rel.right;
    } else {
      singleSide = rel.right;
      manySide = rel.left;
    }

    const query = `
      query {
        singleToMany: ${singleSide.getListByKey(singleSide.listKey).gqlNames.listQueryName} {
          id
          _label_
          ${singleSide.path} {
            id
            _label_
            ${manySide.path} {
              id
              _label_
            }
          }
        }
        manyToSingle: ${manySide.getListByKey(manySide.listKey).gqlNames.listQueryName} {
          id
          _label_
          ${manySide.path} {
            id
            _label_
            ${singleSide.path} {
              id
              _label_
            }
          }
        }
      }
    `;

    const result = await keystone.executeQuery(query);

    const { data: { singleToMany, manyToSingle } = {}, error } = result;

    if (error) {
      throw error;
    }

    manyToSingle.forEach(
      // eg; Group.events
      ({ id, _label_, [manySide.path]: manyItems }) => {
        (manyItems || []).forEach(
          // eg; Group.events.group
          singleItem => {
            if (!singleItem[singleSide.path] || singleItem[singleSide.path].id !== id) {
              // The many side includes an ID to an item which doesn't return the
              // favour. In fact, that ID is just empty.
              // OR: The single item has an ID for an item the many side doesn't include
              dangling.push({
                dangling: { listKey: manySide.listKey, rel: manySide, id, _label_ },
                toward: { listKey: singleSide.listKey, rel: singleSide, id: singleItem.id, _label_: singleItem._label_ },
              });
            }
          }
        );
      }
    );

    singleToMany.forEach(
      // eg; Event.group
      ({ id, _label_, [singleSide.path]: singleItem }) => {
        if (!singleItem) {
          // There's no group associated with this Event, so just move on
          return;
        }
        // eg; Event.group.events
        if (!(singleItem[manySide.path] || []).find(manyItem => manyItem.id === id)) {
          // Dangling reference
          dangling.push({
            dangling: { listKey: singleSide.listKey, rel: singleSide, id, _label_ },
            toward: { listKey: manySide.listKey, rel: manySide, id: singleItem.id, _label_: singleItem._label_ },
          });
        }
      }
    );
  }

  return dangling;
}

async function processManyToMany({ keystone, manyToManyRels }) {
  const dangling = [];
  for (const rel of manyToManyRels) {
    const { data: { leftToRight, rightToLeft } = {}, error } = await keystone.executeQuery(`
      query {
        leftToRight: ${rel.left.getListByKey(rel.left.listKey).gqlNames.listQueryName} {
          id
          _label_
          ${rel.left.path} {
            id
            _label_
            ${rel.right.path} {
              id
              _label_
            }
          }
        }
        rightToLeft: ${rel.right.getListByKey(rel.right.listKey).gqlNames.listQueryName} {
          id
          _label_
          ${rel.right.path} {
            id
            _label_
            ${rel.left.path} {
              id
              _label_
            }
          }
        }
      }
    `);

    if (error) {
      throw error;
    }

    leftToRight.forEach(({ id, _label_, [rel.left.path]: rightItems }) => {
      rightItems.forEach(rightItem => {
        if (!rightItem[rel.right.path].find(leftItem => leftItem.id === id)) {
          // Dangling reference
          dangling.push({
            dangling: { listKey: rel.left.listKey, rel: rel.left, id, _label_ },
            toward: { listKey: rel.right.listKey, rel: rel.right, id: rightItem.id, _label_: rightItem._label_ },
          });
        }
      });
    });

    rightToLeft.forEach(({ id, _label_, [rel.right.path]: leftItems }) => {
      leftItems.forEach(leftItem => {
        if (!leftItem[rel.left.path].find(rightItem => rightItem.id === id)) {
          // Dangling reference
          dangling.push({
            dangling: { listKey: rel.right.listKey, rel: rel.right, id, _label_ },
            toward: { listKey: rel.left.listKey, rel: rel.left, id: leftItem.id, _label_: leftItem._label_ },
          });
        }
      });
    });
  }
  return dangling;
}

output-example.txt

CFPScheduleRequest
    5c9acbc1aaee01001c727d12.event -> 5c9acba6aaee01001c727d11("May Event")

Label
    5bb5834d4aa6f3001bbedecc("javascript").events -> 5c3c3905ef44e1001aed0d96("7.0.0")
    5c3d7b9db50f4b001a351054("designsystem").groups -> 5bab1aa1c0f6a6001a3f3632("Design System Meetup")
    5bb5834d4aa6f3001bbedecc("javascript").groups -> 5bab1aa1c0f6a6001a3f3632("Design System Meetup")

Location
    5c3c387def44e1001aed0d94.events -> 5c3c3905ef44e1001aed0d96("7.0.0")
    5c60d02127263c001b86a35c.events -> 5c60d02227263c001b86a35e("Cars and Coffee III")
    5c9c1fabaaee01001c727d37.events -> 5c9c1facaaee01001c727d39("Deleted")

Presentation
    5dcba4522a377c9e4a3c7bfb.user -> 5b9fafe3a585b8002f7f02d5("Jess Telford")