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

Published on June 12, 2022

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.

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
  createdDate: Date

  // and this!
  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
  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
  .where(' 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

  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


In type orm there is a third parameter allowed addOrderBy.

await connection.manager
  .createQueryBuilder(Pet, 'pets')
  .addOrderBy('', 'ASC', 'NULLS FIRST')

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.

  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
  .select('SUM(user.photosCount)', 'sum')
  .where(' = :id', { id: 1 })

Matching strings in Typeorm

You can use a like query.

var data = await getRepository(PetProfile)
  .where(' like :petname', { petname: `%${petNameSearch}%` })

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
  .where(`user.address->>'state' = :state`, { state: 'florida' })

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


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

Darragh ORiordan

Hi! I'm Darragh ORiordan.

I live and work in Sydney, Australia building and supporting happy teams that create high quality software for the web.

I also make tools for busy developers! Do you have a new M1 Mac to setup? Have you ever spent a week getting your dev environment just right?

My Universal DevShell tooling will save you 30+ hours of configuring your Windows or Mac dev environment with all the best, modern shell and dev tools.

Get DevShell here: ✨

Read more articles like this one...

List of article summaries


Building an AI generated game with Stable Diffusion and data from Wikipedia

Last week I released a game called Doodle:ai.

In the game you’re shown AI generated images and you have to guess the Wikipedia topic it used to create the game.


Easiest way to optimise images for web

Here is how I optimise all pngs and jpgs in a folder for publishing to the web.


Start tracking DORA metrics for your team in just 15 minutes with Apache Dev Lake

DORA (DevOps Research and Assessment) metrics are an excellent way for engineering organisations to measure and improve their performance.

Up until now, monitoring the DORA metrics across Github, Jira, Azure Devops etc required custom tooling or a tedious manual process.

With Apache Dev Lake you can get beautiful reporting for DORA metrics on your local machine in as little as 15 minutes (honestly!).

From Google Sheets to Grafana
From Google Sheets to Grafana


A summary of NDC Sydney 2022 Developer Conference

I attended my first in-person conference for more than 3 years last week! NDC is one of the more well-known developer conferences in Australia and New Zealand. It’s a 5 day conference with 3 days of talks and 2 days of workshops.

There’s so much to learn across all the streams so I try to take notes for each of the talks to quickly reference them later. This post contains all my notes. I’ll add the relevant videos to talks later if they’re released.

A reminder that these notes are just my notes. They’re paraphrased and summarised from what the speaker actually said. Each speakers would have provided must more clarity and went into more detail during their pressos!