Completing the Select Date Page
Completing the Select Date Page
Get the project source code below, and follow along with the lesson material.
Download Project Source CodeTo set up the project on your local machine, please follow the directions provided in the README.md
file. If you run into any issues with running the project source code, then feel free to reach out to the author in the course's Discord channel.
Ok, at this point we are starting to see end-to-end, most of the hard infrastructure pieces have been put together. So let's try to actually make your app do something. I will increase the pace a bit since most of what we're doing now is just building with the blocks we have already established.
You have the schema for bookings in place, but you are not yet using it. Figuring out whether or not a time slot is available involves a bit of set theory. That is something we can choose to do in a couple of places: in the database, using a stored procedure/function or view, on the backend or on the frontend. I personally like to make the database give me basic data that matches basic invariants. Complex logic is hard to debug for the simple reason that debugging is harder in a database than in a general purpose environment like Node or the browser.
The backend, as I see it, should filter and handle data that is potentially privileged, and it should seek to minimize the amount of data that is sent to the frontend. Once that is done, it's perfectly fine to let the frontend apply logic to data. For this reason, I often work with normalized data all the way through the frontend and only combine things there.
The problem we are facing first is showing the customer which dates and times they can make a booking. Times where every provider in the database are booked should appear as unavailable, but any slot where at least one provider is free should appear as bookable. If the customer chooses said slot, the system should figure out which provider to make the booking with.
getAvailableSlots endpoint#
So, let's create an endpoint that gets available slots within a time frame. We'll make a query on the backend that gets us availability for each provider. Then we'll combine it on the frontend to figure out which days and time slots are book-able.
We'll create a new file for this query as it's not quite as trivial as what we've been doing so far. Create /services/backend/src/trpc/getBookedSlots.ts
:
xxxxxxxxxx
import { format } from 'date-fns';
import { Knex } from 'knex';
import { z } from 'zod';
export const slot = z.object({
time: z.date(),
providerId: z.number(),
booked: z.boolean(),
});
export type Slot = z.infer<typeof slot>;
const fmt = 'yyyy-MM-dd HH:mm:SS';
async function getBookedSlots(
knex: Knex,
start: Date,
end: Date
): Promise<Slot[]> {
const interval = '3 hour';
const caseQuery = knex('booking')
.select('id')
.whereRaw('booking.provider_id = provider.id')
.andWhereRaw('during && tsrange(?, ?)', [
knex.raw("date_trunc('hour', time)"),
knex.raw(`date_trunc('hour', time) + INTERVAL '${interval}'`),
]);
const res = await knex
.select<Slot[]>(
knex.raw(
'time, provider.id as "providerId", CASE WHEN EXISTS (?) THEN true ELSE false END AS booked',
caseQuery
)
)
.from(
knex.raw(
`generate_series(?::timestamp, ?::timestamp, INTERVAL '${interval}') AS time`,
[format(start, fmt), format(end, fmt)]
)
)
.joinRaw('JOIN provider on true')
.whereRaw('extract(dow FROM time) > 0') // exclude Sundays
.whereRaw('extract(dow FROM time) < 6') // exclude Saturdays
.whereRaw('extract(hour FROM time) >= 8') // exclude before 8am
.whereRaw('extract(hour FROM time) < 16'); // exclude after 4pm
return res;
}
export default getBookedSlots;
This is a rather complex Knex query, which I would definitely consider turning into a view or stored procedure. Not least because we're pushing Knex itself to its limits and need to fall back to raw
here and there. Kanel does not currently support generating types out of functions or stored procedures but I intend to introduce that so it would be as streamlined as everything else. For now though, we are doing this operation on the backend and so we make an explicit type of the output, called slot
. The query divides every day into time slots of three hours and returns an array for each provider, telling us whether or not that specific slot is available or booked.
Let's expose this function in an endpoint. Update /services/backend/src/trpc/appRouter.ts
:
xxxxxxxxxx
import { z } from 'zod';
import { add } from 'date-fns';
import { publicProcedure, router } from './trpc';
import { serviceType } from '@easybooking/schema/dist/models/public/ServiceType';
import getBookedSlots, { slot } from './getBookedSlots';
const getServiceTypesOutput = z.array(serviceType);
const createServiceTypeInput = z.object({
name: z.string(),
description: z.string(),
});
const getAvailabilityInput = z.object({
startDate: z.date(),
numberOfDays: z.number(),
});
const getAvailabilityOutput = z.array(slot);
const appRouter = router({
ping: publicProcedure.query(async () => 'pong'),
getServiceTypes: publicProcedure
.output(getServiceTypesOutput)
.query(async ({ ctx }) => {
const serviceTypes = await ctx.trx('service_type').select('*');
return serviceTypes;
}),
createServiceType: publicProcedure
.input(createServiceTypeInput)
.output(serviceType)
.mutation(async ({ ctx, input }) => {
const [result] = await ctx
.trx('service_type')
.insert(input)
.returning('*');
return result;
}),
getAvailability: publicProcedure
.input(getAvailabilityInput)
.output(getAvailabilityOutput)
.query(async ({ ctx, input }) => {
const endDate = add(input.startDate, { days: input.numberOfDays });
const res = await getBookedSlots(ctx.trx, input.startDate, endDate);
return res;
}),
});
export type AppRouter = typeof appRouter;
export default appRouter;
This lesson preview is part of the Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL course and can be unlocked immediately with a \newline Pro subscription or a single-time purchase. Already have access to this course? Log in here.
Get unlimited access to Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL, plus 70+ \newline books, guides and courses with the \newline Pro subscription.