index.js

// db/index.js
const mysql = require('serverless-mysql')({
  config: {
    host     : process.env.DB_ENDPOINT,
    database : process.env.DB_NAME,
    user     : process.env.DB_USER,
    password : process.env.DB_PASS
  }
});

module.exports = mysql;

permissions.js

'use strict';

const db = require('../db');

const TABLE_NAME = 'permission';

.....
.....

const collectionHandlers = {
  "GET": listItems,
  "POST": createItem,
};

const itemHandlers = {
  "DELETE": deleteItem,
  "GET": getItem,
  "PUT": putItem,
};

module.exports.router = async (event, context) => {
  let handlers = event["path"].endsWith('permissions') ? collectionHandlers : itemHandlers;

  let httpMethod = event["httpMethod"];
  if (httpMethod in handlers) {
    return await handlers[httpMethod](event, context);
  }

  return {
    statusCode: 405,
    headers: {
      "Access-Control-Allow-Origin" : "*",
      "Access-Control-Allow-Credentials" : true
    },
    body: JSON.stringify({
      message: `Invalid HTTP Method: ${httpMethod}`
    }),
  };
};

role.js

'use strict';

const db = require('../db');
const response = require('./commons/response');

const TABLE_NAME = 'role';
const ROLE_PERMISSION_TABLE = 'role_permissions';
const PERMISSION_TABLE = 'permission';

.....
.....

const createItem = async (event, context) => {
  let body, statusCode;
  const {name, description, permissions} = JSON.parse(event.body);

  try {
    let result = await db.query(`INSERT INTO ${TABLE_NAME} (name, description) VALUES ("${name}", "${description}")`);

    for(let permission of permissions) {
      await db.query(`INSERT INTO ${ROLE_PERMISSION_TABLE} (role_id, permission_id) VALUES (${result.insertId}, ${permission.id})`);
    }

    body = {
      data: result,
      message: 'Operation successful!',
    };
    statusCode = 201;
  } catch (e) {
    body = {
      data: e,
      message: 'Operation Failed!',
    };
    statusCode = 400;
  } finally {
    await db.end();
  }
  return response(statusCode, body);
};

.....
.....

const getItem = async (event, context) => {
  let body, statusCode;
  try {
    let results = await db.query(`SELECT * FROM ${TABLE_NAME} WHERE id=${event["pathParameters"]["id"]}`);
    let permissions = await db.query(
      `SELECT pm.* FROM ${PERMISSION_TABLE} as pm ` +
      `INNER JOIN ${ROLE_PERMISSION_TABLE} as rp ` +
      `ON rp.permission_id = pm.id && rp.role_id = ${event["pathParameters"]["id"]}`
    );
    body = {
      data: results && results.length === 1 ? {
        ...results[0],
        permissions
      } : null,
      message: 'Operation successful!',
    };

    statusCode = 200;
  } catch (e) {
    body = {
      data: e,
      message: 'Operation Failed!',
    };
    statusCode = 400;
  } finally {
    await db.end();
  }
  return response(statusCode, body);
};

const putItem = async (event, context) => {
  let body, statusCode;
  const {permissions, ...data} = JSON.parse(event.body);

  const updateQuery = Object.keys(data).map(item => `${item}="${data[item]}"`).join(', ');

  try {
    let results = await db.query(`UPDATE ${TABLE_NAME} SET ${updateQuery} where id=${event["pathParameters"]["id"]}`);
    body = {
      message: results['affectedRows'] > 0 ? 'Item updated successfully!' : "No such item exists!",
    };

    await db.query(`DELETE FROM ${ROLE_PERMISSION_TABLE} WHERE role_id=${event["pathParameters"]["id"]}`);
    for(let permission of permissions) {
      await db.query(`INSERT INTO ${ROLE_PERMISSION_TABLE} (role_id, permission_id) VALUES (${event["pathParameters"]["id"]}, ${permission.id})`);
    }

    ...
  }
  return response(statusCode, body);
};

const collectionHandlers = {
  "GET": listItems,
  "POST": createItem,
};

const itemHandlers = {
  "DELETE": deleteItem,
  "GET": getItem,
  "PUT": putItem,
};

module.exports.router = async (event, context) => {
  let handlers = event["path"].endsWith('roles') ? collectionHandlers : itemHandlers;

  let httpMethod = event["httpMethod"];
  if (httpMethod in handlers) {
    return await handlers[httpMethod](event, context);
  }

  return {
    statusCode: 405,
    headers: {
      "Access-Control-Allow-Origin" : "*",
      "Access-Control-Allow-Credentials" : true
    },
    body: JSON.stringify({
      message: `Invalid HTTP Method: ${httpMethod}`
    }),
  };
};

users.js

'use strict';

const db = require('../db');
const response = require('./commons/response');
const CognitoIdentityServiceProvider = require('aws-sdk/clients/cognitoidentityserviceprovider');

const TABLE_NAME = 'user';
const USER_ROLE_TABLE = 'user_role';
const ROLE_TABLE = 'role';

const createItem = async (event, context) => {
  const {roles, ...data} = JSON.parse(event.body);
  const fields = [
    'name',
    'email'
  ];

  let body, statusCode;

  let q = `INSERT INTO ${TABLE_NAME} (${fields.join(', ')}) VALUES (${fields.map(field => `"${data[field]}"`).join(', ')})`;

  try {
    let r = await db.query(q);

    if(roles && roles.length > 0)
      for(let role of roles) {
        await db.query(`INSERT INTO ${USER_ROLE_TABLE} (role_id, user_id) VALUES (${role.id}, ${r.insertId})`);
      }

    let res = await db.query(q);

    ...
    
  }

  return response(statusCode, body);
};

.....
.....

const getUserPermissions = async (event, context) => {
  let body, statusCode;

  const user_id = event["pathParameters"]["id"];

  try {
    body = {
      data: await db.query(`SELECT p.* from role_permissions as rp ` +
        `INNER JOIN user_role as ur ON ur.role_id = rp.role_id AND ur.user_id = ${user_id} ` +
        `INNER JOIN permission as p ON p.id = rp.permission_id`
      ),
      message: 'Operation successful!',
    };
    statusCode = 200;
  } catch (e) {
    console.log("e:", e);
    body = {
      data: e,
      message: 'Operation Failed!',
    };
    statusCode = 400;
  } finally {
    await db.end();
  }
  return response(statusCode, body);
};

const collectionHandlers = {
  "GET": listItems,
  "POST": createItem,
};

const itemHandlers = {
  "DELETE": deleteItem,
  "GET": getItem,
  "PUT": putItem,
};

const optionsHandlers = {
  "GET": getUserPermissions,
};

module.exports.router = async (event, context) => {
  let handlers = event["path"].endsWith('permissions') ? optionsHandlers : event["path"].endsWith('users') ? collectionHandlers : itemHandlers;

  let httpMethod = event["httpMethod"];
  if (httpMethod in handlers) {
    return await handlers[httpMethod](event, context);
  }

  return {
    ...
  };
};