PostgreSQL and typeorm - Advanced Querying - (Aggregations, AND, OR, BETWEEN for Dates)

Lesson goal

After this lesson you will be able to recognise and use the most common querying capabilities in postgres used in line-of-business applications.

Database course index

This is part of a full course on persistence in postgres with typeorm and sql!

There is a github repo to go with this course. See part 2 for instructions.

You can see examples of typeorm in a real app on Use Miller on GitHub

Dates and times in Typeorm

There are date only and time only database types in postgres but I rarely see them used. A timestamp with timezone (timestampz) is by far the most used type for dates in postgres.

Typeorm will convert these timestamps to javascript Dates for you.

class ClassWithDateTime {
  @Column({ type: "timestamptz", precision: 3 })
  timestampWithTimezone: Date;
}

Note above that I set the precision to 3. This is to ensure that the seconds precision stored in postgres matches with javascript.

It's very common in databases to have updated, created and deleted timestamp columns.

CreatedAt is almost always used.

Typeorm provides special decorators for these properties because they're so common.

class ClassWithDateTime {
  // typeorm will make sure this is populated
  @CreateDateColumn()
  createdDate: Date;

  // and this!
  @UpdateDateColumn()
  updatedDate: Date;

  // typeorm will use this to do soft deleting
  // be careful using soft-delete everywhere. sometimes you might want a deletion to really be a deletion
  @DeleteDateColumn()
  deletedDate: Date;
}

Glossary: soft delete and hard delete

Databases provide DELETE that will completely delete a row from a table. Many databases are designed to have a less destructive "Delete" by settings some flag that indicates the record is deleted.

If you're using soft delete you always add an where deleted = false when reading records to get current values.

In typeorm this soft delete flag is a date column called "DeletedAt".

Typeorm is designed to use this field in queries so this is how you should perform soft deletes if you need them.

Most typeorm querying supports an optional fields called includeDeleted that is set to true.

To remove records when using soft delete you should use repository.softDelete() and repository.restore().

Logical operators

The AND operator is the default operator on where propertie in typeorm. You just pass them in!

const result = await myRepository.find({
  where: {
    price: LessThan(100),
    name: "itemName",
  },
});

To get an OR you have to pass in multiple objects to where.

const result = await myRepository.find({
  where: [
    {
      price: LessThan(100),
    },
    {
      name: "itemName",
    },
  ],
});

Typeorm selection where operators

Type orm provides helpers for common SQL where filters

!= - Not()
< - LessThan()
<= - LessThanOrEqual()
> - MoreThan()
>= - MoreThanOrEqual()
LIKE = Like()
BETWEEN = Between()
IN = In()
IS NULL = IsNull()
(--with a complex query in here) = Brackets()
"currentDate" > NOW() = Raw((alias) => ${alias} > NOW())

Typeorm Between for dates

const users = await userRepository.find({
  where: {
    createdAt: Between(new Date(2022, 6, 1), new Date(2022, 6, 30)),
  },
});

Typeorm NOT IN

const subQuery = petStore.createQueryBuilder("account").select("store_id");

const petsToStockInStore = petRepository
  .createQueryBuilder("petsNotStocked")
  .where("petStore.id NOT IN (" + subQuery.getSql() + ")");

const results = await growerQb.getMany();

Arrays in typeorm for postgres

Postgres allows you to store arrays in a single column. While this shouldn't be abused, there are things like tag clouds that could be stored in here.

You define an array in the typeorm column, but do not set the data type to array.

class myArrayClass {
  @Column({ array: true, default: "{}" })
  tags: string[];

  @Column("int", { array: true })
  ids: number[];
}

BIGINTs in typeorm for postgres

The bigint data type is becoming more necessary as applications with large number of records for ids become more common. Integer type doesn't work any more for some of these large tables. Basecamp famously had an issue with regular ints and ran out of ids in a table.

BigInts are also used for small denominations of crypto currency. Because they're not supported in all javascript engines yet, I would recommend parsing them into strings.

Here is how to use BigInt in typeorm

class myBigIntClass {
  @Column({ type: "bigint" })
  columnName: string;
}

Sorting in Typeorm

To sort you can pass in an order object

petRepository.find({
  order: {
    name: "ASC",
  },
});

It's worth noting that the SQL standard doesn't specify what to do with NULL values when sorting so different databases handle this differently. Postgres will put all null values at the end of your sorted list. MySQL will put them at the top.

To force a NULL ordering requirement in Postgres use this

SELECT * FROM pets ORDER BY "name" ASC NULLS LAST;
SELECT * FROM pets ORDER BY "name" ASC NULLS FIRST;

In type orm there is a third parameter allowed addOrderBy.

await connection.manager
  .createQueryBuilder(Pet, "pets")
  .addOrderBy("pet.name", "ASC", "NULLS FIRST")
  .getOne();

Paging in Typeorm

To page results you can use take and skip. It's recommended to specify a sort when paging so your results are always as expected.

petRepository.find({
  order: {
    id: "ASC",
  },
  skip: 0,
  take: 10,
});

Aggregating in Typeorm

To select a sum, min, max or average you have to use a query builder and getRawOne().

const { sum } = await dataSource
  .getRepository(User)
  .createQueryBuilder("user")
  .select("SUM(user.photosCount)", "sum")
  .where("user.id = :id", { id: 1 })
  .getRawOne();

Matching strings in Typeorm

You can use a like query.

var data = await getRepository(PetProfile)
  .createQueryBuilder("pet")
  .where("pet.name like :petname", { petname: `%${petNameSearch}%` })
  .getMany();

Querying json in Postgres

If you store JSONB in a column you can query it through typeorm by using query builder.

Note that even though we're using raw sql in the where clause of the query builder we still use a parametrised query. Don't use string concat to pass the variable in the where string or you will open yourself to sql injection.

In the following example user.address is stored in jsonb format. We want to select all Floridians.

const data = await dataSource
  .getRepository(User)
  .createQueryBuilder("user")
  .where(`user.address->>'state' = :state`, { state: "florida" });

There are detailed documents on jsonb in Postgres on the postgres site.

Summary

You should have an idea of some built in functions in databases, and how to use them in typeorm.

These are the most common things I see in my day-to-day work but the typorm and postgres docs go into much more detail on their respective documentation sites.

Darragh ORiordan