Serverless code for deploying trade-net-v2 API endpoints via AWS

handler.js

'use strict'

const query = require('./query')
const { to } = require('await-to-js')
const middy = require('middy')
const { cors } = require('middy/middlewares')

const getCountries = async () => {
  let err
  let results
  const q = `
        SELECT country_code AS id, 
               country_name AS name
        FROM countries_v2
        ORDER BY country_name ASC
        `
  ;[err, results] = await to(query(q))
  if (err) {
    console.log(err)
    return {
      statusCode: err.statusCode || 500,
      headers: { 'Content-Type': 'text/plain' },
      body: 'Could not fetch the countries.',
    }
  } else {
    return {
      statusCode: 200,
      body: JSON.stringify(results),
    }
  }
}
module.exports.getCountriesHandler = middy(getCountries).use(cors())

const getCommodities = async () => {
  let err
  let results
  const q = `
        SELECT item_code AS id, 
               item_name AS name
        FROM commodities_v2
        ORDER BY item_name ASC
        `
  ;[err, results] = await to(query(q))
  if (err) {
    console.log(err)
    return {
      statusCode: err.statusCode || 500,
      headers: { 'Content-Type': 'text/plain' },
      body: 'Could not fetch the commodities.',
    }
  } else {
    return {
      statusCode: 200,
      body: JSON.stringify(results),
    }
  }
}

module.exports.getCommoditiesHandler = middy(getCommodities).use(cors())

const getElementsByCommodity = async event => {
  let err
  let results
  const q = `
        SELECT element_code AS id,
               element_name AS name
        FROM trade_matrix_v2
        WHERE item_code = $1
        GROUP BY element_code, element_name
        ORDER BY element_name ASC
        `
  const params = [event.queryStringParameters.item_code]
  ;[err, results] = await to(query(q, params))
  if (err) {
    console.log(err)
    return {
      statusCode: err.statusCode || 500,
      headers: { 'Content-Type': 'text/plain' },
      body: 'Could not fetch the elements.',
    }
  } else {
    return {
      statusCode: 200,
      body: JSON.stringify(results),
    }
  }
}
module.exports.getElementsByCommodityHandler = middy(getElementsByCommodity).use(cors())

const getTradeRelationships = async event => {
  let err
  let results
  const q = `
        SELECT reporter_country_code,
               partner_country_code,
               unit_name,
               year_2012,
               year_2013,
               year_2014,
               year_2015,
               year_2016,
               year_2017,
               year_2018,
               year_2019,
               year_2020,
               year_2021,
               year_2022,
               year_2023
        FROM trade_matrix_v2
        WHERE item_code = $1  
              AND element_code = $2
        `
  const params = [event.queryStringParameters.item_code, event.queryStringParameters.element_code]
  ;[err, results] = await to(query(q, params))
  if (err) {
    console.log(err)
    return {
      statusCode: err.statusCode || 500,
      headers: { 'Content-Type': 'text/plain' },
      body: 'Could not fetch the trade relationships.',
    }
  } else {
    return {
      statusCode: 200,
      body: JSON.stringify(results),
    }
  }
}
module.exports.getTradeRelationshipsHandler = middy(getTradeRelationships).use(cors())

package.json

{
  "name": "trade-net-v2-api",
  "version": "2.0.0",
  "description": "Serverless code for deploying trade-net-v2 API endpoints via AWS",
  "main": "handler.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "await-to-js": "3.0.0",
    "middy": "0.36.0",
    "pg": "8.14.1"
  },
  "devDependencies": {
    "serverless-offline": "14.4.0",
    "serverless-secrets-plugin": "0.1.0"
  }
}

query.js

const { Pool } = require('pg')

const pool = new Pool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  port: 5432,
  database: process.env.DB_NAME,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
  ssl: true,
})

module.exports = async (sql, params = []) => {
  const client = await pool.connect()
  try {
    const result = await client.query(sql, params)
    return result && result.rows instanceof Array ? result.rows : []
  } catch (err) {
    throw err
  } finally {
    client.release(true)
  }
}

secrets.dev.yml

DB_NAME: <db_name>
DB_USER: <db_owner>
DB_HOST: <db_host>
DB_PASSWORD: <db_password>

serverless.yml

service: trade-net-api

custom:
  secrets: ${file(secrets.dev.yml)}

provider:
  name: aws
  apiGateway:
    minimumCompressionSize: 1024
  runtime: nodejs18.x
  timeout: 30
  region: us-east-1
  environment:
    DB_NAME: ${self:custom.secrets.DB_NAME}
    DB_USER: ${self:custom.secrets.DB_USER}
    DB_PASSWORD: ${self:custom.secrets.DB_PASSWORD}
    DB_HOST: ${self:custom.secrets.DB_HOST}

functions:
  getTradeRelationships:
    handler: handler.getTradeRelationshipsHandler
    description: Retrieve trade relationships between countries for a specified commodity and element
    events:
      - http:
          path: relationships
          method: get
          cors: true
          request:
            parameters:
              querystrings:
                item_code: true
                element_code: true
  getCountries:
    handler: handler.getCountriesHandler
    description: Retrieve all available countries
    events:
      - http:
          path: countries
          method: get
          cors: true
  getElementsByCommodity:
    handler: handler.getElementsByCommodityHandler
    description: Retrieve all available element types for the specified commodity
    events:
      - http:
          path: elements
          method: get
          cors: true
          request:
            parameters:
              querystrings:
                item_code: true
  getCommodities:
    handler: handler.getCommoditiesHandler
    description: Retrieve all available commodities
    events:
      - http:
          path: commodities
          method: get
          cors: true

plugins:
  - serverless-offline
  - serverless-secrets-plugin