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()
• 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 Between for dates

Note: JavaScript months are 0-indexed, so 6 below is July, not June. Use ISO strings or be careful with month values.

const users = await userRepository.find({
  where: {
    createdAt: Between(new Date('2022-07-01'), new Date('2022-07-30')),
  },
})

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()

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 because JavaScript's Number type can't safely represent values larger than Number.MAX_SAFE_INTEGER (about 9 quadrillion).

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 there is a third parameter allowed addOrderBy.

await dataSource
  .createQueryBuilder(Pet, 'pets')
  .addOrderBy('pets.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.

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