Fetching data from MySQL to GraphQL using Sequelize failed

Question

First, here's my table on MySQL

+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| MemberID  | int(11)     | NO   | PRI | NULL    | auto_increment |
| FirstName | varchar(50) | NO   |     | NULL    |                |
| LastName  | varchar(50) | NO   |     | NULL    |                |
| CityID    | int(11)     | NO   | MUL | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+

I created a connection from my Node JS server to MySQL using Sequelize as the following :

var db = new Sequelize(
  'member','root', null,
  {
    host    : 'localhost',
    port    :  3306,
    dialect : 'mysql'
  }
);

Then I defined my MySQL table on my Node JS server using Sequelize as the following :

var memberlist = db.define('members', {
    MemberID  : Sequelize.INTEGER,
    FirstName : Sequelize.STRING,
    LastName  : Sequelize.STRING
});

Then I made a GraphQL Schema to fetch the data along with the corresponding resolver for the schema

 var schema = buildSchema(`

    type Members {
      MemberID: ID!
      FirstName: String!
      LastName: String!
      CityID: City!
    }

    type City {
      CityID: ID!
      CityName: String!
    }

    type Query {
      allMembers: [Members]!
    }
`);

 var root = {

    allMembers  : function(){
       memberlist.find({}).complete(function (err,data) 
       {}).then(({data}) => data)},

 }

var app = express();
 app.use('/graphql', graphqlHTTP({
    schema: schema,
    rootValue: root,
    graphiql: true
}));

When I tried to run the query on GraphQL, I got the following error :

Executing (default): SELECT `id`, `MemberID`, `FirstName`, `LastName`, 
`createdAt`, `updatedAt` FROM `members` AS `members` LIMIT 1;

Unhandled rejection SequelizeDatabaseError: Unknown column 'id' in 'field 
list'

Where did the id (and createdAt, updateAt) field come from since I didn't define them on Sequelize?

I can speculate that Sequelize is adding a primary key `id` column under the hood. But that would explain why the `id` column _is_ there, not why it is not there. — Tim Biegeleisen, Jan 21 '18 at 05:22
You should define a `primary key` when you define your table using Sequelize. Possible duplicate of [sequelize table without column 'id'](https://stackoverflow.com/questions/29233896/sequelize-table-without-column-id) — walter, Jan 21 '18 at 05:38

Michael McCabe · Answer 1 · 2018-01-21T11:05:56.787

I am presuming here that if you do not explicitly tell sequelize what the primary key is in your model, it will assume it is a field called id

Try the following to tell sequelize what your primary key is;

const memberlist = db.define('members', {
  MemberID  : {
    type: DataTypes.INTEGER,
    primaryKey: true,
  }
  FirstName : Sequelize.STRING,
  LastName  : Sequelize.STRING
});

The createdAt and updatedAt fields are added automatically for you. To stop that happening use

const memberlist = db.define('members', {
  MemberID  : {
    type: DataTypes.INTEGER,
    primaryKey: true,
  }
  FirstName : Sequelize.STRING,
  LastName  : Sequelize.STRING
}, {
  timestamps: false
});

Fetching data from MySQL to GraphQL using Sequelize failed

1 Answers1