How do I associate a model with a "has one" relationship on an GraphQL interface

Question

I have a GraphQL/Apollo server using Sequelize/mysql. My GraphQL types (Employee, Contractor) each implement a Person interface. My db model contains an employee, contractor, and events table. I would like my Person Type to have a "has many" relationship with Events. While my Event Type "belongs to" a Person Type either Employee or Contractor.

I'm guessing it has something do do with the person_id field in the Event Type. I can get it to work without the interface on a single table "Employee" and changing person_id to employee_id. So im guessing it just doesnt know how to distinguish between Employee and Contractor to reference that table?

//typeDefs.js

const typeDefs = gql`
  type Event {
    id: ID!
    person_id: ID!
    location: String!
    escort: String!
  }

  interface Person {
    id: ID!
    first_name: String!
    last_name: String!
    department_company: String!
    events: [Event]
  }

  type Employee implements Person {
    id: ID!
    first_name: String!
    last_name: String!
    department_company: String!
    events: [Event]
    employee_sh_id: String
  }

  type Contractor implements Person {
    id: ID!
    first_name: String!
    last_name: String!
    department_company: String!
    events: [Event]
    escort_required: Boolean!
  }

//Employee model

module.exports = (sequelize, DataTypes) => {
  const Employee = sequelize.define('Employee', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    first_name: DataTypes.STRING,
    last_name: DataTypes.STRING,
    department_company: DataTypes.STRING,
    emplyee_sh_id: DataTypes.STRING
  }, {});
  Employee.associate = function(models) {
      Employee.hasMany(models.Event);
  };
  return Employee;
};

// Contractor model

module.exports = (sequelize, DataTypes) => {
  const Contractor = sequelize.define('Contractor', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    first_name: DataTypes.STRING,
    last_name: DataTypes.STRING,
    department_company: DataTypes.STRING,
    escort_required: DataTypes.BOOLEAN,
  }, {});
  Contractor.associate = function(models) {
      Contractor.hasMany(models.Event);
  };
  return Contractor;
};

// Event model

module.exports = (sequelize, DataTypes) => {
  const Event = sequelize.define(
    "Event",
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      person_id: DataTypes.INTEGER,
      location: DataTypes.STRING,
      escort: DataTypes.STRING
    },
    {}
  );
  Event.associate = function(models) {
    Event.belongsTo(models.Employee),
    Event.belongsTo(models.Contractor)
  };
  return Event;
};

// resolvers.js

const resolvers = {
  Query: {
    async employee(root, { id }, { models }) {
      return models.Employee.findByPk(id);
    },
    async contractor(root, { id }, { models }) {
      return models.Contractor.findByPk(id);
    },
    async employees(root, args, { models }) {
      return models.Employee.findAll();
    },
    async contractors(root, args, { models }) {
      return models.Contractor.findAll();
    },
    async event(root, { id }, { models }) {
      return models.Event.findByPk(id);
    },
    async events(root, args, { models }) {
      return models.Event.findAll();
    }
  },

  Mutation: {
    async addEmployee(
      root,
      {
        first_name,
        last_name,
        department_company,
        employee_sh_id
      },
      { models }
    ) {
      return models.Employee.create({
        first_name,
        last_name,
        department_company,
        employee_sh_id
      });
    },

    async addContractor(
      root,
      {
        first_name,
        last_name,
        department_company,
        escort_required,
      },
      { models }
    ) {
      return models.Contractor.create({
        first_name,
        last_name,
        department_company,
        escort_required,
      });
    },

    async addEvent(
      root,
      { person_id, location, escort },
      { models }
    ) {
      return models.Event.create({
        person_id,
        location,
        escort
      });
    },

  Person: {
    __resolveType: person => {
      if (person.employee) {
        return "Employee";
      }
      return "Contractor";
    }
  },

  Employee: {
    events: (parent, args, context, info) => parent.getEvents(),
  },

  Contractor: {
    events: (parent, args, context, info) => parent.getEvents(),
  }
};

Answer 1

Do you even need an interface?

The primary purpose behind abstract types like interfaces and unions is that they allow a particular field to resolve to one of a set of types. If you have Contractor and Employee types and want a particular field to return either type, it makes sense to add an interface or union to handle that scenario:

type Event {
  contractors: [Contractor!]!
  employees: [Employee!]!
  people: [Person!]! # could be either
}

If you don't need this functionality, you don't really need any abstract types. (Side note: you can use interfaces just to enforce congruence between types that share fields, but at that point you're just using them as a validation tool and their existence probably shouldn't impact how you design the underlying data layer).

There is no silver bullet

Dealing with inheritance in relational databases can be tricky and there's no one-size-fits-all answer. Things get even weirded when using Sequelize or another ORM because your solution has to work within the limits of that particular library as well. Here's a couple of different ways you could approach this problem, though it's by far not an exhaustive list:

• If you only have a couple of fields that return a Person type, you can get away with having separate tables and separate models and just merging the results yourself. Something like:

people: async (event) => {
  const [employees, contractors] = await Promise.all([
    event.getEmployees(),
    event.getContractors(),
  ])
  const people = employees.concat(contractors)
  // Sort here if needed
  return people
}

This does mean you're now querying the DB twice, and potentially spending extra time doing sorting that the DB would have otherwise done for you. However, it means you can maintain separate tables and models for Contractors and Employees, which means querying for those entities is straightforward.

• Lump both Contractors and Employees under a single table, using some kind of type field to distinguish between the two. You can then use scopes to help you model the relationships appropriately in Sequelize:

Event.hasMany(models.Person, { as: 'employees', scope: {type: 'EMPLOYEE'} ... })
Event.hasMany(models.Person, { as: 'contractors', scope: {type: 'CONTRACTOR'} ... })
Event.hasMany(models.Person, { as: 'people', /** no scope **/ ... })

This works, even if it doesn't "feel" right to have everything in one table. You have to remember to scope your associations and queries correctly.

• If you're using Sequelize strictly as an ORM and not generating your database from your Sequelize models (i.e. not calling sync), it's also possible to model a view as a Sequelize model. Views are a bit of a pain to write and maintain, but this would allow you keep separate tables for Employees and Contractors while creating a virtual table from the other two that could be used to query all People.