Connecting to PostgreSQL using TypeORM

In my previous article we learned how to connect to a PostgreSQL database using the node-postgres package pg. This works fine, and will be perfect for many applications, but one may also choose to opt for using an ORM instead.

What is an ORM?

ORM stands for object-relational-mapping and allows you to interact with your database using objects from your programming language's type system rather than hand writing queries. This allows ORMs to simplify your logic that manipulates data in the database. In this article, we've opted to use TypeORM as it is widely used, and it supports many of the advanced features expected from an ORM framework.

Setup

We'll be demonstrating examples with both Data Mapper and Active record patterns.

Both the Data Mapper and Active Record patterns are supported by TypeORM, and we'll cover the former first. Data Mapper allows you to represent tables and other data as Entity classes. You can then define properties in these classes that map to the respective columns in the database, and even include custom methods in the entities that manipulate them.

For starters, you'll want to have TypeScript and TypeORM installed. TypeORM uses TypeScript exclusive features for defining your entities, and although it's not required to use TypeORM, it makes it much easier to do so. TypeScript is an extension to JavaScript that adds a strict type system. At the time of writing this article, TypeORM requires TypeScript v3.3 at a minimum, and this is subject to change.

# Install TypeScript globally if it isn't already installed.
npm install -g typescript

# Check that you're using a supported version of TypeScript.
tsc --version

Now, TypeORM has to be installed! This can be done with the following npm command in your project root:

# Initialize a node project.
npm init

# Install needed dependencies.
npm install typeorm pg --save

Let's define a database and a simple schema with a people tables like we did in our node-postgres article. Please check out that article if you need instructions on how to set up a PostgreSQL to develop against.

-- Create a database for our demo.
CREATE DATABASE typeormdemo;

-- Setup the database schema.
CREATE TABLE people (
    id BIGSERIAL,
    fullname TEXT,
    gender TEXT,
    phone TEXT,
    age INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

Data Mapper

The Data Mapper pattern allows you to define Entities that represent your data types in the database, and repositories to store your query methods and other domain logic. Entities in Data Mapper are very simple.

The following example is how an Entity for our person table is defined using the Data Mapper pattern. This is contained in a file called person.ts.

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

// The property "name" sets the table name. This is usually implied from the
// class name, however this can be overridden if needed.
@Entity({ name: "people" })
export class Person {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  fullname!: string;

  @Column()
  gender!: string;

  @Column()
  phone!: string;

  @Column()
  age!: number;

  @Column({ name: "created_at" })
  createdAt?: Date;
}

Note that this Entity uses decorators that are experimental at the time of writing this article. Make sure you set experimentalDecorators and emitDecoratorMetadata in your tsconfig.json for this to work. You can generate a tsconfig.json by running tsc --init. Let's break down what these decorators and their options mean.

**@Column**: Put above a property that maps to a column. The name of the property is assumed to be the name of the column in the table. If the name in the Entity doesn't match the database column name, you can specify a name in an options object in the decorator. **@PrimaryGeneratedColumn()**: This signals to TypeORM that this is a PRIMARY KEY column that uniquely represents the entity.

To write a query method, we'll need to create a repository. A repository is a class that contains query methods and other helpers. Domain logic that works with Entities is separated from the definition of the data types themselves. This will go in people-repository.ts.

import { EntityRepository, Repository } from "typeorm";
import { Person } from "./person";

@EntityRepository(Person)
export class PersonRepository extends Repository<Person> {
  findByName(fullname: string) {
    return this.createQueryBuilder("people")
      .where("people.fullname = :fullname", { fullname })
      .getOne();
  }

  updateName(id: number, fullname: string) {
    return this.createQueryBuilder("people")
      .update()
      .set({ fullname: fullname })
      .where("people.id = :id", { id })
      .execute();
  }
}

The following code can be used to set up a connection and interact with the database with our Entity and Repository. It's a better practice to change the fullname property on the class instance and use save() to update it in the database. However, I wanted to demonstrate how the query builder can be used for UPDATE queries as well. We put this in our main index.ts file.

import { createConnection } from "typeorm";

import { Person } from "./person";
import { PersonRepository } from "./person-repository";

(async () => {
  // Initialize a connection pool against the database.
  const connection = await createConnection({
    type: "postgres",
    host: "localhost",
    port: 5432,
    username: "postgres",
    password: "Theeb6uu",
    database: "typeormdemo",
    entities: [Person],
  });
  const personRepository = connection.getCustomRepository(PersonRepository);

  // Register a new person in the database by calling the repository.
  const newPerson = new Person();
  newPerson.fullname = "Jane Doe";
  newPerson.gender = "F";
  newPerson.phone = "5555555555";
  newPerson.age = 29;
  await personRepository.save(newPerson);

  // Find the person we just saved to the database using the custom query
  // method we wrote in the person repository.
  const existingPerson = await personRepository.findByName("Jane Doe");
  if (!existingPerson) {
    throw Error("Unable to find Jane Doe.");
  }

  // Change the person's full name.
  await personRepository.updateName(existingPerson.id, "Jane Johnson");

  // Remove the person from the database.
  await personRepository.remove(existingPerson);

  // Clean up our connection pool so we can exit.
  await connection.close();
})();

Now, with all the code in place, it can be executed with tsc && node index.js. The above demos a basic CRUD flow just like we do in our previous article where we use raw SQL. All methods for interacting with the data exist in the Repository class, and this class has our own custom functions and pre-existing functions for saving and removing data that we use.

Active Record

The Active Record approach allows you to define query methods in the model itself, rather than doing it in a repository like we did with the Data Mapper pattern. You may have used the Active Record pattern before if you've ever used Ruby on Rails. For our example, there won't be very many changes.

We can actually use the same Entity as we did in the Data Mapper class, however we will move our query methods into this class. We also make one of the queries static so we don't need an instance of the class to call the method. The name update method can remain an instance method as it can utilize the id property in an instance to simplify making calls to it. Here is the new person.ts.

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

@Entity({ name: "people" })
export class Person extends BaseEntity {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column()
  fullname!: string;

  @Column()
  gender!: string;

  @Column()
  phone!: string;

  @Column()
  age!: number;

  @Column({ name: "created_at" })
  createdAt?: Date;

  /*
   * The following methods used to be in the PersonRepositoy class, but are now
   * in the entity instead.
   */

  static findByName(fullname: string) {
    return this.createQueryBuilder("people")
      .where("people.fullname = :fullname", { fullname })
      .getOne();
  }

  updateName(fullname: string) {
    const id = this.id;

    return Person.createQueryBuilder("people")
      .update()
      .set({ fullname: fullname })
      .where("people.id = :id", { id })
      .execute();
  }
}

The code in index.ts is similar to the data mapper approach, but differs in how the query methods are called. Since we don't have a repository class with Active Record, we just call the methods directly on the Entity instance.

import { createConnection } from "typeorm";

import { Person } from "./person";

(async () => {
  // Initialize a connection pool against the database.
  const connection = await createConnection({
    type: "postgres",
    host: "localhost",
    port: 5432,
    username: "postgres",
    password: "Theeb6uu",
    database: "typeormdemo",
    entities: [Person],
  });

  // Register a new person in the database by calling the repository.
  const newPerson = new Person();
  newPerson.fullname = "Jane Doe";
  newPerson.gender = "F";
  newPerson.phone = "5555555555";
  newPerson.age = 29;
  await newPerson.save();

  // Find the person we just saved to the database using the custom query
  // method we wrote in the person repository.
  const existingPerson = await Person.findByName("Jane Doe");
  if (!existingPerson) {
    throw Error("Unable to find Jane Doe.");
  }

  // Change the person's full name.
  await existingPerson.updateName("Jane Johnson");

  // Remove the person from the database.
  await existingPerson.remove();

  // Clean up our connection pool so we can exit.
  await connection.close();
})();

Both methods are valid, and you should choose whatever you're more comfortable with, and whatever makes the most sense for your project.

Conclusion

TypeORM is a useful tool, and is capable of so much more than what is showcased here. We've only skimmed the surface of what TypeORM provides, and there is so much more. There is support for many more advanced features such as lazy relations, migrations, pagination, etc. The full documentation on their website has a lot of useful information.

You can find all the source code in this GitHub repo!