How to make GraphQL schema for MySQL models?

Question

I have the following table in mysql.

Users

CREATE TABLE users(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

and Posts

CREATE TABLE posts(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    created_by INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    post_title VARCHAR(100) NOT NULL UNIQUE,
    post_body VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

I am using nodejs and the mysql npm package. How can I make graphQL schema for the models?

I searched a lot but failed to find any solution to this. Mostly people are using sequelize for this purpose. Is it better than mysql package?

Answer 1

Yes You can use sequelize orm for connecting graphql to Mysql database

Refference :- http://docs.sequelizejs.com/manual/installation/getting-started

Sample Schema and resolvers are given

Schema.js

const typeDefinitions = `



type Author {

  authorId: Int
  firstName: String
  lastName: String
  posts: [Post]

}

type Post {

  postId: Int
  title: String 
  text: String
  views: Int
  author: Author

}

input postInput{
  title: String 
  text: String
  views: Int
}


type Query {

  author(firstName: String, lastName: String): [Author]
  posts(postId: Int, title: String, text: String, views: Int): [Post]

}



type Mutation {

createAuthor(firstName: String, lastName: String, posts:[postInput]): Author

updateAuthor(authorId: Int, firstName: String, lastName: String, posts:[postInput]): String

}


schema {
  query: Query
  mutation:Mutation
}
`;

export default [typeDefinitions];

connectors.js

import rp from 'request-promise';
var Sequelize = require('sequelize');
var db = new Sequelize('test', 'postgres', 'postgres', {
  host: '192.168.1.168',
  dialect: 'postgres',

  pool: {
    max: 5,
    min: 0,
    idle: 10000
  }

});


const AuthorModel = db.define('author', {
  authorId: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, field: "author_id" },
  firstName: { type: Sequelize.STRING, field: "first_name" },
  lastName: { type: Sequelize.STRING, field: "last_name" },
},{
        freezeTableName: false,
        timestamps: false,
        underscored: false,
        tableName: "author"
    });


const PostModel = db.define('post', {
    postId: { type: Sequelize.INTEGER, primaryKey: true, autoIncrement: true, field: "post_id" },
  text: { type: Sequelize.STRING },
  title:  { type: Sequelize.STRING },
  views: { type: Sequelize.INTEGER },
},{
        freezeTableName: false,
        timestamps: false,
        underscored: false,
        tableName: "post"
    });


AuthorModel.hasMany(PostModel, {
    foreignKey: 'author_id'
});
PostModel.belongsTo(AuthorModel, {
    foreignKey: 'author_id'
});

const Author = db.models.author;
const Post = db.models.post;

export { Author, Post };

resolver.js

import { Author } from './connectors';
import { Post } from './connectors';


const resolvers = {

  Query: {
    author(_, args) {
      return Author.findAll({ where: args });
    },
    posts(_, args) {
      return Post.findAll({ where: args });
    }
  },

  Mutation: {

    createAuthor(_, args) {
      console.log(args)
      return Author.create(args, {
        include: [{
          model: Post,
        }]
      });
    },

    updateAuthor(_, args) {

      var updateProfile = { title: "name here" };
      console.log(args.authorId)
      var filter = {
        where: {
          authorId: args.authorId
        },
        include: [
          { model: Post }
        ]
      };
      Author.findOne(filter).then(function (product) {
        Author.update(args, { where: { authorId: args.authorId } }).then(function (result) {
          product.posts[0].updateAttributes(args.posts[0]).then(function (result) {
            //return result;
          })
        });
      })
      return "updated";
    },

  },


  Author: {
    posts(author) {
      return author.getPosts();
    },
  },
  Post: {
    author(post) {
      return post.getAuthor();
    },
  },
};

export default resolvers;

Answer 2

You can try a new open source tool called SwitchQL (github.com/SwitchQL/SwitchQL). I've been working on the project for a while.

You pass it your connection string and it returns everything you need to run a graphql server on top of an existing database. It also returns Apollo compliant client mutation and queries.

We only support Postgres at the moment, but if you'd like to look into helping us support MySQL let me know!