⚡ Node Sequelize PostgreSQL

• Node.js + Express used with Sequelize Object-Relational Mapping (ORM) to perform promise-based Create, Read, Update & Delete (CRUD) operations on linked data tables in a PostgreSQL database
• Note: to open web links in a new window use: ctrl+click on link

📄 Table of contents

• ⚡ Node Sequelize PostgreSQL
  • 📄 Table of contents
  • 📚 General info
  • 📷 Screenshots
  • 📶 Technologies
  • 💾 Setup
  • 🔧 Testing
  • 💻 Code Examples
  • 🆒 Features
  • 📋 Status & To-Do List
  • 👏 Inspiration
  • 📁 License
  • ✉️ Contact

📚 General info

• SQL database data based on Sequelize Project and Workpackage models

• Node.js project structure best practises observed with separate routes & controller code

• The use of Projects and Workpackages is based on my Engineering experience in Norway where all Maintenance and Modification projects are documented in engineering workpackages that all have a unique workpackage id number and are tied to a project using the project id number

• Project Structure:

├── package.json
└── src
  ├── app.js
  ├── controllers
  │  ├── projects.controller.js
  │  └── workpackages.controller.js
  ├── db
  │  └── database.js
  ├── index.js
  ├── models
  │  ├── Project.js
  │  └── Workpackage.js
  └── routes
    ├── projects.routes.js
    └── workpackages.routes.js

• Database Overview:

📷 Screenshots

📶 Technologies

• Node.js v16 Javascript runtime using the Chrome V8 engine
• Express v4 web framework for node
• Sequelize v6 TypeScript and Node.js Object-relational mapping (ORM) for Postgres, MySQL, MariaDB, SQLite and SQL Server
• Thunder Client lightweight REST Client used to test CRUD operations
• DBeaver relational database tool used to connect to a PostgreSQL database
• PostgreSQL v14 object-relational database
• DB Diagram used to create the Database Overview
• Structure-codes CLI used to create the Project Structure
• morgan v1 HTTP request logger middleware for node.js

💾 Setup

• Assuming you have PostgreSQL database installed, install DBeaver and connect to your PostgreSQL database using DBeaver
• npm i to install dependencies
• Create .env and add database credentials - see .example.env
• npm run dev runs app in the development mode with auto-restart.
• Open http://localhost:3000/projects to see projects list in browser
• Open http://localhost:3000/workpackages to see workpackages list in browser
• CRUD operations can be performed using the Thunder Client VS Code extension
• PostgreSQL console can be used to work with database: \c projects to connect to projects database, \dt to list tables, SELECT * FROM projects; to see projects table

🔧 Testing

• All CRUD functions tested using Thunder Client

💻 Code Examples

• models/Workpackage.js Workpackage model using Sequelize.define

export const Workpackage = sequelize.define(
  "workpackages",
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    },
    title: {
      type: DataTypes.STRING,
    },
    description: {
      type: DataTypes.STRING,
    },
    completed: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
    checked: {
      type: DataTypes.BOOLEAN,
      defaultValue: false,
    },
  },
  {
    timestamps: false,
  }
);

🆒 Features

• Sequelize is easy to learn and the database synchronisation function is useful

📋 Status & To-Do List

• Status: Working
• To-Do: Optional: add frontend cards or table to display data - could use EJS

👏 Inspiration

• Sequelize documentation: Model Basics
• Sequelize documentation: Model Querying finders

📁 License

• This project is licensed under the terms of the MIT license.

✉️ Contact

• Repo created by ABateman, email: gomezbateman@yahoo.com