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 (timestamptz) 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 millisecond precision, which matches JavaScript's Date object.

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 a where deletedAt IS NULL 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 field called withDeleted that you can set to true to include soft-deleted records.

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 properties 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()
• ILIKE = ILike() (postgres case-insensitive matching)
• BETWEEN = Between()
• IN = In()
• IS NULL = IsNull()
• "currentDate" > NOW() = Raw((alias) => ${alias} > NOW())

Note: Brackets() is used with QueryBuilder for grouping conditions, not with the repository find() method.

Typeorm date ranges

Between() includes both exact endpoints. That can be surprising with dates: an end value of 2022-07-30 means midnight at the start of that day, not the whole day. For calendar ranges I normally use an inclusive start and exclusive end.

const users = await userRepository
  .createQueryBuilder('user')
  .where('user.createdAt >= :start AND user.createdAt < :end', {
    start: new Date('2022-07-01T00:00:00Z'),
    end: new Date('2022-08-01T00:00:00Z'),
  })
  .getMany()

Typeorm NOT IN

const subQuery = petStoreRepo.createQueryBuilder('store').select('store.id')

const petsToStockInStore = await petRepository
  .createQueryBuilder('pet')
  .where(`pet.storeId NOT IN (${subQuery.getQuery()})`)
  .setParameters(subQuery.getParameters())
  .getMany()

Be careful if the subquery can return NULL: SQL's three-valued logic can make NOT IN return no matches. NOT EXISTS is often safer for that case.

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('text', { 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. TypeORM returns bigint values as strings by default because JavaScript's Number type can't safely represent values larger than Number.MAX_SAFE_INTEGER (about 9 quadrillion). The postgres driver also has a parseInt8 option, but only use it when you know every value will stay inside that safe range.

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 null values at the end for ASC and at the beginning for DESC by default.

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 the third argument to addOrderBy controls NULLS FIRST or NULLS LAST.

await dataSource
  .createQueryBuilder(Pet, 'pets')
  .addOrderBy('pets.name', 'ASC', 'NULLS FIRST')
  .getMany()

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

Current TypeORM repositories have sum(), minimum(), maximum() and average() helpers for simple aggregates.

const total = await dataSource.getRepository(User).sum('photosCount', {
  id: 1,
})

For grouped or more complicated aggregates, or when using the older TypeORM version in the sample repository, 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. In postgres LIKE is case-sensitive; use ILike() or the ILIKE operator when you want case-insensitive matching.

const data = await dataSource
  .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' })
  .getMany()

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.

Lesson index