By principle, Node.js acts as middleware for front-end software typically based on Angular or React
The need for database connection depends upon external libraries like TypeORM ☛, Prisma ☛, or Sequelize ☛ in the SQL world. TypeORM and Java Persistence API -JPA- ☛ are siblings in using annotations, i.e.,
@
, the former acting in the Node.js world while the latter is a key Java standard. Both TypeORM ☛ or Prisma ☛ target well-known RDBMS like SQLite ☛ or MariaDB ☛MongoDB ☛ NoSQL DBMS benefits from directly using the MongoDB ☛ Node.js driver ☛ even though both TypeORM ☛ or Prisma ☛ may deal with MongoDB ☛ as well
![]()
TypeORM ☛ ⤳ entities
import {Entity, PrimaryColumn, Column} from "typeorm"; import * as Joi from 'joi'; // create table CRIMINAL_CASE( // CRIMINAL_CASE_NUMBER varchar(10), // JURISDICTION_NAME varchar(30), // DATE_OF_CRIMINAL_CASE Date, // constraint CRIMINAL_CASE_key primary key(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME)); @Entity() export class CriminalCase { @PrimaryColumn({length: 10}) criminal_case_number: string; @PrimaryColumn({length: 30}) jurisdiction_name: string; @Column() date_of_criminal_case: Date; }
TypeORM ☛ ⤳ relationships
import {Entity, PrimaryColumn, Column, ManyToMany, ManyToOne, OneToMany, JoinTable, JoinColumn} from "typeorm"; import * as Joi from 'joi'; import {CriminalCase} from "../Entities/CriminalCase"; import {JudicialDecision} from "../Entities/JudicialDecision"; import {Motive} from "../Entities/Motive"; // create table PRISONER( // PRISON_FILE_NUMBER varchar(10), // GIVEN_NAME varchar(30), // SURNAME varchar(30), // DATE_OF_BIRTH Date, // PLACE_OF_BIRTH varchar(30), // DATE_OF_INCARCERATION Date, // CRIMINAL_CASE_NUMBER varchar(10) not null, // JURISDICTION_NAME varchar(30) not null, // MOTIVE_NUMBER varchar(10) not null, // constraint PRISONER_key primary key(PRISON_FILE_NUMBER), // constraint INCARCERATION_MAIN foreign key(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME) references CRIMINAL_CASE(CRIMINAL_CASE_NUMBER,JURISDICTION_NAME), // constraint INCARCERATION_MOTIVE foreign key(MOTIVE_NUMBER) references MOTIVE(MOTIVE_NUMBER)); @Entity() export class Prisoner { @PrimaryColumn({length: 10}) prison_file_number: string; @Column({length: 30}) given_name: string; @Column({length: 30}) surname: string; @Column() date_of_birth: Date; @Column() place_of_birth: string; @Column() date_of_incarceration: Date; @ManyToOne(type => CriminalCase) // '@ManyToOne' does not require '@OneToMany' @JoinColumn([{ name: "CRIMINAL_CASE_NUMBER", referencedColumnName: "criminal_case_number" // Name of attribute in class 'CriminalCase' }, { name: "JURISDICTION_NAME", referencedColumnName: "jurisdiction_name" // Name of attribute in class 'CriminalCase' }]) incarceration_main: CriminalCase; @ManyToOne(type => Motive) // '@ManyToOne' does not require '@OneToMany' @JoinColumn({name: "MOTIVE_NUMBER"}) incarceration_motive: Motive; @ManyToMany(type => CriminalCase) @JoinTable({ name: 'PRISONER_CRIMINAL_CASE', joinColumns: [{name: 'PRISON_FILE_NUMBER', referencedColumnName: 'prison_file_number'}], inverseJoinColumns: [{name: 'CRIMINAL_CASE_NUMBER', referencedColumnName: 'criminal_case_number'}, {name: 'JURISDICTION_NAME', referencedColumnName: 'jurisdiction_name'}] }) offense: CriminalCase[]; @OneToMany(type => JudicialDecision, judicial_decision => judicial_decision.prisoner) judicial_decision: JudicialDecision[]; }
TypeORM ☛ ⤳ connection configuration here…
import {ConnectionOptions, DataSource} from "typeorm"; const options: ConnectionOptions = { type: "sqlite", database: "./New_York_City_Penitentiary_database", entities: [ __dirname + "/js/Entities/*.js" ], logging: false, synchronize: false // Don't overwrite existing schema (and data)... }; const New_York_City_Penitentiary_database = new DataSource(options); New_York_City_Penitentiary_database.connect().then(connection => { …
MariaDB ☛ settings ☛
import "reflect-metadata"; // https://www.npmjs.com/package/reflect-metadata import { DataSource } from "typeorm"; import { Job_application } from "./entity/Job_application"; export const AppDataSource = new DataSource({ type: "mariadb", url: process.env.DATABASE_URL, // '.env.' file ⤳ 'DATABASE_URL="mariadb://franck:franck@localhost:3306/ft"' synchronize: true, logging: false, entities: [Job_application], migrations: [], subscribers: [], });
TypeORM ☛ ⤳ queries
// https://github.com/typeorm/typeorm/blob/master/docs/select-query-builder.md#using-subqueries web.get('/Under_remand', async (request, response) => { // 'web' is an Express app. // Ver. 1: // const prisoners: Prisoner[] = await database.getRepository(Prisoner).find({relations: ["judicial_decision"]}); // response.send(prisoners.filter(prisoner => prisoner.judicial_decision.filter(judicial_decision => judicial_decision.decision_type_number === "1").length === 0)); // Ver. 2 ('SELECT * FROM Prisoner WHERE prison_file_number NOT IN (SELECT prison_file_number FROM Conviction)'): const sub_query = await database.getRepository(Conviction).createQueryBuilder('conviction').select("conviction.prison_file_number"); const result = await database.getRepository(Prisoner).createQueryBuilder('prisoner') .where("prisoner.prison_file_number NOT IN (" + sub_query.getQuery() + ")") .getMany(); response.send(result); });
Exercise
- Implement Railcar control system case study here…