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:

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

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

    @Column()
    name: string;

    @Column()
    email: string;
}
```

The old behavior:

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

console.log(user); // undefined
```

Current behavior:

```typescript
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:

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

The new one:

```typescript
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.

```typescript
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:

```typescript
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:

\
```javascript
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:

\
```sql
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:

\
```sql
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.

\
```typescript
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.

Read My Stories

Too Long; Didn't Read

Cassandra Forward a free Cassandra-focused community event

Unexpected Discoveries in TypeORM 0.3.11

Too Long; Didn't Read

@akankov

RELATED STORIES