title: Sequelize one-to-many association errors
body: <p>I’m currently working on a new API which uses SequelizeJS with PostgreDB. I normally work with MongoDB and am having some trouble getting the associations working based on specific columns (not the standard id). I want to reduce how many database calls I make in common operations. </p>
  
  <p>I have 3 tables named as follows: Sites, Buildings, Areas.<br>
  - Sites have many Buildings (Site: reference &lt;->Building: siteReference)<br>
  - Buildings have many Areas (Building: Reference &lt;-> Area: buildingReference)<br>
  - A building belongs to a site<br>
  - Areas belong to a Building   </p>
  
  <p>I have followed the Sequelize docs as best as I can (finding it a little difficult to understand the examples), and believed that the following would work (at this moment just sites&lt;->buildings):</p>
  
  <pre><code>Sites.hasMany(Buildings, { as: 'relatedBuildings', targetKey: 'siteReference'});
  Buildings.belongsTo(Sites, { as: 'parentSite', sourceKey: 'reference'});
  
  // Query function
  return mainDB.Sites
      .findAll({
          raw: true,
          include: [{
              model: mainDB.Buildings,
              as: 'relatedBuildings'
          }]
      })
      .then(dbRes =&gt; { console.log(dbRes));
      .catch(error =&gt; {console.log('ERROR', error.message)})
  </code></pre>
  
  <p>However, when I run the above code I receive the following errors:</p>
  
  <pre><code>First Error: relation "Sites" does not exist (note: the associations are stopping this table from being created)
  
  Second Error: 
  "status": 409,
              "message": "A database error occurred. Further details attached.",
              "value": {
                  "name": "SequelizeDatabaseError",
                  "parent": {
                      "name": "error",
                      "length": 110,
                      "severity": "ERROR",
                      "code": "42P01",
                      "position": "1678",
                      "file": "parse_relation.c",
                      "line": "1160",
                      "routine": "parserOpenTable",
                      "sql": "SELECT \"Sites\".\"id\", \"Sites\".\"reference\", \"Sites\".\"name\", \"Sites\".\"description\", \"Sites\".\"addressLine1\", \"Sites\".\"addressLine2\", \"Sites\".\"city\", \"Sites\".\"county\", \"Sites\".\"postcode\", \"Sites\".\"country\", \"Sites\".\"lat\", \"Sites\".\"long\", \"Sites\".\"type\", \"Sites\".\"companyReference\", \"Sites\".\"namedContactReference\", \"Sites\".\"status\", \"Sites\".\"created_at\", \"Sites\".\"updated_at\", \"relatedBuildings\".\"id\" AS \"relatedBuildings.id\", \"relatedBuildings\".\"reference\" AS \"relatedBuildings.reference\", \"relatedBuildings\".\"name\" AS \"relatedBuildings.name\", \"relatedBuildings\".\"description\" AS \"relatedBuildings.description\", \"relatedBuildings\".\"addressLine1\" AS \"relatedBuildings.addressLine1\", \"relatedBuildings\".\"addressLine2\" AS \"relatedBuildings.addressLine2\", \"relatedBuildings\".\"city\" AS \"relatedBuildings.city\", \"relatedBuildings\".\"county\" AS \"relatedBuildings.county\", \"relatedBuildings\".\"postcode\" AS \"relatedBuildings.postcode\", \"relatedBuildings\".\"country\" AS \"relatedBuildings.country\", \"relatedBuildings\".\"lat\" AS \"relatedBuildings.lat\", \"relatedBuildings\".\"long\" AS \"relatedBuildings.long\", \"relatedBuildings\".\"type\" AS \"relatedBuildings.type\", \"relatedBuildings\".\"siteReference\" AS \"relatedBuildings.siteReference\", \"relatedBuildings\".\"namedContactReference\" AS \"relatedBuildings.namedContactReference\", \"relatedBuildings\".\"status\" AS \"relatedBuildings.status\", \"relatedBuildings\".\"created_at\" AS \"relatedBuildings.created_at\", \"relatedBuildings\".\"updated_at\" AS \"relatedBuildings.updated_at\", \"relatedBuildings\".\"site_id\" AS \"relatedBuildings.site_id\", \"relatedBuildings\".\"related_buildings_id\" AS \"relatedBuildings.related_buildings_id\" FROM \"Sites\" AS \"Sites\" LEFT OUTER JOIN \"Buildings\" AS \"relatedBuildings\" ON \"Sites\".\"id\" = \"relatedBuildings\".\"site_id\";"
                  },
                  "original": {
                      "name": "error",
                      "length": 110,
                      "severity": "ERROR",
                      "code": "42P01",
                      "position": "1678",
                      "file": "parse_relation.c",
                      "line": "1160",
                      "routine": "parserOpenTable",
                      "sql": "SELECT \"Sites\".\"id\", \"Sites\".\"reference\", \"Sites\".\"name\", \"Sites\".\"description\", \"Sites\".\"addressLine1\", \"Sites\".\"addressLine2\", \"Sites\".\"city\", \"Sites\".\"county\", \"Sites\".\"postcode\", \"Sites\".\"country\", \"Sites\".\"lat\", \"Sites\".\"long\", \"Sites\".\"type\", \"Sites\".\"companyReference\", \"Sites\".\"namedContactReference\", \"Sites\".\"status\", \"Sites\".\"created_at\", \"Sites\".\"updated_at\", \"relatedBuildings\".\"id\" AS \"relatedBuildings.id\", \"relatedBuildings\".\"reference\" AS \"relatedBuildings.reference\", \"relatedBuildings\".\"name\" AS \"relatedBuildings.name\", \"relatedBuildings\".\"description\" AS \"relatedBuildings.description\", \"relatedBuildings\".\"addressLine1\" AS \"relatedBuildings.addressLine1\", \"relatedBuildings\".\"addressLine2\" AS \"relatedBuildings.addressLine2\", \"relatedBuildings\".\"city\" AS \"relatedBuildings.city\", \"relatedBuildings\".\"county\" AS \"relatedBuildings.county\", \"relatedBuildings\".\"postcode\" AS \"relatedBuildings.postcode\", \"relatedBuildings\".\"country\" AS \"relatedBuildings.country\", \"relatedBuildings\".\"lat\" AS \"relatedBuildings.lat\", \"relatedBuildings\".\"long\" AS \"relatedBuildings.long\", \"relatedBuildings\".\"type\" AS \"relatedBuildings.type\", \"relatedBuildings\".\"siteReference\" AS \"relatedBuildings.siteReference\", \"relatedBuildings\".\"namedContactReference\" AS \"relatedBuildings.namedContactReference\", \"relatedBuildings\".\"status\" AS \"relatedBuildings.status\", \"relatedBuildings\".\"created_at\" AS \"relatedBuildings.created_at\", \"relatedBuildings\".\"updated_at\" AS \"relatedBuildings.updated_at\", \"relatedBuildings\".\"site_id\" AS \"relatedBuildings.site_id\", \"relatedBuildings\".\"related_buildings_id\" AS \"relatedBuildings.related_buildings_id\" FROM \"Sites\" AS \"Sites\" LEFT OUTER JOIN \"Buildings\" AS \"relatedBuildings\" ON \"Sites\".\"id\" = \"relatedBuildings\".\"site_id\";"
                  },
                  "sql": "SELECT \"Sites\".\"id\", \"Sites\".\"reference\", \"Sites\".\"name\", \"Sites\".\"description\", \"Sites\".\"addressLine1\", \"Sites\".\"addressLine2\", \"Sites\".\"city\", \"Sites\".\"county\", \"Sites\".\"postcode\", \"Sites\".\"country\", \"Sites\".\"lat\", \"Sites\".\"long\", \"Sites\".\"type\", \"Sites\".\"companyReference\", \"Sites\".\"namedContactReference\", \"Sites\".\"status\", \"Sites\".\"created_at\", \"Sites\".\"updated_at\", \"relatedBuildings\".\"id\" AS \"relatedBuildings.id\", \"relatedBuildings\".\"reference\" AS \"relatedBuildings.reference\", \"relatedBuildings\".\"name\" AS \"relatedBuildings.name\", \"relatedBuildings\".\"description\" AS \"relatedBuildings.description\", \"relatedBuildings\".\"addressLine1\" AS \"relatedBuildings.addressLine1\", \"relatedBuildings\".\"addressLine2\" AS \"relatedBuildings.addressLine2\", \"relatedBuildings\".\"city\" AS \"relatedBuildings.city\", \"relatedBuildings\".\"county\" AS \"relatedBuildings.county\", \"relatedBuildings\".\"postcode\" AS \"relatedBuildings.postcode\", \"relatedBuildings\".\"country\" AS \"relatedBuildings.country\", \"relatedBuildings\".\"lat\" AS \"relatedBuildings.lat\", \"relatedBuildings\".\"long\" AS \"relatedBuildings.long\", \"relatedBuildings\".\"type\" AS \"relatedBuildings.type\", \"relatedBuildings\".\"siteReference\" AS \"relatedBuildings.siteReference\", \"relatedBuildings\".\"namedContactReference\" AS \"relatedBuildings.namedContactReference\", \"relatedBuildings\".\"status\" AS \"relatedBuildings.status\", \"relatedBuildings\".\"created_at\" AS \"relatedBuildings.created_at\", \"relatedBuildings\".\"updated_at\" AS \"relatedBuildings.updated_at\", \"relatedBuildings\".\"site_id\" AS \"relatedBuildings.site_id\", \"relatedBuildings\".\"related_buildings_id\" AS \"relatedBuildings.related_buildings_id\" FROM \"Sites\" AS \"Sites\" LEFT OUTER JOIN \"Buildings\" AS \"relatedBuildings\" ON \"Sites\".\"id\" = \"relatedBuildings\".\"site_id\";"
              }
  </code></pre>
  
  <p>Ultimately I am aiming to get an output as follows when I run a FindAll() on my Sites table:</p>
  
  <pre><code>Site
  - Building
  - - Area
  - - Area
  - Building
  - - Area
  - Building
  - - Area
  - - Area
  - - Area
  Site
  - Building
  - - Area
  - - Area
  - Building
  - - Area
  - - Area
  Site
  - Building
  - - Area
  - - Area
  </code></pre>
  
  <p>Where am I going wrong?</p>
  
  <p><strong>UPDATE</strong> - here are my models (note I formally define them elsewhere)</p>
  
  <p>Sites</p>
  
  <pre><code>const Sites = {
      name: 'Sites',
      schema: {
          id: {
              type: DataTypes.INTEGER,
              primaryKey: true,
              autoIncrement: true,
              allowNull: false
          },
          reference: {
              type: DataTypes.STRING(100),
              unique: true,
              allowNull: false
          },
          name: {
              type: DataTypes.STRING,
              allowNull: false
          },
          description: {
              type: DataTypes.STRING(512),
              allowNull: true
          },
          addressLine1: {
              type: DataTypes.STRING,
              allowNull: false
          },
          addressLine2: {
              type: DataTypes.STRING,
              allowNull: false
          },
          city: {
              type: DataTypes.STRING,
              allowNull: false
          },
          county: {
              type: DataTypes.STRING,
              allowNull: false
          },
          postcode: {
              type: DataTypes.STRING,
              allowNull: false
          },
          country: {
              type: DataTypes.STRING,
              allowNull: false
          },
          lat: {
              type: DataTypes.STRING,
              allowNull: false
          },
          long: {
              type: DataTypes.STRING,
              allowNull: false
          },
          type: {
              type: DataTypes.STRING,
              allowNull: false
          },
          companyReference: {
              type: DataTypes.STRING(100),
              allowNull: false
          },
          namedContactReference: {
              type: DataTypes.STRING(100),
              allowNull: true
          },
          status: {
              type: DataTypes.STRING(20),
              allowNull: false
          }
      },
      options: {
          underscored: true
      }
  };
  
  export default Sites;
  </code></pre>
  
  <p>Buildings</p>
  
  <pre><code>const Buildings = {
      name: 'Buildings',
      schema: {
          id: {
              type: DataTypes.INTEGER,
              primaryKey: true,
              autoIncrement: true,
              allowNull: false
          },
          reference: {
              type: DataTypes.STRING(100),
              unique: true,
              allowNull: false
          },
          name: {
              type: DataTypes.STRING,
              allowNull: false
          },
          description: {
              type: DataTypes.STRING(256),
              unique: false,
              allowNull: true
          },
          addressLine1: {
              type: DataTypes.STRING,
              allowNull: false
          },
          addressLine2: {
              type: DataTypes.STRING,
              allowNull: false
          },
          city: {
              type: DataTypes.STRING,
              allowNull: false
          },
          county: {
              type: DataTypes.STRING,
              allowNull: false
          },
          postcode: {
              type: DataTypes.STRING,
              allowNull: false
          },
          country: {
              type: DataTypes.STRING,
              allowNull: false
          },
          lat: {
              type: DataTypes.STRING,
              allowNull: false
          },
          long: {
              type: DataTypes.STRING,
              allowNull: false
          },
          type: {
              type: DataTypes.STRING,
              allowNull: false
          },
          siteReference: {
              type: DataTypes.STRING(100),
              allowNull: false
          },
          namedContactReference: {
              type: DataTypes.STRING(100),
              allowNull: true
          },
          status: {
              type: DataTypes.STRING(20),
              allowNull: false
          }
      },
      options: { underscored: true }
  };
  export default Buildings;
  </code></pre>
  
  <p>Areas</p>
  
  <pre><code>const Areas = {
      name: 'Areas',
      schema: {
          id: {
              type: DataTypes.INTEGER,
              primaryKey: true,
              autoIncrement: true,
              allowNull: false
          },
          reference: {
              type: DataTypes.STRING(100),
              unique: true,
              allowNull: false
          },
          buildingReference: {
              type: DataTypes.STRING(100),
              unique: false,
              allowNull: false
          },
          panelReference: {
              type: DataTypes.STRING(100),
              unique: false,
              allowNull: false
          },
          name: {
              type: DataTypes.STRING(128),
              unique: false,
              allowNull: false
          },
          description: {
              type: DataTypes.STRING(512),
              allowNull: true
          },
          drawingReference: {
              type: DataTypes.STRING(100),
              unique: false,
              allowNull: false
          },
          status: {
              type: DataTypes.STRING(10),
              allowNull: false
          }
      },
      options: { underscored: true }
  };
  export default Areas;
  </code></pre>