I am coding a serverless-kubeless api now for the mysql world database. I had to solve this problem yesterday. I arrived at the following solution. It's not feature complete. But you didn't ask for that. So here is a working GET endpoint which accepts various query parameters to customise the query.
'use strict';
const pool = require('./database');
module.exports.handler = async (event, context) => new Promise((resolve, reject) => {
let request = event.extensions.request;
let response = event.extensions.response;
try{
let handleResults = (err, results, fields) => {
if(err){
response.status(500).send({
success: false,
message: err.message,
});
}else{
response.status(200).send({
success: true,
count: results.length,
data: results,
});
}
}
if(typeof(request.query.id) !== "undefined"){
// search for a specific region by id
if (Number.isNaN(Number(request.query.id))) {
response.status(500).send({
success: false,
message: "id query param was not a number",
});
}
pool.query("select id,name,code,country_id from regions where id = ?", [request.query.id], handleResults);
}else if(typeof(request.query.country) !== "undefined"){
// search for a region list from a specific country
if (Number.isNaN(Number(request.query.country))) {
response.status(500).send({
success: false,
message: "country query param was not a number",
});
}
pool.query("select id,name,code,country_id from regions where country_id = ?", [request.query.country], handleResults);
}else{
response.status(400).send({
success: false,
message: "Could not find country, or region query parameter. Require a search term"
});
}
}catch(exception){
response.status(500).send({
success: false,
message: exception.message
});
}
});
and database.js:
const mysql = require("mysql");
const util = require('util');
const pool = mysql.createPool({
connectionLimit: 10,
host: process.env.DATABASE_HOSTNAME,
user: process.env.DATABASE_USERNAME,
port: process.env.DATABASE_PORT,
password: process.env.DATABASE_PASSWORD,
database: process.env.DATABASE_NAME,
});
pool.getConnection((err, connection) => {
if (err) {
if (err.code === 'PROTOCOL_CONNECTION_LOST') {
console.error('Database connection was closed.');
}
if (err.code === 'ER_CON_COUNT_ERROR') {
console.error('Database has too many connections.');
}
if (err.code === 'ECONNREFUSED') {
console.error('Database connection was refused.');
}
}
if (connection) connection.release();
return;
});
// Magic happens here.
pool.query = util.promisify(pool.query);
module.exports = pool;