Getting Started with TypeORM
Tags: typeorm, node, typescript
Published: 2021-04-07 19:42:43

## 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:

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

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

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

1. Launch a local database server for development
2. 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](https://www.docker.com/products/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):

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

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

1. Store the configuration in _ormconfig.json_ or _ormconfig.js_. TypeORM will read these files automatically.
2. 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:

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

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

```json
{
  "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_:

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

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

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

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

```bash
yarn typeorm migration:revert
```

## Querying the Database

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

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

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

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

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

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

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

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

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

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

```bash
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_:

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

```typescript
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](https://typeorm.io/#/relations)

## Query Builder

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

```typescript
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](https://typeorm.io/#/select-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.

```typescript
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,
    })),
  });
});
// ...
```

Learn more here: [EntityManager API](https://typeorm.io/#/entity-manager-api)

## Useful links

- [Source code](https://github.com/d-dmytro/getting-started-with-typeorm)