Documentation
Architecture
SKILL-db API Containerized
Docker Compose
Docker Compose is a tool that allows you to define and run multi-container Docker applications. It uses a YAML file to configure and manage multiple Docker containers as a single application. With Docker Compose, you can easily define the services, networks, and volumes required for your application and run them with a single command.
Docker
Docker is an open-source platform that enables you to automate the deployment, scaling, and management of applications using containerization. Containers are lightweight, isolated environments that contain everything needed to run an application, including the code, runtime, system tools, and libraries. Docker allows you to build, ship, and run applications consistently across different environments, providing portability and flexibility.
Docker-compose is used to build this API.
version: '2'
services:
sfia-db:
image: postgres:15
container_name: db
restart: always
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
env_file:
- database.env
ports:
- 5432:5432
node-api:
image: node:alpine
container_name: api
working_dir: /src
user: "node"
restart: always
volumes:
- ./api:/src
- ./api/dist:/src/dist
env_file:
- database.env
ports:
- 443:443
- 3000:3000
command: sh -c "npm install && npm run build && node dist/index.js"
Skill Framework Data in PostgreSQL
PostgreSQL
PostgreSQL is a robust and highly extensible open-source relational database management system (RDBMS). It provides advanced features and capabilities, making it suitable for a wide range of applications. PostgreSQL supports ACID properties, ensuring data integrity, and offers a comprehensive set of SQL functions and operators.
With its rich feature set, PostgreSQL enables efficient management of large datasets, supports advanced functionalities like indexing and transactions, and provides support for spatial data, JSON, and full-text search. It is highly customizable through extensions and plugins, allowing developers to tailor the database to their specific needs.
SKILL-db PostgreSQL structure
Table: sfia8_skills
| Column Name | Data Type | Description |
|---|---|---|
| id | character(4) | Unique identifier |
| title | character varying(64) | Title of the skill |
| category | character varying(64) | Category of the skill |
| subcategory | character varying(64) | Subcategory of the skill |
| gen_description | character varying(1024) | General description of the skill |
| l1_description | character varying(1024) | Level 1 description of the skill |
| l2_description | character varying(1024) | Level 2 description of the skill |
| l3_description | character varying(1024) | Level 3 description of the skill |
| l4_description | character varying(1024) | Level 4 description of the skill |
| l5_description | character varying(1024) | Level 5 description of the skill |
| l6_description | character varying(1024) | Level 6 description of the skill |
| l7_description | character varying(1024) | Level 7 description of the skill |
Table: soft_skills
| Column Name | Data Type | Description |
|---|---|---|
| id | character(4) | Unique identifier |
| title | character varying(64) | Title of the soft skill |
| category | character varying(64) | Category of the soft skill |
| gen_description | character varying(1024) | General description of the soft skill |
Table: sfia8_responsibilities
| Column Name | Data Type | Description |
|---|---|---|
| id | character(4) | Unique identifier |
| title | character varying(16) | Title of the responsibility |
| l1_description | character varying(2048) | Level 1 description of the responsibility |
| l2_description | character varying(2048) | Level 2 description of the responsibility |
| l3_description | character varying(2048) | Level 3 description of the responsibility |
| l4_description | character varying(2048) | Level 4 description of the responsibility |
| l5_description | character varying(2048) | Level 5 description of the responsibility |
| l6_description | character varying(2048) | Level 6 description of the responsibility |
| l7_description | character varying(2048) | Level 7 description of the responsibility |
Technologies
REST
REST (Representational State Transfer) is an architectural style for designing networked applications. It emphasizes a stateless client-server communication model, where clients send requests to servers to perform operations on resources. RESTful APIs are designed around standard HTTP methods (GET, POST, PUT, DELETE) and use resource-based URLs to access and manipulate data. REST allows for scalability, simplicity, and interoperability, making it a widely adopted approach for building web services.
TypeScript
TypeScript is a statically typed superset of JavaScript that compiles to plain JavaScript. It introduces static type checking, which enables developers to catch errors and bugs during development. TypeScript enhances JavaScript with features such as static types, interfaces, classes, and modules, providing better tooling and scalability to JavaScript projects. It is widely used for building large-scale applications and is supported by popular frameworks like Angular and React.
Nginx
Nginx is a high-performance, open-source web server and reverse proxy server. It is known for its scalability, stability, and efficient handling of concurrent connections. Nginx is often used as a load balancer, caching server, and SSL/TLS terminator. It can also serve as a web server for static files or as a reverse proxy to distribute requests to multiple backend servers. Nginx is highly configurable and widely used to improve the performance and reliability of web applications.
Express
Express.js is a minimal and flexible web application framework for Node.js. It provides a robust set of features for building web applications and APIs. Express offers a simple and intuitive syntax, allowing developers to create routes, handle requests and responses, and manage middleware. It is widely used for developing server-side applications and RESTful APIs in Node.js. Express has a large ecosystem of extensions and middleware that enhance its functionality.
Dependencies
The SKILL-db API utilizes the following dependencies:
- Express: A fast and minimalist web application framework for Node.js.
- swagger-ui-express: Middleware for serving Swagger UI to visualize and interact with the API documentation.
const express = require('express')
const swaggerUi = require('swagger-ui-express')
import { swaggerSpec } from './scripts/swagger'
import { skills } from './routes/skills'
import { responsibilities } from './routes/responsibilities'
import { schema } from './routes/schema'
Server Setup
The server is set up using Express to handle incoming requests. The API is configured to listen on port 3000.
export const app = express()
const port = 3000
app.listen(port, () => {
console.log(`[server]: Server is running at http://localhost:${port}`)
});
Routes and Swagger Setup
The API routes for skills, responsibilities, and schema are defined and used in the Express app.
app.use('/skills', skills)
app.use('/responsibilities', responsibilities)
app.use('/schema', schema)
Additionally, Swagger UI is set up to provide API documentation at the '/api-docs' endpoint.
app.use('/api-docs', swaggerUi.serve, swaggerUi.setup(swaggerSpec))
Code with good comments
package.json
{
"name": "api",
"version": "1.0.0",
"description": "API for SFIA database",
"main": "index.ts",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1",
"build": "npx tsc",
"start": "node dist/index.js"
},
"author": "",
"license": "ISC",
"devDependencies": {
"@types/express": "^4.17.17",
"@types/node": "^20.2.5",
"@types/pg": "^8.10.1",
"@types/swagger-jsdoc": "^6.0.1",
"@types/swagger-ui-express": "^4.1.3",
"dotenv": "^16.0.3",
"express": "^4.18.2",
"typescript": "^5.0.4"
},
"dependencies": {
"fs": "^0.0.1-security",
"http-status-codes": "^2.2.0",
"install": "^0.13.0",
"path": "^0.12.7",
"pg": "^8.11.0",
"swagger-jsdoc": "^6.2.8",
"swagger-ui-express": "^4.6.3"
},
"keywords": []
}
routes/responsibilities.ts
//dependicies
const express = require('express')
import { NextFunction, Request, Response } from "express"
import { StatusCodes } from 'http-status-codes'
import pg from "../scripts/postgresql"
//route setup
export const responsibilities = express.Router()
/**
* @swagger
* /responsibilities:
* get:
* summary: Retrieve a list of all responsibilities.
* description: Retrieve a list of all responsibilities from the responsibilities table.
* responses:
* 200:
* description: A list of all responsibilities.
* content:
* application/json:
* schema:
* type: object
* properties:
* data:
* type: array
* items:
* type: object
* properties:
* id:
* type: string
* description: Responsibility ID.
* example: auto
* title:
* type: string
* description: Responsibility title.
* example: Autonomy
* l1_description:
* type: string
* description: Responsibility level 1 description.
* example: Works under close direction. Uses little discretion in attending to enquiries. Is expected to seek guidance in unexpected situations.
* l2_description:
* type: string
* description: Responsibility level 2 description.
* example: Works under routine direction. Uses limited discretion in resolving issues or enquiries. Determines when to seek guidance in unexpected situations. Plans own work within short time horizons.
* l3_description:
* type: string
* description: Responsibility level 3 description.
* example: Works under general direction. Receives specific direction, accepts guidance and has work reviewed at agreed milestones. Uses discretion in identifying and responding to complex issues related to own assignments. Determines when issues should be escalated to a higher level. Plans and monitors own work (and that of others where applicable) competently within limited deadlines.
* l4_description:
* type: string
* description: Responsibility level 4 description.
* example: Works under general direction within a clear framework of accountability. Exercises substantial personal responsibility and autonomy. Uses substantial discretion in identifying and responding to complex issues and assignments as they relate to the deliverable/scope of work. Escalates when issues fall outside their framework of accountability. Plans, schedules and monitors work to meet given objectives and processes to time and quality targets.
* l5_description:
* type: string
* description: Responsibility level 5 description.
* example: Works under broad direction. Work is often self-initiated. Is fully responsible for meeting allocated technical and/or group objectives. Analyses, designs, plans, executes and evaluates work to time, cost and quality targets. Establishes milestones and has a significant role in the assignment of tasks and/or responsibilities..
* l6_description:
* type: string
* description: Responsibility level 6 description.
* example: Has defined authority and accountability for actions and decisions within a significant area of work, including technical, financial and quality aspects. Establishes organisational objectives and assigns responsibilities.
* l7_description:
* type: string
* description: Responsibility level 7 description.
* example: At the highest organisational level, has authority over all aspects of a significant area of work, including policy formation and application. Is fully accountable for actions taken and decisions made, both by self and others to whom responsibilities have been assigned.
* 404:
* description: Responsibilities are not found.
* 500:
* description: Internal server error.
*/
responsibilities.get('/', async (req: Request, res: Response, next: NextFunction) => {
try {
let rows: string[] = await pg.responsibilities()
if (rows.length === 0) {
const error: any = new Error(`No responsibilities found.`)
error.statusCode = StatusCodes.NOT_FOUND
throw error
}
res.status(StatusCodes.OK).json(rows)
} catch (err) {
next(err) //pass the error to the error handling middleware
}
})
//error handling middleware
responsibilities.use((err: any, req: Request, res: Response, next: NextFunction) => {
console.error(err)
//determine an appropriate status code based on the error
const statusCode = err.statusCode || StatusCodes.INTERNAL_SERVER_ERROR
//return the error message with the appropriate status code
res.setHeader('Content-Type', 'application/json')
const errorResponse = {
error: err.message,
statusCode: statusCode,
}
res.status(statusCode).json(errorResponse)
})
routes/schema.ts
//dependicies
const express = require('express')
import { NextFunction, Request, Response } from "express"
import { StatusCodes } from 'http-status-codes'
import path from 'path'
import fs from 'fs'
//route setup
export const schema = express.Router()
/**
* @swagger
* /schema:
* get:
* summary: Retrieve the database schema in PNG format.
* description: |
* <img src="/schema" alt="Database Schema">
* responses:
* 200:
* description: Database schema in PNG format.
* content:
* image/png:
* schema:
* type: string
* format: binary
* 404:
* description: File not found.
* 500:
* description: Internal server error.
*/
schema.use('/', async (req: Request, res: Response, next: NextFunction) => {
const filePath = path.join(__dirname, '../../assets/database_schema.png')
try {
// Check if the file exists
if (fs.existsSync(filePath)) {
res.status(StatusCodes.OK).sendFile(filePath)
} else {
res.status(StatusCodes.NOT_FOUND).send('File not found')
}
} catch (err) {
next(err) //pass the error to the error handling middleware
}
})
//error handling middleware
schema.use((err: any, req: Request, res: Response, next: NextFunction) => {
console.error(err)
//determine an appropriate status code based on the error
const statusCode = err.statusCode || StatusCodes.INTERNAL_SERVER_ERROR
//return the error message with the appropriate status code
res.setHeader('Content-Type', 'application/json')
const errorResponse = {
error: err.message,
statusCode: statusCode,
}
res.status(statusCode).json(errorResponse)
})
routes/skills.ts
//dependicies
const express = require('express')
import { NextFunction, Request, Response } from "express"
import { StatusCodes } from 'http-status-codes'
import pg from "../scripts/postgresql"
//route setup
export const skills = express.Router()
/**
* @swagger
* /skills:
* get:
* summary: Retrieve a list of skills.
* description: Retrieve a list of skills from the skills table.
* parameters:
* - in: query
* name: softskills
* schema:
* type: boolean
* enum: [True, False]
* description: If is set to true, a list of all soft skills from the soft skills table is attached to the response.
* required: false
* responses:
* 200:
* description: A list of skills.
* content:
* application/json:
* schema:
* type: object
* properties:
* data:
* type: array
* items:
* type: object
* properties:
* id:
* type: string
* description: Skill ID.
* example: rsch
* title:
* type: string
* description: Skill title.
* example: Research
* category:
* type: string
* description: Skill category.
* example: Strategy and architecture
* subcategory:
* type: string
* description: Skill subcategory.
* example: Strategy and planning
* gen_description:
* type: string
* description: Skill general description.
* example: Systematically creating new knowledge by data gathering, innovation, experimentation, evaluation and dissemination.
* l1_description:
* type: string
* description: Skill level 1 description.
* example: null
* l2_description:
* type: string
* description: Skill level 2 description.
* example: Within given research goals, assists in selection and review of credible and reliable resources. Searches for relevant material using specialised websites and sources, reads relevant articles to update knowledge of the relevant field. Reports on work carried out and may contribute sections of publication-quality material. Curates, under guidance, a personal collection of relevant material.
* l3_description:
* type: string
* description: Skill level 3 description.
* example: Within given research goals, builds on and refines appropriate outline ideas for research, including evaluation, development, demonstration and implementation. Applies standard methods to collect and analyse quantitative and qualitative data. Creates research reports to communicate research methodology, findings and conclusions. Contributes sections of publication-quality material. Uses available resources to update knowledge of any relevant field and curates a personal collection of relevant material. Participates in research communities.
* l4_description:
* type: string
* description: Skill level 4 description.
* example: Builds on and refines appropriate outline ideas for the evaluation, development, demonstration and implementation of research. Contributes to research goals and funding proposals. Collects and analyses qualitative and quantitative data as required. Contributes to research plans and identifies appropriate opportunities for publication and dissemination of research findings. Makes an active contribution to research communities. Presents papers at conferences, contributes significant sections of publication-quality material, and presents reports to clients.
* l5_description:
* type: string
* description: Skill level 5 description.
* example: Agrees research goals and methods and performs research projects to generate original ideas. Attracts and manages external research funding. Maintains a strong external network within own area of specialism. Provides advice and guidance on performing research. Selects, adopts and adapts data collection tools and techniques. Develops, reviews and constructively criticises the research and ideas of others. Shares practical demonstrations of research findings. Takes part in professional activities outside own employing organisation. Presents papers at significant conferences, writes articles for specialist journals, and presents reports to key stakeholders.
* l6_description:
* type: string
* description: Skill level 6 description.
* example: Develops the organisation's research policy and supervises the work of research functions. Promotes activities externally, attracts and manages significant portfolios of research funding. Sets research goals and authorises research proposals. Leads strategic and/or interdisciplinary research projects. Maintains a strong external network reaching beyond own immediate area of specialism. Takes a leading part in professional activities outside own employing organisation. Presents keynote papers at major conferences, writes articles for high impact journals, and presents reports to major clients.
* l7_description:
* type: string
* description: Skill level 7 description.
* example: null
* 404:
* description: Skills are not found.
* 500:
* description: Internal server error.
*/
skills.get('/', async (req: Request, res: Response, next: NextFunction) => {
let softskills = req.query.softskills ? (req.query.softskills as string).toLowerCase() === 'true' : false
try {
let rows: string[] = await pg.skills(softskills)
if (rows.length === 0) {
const error: any = new Error(`No skills found.`)
error.statusCode = StatusCodes.NOT_FOUND
throw error
}
res.status(StatusCodes.OK).json(rows)
} catch (err) {
next(err) //pass the error to the error handling middleware
}
})
/**
* @swagger
* /skills/category:
* get:
* summary: Retrieve a filtered list of skills.
* description: Retrieve a list of skills from the database based on a combination of provided category and subcategory parameters. At least one parameter should be provided.
* parameters:
* - in: query
* name: category
* schema:
* type: string
* enum: ['Strategy and architecture', 'Change and transformation', 'Development and implementation', 'Delivery and operation', 'People and skills', 'Relationships and engagement', 'Soft skills']
* description: Filters and returns skills based on a specified category.
* required: false
* - in: query
* name: subcategory
* schema:
* type: string
* enum: ['Strategy and planning', 'Security and privacy', 'Governance, risk and compliance', 'Advice and guidance', 'Change implementation', 'Change analysis', 'Change planning', 'Systems development', 'Data and analytics', 'User experience', 'Content management', 'Computational science', 'Technology management', 'Service management', 'Security services', 'People management', 'Skills management', 'Stakeholder management', 'Sales and marketing']
* description: Filters and returns skills based on a specified subcategory. If category="Soft skills", this query is ignored.
* required: false
* responses:
* 200:
* description: A list of skills.
* content:
* application/json:
* schema:
* type: object
* properties:
* data:
* type: array
* items:
* type: object
* properties:
* id:
* type: string
* description: Skill ID.
* example: rsch
* title:
* type: string
* description: Skill title.
* example: Research
* category:
* type: string
* description: Skill category.
* example: Strategy and architecture
* subcategory:
* type: string
* description: Skill subcategory.
* example: Strategy and planning
* gen_description:
* type: string
* description: Skill general description.
* example: Systematically creating new knowledge by data gathering, innovation, experimentation, evaluation and dissemination.
* l1_description:
* type: string
* description: Skill level 1 description.
* example: null
* l2_description:
* type: string
* description: Skill level 2 description.
* example: Within given research goals, assists in selection and review of credible and reliable resources. Searches for relevant material using specialised websites and sources, reads relevant articles to update knowledge of the relevant field. Reports on work carried out and may contribute sections of publication-quality material. Curates, under guidance, a personal collection of relevant material.
* l3_description:
* type: string
* description: Skill level 3 description.
* example: Within given research goals, builds on and refines appropriate outline ideas for research, including evaluation, development, demonstration and implementation. Applies standard methods to collect and analyse quantitative and qualitative data. Creates research reports to communicate research methodology, findings and conclusions. Contributes sections of publication-quality material. Uses available resources to update knowledge of any relevant field and curates a personal collection of relevant material. Participates in research communities.
* l4_description:
* type: string
* description: Skill level 4 description.
* example: Builds on and refines appropriate outline ideas for the evaluation, development, demonstration and implementation of research. Contributes to research goals and funding proposals. Collects and analyses qualitative and quantitative data as required. Contributes to research plans and identifies appropriate opportunities for publication and dissemination of research findings. Makes an active contribution to research communities. Presents papers at conferences, contributes significant sections of publication-quality material, and presents reports to clients.
* l5_description:
* type: string
* description: Skill level 5 description.
* example: Agrees research goals and methods and performs research projects to generate original ideas. Attracts and manages external research funding. Maintains a strong external network within own area of specialism. Provides advice and guidance on performing research. Selects, adopts and adapts data collection tools and techniques. Develops, reviews and constructively criticises the research and ideas of others. Shares practical demonstrations of research findings. Takes part in professional activities outside own employing organisation. Presents papers at significant conferences, writes articles for specialist journals, and presents reports to key stakeholders.
* l6_description:
* type: string
* description: Skill level 6 description.
* example: Develops the organisation's research policy and supervises the work of research functions. Promotes activities externally, attracts and manages significant portfolios of research funding. Sets research goals and authorises research proposals. Leads strategic and/or interdisciplinary research projects. Maintains a strong external network reaching beyond own immediate area of specialism. Takes a leading part in professional activities outside own employing organisation. Presents keynote papers at major conferences, writes articles for high impact journals, and presents reports to major clients.
* l7_description:
* type: string
* description: Skill level 7 description.
* example: null
* 400:
* description: Both category and subcategory queries are missing. Define at least one query.
* 404:
* description: Skills are not found.
* 500:
* description: Internal server error.
*/
skills.get('/category', async (req: Request, res: Response, next: NextFunction) => {
const category = req.query.category as string
const subcategory = req.query.subcategory as string
try {
let rows: string[]
if (category && subcategory) {
rows = await pg.skills_category_subcategory(category, subcategory)
} else if (category) {
rows = await pg.skills_category(category)
} else if (subcategory) {
rows = await pg.skills_subcategory(subcategory)
} else {
const error: any = new Error('Category and subcategory are missing. Define at least one query.')
error.statusCode = StatusCodes.BAD_REQUEST
throw error
}
if (rows.length === 0) {
const error: any = new Error(`No skills found${category ? ' for the specified category' : ''}${subcategory ? ' for the specified subcategory' : ''}.`)
error.statusCode = StatusCodes.NOT_FOUND
throw error
}
res.status(StatusCodes.OK).json(rows)
} catch (err) {
next(err) //pass the error to the error handling middleware
}
})
//error handling middleware
skills.use((err: any, req: Request, res: Response, next: NextFunction) => {
console.error(err)
//determine an appropriate status code based on the error
const statusCode = err.statusCode || StatusCodes.INTERNAL_SERVER_ERROR
//return the error message with the appropriate status code
res.setHeader('Content-Type', 'application/json')
const errorResponse = {
error: err.message,
statusCode: statusCode,
}
res.status(statusCode).json(errorResponse)
})
scripts/postgresql.ts
//dependicies
import { Pool } from "pg"
const pool = new Pool({
host: "db",
port: Number(process.env.POSTGRES_PORT),
database: process.env.POSTGRES_DB,
user: process.env.POSTGRES_USER,
password: process.env.POSTGRES_PASSWORD,
});
//fetches all data in the skills table
const skills = async (softskills: boolean) => {
let query = 'SELECT * FROM sfia8_skills'
if (softskills) query += ` UNION SELECT
id, title, category,
Null as subcategory,
gen_description,
Null as l1_description,
Null as l2_description,
Null as l3_description,
Null as l4_description,
Null as l5_description,
Null as l6_description,
Null as l7_description
FROM soft_skills`
return (await pool.query(query)).rows
}
//fetches all skills from the sfia8_skills or soft_skills table filtered based on a given category
const skills_category = async (category: string) => {
category = category.toLowerCase()
const table = category === "soft skills" ? "soft_skills" : "sfia8_skills"
return (await pool.query(`SELECT * FROM ${table} WHERE lower(category) = $1`, [category])).rows
}
//fetches skills from the database filtered based on a given category and subcategory
const skills_category_subcategory = async (category: string, subcategory: string) => {
category = category.toLowerCase()
subcategory = subcategory.toLowerCase()
subcategory = category === "soft skills" ? "*" : subcategory
return (await pool.query(`SELECT * FROM sfia8_skills WHERE lower(category) = $1 AND lower(subcategory) = $2`, [category, subcategory])).rows
}
//fetches all skills from sfia8_skills filtered based on a given subcategory
const skills_subcategory = async (subcategory: string) => {
subcategory = subcategory.toLowerCase()
return (await pool.query(`SELECT * FROM sfia8_skills WHERE lower(subcategory) = $1`, [subcategory])).rows
}
//fetches all responsibilities from the sfia8_responsibilities table
const responsibilities = async () => {
return (await pool.query(`SELECT * FROM sfia8_responsibilities`)).rows
}
export default { skills, skills_category, skills_category_subcategory, skills_subcategory, responsibilities }
scripts/swagger.ts
//dependicies
const swaggerJsdoc = require('swagger-jsdoc')
//swagger specs
const options = {
definition: {
openapi: '3.0.0',
info: {
title: 'Express API for the SKILL database.',
version: '1.0.0',
description: 'This is the documentation of the REST API made for the SKILL database server. The API and the database are developed by the IoTitude team during WIMMA Lab 2023.',
license: {
name: 'Licensed Under MIT',
url: 'https://spdx.org/licenses/MIT.html',
},
contact: {
name: 'WIMMA Lab',
url: 'https://www.wimmalab.org',
},
},
servers: [
{
url: 'http://localhost:3000',
description: 'Development server',
},
],
},
apis: ['./dist/routes/*.js'] // files containing annotations
}
export const swaggerSpec = swaggerJsdoc(options) //initialize swagger-jsdoc -> returns validated swagger spec in json format