Getting Started with TypeORM

6 Apr, 2021

What is TypeORM
ORM Patterns
Active Record VS Data Mapper
Adding TypeOrm to an Express Project
Querying the Database
Adding Relationships
Query Builder
Raw SQL Queries
Useful links

What is TypeORM

TypeORM is an Object Relational Mapping (ORM) tool. An ORM maps the entity objects in your application (e.g., an employee, a company, etc.) to the tables and rows in a database. An ORM helps us to interact with a database; it maps the data stored in the database (in tables, columns, and rows) to objects we can use in our applications.

Let's say, we have a table "customers":

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL
);

SELECT * FROM customers WHERE id = 1;

id    name    email
1     John    john@example.com

TypeORM allows us to select rows from this table and map those rows to objects:

const customer = customersRepository.findOne({ id: 1 });

console.log(customer);

/*
The customer is an object:
{
  id: 1,
  name: "John",
  email: "john@example.com"
}
*/

Instead of using SQL to compose queries, we use the API provided to us by the ORM tool which transforms our API calls like findOne({ id: 1 }) to SQL like SELECT * FROM some_table WHERE id = 1 And then, the ORM transforms the received data to objects and passes these objects to us.

As any other ORM, TypeORM allows us to declare relationships between data, such that when we use its API, the relationships between tables are taken into account.

ORM Patterns

TypeORM supports two ORM patterns: Active Record and Data Mapper.

These patterns define the way we access the data on the side of the application.

The Active Record pattern is when we add the methods to access and modify the data directly to the object that represents the data:

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

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

  @Column()
  email: string;
}

// user.service.ts
import { User } from './user.entity';

export const createUser = async () => {
  const user = new User();
  user.email = 'john@example.com';
  await user.save();

  // Or, to get the data:
  const user2 = User.findOne({ id: 1 });
};

The Data Mapper pattern is when we use so-called repository objects to access and modify the data. The repository objects map the data to simple entity objects (e.g., user, car, hotel, etc.) which only represent the data:

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

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

  @Column()
  email: string;
}

// user.service.ts
import { getRepository } from 'typeorm';
import { User } from './user.entity';

export const createUser = async () => {
  const user = new User();
  user.email = 'john@example.com';

  const userRepository = getRepository(User);
  await userRepository.save(user);

  // Or, to get the data:
  const user2 = await userRepository.findOne({ id: 1 });
};

Active Record VS Data Mapper

Active Record couples the entity's data representation and data access together in a single object. On the other hand, Data Mapper decouples the data representation and data access.

People say that Active Record is easy to use and understand and it is suitable for small projects, while Data Mapper is a little more complex to understand, but it brings more benefit in terms of organising the code in larger projects.

Personally, at the time I'm writing this, I'd prefer to use Data Mapper in both, large and small projects, because it allows me to separate the application's entities from the way the data is accessed. Such that, the entities are represented by simple objects without any additional methods for querying and updating data.

Though, choosing either of these patterns is up to you.

Adding TypeOrm to an Express Project

I prepared a simple app based on Express and TypeScript for this example.

Now we need to do two things:

Launch a local database server for development
Add TypeORM to the project

Launching a Local Database Server Using Docker Compose

For a local database server, I'm going to launch PostgreSQL in a Docker container and use Docker Compose to manage this container.

This section is based upon the assumption that you have experience with Docker and have it installed. If you don't, you can learn more about it here: https://www.docker.com/get-started To be able to use Docker on my machine, I installed Docker Desktop.

If you don't want to use Docker, you can install PostgreSQL server directly.

Let's start by defining the container's configuration using docker-compose.yml (create this file in the project's root):

version: '3.1'

services:
  postgres:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    ports:
      - 127.0.0.1:5432:5432

In this file we say that we want docker-compose to launch a container using the image "postgres" and forward port 5432 in the host machine to the same port in the container.

Also, we use environment variables to pass the credentials we want the database server to be created with. Now, let's set these environment variables using a .env file (create this file in the project's root):

POSTGRES_USER=myapp
POSTGRES_PASSWORD=123456
POSTGRES_DB=myapp

Docker-compose should read our .env file automatically.

Ensure that you don't commit .env file to the repository, because it has sensitive information. You can add this file to .gitignore:

node_modules
/build
.env

Now, we should be able to launch our local database container using the following command:

docker-compose up

This command creates the necessary Docker setup for the containers defined in docker-compose.yml and launches the containers (in our case, one postgres container). Then, our postgres container starts the PostgreSQL server with the credentials we supplied in docker-compose.yml.

You can stop docker-compose up by focusing the terminal window where you started it and pressing CTRL + C.

Also, you can run docker-compose up in detached mode: docker-compose up -d. This way, the command runs in the background, so you'll be able to continue working with the current terminal window.

To list the containers that were started, run docker-compose ps.

To stop docker-compose up both, in normal and detached (-d) modes, you can run: docker-compose stop in the same directory.

Finally, to destroy the containers run docker-compose down.

You should run docker-compose commands in the same directory where the docker-compose.yml file is located or in a subdirectory (docker-compose looks for the config file in the parent directories as well).

Setting Up TypeORM

First, let's install TypeORM. It depends on a couple of other packages:

yarn add typeorm pg reflect-metadata

Together with TypeORM we install pg, a database client that TypeORM will use to connect to the PostgreSQL database and run the queries.

Also, we install reflect-metadata. TypeORM's API is heavily based on decorators and TypeORM uses this package to parse the decorators.

Next, let's add the configuration. There is a number of ways to configure TypeORM:

Store the configuration in ormconfig.json or ormconfig.js. TypeORM will read these files automatically.
Or pass the configuration object directly when connecting to the database.

In this tutorial, I'll store the configuration in ormconfig.js, because this file is used automatically by TypeORM, not only when connecting to the database within the app, but also when running the TypeORM's CLI commands (like typeorm migration:create or typeorm migration:run).

Now, that we installed TypeORM, let's go ahead and create ormconfig.js in the project's root:

module.exports = {
  type: 'postgres',
  host: process.env.POSTGRES_HOST,
  username: process.env.POSTGRES_USER,
  password: process.env.POSTGRES_PASSWORD,
  database: process.env.POSTGRES_DB,
  port: process.env.POSTGRES_PORT,
  entities: ['src/entities/*.ts'],
};

TypeORM supports a number of databases. Therefore, we use the type setting to inform TypeORM that we will work with postgres.

We use two environment variables that we haven't defined yet: POSTGRES_HOST and POSTGRES_PORT. Let's add them to our .env:

...
POSTGRES_HOST=127.0.0.1
POSTGRES_PORT=5432

We use the entities setting to tell TypeORM where we keep our entity classes. We'll add an entity class later in this tutorial.

Now that the configuration is ready, let's connect to the database. Open src/index.ts and modify it as follows:

import 'reflect-metadata';
import { createConnection } from 'typeorm';
import app from './app';

const port = process.env.PORT || 3000;

const start = async () => {
  await createConnection();

  app.listen(port, () => {
    console.log(`The server is listening on port ${port}`);
  });
};

start().catch(console.error);

We need to import reflect-metadata in a global place in our app, so I decided to import it in index.ts.

Here, we ensure that TypeORM is ready to run the database queries before the application is started. The createConnection function sets up a connection pool. Learn more about it here: https://github.com/typeorm/typeorm/blob/master/docs/connection.md#what-is-connection

If you run the app now, yarn dev, TypeORM should be able to connect to the database if you started it earlier using docker-compose.

Creating, Running, and Reverting Migrations

Let's create a table for users and do it using a migration.

TypeORM includes a CLI for creating and running migrations. But, there is a gotcha. By default the CLI works with .js files only. To use .ts files with TypeORM CLI we have to run the CLI itself using ts-node. So, let's open package.json and create a script for this:

{
  "scripts": {
    "typeorm": "node --require ts-node/register ./node_modules/typeorm/cli.js"
  }
}

Now we can run TypeORM CLI over ts-node using a simple command: yarn typeorm ...

Before creating a migration, let's add a few settings to ormconfig.js:

module.exports = {
  // ...
  migrations: ['db/migrations/*.ts'],
  cli: {
    migrationsDir: 'db/migrations',
  },
};

We use these settings to specify where we'd like to store migrations.

Now let's create a migration for the users table:

yarn typeorm migration:create -n users

This command should have created a migration file in the folder db/migrations. Let's modify this file to create the users table:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class users1617475742590
  implements MigrationInterface
{
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `
        CREATE TABLE users (
          id SERIAL,
          email VARCHAR(255) NOT NULL,
          name VARCHAR(255) NOT NULL,
          CONSTRAINT unique_users_email UNIQUE (email),
          CONSTRAINT pk_users_id PRIMARY KEY (id)
        );
      `
    );
  }

  public async down(
    queryRunner: QueryRunner
  ): Promise<void> {
    await queryRunner.dropTable('users');
  }
}

A migration has two methods: up and down. The method up is called when you apply the migration and down is called when you revert it.

Now, let's run this migration:

yarn typeorm migration:run

This command applies all migration files from the migrations directory that haven't been applied already. To know if a migration has been applied already or not, TypeORM creates the migrations table to keep track of all migrations that have been applied.

To revert the latest migration, you can run:

yarn typeorm migration:revert

Querying the Database

Let's start by creating an entity class for the users (src/entities/user.ts):

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

@Entity('users')
class User {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar', unique: true })
  email!: string;

  @Column({ type: 'varchar' })
  name!: string;
}

export default User;

We should create the entity files in the directory we specified earlier in ormconfig.js using the entities setting:

module.exports = {
  // ...
  entities: ['src/entities/*.ts'],
  // ...
};

Next, let's add a route for creating users to src/app.ts:

import 'express-async-errors';
import { getRepository } from 'typeorm';
import User from './entities/user';
// ...
app.post('/users', async (req, res) => {
  const { email, name } = req.body;

  const user = new User();
  user.email = email;
  user.name = name;

  await getRepository(User).save(user);

  res.status(201).json({
    user: {
      id: user.id,
      email: user.email,
      name: user.name,
    },
  });
});
// ...

I installed express-async-errors for catching and handling errors from async route handlers. Without this package, errors thrown by async route handlers will not be caught and the requests with errors will timeout instead of returning an error code 500 immediately.

Now, let's add a route for getting users:

// src/app.ts
// ...
app.get('/users', async (_req, res) => {
  const userRepo = getRepository(User);
  const users = await userRepo.find({ take: 10 });
  res.json({
    users: users.map((user) => ({
      id: user.id,
      email: user.email,
      name: user.name,
    })),
  });
});
// ...

Adding Relationships

I want to allow a user to create blog posts. One user can have many blog posts and one blog post can have just one user. So, there is a one-to-many relationship between users and posts and many-to-one relationship between posts and users. In this section we'll add the posts table and set up the relationships.

First, let's create a migration for the posts table:

yarn typeorm migration:create -n posts

Now you should see a new migration file in db/migrations. Let's add the schema for the posts to this file:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class posts1617772850315
  implements MigrationInterface
{
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `
        CREATE TABLE posts (
          id SERIAL,
          title VARCHAR(255) NOT NULL,
          user_id INT NOT NULL,
          CONSTRAINT fk_users_posts FOREIGN KEY (user_id) REFERENCES users (id),
          CONSTRAINT pk_posts_id PRIMARY KEY (id)
        );
      `
    );
  }

  public async down(
    queryRunner: QueryRunner
  ): Promise<void> {
    await queryRunner.dropTable('posts');
  }
}

The posts table has a foreign key "user_id" that references "id" in the users table. This defines our one-to-many/many-to-one relationship between users and posts.

Next, let's create an entity class for the posts:

import {
  Column,
  Entity,
  JoinColumn,
  ManyToOne,
  PrimaryGeneratedColumn,
} from 'typeorm';
import User from './user';

@Entity('posts')
class Post {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar' })
  title!: string;

  @Column({ type: 'integer', name: 'user_id' })
  userId!: number;

  @ManyToOne(() => User, (user) => user.posts)
  @JoinColumn({ name: 'user_id' })
  user?: User;
}

export default Post;

Here, we use ManyToOne decorator to declare that the post entity can have only one user.

In the "userId" column declaration I specify the actual name of the "userId" column in the posts table, which is "user_id". TypeORM will use this info to map the user_id column in the posts table to the userId property in the Post entity class.

Next, let's declare the one-to-many relationship from users to posts in the existing User entity class (src/entities/user.ts):

import {
  // ...
  OneToMany,
} from 'typeorm';
import Post from './post';

@Entity('users')
class User {
  // ...

  @OneToMany(() => Post, (post) => post.user)
  posts?: Post[];
}

export default User;

Now, let's run the migration:

yarn typeorm migration:run

Next, let's make it possible for a user to create posts. For this let's add a route handler to src/app.ts:

// ...
app.post('/posts', async (req, res) => {
  const { userId, title } = req.body;

  const user = await getRepository(User).findOne({
    id: userId,
  });

  if (!user) {
    throw new Error('User not found');
  }

  const post = new Post();
  post.title = title;
  post.user = user;

  await getRepository(Post).save(post);

  res.status(201).json({
    post: {
      id: post.id,
      title: post.title,
      user: {
        id: post.user.id,
      },
    },
  });
});
// ...

Finally, I want to show how to fetch all the user's posts through the User entity just to show how the relationships setup works in TypeORM:

app.get('/users/:id/posts', async (req, res) => {
  const { id } = req.params;
  const user = await getRepository(User).findOne({
    where: { id: parseInt(id, 10) },
    relations: ['posts'],
  });

  if (!user) {
    throw new Error('User not found');
  }

  res.json({
    posts: user.posts,
  });
});

By default, TypeORM won't fetch the associated relations. You have to specify which relations you'd like to fetch together with the entity. If you won't add relations: ['posts'] to the findOne options, user.posts will be undefined.

Learn more about different kinds of relationships on the TypeORM's website: Relations

Query Builder

You can build complex SELECT, INSERT, UPDATE, and DELETE queries using a query builder. Have a look at this example:

import { getConnection } from 'typeorm';

// ...

app.get('/users-stats', async (_req, res) => {
  const results = await getConnection()
    .createQueryBuilder()
    .select('user.id', 'userId')
    .addSelect('COUNT(post.id)', 'postsCount')
    .from(User, 'user')
    .innerJoin(Post, 'post', 'post.user_id = user.id')
    // .where('user.role = :role', { role: 'guest' })
    .orderBy('COUNT(post.id)', 'DESC')
    .offset(0)
    .limit(10)
    .groupBy('user.id')
    .getRawMany();
  // or .getMany()

  res.json({
    results: results.map((result) => ({
      userId: result.userId,
      postsCount: Number(result.postsCount),
    })),
  });
});
// ...

Learn more about the query builder here: Select using Query Builder

Raw SQL Queries

Finally, if the query builder doesn't let you build a query that you want, you can run raw SQL queries as well using the EntityManager API.

import { getManager } from 'typeorm';

// ...

type PostsSQLQueryResult = {
  id: number;
  title: string;
  user_id: number;
}[];

app.get('/posts', async (req, res) => {
  const { limit = 10, offset = 0 } = req.query;
  const manager = getManager();
  const rawData: PostsSQLQueryResult = await manager.query(
    'SELECT * FROM posts ORDER BY id DESC LIMIT $1 OFFSET $2',
    [limit, offset]
  );
  res.json({
    posts: rawData.map((row) => ({
      id: row.id,
      title: row.title,
      userId: row.user_id,
    })),
  });
});
// ...