DEV Community: Yoshiaki Matsutomo

TypeORM - Prevent SQL Injection with Node.js, React and TypeScript

Yoshiaki Matsutomo — Tue, 08 Jun 2021 12:11:50 +0000

Introduction

I have demonstrated some data manipulations with ORM(Object-Relational Mapping) frameworks such as TypeORM so far. Today's topic is still cases with TypeORM. However, it focuses on the security, which is my main field at the moment. I think you understand why I select topics regarding complex queries, performance, and the difference from the Microsoft ecosystem.

Demo Application

I developed a demo application in GitHub.

Demo Application for SQL injection tutorials

Utilization of ORM is Safe?

Some experienced developers mention that ORM(Object-Relational Mapping) frameworks such as TypeORM support prevent SQL injection, which is a traditional security matter. However, I still find high-risk cases with ORM frameworks in GitHub repositories, public project spaces, or in my work experience.

Example High-Risk Code with TypeORM

This is a typical high-risk case with TypeORM even though they use the ORM framework that prevents the SQL injection.



    // TO-DO temporary solution
    // I need to update it when I have a spare time
    const users = await this.userRepository.query(
      `SELECT user.id as id,
        user.firstName as firstName,
        user.lastName as lastName,
        user.age as age,
        user.clientId as clientId,
        user.createdDateTime as createdDateTime,
        user.updatedDateTime as updatedDateTime,
        Client.companyName as 'clientCompanyName',
        Client.createdDateTime as 'clientCreatedDateTime',
        Client.updatedDateTime as 'clientUpdatedDateTime'
      FROM User INNER JOIN Client ON user.clientId = Client.id 
      WHERE clientId = ${clientId} 
        AND (firstName LIKE '%${name}%' 
        OR lastName LIKE '%${name}%')`
    );

Why ORM is Safe?

Let's step back once. We should review the traditional way of executing SQL.




const users = await connection.query(
  `SELECT * 
   FROM users 
   WHERE clientId = ${clientId} 
    AND name LIKE %${name}%;`);

await connection.end();

How do you think if we enter %' OR 1=1 -- in the form to search users?

The method accepts the value, and the generated SQL is:



SELECT * 
FROM users 
WHERE clientId = 1 
  AND name LIKE '%%' OR 1=1 -- %;

The application should return only users of a client ID, 1, but this actually returns all users including the other clients'.

On the other hand, most developers basically utilize ORM frameworks in the modern age. Below is the case with TypeORM.



await this.userRepository.find({
  where: [
    { clientId: clientId, 
      name: Like(`%${name}%`) },
  ],
});

The code generates the same SQL code as
SELECT * FROM users WHERE clientId = 1 AND name LIKE '%{{name}}%';. However, ORM frameworks treat the value, name as prepared statements, not partial SQL syntaxes. Of course, the return value is empty.

POINT: Prepared Statements
The certain values are left unspecified when the SQL is sent and compiled. The application binds the values to the parameters, and the database executes the statement.

This is Serious Vulnerability?

Some people do not think this is a critical issue. They might say:

Hey Yoshi! Do not worry too much. The application just displays user's information which does not include their privacy, such as their password. Take it easy!

UNION-based SQL Injection

If an attacker notices that the system has a security hole here, they will enter %' OR 1=1 UNION SELECT 'fakeName' as companyName, email as firstName, password as lastName FROM Login; -- to get login details. "First Name" column has user's login email addresses and "Last Name" column has their password.

POINT: External Authentication
Some enterprise applications utilize external authentication provided by Firebase Auth and Auth0. However, I also know that some companies still manage legacy applications with old-fashioned frameworks, which are not supported by them.

POINT: Password Encryption
The sample app stores the password as plain text, which is not encrypted, and you might think that you are okay because your app encrypts critical data, but do you adopt the latest encryption methodology?

This is Still Serious Vulnerability?

Some people still do not think that this is a critical issue, and they might say:

Hey Yoshi! Do not worry! Crackers have no idea about all table schemas including a login table. If not, they are not able to execute it. Take it easy, man!

For SQLite, they will enter %' OR 1=1 UNION SELECT 'fakeName' as companyName, sql as firstName, 'fakelastname' as lastName FROM sqlite_master; -- to show table schemas. "First Name" column has tables' information.

Solution

The first code should be replaced by the below code. Yes, this is simple.



     const users = await this.userRepository.find({
      where: [
        { clientId: clientId, firstName: Like(`%${name}%`) },
        { clientId: clientId, lastName: Like(`%${name}%`) },
      ],
      relations: ["client"]
    });

Causes of Vulnerability

Some C-level or managers assume that the cause is the lack of education for junior developers, and they attempt having the security training or pair-work with senior engineers.

Is this true?

The fundamental knowledge of secure coding is mandatory. However, to be honest with you, this might be one of the reasons and I have to conclude this is not correct. I deeply investigated and found some potential causes.

Lack of Third-party's Module Information

I moved from the Microsoft development world to OSS, which is the development in Node.js/TypeScript with third-party modules. I think that their official documents provide too general and basic information only in comparison with Microsoft.

They basically provide the cases with detailed examples and combination of libraries. It is easy because most libraries are provided by Microsoft themselves!

For instance, with TypeORM, I found the below code that developers use the official ways for simple code. However, if they have to write complex data operations such as creating join tables or executing with subqueries, they copy and paste the raw SQL and leave it once.



import { Service, Token } from "typedi";
import { Repository, UpdateResult } from "typeorm";
import { InjectRepository } from "typeorm-typedi-extensions";
import { Client } from "../entity/Client";
import { User } from "../entity/User";

export interface IRiskyUserUsecase {
  getAll(clientId: number): Promise<User[]>;
  save(user: User): Promise<User>;
  update(id: number, user: User): Promise<UpdateResult>;
  remove(user: User): Promise<User>;
  search(clientId: typeof Client.prototype.id, name: string): Promise<User[]>;
}
export const RiskyUserUsecaseToken = new Token<IRiskyUserUsecase>();

@Service(RiskyUserUsecaseToken)
export class RiskyUserUsecase implements IRiskyUserUsecase {
  @InjectRepository(User)
  private userRepository: Repository<User>;

  async getAll(clientId: number) {
    return await this.userRepository.find({ clientId: clientId });
  }

  async save(user: User) {
    return await this.userRepository.save(user);
  }

  async update(id: number, user: User) {
    return await this.userRepository.update(id, user);
  }

  async remove(user: User) {
    return await this.userRepository.remove(user);
  }

  // TO-DO temporary solution
  // I need to update it when I have a spare time
  async search(clientId: typeof Client.prototype.id, name: string) {
    const users = await this.userRepository.query(
      `SELECT user.id as id,
      user.firstName as firstName,
      user.lastName as lastName,
      user.age as age,
      user.clientId as clientId,
      user.createdDateTime as createdDateTime,
      user.updatedDateTime as updatedDateTime,
      Client.companyName as 'clientCompanyName',
      Client.createdDateTime as 'clientCreatedDateTime',
      Client.updatedDateTime as 'clientUpdatedDateTime'
      FROM User INNER JOIN Client ON user.clientId = Client.id 
      WHERE clientId = ${clientId} AND (firstName like '%${name}%' OR lastName like '%${name}%')`
    );
    return users.map(
      (u): User => {
        return {
          id: u.id,
          firstName: u.firstName,
          lastName: u.lastName,
          age: u.age,
          clientId: u.clientId,
          client: {
            id: u.clientId,
            companyName: u.clientCompanyName,
            createdDateTime: u.clientCreatedDateTime,
            updatedDateTime: u.clientUpdatedDateTime,
          },
          createdDateTime: u.createdDateTime,
          updatedDateTime: u.updatedDateTime,
        };
      }
    );
  }
}

It would be best if you maximize the full benefits of TypeORM and do not execute the raw SQL. However, at least, you should use the prepared statements for security reasons like below if you need.




---

@Service(RiskyUserUsecaseToken)
export class RiskyUserUsecase implements IRiskyUserUsecase {
  @InjectRepository(User)
  private userRepository: Repository<User>;

  // TO-DO temporary solution
  // I need to update it when I have a spare time
  async search(clientId: typeof Client.prototype.id, name: string) {
    const users = await this.userRepository.query(
      `SELECT user.id as id,
      user.firstName as firstName,
      user.lastName as lastName,
      user.age as age,
      user.clientId as clientId,
      user.createdDateTime as createdDateTime,
      user.updatedDateTime as updatedDateTime,
      Client.companyName as 'clientCompanyName',
      Client.createdDateTime as 'clientCreatedDateTime',
      Client.updatedDateTime as 'clientUpdatedDateTime'
      FROM User INNER JOIN Client ON user.clientId = Client.id 
      WHERE clientId = :clientId AND (firstName like '%:name%' OR lastName like '%:name%')`,
      [{ clientId: clientId }, { name: name }]
    );
    return users.map(
      (u): User => {
        return {
          id: u.id,
          firstName: u.firstName,
          lastName: u.lastName,
          age: u.age,
          clientId: u.clientId,
          client: {
            id: u.clientId,
            companyName: u.clientCompanyName,
            createdDateTime: u.clientCreatedDateTime,
            updatedDateTime: u.clientUpdatedDateTime,
          },
          createdDateTime: u.createdDateTime,
          updatedDateTime: u.updatedDateTime,
        };
      }
    );
  }
}

But again, you should not do that for code-reviewers. Can you recognize the difference between the raw SQL with and without prepared statements?

It must be tough!

Upgrade of Old-Fashioned Applications

We sometimes develop pure new applications = we can select the appropriate frameworks and environments, which are the modern architecture.

On the other hand, we have to convert from the old applications to ones with the modern frameworks. For example:

Conversion from Web Form applications in .Net to MVC App/SPA in .Net Core
Conversion from MVC applications in PHP to React applications in Node.js/TypeScript

In this case, it might occur the above case because developers try completing the task within limited time allocation = they use existing code.

Error of Human Resource/Time Allocation

I assume that this is the main reason. Developers handle their tasks with limited human/time resources every day, even though it is sometimes unreasonable and puts pressure on them. It is fantastic if they can always choose the best solution, but it is difficult.

Managers and leads, do you think that you understand the current situation and manage it properly?

It is unacceptable for us to fire developers making security mistakes even though nobody wants to write insecure code, and it often happens due to external factors. Without thinking about why they made such a choice, it would not be the ultimate solution.

Potential Solutions for Developers

Review the parts of comment out with TO-DO and temporary solutions once - Developers and code-reviewers might not imagine how simple mistakes pose critical incidents. It is good timing to review them.
Security issues would come from human errors - Please do not focus on improving their security skills/knowledge. This is significant but you would also need to review your team management and development environment.

Potential Solutions by Publishers

Provide intermediate/senior-level information and a variety of examples as well - If the information is too general, developers might select lazy ways even though they do not want to.
Provide information regarding the utilization of the modern modules/library's combination - I faced multiple times that when I used the third-party module independently, it worked well. But when I used it with the other modules, it did not work. If developers have trouble implementing complex tasks with multiple third-party modules, they might select temporary solutions.

Demo Application

I developed a demo application in GitHub.

Demo Application for SQL injection tutorials

Conclusion

I will continuously publish security articles. However, I also understand the importance of providing the technical skills/knowledge in a non-security field. I believe that if we can provide helpful information about the development itself or project management, it would indirectly prevent security matters.

We are with you, friends.

Bio

When I was 30 years old, I went to Australia for changing my career on a Working Holiday visa. I graduated from University of Sydney with a Bachelor of Computer Science and Technology. During the period, I also worked as a Fraud Prevention Analyst at Amex in Sydney, Australia (yes, it was super busy. I went to my uni during the day and worked at night...)

After graduation, I worked as a C#/.Net developer for an Australian small business for 5 years. Now, I came back to Japan and work as a TypeScript/Go/React developer for a Japanese security start-up company.

I love learning new fields which is a big challenge. I am happy if you support me to accelerate the improvement of my skill/knowledge. Please feel free to contact me if you are interested in my unique career.

Thanks,

TypeORM - Multiple DB Calls vs Single DB Call

Yoshiaki Matsutomo — Fri, 23 Apr 2021 08:06:10 +0000

Introduction

The last article, TypeORM - Query Builder with Subquery demonstrates how you should build complex SQL queries. I hope this helps your work and study. Today's topic is still cases with TypeORM, but the angle is slightly different from the last one.

Which is Better?

Some senior developers mention that you should reduce database calls as much as possible. The potential reasons are:

The network connection is always a bottleneck. It will help if you reduce the number of DB calls.
The code-side should not construct, order and filter dataset. The DB is basically faster than the code's data manipulation if optimized by indexes or proper configuration.

In this case, the subqueries with TypeORM might be a good option. On the other hand, some experienced developers say that you should execute SQLs separately and build appropriate datasets in the code. The reasons are:

The returned dataset should not be large in size. Transferring big data size between DBs and applications might be a bottleneck.
Inside subqueries might execute without the index's or DB optimization's benefits. You should minimize the records themselves in the subqueries if you want.

Sample Code with TypeORM

Let's review the query on the last article.

Target SQL converted to TypeScript with TypeORM

SELECT 
  cs.course_id as course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) AS submitted_date, 
  IFNULL(count(cs.courseId), 0) as correct_submission_number, 
  IFNULL(total_exam.number, 0) as total_number 
FROM 
  assessment as asses 
  INNER JOIN submission as sub ON asses.submission_id = sub.id 
  INNER JOIN subject_exam as se ON se.exam_id = sub.exam_id 
  INNER JOIN course_subject as cs ON cs.subject_id = se.subject_id 
  LEFT OUTER JOIN (
    SELECT 
      cs.course_id as course_id, 
      IFNULL(COUNT(cs.course_id), 0) as number 
    FROM 
      course_subject as cs 
      LEFT OUTER JOIN subject_exam as se ON cs.subject_id = se.subject_id
    WHERE
      cs.dept_id = 'abcdefg' 
    GROUP BY 
      cs.course_id
  ) as total_exam ON total_exam.course_id = cs.course_id 
WHERE 
  asses.result = '2' -- = pass
  AND asses.status = '2' -- = submitted
  AND cs.dept_id = 'abcdefg'
GROUP BY 
  cs.course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) 
ORDER BY 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) asc, 
  cs.course_id asc;

TypeScript with TypeORM


import { getManager } from "typeorm";

// in class 'GetDailyStats'

//Build a subquery to get the total number of exams
const totalExamNumbers: any = getManager().createQueryBuilder()
    .select("cs.courseId", "course_id")
    .addSelect("IFNULL(COUNT(*), 0)", "number")
    .from(CourseSubject, "cs")
    .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id");

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalExamNumbers.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalExamNumbers.getParameters())

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: s.totalSubmissions
    };
    return item;
});

return dailyStats;

IMPORTANT: What is setParameters()?
When you set some parameters on a subquery, setting values is not ready to execute with the main query. You explicitly need to set them on your main query before calling getRawMany().

Do we need to call setParameters() for parameters on the main query? No, you do not need it. It is just for separated queries.

HINT: What is IFNULL in MySQL?
The IFNULL() function returns a specified value if the expression is NULL.

HINT: Grouping by DATETIME?
If you want to change the grouping rules by DATETIME columns, please change the DATE_FORMAT parts.

Hourly base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d %H:00:00')
Daily base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')
Monthly base: DATE_FORMAT(asses.created_datetime, '%Y-%m')
Yearly base: DATE_FORMAT(asses.created_datetime, '%Y')

Link Two Objects

Let's think about the way of constructing the dataset in the code. In the above case, dailyStatsQuery needs to link to totalExamNumbers in the code. When you link them, linking two arrays will consume time even though the records are already filtered by deptId. We might think to create a key and value object (Hashmap in Java and Dictionary in C#) because the field, courseId is a unique ID.


import { getManager } from "typeorm";

// in class 'GetDailyStats'

//Calculate the total number of exams
const totalExamNumbers: any = getManager().createQueryBuilder()
    .select("cs.courseId", "course_id")
    .addSelect("IFNULL(COUNT(*), 0)", "number")
    .from(CourseSubject, "cs")
    .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .getRawMany();

// [1st way] Convert from an array to an associative object array
const totalExamNumberHashMap1 = totalExamNumbers.reduce(
  (c, e) => ({ ...c, [e.course_id]: e }),
  {}
);

// [2nd way] Convert from an array to an associative object array
const totalExamNumberHashMap2 = Object.assign(
  {},
  ...totalExamNumbers.map((e) => ({
    [e.course_id]: e,
  }))
);

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: totalExamNumberHashMap1[s.courseId] 
        ? totalExamNumberHashMap1[s.courseId].number : 0
    };
    return item;
});

return dailyStats;

IMPORTANT:
I showed you the two ways of key & value object conversion by reduce and Object.assign. However, I have not considered the performance. If you give me better solutions, I appreciate :)

Potential Cases

Which cases should we select either a single DB call or multiple DB calls?

Select Multiple DB Calls

Cloud Database - Many enterprises utilize cloud-based DBs, which provide scalability, availability, etc. instead of DBs managed by ourselves. However, for instance, if your application accesses them via a public IP OR it is physically far from your apps, you might consider the data size. Multiple DB calls with small data might be faster than a single DB call.
Not Perform with DB Benefits - As I mentioned, some cases such as subqueries might not receive the index's or DB optimization's benefits.

Select Single DB Call

Simply Avoid Multiple Calls - This is not just for the communication between your applications and databases. Of course, your applications should not communicate any services as much as possible because this via the network is a bottleneck.
Complex Dataset - In the above case, linking totalExamNumbers to others should not be big trouble because it has a unique ID. However, if it needs to link to objects without specific unique IDs, completing the data operations by DBs would be better. You might need proper indexes, stored procedures, or views, which support complex data manipulations.
Send Raw Data or Flat Tables - Your backends (~= APIs) are utilized for multiple services such as web applications or data analysis tools etc. For some BI or data analysis tools, flat tables as star schema are utilized. For example with the above case, BI tools (= receivers who request the dataset) expect the dataset is a static data source's table but it actually comes from the SQL select results, which are dynamically generated and are stored somewhere such as memory data stores.

Quick Performance Check

If you want to check the performance of your queries without the additional work, console.time("Function 1") and console.timeEnd("Function 2") might help you.

// [1st way] Convert from an array to an associative object array
console.time("totalExamNumberHashMap1");
const totalExamNumberHashMap1 = totalExamNumbers.reduce(
  (c, e) => ({ ...c, [e.course_id]: e }),
  {}
);
console.timeEnd("totalExamNumberHashMap1")

// [2nd way] Convert from an array to an associative object array
console.time("totalExamNumberHashMap2");
const totalExamNumberHashMap2 = Object.assign(
  {},
  ...totalExamNumbers.map((e) => ({
    [e.course_id]: e,
  }))
);
console.timeEnd("totalExamNumberHashMap2")

Conclusion

TypeORM is a powerful tool to implement the Code-First approach. On the other hand, we have to deeply think about how it should be utilized effectively depends on your environment and situation.

Bio

Thanks,

TypeORM - Query Builder with Subquery

Yoshiaki Matsutomo — Thu, 18 Mar 2021 04:17:56 +0000

Introduction

I was a .Net developer and am a Node.js/Typescript developer at the moment. Of course, I used to write SQL and use LINQ which supports the code-base SQL execution. This describes how I convert from SQL to TypeScript coding with TypeORM.

Basic Query

Entity model of TypeORM

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class Student {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    isActive: boolean;

}

TypeScript with TypeORM

const student = await connection
    .getRepository(Student)
    .createQueryBuilder("stu")
    .where("stu.firstName = :name", { name: "Sam" })
    .getOne();

Actual SQL of the above code

SELECT * 
FROM Student as stu
WHERE stu.firstName = 'Sam'
LIMIT 1;

Query with Subqueries

Building a simple SELECT query with entities is easy. However, this is not enough for creating graphs or displaying calculated results on the tables. This is the main part to demonstrate how we should build complex queries with TypeORM.

Target SQL converted to TypeScript with TypeORM

SELECT 
  cs.course_id as course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) AS submitted_date, 
  IFNULL(count(cs.courseId), 0) as correct_submission_number, 
  IFNULL(total_exam.number, 0) as total_number 
FROM 
  assessment as asses 
  INNER JOIN submission as sub ON asses.submission_id = sub.id 
  INNER JOIN subject_exam as se ON se.exam_id = sub.exam_id 
  INNER JOIN course_subject as cs ON cs.subject_id = se.subject_id 
  LEFT OUTER JOIN (
    SELECT 
      cs.course_id as course_id, 
      IFNULL(COUNT(cs.course_id), 0) as number 
    FROM 
      course_subject as cs 
      LEFT OUTER JOIN subject_exam as se ON cs.subject_id = se.subject_id
    WHERE
      cs.dept_id = 'abcdefg' 
    GROUP BY 
      cs.course_id
  ) as total_exam ON total_exam.course_id = cs.course_id 
WHERE 
  asses.result = '2' -- = pass
  AND asses.status = '2' -- = submitted
  AND cs.dept_id = 'abcdefg'
GROUP BY 
  cs.course_id, 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) 
ORDER BY 
  DATE_FORMAT(
    asses.created_datetime, '%Y-%m-%d'
  ) asc, 
  cs.course_id asc;

TypeScript with TypeORM


import {getManager} from "typeorm";

// in class 'GetDailyStats'

//Build a subquery to get the total number of exams
const totalExamNumbers: any = getManager().createQueryBuilder()
    .select("cs.courseId", "course_id")
    .addSelect("IFNULL(COUNT(*), 0)", "number")
    .from(CourseSubject, "cs")
    .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id");

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalExamNumbers.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .andWhere("cs.dept_id = :deptId", {
        deptId: deptId
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalExamNumbers.getParameters())

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: s.totalSubmissions
    };
    return item;
});

return dailyStats;

You can consolidate the main query and subquery if you want but I prefer the divided ones because of the readability and the possibility of sharing the subqueries with the other classes.

IMPORTANT: What is setParameters()?
When you set some parameters on a subquery, setting values is not ready to execute with the main query. You explicitly need to set them on your main query before calling getRawMany().

Do we need to call setParameters() for parameters on the main query? No, you do not need it. It is just for separated queries.

HINT: What is IFNULL in MySQL?
The IFNULL() function returns a specified value if the expression is NULL.

HINT: Grouping by DATETIME?
If you want to change the grouping rules by DATETIME columns, please change the DATE_FORMAT parts.

Hourly base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d %H:00:00')
Daily base: DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')
Monthly base: DATE_FORMAT(asses.created_datetime, '%Y-%m')
Yearly base: DATE_FORMAT(asses.created_datetime, '%Y')

Subqueries with Optional Parameters

How should we create a query with optional values? No worries, this is easy with TypeORM.

in class 'SubQueryBuilder'


import {getManager} from "typeorm";

public getTotalNumberExams(deptId? : string | undefined) {

    const subquery: any = getManager().createQueryBuilder()
        .select("cs.courseId", "course_id")
        .addSelect("IFNULL(COUNT(cs.courseId), 0)", "umber")
        .from(CourseSubject, "cs")
        .leftJoin(SubjectExam, "se", "cs.subject_id = se.subject_id")
        .groupBy("cs.course_id");

    //Filter the list if a deptId is not undefined
    if (deptId !== undefined) {
        subquery.where("cs.deptId = :deptId", {
            deptId: deptId
        });

        return subquery;
    }
}

in class 'GetDailyStats'


import {getManager} from "typeorm";
import {getTotalNumberExams} from "SubQueryBuilder";

// in class 'GetDailyStats'

const totalNumberExams = getTotalNumberExams(deptId);

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalNumberExams.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalNumberExams.getParameters())

//Filter the list if a deptId is not undefined
if (deptId !== undefined) {
    dailyStatsQuery.andWhere("cs.deptId = :deptId", {
        deptId: deptId
    });

// Execute the generated query
const dailyStatsRaws = await dailyStatsQuery.getRawMany();

//Convert raws to our appropriate objects 
const dailyStats = dailyStatsRaws.map((s: any) => {
    const item: DailyStatsItem = {
        courseId: s.courseId,
        submittedDate: s.submittedDate,
        correctSubmissions: s.correctSubmissions,
        totalSubmissions: s.totalSubmissions
    };
    return item;
});

return dailyStats;

Subquery's Performance

Some experienced developers mention you should not use subqueries often because inside subqueries might execute without the index's or DB optimization's benefits.

Please check an article, TypeORM - Multiple DB Calls vs Single DB Call. It might be helpful for you.

Check Generated SQL

TypeORM supports checking the generated SQL syntaxes. Please simplely replace from getRawMany() to getQuery().

in class 'GetDailyStats'


import {getManager} from "typeorm";
import {getTotalNumberExams} from "SubQueryBuilder";

// in class 'GetDailyStats'

const totalNumberExams = getTotalNumberExams(deptId);

//Build a main query with the subquery for stats
const dailyStatsQuery: any = getManager().createQueryBuilder()
    .select("cs.courseId", "courseId")
    .addSelect("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "submitted_date")
    .addSelect("IFNULL(COUNT(cs.courseId), 0)", "correct_submission_number")
    .addSelect("IFNULL(total_exam.number, 0)", "total_number")
    .from(Assessment, "asses")
    .innerJoin(Submission, "sub", "asses.submission_id = sub.id")
    .innerJoin(SubjectExam, "se", "se.exam_id = sub.exam_id")
    .innerJoin(CourseSubject, "cs", "cs.subject_id = se.subject_id")
    .leftJoin("(" + totalNumberExams.getQuery() + ")", "total_exam", "total_exam.course_id = cs.course_id")
    .where("asses.result = :result", {
        result: AssessmentResult.PASS
    })
    .andWhere("asses.status = :status", {
        status: AssessmentStatus.SUBMITTED
    })
    .groupBy("cs.course_id")
    .addGroupBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')")
    .orderBy("DATE_FORMAT(asses.created_datetime, '%Y-%m-%d')", "ASC")
    .addOrderBy("cs.course_id", "ASC")
    .setParameters(totalNumberExams.getParameters())

//Filter the list if a deptId is not undefined
if (deptId !== undefined) {
    dailyStatsQuery.andWhere("cs.deptId = :deptId", {
        deptId: deptId
    });

// Generate an actual SQL query
const actualSqlQuery = await dailyStatsQuery.getQuery();
console.log(actualSqlQuery);

Conclusion

TypeORM is a powerful tool to implement the Code-First approach. I am going to share useful information if I find it.

Bio

Thanks,

Comparison between TypeORM and Entity Framework with LINQ

Yoshiaki Matsutomo — Wed, 17 Mar 2021 06:29:03 +0000

Introduction

I was a .Net developer and am a Node.js/Typescript developer at the moment. Of course, I faced many differences such as the coding style, development environment and principles etc. On the other hand, I also found some similarities to the Microsoft ecosystem. This topic, TypeORM is one of the good example frameworks.

What Entity Framework and TypeORM are

Entity Framework and TypeORM are both ORM(Object-Relational Mapping) frameworks.

What ORM is

ORM is a programming technique to query and manipulate data from data sources using an object-oriented paradigm. ORM supports developers who do not have enough knowledge about complex SQL syntaxes and DB methodology = ORM acts as a translator between the data sources and your system in your preferred languages such as Java or Python. Entity Framework and TypeORM are both open source libraries but Entity Framework is utilized in Microsoft ecosystem and TypeORM is specialized for a JS/Node.js environment.

Why We Use

Code First -> In the Code-First approach, we can focus on the domain of our application and start creating classes for our domain entity rather than 'DB First' (= design our DB first and then create the classes which match our DB design).
Flexibility -> It provides the concept of DB abstraction which makes us switch/migrate DB itself and data easily, and maintain the consistent code for our applications.
Safer -> They basically use placeholders for setting parameters = It reduces the risk of SQL injection attacks. However, is it perfectly blocked suspicious attacks? No, you still need the basic security knowledge even though you use ORM libraries.

Use Cases

Entity

Entity model of Entity Framework

using System.ComponentModel.DataAnnotations;

public class Student
{
    [Key]
    public int id { get; set; }
    public string firstName { get; set; }
    public string lastName { get; set; }
    public boolean isActive { get; set; }
}

Entity model of TypeORM

import {Entity, PrimaryGeneratedColumn, Column} from "typeorm";

@Entity()
export class Student {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    firstName: string;

    @Column()
    lastName: string;

    @Column()
    isActive: boolean;

}

SLECT

C# LINQ method in Entity Framework

using (var context = new CollegeDBEntities())
{    
    var student = context.Student
                  .Where(s => s.firstName == "Sam")
                  .FirstOrDefault<Student>();
}

C# LINQ query in Entity Framework

using (var context = new CollegeDBEntities())
{
    var query = from stu in context.Student
                where stu.firstName == "Sam"
                select stu;

    var student = query.FirstOrDefault<Student>();
}

TypeScript with TypeORM

const student = await connection
    .getRepository(Student)
    .createQueryBuilder("stu")
    .where("stu.firstName = :name", { name: "Sam" })
    .getOne();

Generated SQL

SELECT * 
FROM Student as stu
WHERE stu.firstName = 'Sam'
LIMIT 1;

I think that .Net developers who utilize LINQ methods often should not face any troubles in developing applications with TypeORM.

What is LINQ?
LINQ(Language-Integrated Query) is a library for the integration of query capabilities directly into the C# language and of course, it runs with Entity Framework objects. If you are familiar with SQL, LINQ queries might be easier than LINQ methods.

Benefits

I mentioned some benefits in the section 'Why We Use'. I want to share some actual cases.

Migration

How do you effectively update your schema and the related existing functions? When we have already had data in our DB and the related functions such as stored procedures, indexes and triggers etc., it could be a big job because you might refresh your tables/objects and re-create them = you might lose the data and the other DB objects. Both ORM frameworks provide migration tools that automatically update the schema/objects when our models change without losing the existing data and any related DB objects.

Data seed

How do you insert the data into your DB during the developing/testing phases? Without ORM frameworks, we might need to execute SQLs to insert some test data. Both of them seed the data to our DB during the DB initialization processes.

Seed your original records by Entity Framework


DropCreateDatabaseAlways<CollegeDBContext>
{
    protected override void Seed(CollegeDBContext context)
    {
        IList<Student> studentList = new List<Student>();

        studentList.Add(new Student() { firstName = "Sam", lastname = "Smith" });
        studentList.Add(new Student() { firstName = "James", lastname = "Williams" });
        studentList.Add(new Student() { firstName = "Lisa", lastname = "Adams" });

        context.Student.AddRange(studentList);
        base.Seed(context);
    }
}

Seed dummy records by Entity Framework with Bogus


DropCreateDatabaseAlways<CollegeDBContext>
{
    protected override void Seed(CollegeDBContext context)
    {
        var testStudents = new Faker<Student>()
        .RuleFor(s => s.firstName, f => f.Name.FirstName(f.PickRandom<Gender>()))
        .RuleFor(s => u.lastName, f => f.Name.LastName(f.PickRandom<Gender>()));

        var studentList = testStudents.Generate(5);

        context.Student.AddRange(studentList);
        base.Seed(context);
    }
}

Seed dummy records by TypeORM with Faker module

// Student.factory.ts
define(Student, (faker: typeof Faker) => {
  const gender = faker.random.number(1);
  const firstName = faker.name.firstName(gender);
  const lastName = faker.name.lastName(gender);

  const student = new Student();
  student.firstName = firstName;
  student.lastName = lastName;
  return student
})

// CreateStudent.seed.ts
export default class CreateStudents implements Seeder {
  public async run(factory: Factory, connection: Connection): Promise<any> {
    await factory(Student)().createMany(5)
  }
}

What is Bogus and Faker?
They are libraries randomly to create a variety of dummy data such as human name, address, date and product/company name etc. = reduce our boring time of generating dummy records and have testing cases with multiple dummy values instead of our static ones.

Concolusion

The programing language barrier is an obstacle for new developers who change languages like natural language, Japanese vs English. Moreover, as you know, the lifecycle of advanced technology/framework/theory is rapidly changed. The two factors would negatively influence our motivation. However, the direction would be the same in any languages because they are invented to achieve the same goal = effective/safe/beautiful development processes and products themselves.

What Next is

I will publish more details of my learning obtained from my individual investigation and work in commercial development.

Bio

Thanks,