how can i use the Postgres DateRange type with TypeOrm

Question

i try to implement basic Hotel booking system with Nest.js framework. i use TypeOrm and Postgres Database.

i have a booking Entity (room reservation in the hotel) and Booking should have a DateRange (from check-in to checkout date).

i cannot find how to use the DateRange object type with TypeOrm.

@Entity({ name: 'booking' })
export class Booking{
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ 
        type: 'daterange', // I expect to find DateRange type here
    })
    date_range: any;
}

any idea?

Desdee

Answer 1

The daterange type is read as string and written as string. You need to use/parse the correct string represnation of the postgres range type.

To make parsing easier, you can add a constraint on the table to only allow inclusive ranges. Add this decorator to the class:

@Check('booking_range_valid', 'NOT isempty(range) AND lower_inc(range) AND NOT upper_inc(range)')

You need to write your own converter, to convert the Postgres daterange value to an interval representation. If you use Luxon Interval, you can use:

// Just a small helper to make transformers work with find operators
export const fixTransformer = (transformer: ValueTransformer) => ({
  to(value: unknown) {
    if (value instanceof FindOperator) {
      return new FindOperator(value.type , transformer.to(value.value),
          value.useParameter, value.multipleParameters);
    } else {
      // eslint-disable-next-line @typescript-eslint/no-unsafe-return
      return transformer.to(value);
    }
  },
  from(value: unknown) {
    // eslint-disable-next-line @typescript-eslint/no-unsafe-return
    return transformer.from(value);
  }
})
import {DateTime, Interval} from 'luxon';

// Postgres daterange string representation. [ means that start day is included, ) that end day is not.
const pattern = /\[(\d{4}-\d{2}-\d{2}),\s*(\d{4}-\d{2}-\d{2})\)/;
export const  dateRangeIntervalTransformer = fixTransformer({
    to(value: Interval | undefined): string | undefined {
        if (value !== undefined && !value.isValid) {
            throw new Error(`Got an invalid daterange, reason was ${value.invalidReason || '?'}`);
        }
        if (value) {
            if (!value.start.startOf('day').equals(value.start)) {
                throw new Error('Start of daterange needs to be the start of the day.');
            }
            if (!value.end.startOf('day').equals(value.end)) {
                throw new Error('End of daterange needs to be the start of the day.');
            }
            return '[' + value.start.toISODate() + ',' + value.end.toISODate() + ')';
        } else {
            return undefined;
        }
    },
    from(value: string | undefined): Interval | undefined {
        if (value) {
            const match = pattern.exec(value);
            if (!match) {
                throw new Error(`Date range does not match pattern ${pattern.source}: ${value}`);
            }
            return Interval.fromDateTimes(
                DateTime.fromISO(match[1]).startOf('day'),
                DateTime.fromISO(match[2]).startOf('day'));
        } else {
            return undefined;
        }
    }
});

Then use it in your entity:

@Entity({ name: 'booking' })
@Check('booking_range_valid', 'NOT isempty(range) AND lower_inc(range) AND NOT upper_inc(range)')
export class Booking{
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ 
        type: 'daterange',
        nullable: false, // < If you allow null, change the converter to handle null values
        transformer: dateRangeIntervalTransformer
    })
    date_range = Interval.after(DateTime.local().startOf('day'), {days: 1}); // < default value
}

Anarchic