Database tiers with Node.js ⤳ SQL



Resources
Database tiers (a.k.a. back-end software)

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 here… even though both TypeORM or Prisma may deal with MongoDB as well.

SQL ⤳ TypeORM

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 => { …

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