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
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()
)
Between for dates
const users = await userRepository.find({
where: {
createdAt: Between(new Date(2022, 6, 1), new Date(2022, 6, 30)),
},
})
NOT IN using typeorm
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
To sort you can pass in an order object
petRepository.find({
order: {
name: 'ASC',
},
})
Paging
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
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
You can use a like
query.
var data = await getRepository(PetProfile)
.createQueryBuilder('pet')
.where('pet.name like :petname', { petname: `%${petNameSearch}%` })
.getMany()
Querying json
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' })
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.
Top comments (0)