TypeORM: 2 Ways to Exclude a Column from being Selected

When working with TypeORM, there might be cases where you want to exclude one or multiple columns (fields) from being selected. This succinct article will show you two different ways to do that.

Make a column unselectable when defining your entity

Let’s say we have a table named users with three columns: id, username, and password. When we want to retrieve information about users, we don’t want to return passwords because they are sensitive. What we need to do is just set the select property to false on the column password, like so:

// user.entity.ts
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

@Entity({name: 'users'})
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({unique: true})
  username: string;

  // This column will not be selected by find functions and  QueryBuilder 
  @Column({select: false})
  password: string
}

Test it:

const result = await userRepository.find();
console.log(result);

The result looks like this:

[ User { id: 1, username: 'goodman', password: undefined } ]

Using partial selection with QueryBuilder

This method allows you to choose which fields to return and can be flexibly changed easily in different places (e.g., a company system doesn’t return sensitive information to its normal employees but returns the same information for the supreme admin).

Suppose we still create the User entity as the preceding example but do not set the select option of column password to false:

// user.entity.ts
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm'

@Entity({name: 'users'})
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({unique: true})
  username: string;

  @Column()
  password: string
}

Now, we can use QueryBuilder to get only the fields we want:

  const result = await dataSource.createQueryBuilder(User, 'user')
    .select('user.id')
    .addSelect('user.username').getMany();

console.log(result);

The output will look as follows:

[ User { id: 1, username: 'goodman', password: undefined } ]

Conclusion

We’ve explored more than one solution to exclude certain fields from query results. The first is shorter and simpler, and the second is more flexible. TypeORM is an amazing tool that helps our lives much easier when performing CRUD operations. If you’d like to learn more about it and other stuff in the modern backend development world, take a look at the following articles:

• TypeORM: Adding Fields with Nullable/Default Data
• Using ENUM Type in TypeORM
• TypeORM: Adding created_at and updated_at columns
• 2 Ways to View the Structure of a Table in PostgreSQL
• Best Node.js Open Source Headless CMS
• Node.js: Generate Images from Text Using Canvas

You can also check our database topic page for the latest tutorials and examples.