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 ☛ 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 ☛ 

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