Introduction

Working With Monorepos

Workspaces

Shared Configs

Frontend

Schema

Backend

Introduction and Getting Started

Analyzing

Designing the Customer-facing UI with TailwindCSS

Data Model

Analysis and Design

PostgreSQL Ranges

The Provider Table

The Booking Table

Modelling with advanced Postgres features

Tailwind CSS

Foundation

Frontend State

Shared Components

Select Day

Frontend UI

Introducing tRPC

Connecting to tRPC

Refining the tRPC setup

Connecting to the Database

Generating Zod schemas

Endpoints

Testing tRPC Endpoints

Testing With the Database

Testing Frontend

Testing

Completing the Select Date Page

Infrastructure for Ranges

Completing Booking

Connecting The Dots

Conclusion and extra resources

This comprehensive course will equip you with the skills and knowledge to build modern full-stack applications using TypeScript, TailwindCSS, tRPC, and PostgreSQL. You will learn how to create a robust and scalable application architecture, from the backend data layer to the frontend UI.

PostgreSQL

NodeJS

Knex

React

TypeScript

tRPC

Monorepos simplify code development and can be split when necessary.

Setting up npm workspaces in our monorepo

Setting up shared Typescript, ESLint and Prettier configs

Master full-stack development with a single repository for everything!

Learn the art of full-stack development with this comprehensive course. Through implementing the latest technologies and techniques, the course directly addresses the most common challenges full-stack developers face:
- **Code consistency and sharing:** Monorepos eliminate the need for multiple repositories, ensuring consistent code across your application and facilitating seamless code sharing.
- **Technology integration:** Learn how to integrate modern technologies like TypeScript, Tailwind CSS, and tRPC, empowering you to build robust and maintainable applications.
- **Database management:** Master advanced PostgreSQL features to optimize data storage, retrieval, and manipulation, ensuring a high-performance and scalable backend.

Build type-safe full-stack applications with database-centric strategies

This comprehensive full-stack development course covers monorepos, TypeScript, Tailwind CSS, tRPC, and PostgreSQL, empowering you to build modern, scalable applications. Structured into 7 modules of bite-sized lessons, each module is designed to be engaging and informative, combining video lectures, hands-on coding exercises, and real-world examples.

Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL

Writing PostgreSQL ranges for the application database.

Breaking down the UI to build it with Tailwind CSS

Master monorepos for streamlined development workflows and enhanced collaboration

Implement TypeScript for type-safe code that prevents errors and improves maintainability

Utilize Tailwind CSS to create consistent and beautiful user interfaces

Employ tRPC for seamless data exchange between frontend and backend

Harness advanced PostgreSQL features for optimized data storage, retrieval, and manipulation

Build modern, scalable full-stack applications using a unified approach

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.

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

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 

 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 

. 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 

---
title: Completing the Select Date Page
description: Completing the Select Date Page
isPublicLesson: true
privateVideoUrl: https://stream.mux.com/4tEMC008I1Z02PMMR01ypeWFnfkOgqvgWGvW01haIEi273w.m3u8
code: https://gitlab.com/fullstackio/books/newline-course-apps/fullstack-typescript-with-tailwindcss-and-trpc-using-modern-features-of-postgresql-app/-/tree/main/module_07/lesson_07.01?ref_type=heads
---

## Completing the Select Date Page

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`:

```ts
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`:

```ts
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;
    }),

Learn

The newline Guide to Building Your First GraphQL Server with Node and TypeScript

Teach

Amelia Wattenberger

Author of Fullstack D3

Community

Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL

Module 1: Introduction and Getting Started

Introduction:

Start Here

Lesson 1: (3:09)

Introduction

Lesson 2: (6:35)

Working With Monorepos

Lesson 3: (6:45)

Workspaces

Lesson 4: (17:56)

Shared Configs

Lesson 5: (9:27)

Frontend

Lesson 6: (16:20)

Schema

Lesson 7: (5:21)

Backend

Module 2: Analysis and Design

Lesson 1: (3:09)

Analyzing

Lesson 2: (4:04)

Designing the Customer-facing UI with TailwindCSS

Lesson 3: (2:49)

Data Model

Module 3: Modelling with advanced Postgres features

Lesson 1: (6:18)

PostgreSQL Ranges

Lesson 2: (16:10)

The Provider Table

Lesson 3: (4:47)

The Booking Table

Module 4: Frontend UI

Lesson 1: (1:25)

Tailwind CSS

Lesson 2: (7:58)

Foundation

Lesson 3: (21:53)

Frontend State

Lesson 4: (4:52)

Shared Components

Lesson 5: (16:13)

Select Day

Module 5: Endpoints

Lesson 1: (7:45)

Introducing tRPC

Lesson 2: (17:22)

Connecting to tRPC

Lesson 3: (7:22)

Refining the tRPC setup

Lesson 4: (7:28)

Connecting to the Database

Lesson 5: (4:56)

Generating Zod schemas

Module 6: Testing

Lesson 1: (4:33)

Testing tRPC Endpoints

Lesson 2: (25:00)

Testing With the Database

Lesson 3: (12:37)

Testing Frontend

Module 7: Connecting The Dots

Lesson 1: (21:07)

Completing the Select Date Page

Lesson 2: (14:07)

Infrastructure for Ranges

Lesson 3: (15:49)

Completing Booking

Module 8: Conclusion and extra resources

Lesson 1: (1:02)

Conclusion and extra resources

Completing the Select Date Page