Unexpected Discoveries in TypeORM 0.3.11

TypeORM is an Object-Relational Mapping (ORM) tool that is designed to work with TypeScript. It allows developers to interact with databases using an object-oriented syntax and supports a wide range of popular databases, including MySQL, PostgreSQL, and SQLite. But most probably, you already know that. So the point of that article is not to explain what TypeORM is but to show some unexpected discoveries I made while working with it.

Upgrading TypeORM to 0.3.11

Recently I was working on a project that uses TypeORM. The project was quite a bit outdated, and the version of TypeORM was 0.2.*. I decided to upgrade it to the latest version 0.3.11. I was expecting to find some breaking changes, and indeed they made it.

Things I liked about the new version

Let's say a few words about the incompatible changes I liked. First of all .findOne() of findOneBy() will no longer return an undefined. Instead, these methods will return null. This is a good change, null is a value and having the result as null means that we did something but had an empty result. undefined, on the other hand, is a type, and it means that we did not do anything. So it is a good change.

Let's look at the example:

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

@Entity()
export class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column()
    email: string;
}

The old behavior:

const user = await User.findOne({name: "the name we don't have in the database"});

console.log(user); // undefined

Current behavior:

const user = await User.findOne({name: "the name we don't have in the database"});

console.log(user); // null

Things I'm OK in the new version

Next, the changes in API are related to the .find() method. The old version was more flexible and allowed to pass just where parameters or the whole FindOptions object. The new version requires passing the whole FindOptions object. The new version is strict, and you should always pass the whole FindOptions object.

The old behavior:

const users = await User.find({
    name: "Alice"
})

The new one:

const users = await User.find({
    where: {name: "Alice"}
})

Lucky for us, the new version has a new method called .findMy() and will accept just where parameters.

const users = await User.findBy({
    name: "Alice"
})

Things I don't like in the new version

And last but not least is the changes in handling null and undefined values of where parameters. This one came as a surprise for me as I was not able to find any information about that change in the documentation and the changelog. And actually, I stumbled upon that change only in the testing stage. In general, the application I was working on has an impressive test coverage (more than 90%), and REST API has a pretty good validation layer. So it is not easy to send a null or undefined parameter to the API. So let me get to the issue itself.

Let's imagine that in some places, we don't have a proper query params validation, and we can send a null or undefined to the API.

Let's have a look at the example:

function findUserByEmail(email: string): Promise<User | null> {
    return User.findOneBy({
        email: email
    });
}

In the perfect Typescript world, that function looks just fine, and it is not possible to directly pass null or undefined in the code - it will break the compilation. But at the end of the day, it will be compiled into JavaScript and the resulting code will look something like this:

function findUserByEmail(email) {
    return User.findOneBy({
        email: email
    });
}

So it is possible to pass null or undefined to the findUserByEmail function.

And now, let's have a look at the generated SQL queries for the following function calls.

For the valid email parameter, both versions of TypeORM will generate the same query:

select *
from
    user
where
    email = :email limit 1

I'm simplifying the query here, but the point is that the query is the same.

But for the null and undefined parameters, the query in the new version will be different:

select *
from
    user limit 1

So as you can see, that query will return the first user from the database. And that is not what we want.

The reason for this is that TypeORM developers decided to skip the where clause if the where parameter is null or undefined. In the previous version, such parameters will trigger an error, and the query will not be executed. But in the new version, everything will be skipped, and the query will be executed without the where clause. There is a Github issue related to that, but it looks like it is not going to be fixed soon. https://github.com/typeorm/typeorm/issues/9316

So instead of waiting for the fix, I decided to write a small wrapper class to provide an additional layer of where params validation. The wrapper class will check if the where parameter is null or undefined and will throw an error.

import {BaseEntity, FindOneOptions} from 'typeorm'
import {FindManyOptions} from 'typeorm/find-options/FindManyOptions'
import {FindOptionsWhere} from 'typeorm/find-options/FindOptionsWhere'

interface Constructable {
    new(...args: any[]): any
}

function paramsCheckingMixin<T extends Constructable>(base: T): T {
    return class extends base {
        /**
         * Counts entities that match given options.
         */
        static async count<T extends BaseEntity>(
            this: { new(): T } & typeof BaseEntity,
            options?: FindManyOptions<T>
        ): Promise<number> {
            await validateWhereOptions(options?.where)

            return this.getRepository<T>().count(options as FindManyOptions<BaseEntity>)
        }

        /**
         * Counts entities that match given WHERE conditions.
         */
        static async countBy<T extends BaseEntity>(
            this: { new(): T } & typeof BaseEntity,
            where: FindOptionsWhere<T>
        ): Promise<number> {
            await validateWhereOptions(where)

            return this.getRepository<T>().countBy(where as FindOptionsWhere<BaseEntity>)
        }

        /**
         * Finds entities that match given options.
         */
        static async find<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            options?: FindManyOptions<T>
        ): Promise<T[]> {
            await validateWhereOptions(options?.where)

            return this.getRepository<T>().find(options)
        }

        /**
         * Finds entities that match given WHERE conditions.
         */
        static async findBy<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            where: FindOptionsWhere<T>
        ): Promise<T[]> {
            await validateWhereOptions(where)

            return this.getRepository<T>().findBy(where)
        }

        /**
         * Finds entities that match given find options.
         * Also counts all entities that match given conditions,
         * but ignores pagination settings (from and take options).
         */
        static async findAndCount<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            options?: FindManyOptions<T>
        ): Promise<[T[], number]> {
            await validateWhereOptions(options?.where)

            return this.getRepository<T>().findAndCount(options)
        }

        /**
         * Finds entities that match given WHERE conditions.
         * Also counts all entities that match given conditions,
         * but ignores pagination settings (from and take options).
         */
        static async findAndCountBy<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            where: FindOptionsWhere<T>
        ): Promise<[T[], number]> {
            await validateWhereOptions(where)

            return this.getRepository<T>().findAndCountBy(where)
        }

        /**
         * Finds first entity that matches given conditions.
         */
        static async findOne<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            options: FindOneOptions<T>
        ): Promise<T | null> {
            await validateWhereOptions(options?.where)

            return this.getRepository<T>().findOne(options)
        }

        /**
         * Finds first entity that matches given conditions.
         */
        static async findOneBy<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            where: FindOptionsWhere<T>
        ): Promise<T | null> {
            await validateWhereOptions(where)

            return this.getRepository<T>().findOneBy(where)
        }

        /**
         * Finds first entity that matches given conditions.
         */
        static async findOneOrFail<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            options: FindOneOptions<T>
        ): Promise<T> {
            await validateWhereOptions(options?.where)

            return this.getRepository<T>().findOneOrFail(options)
        }

        /**
         * Finds first entity that matches given conditions.
         */
        static async findOneByOrFail<T extends AppBaseEntity>(
            this: { new(): T } & typeof AppBaseEntity,
            where: FindOptionsWhere<T>
        ): Promise<T> {
            await validateWhereOptions(where)

            return this.getRepository<T>().findOneByOrFail(where)
        }
    }
}

export abstract class AppBaseEntity extends paramsCheckingMixin(BaseEntity) {
}

function validateWhereOptions(where?: FindOptionsWhere<BaseEntity>[] | FindOptionsWhere<BaseEntity>): Promise<void> {
    if (!where) {
        return Promise.resolve()
    }

    if (!Array.isArray(where)) {
        where = [where]
    }
    const errors: string[] = []
    where.forEach((findOptionsWhere) => {
        for (const key in findOptionsWhere) {
            if (findOptionsWhere[key] === null || findOptionsWhere[key] === undefined) {
                errors.push(`Invalid value of where parameter ${key}`)
            }
        }
    })

    if (errors.length) {
        return Promise.reject(errors.join('. '))
    }

    return Promise.resolve()
}

The wrapper class is used as a base class for all entities.

Conclusion

I believe that security is a very important part of any application, as we all care about our users and their data. Unfortunately, it is often overlooked, and developers don't pay enough attention to it. I hope that this article will help you to improve your application security on the database level and help you avoid some common mistakes.