Introduction

Working With Monorepos

Setting up npm workspaces in our mono-repo

Workspaces

Setting up shared TypeScript, ESLint and Prettier configs

Shared Configs

Setting up the frontend with Vite, React and Tailwind

Frontend

Setting up the database schema and migrations

Schema

Backend

This module provides a walkthough of the setting and configuration so we’re ready to start building. This includes linting, code formatting, testing, npm workspaces, and build scripts. You will learn how to set up a frontend project with React and TailwindCSS, and a backend project with Koa. Finally, you will also set up Schemalint and Kanel to generate types and tRPC to act as the communication layer between backend and frontend.

Introduction and Getting Started

Looking into how to analyze a problem and create a product

Analyzing

Designing the Customer-facing UI with TailwindCSS

Data Model

In this module you will learn how to analyze a problem, design data model and UI for it and how to drive the architecture from the two. You will also learn about the considerations when deciding how to architect a solution, how to enable the optimal user journey, and potential future scenarios while creating a suited data model.

Analysis and Design

PostgreSQL Ranges

Getting properly started with our database schema

The Provider Table

Adding a booking table to our database schema

The Booking Table

In this module you will learn how to use the Postgres range type and enforce data consistency directly in the database.

Modelling with advanced Postgres features

Tailwind CSS

Foundation

Frontend State

Shared Components

Select Day

In this module you will build the UI that the customer is going to use usin Tailwind CSS. The flow will go through the process of booking a session with error handling.

Frontend UI

Introducing tRPC

Connecting to the tRPC server from the frontend

Connecting to tRPC

Refining the tRPC setup

Connecting our tRPC routes to the Database

Connecting to the Database

Generating Zod schemas

In this module, you will about designing API’s with tRPC and how to implement them. With the data model and UI as drivers, you will also learn which endpoints are required and how to implement them in tRPC.

Endpoints

Testing tRPC Endpoints

Testing With the Database

Testing Frontend

In this module, you will create unit tests of the frontend by mocking the tRPC endpoints using Vitest.

Testing

Completing the Select Date Page

Infrastructure for Ranges

Completing Booking

In this modele, you add some final touches to your app in the form of completing the Select Date page, creating the infrastructure for ranges, and completing the booking process.

Connecting The Dots

Conclusion and extra resources

In this module, you will learn about some additional resources realted to auth and security, DevOps, complex methods, and client side routing.

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

Vitest

Shared Components for Styling your React Application

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

In this comprehensive course you will gain 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 full-stack application architecture using PostgreSQL, NodeJS, Koa, Knex, React, TypeScript, tRPC, and Tailwind CSS, that is enforced with type safety, from the backend and data layer to the frontend UI.

### How The Course Works

- **Online**: The "Real-Time Collaborative Apps with Next.js and Supabase" course is a comprehensive online program spanning six modules.
- **Remote Access**: Take advantage of the course from any location, allowing you the flexibility to engage with the materials at your convenience.
- **Self-Paced Learning**: Tailor your learning experience to your schedule, enabling you to progress through the course content at your own pace.

**Community Engagement**: Collaborate and learn alongside a diverse community of fellow students, fostering an environment of shared knowledge and support.

**Structured Learning**: Experience a well-organized curriculum designed to provide a cohesive and comprehensive understanding of Angular Signals in a logical progression.

### Embrace the Full TypeScript Stack for Next-Level Web Development

"Move fast and break nothing" is the slogan on the tRPC landing page. Having type safety across your entire stack, from the database schema to the frontend UI, is a game changer. The course focuses on creating a type-safe application with the database as the source of truth.

The components used in this course are all mature and well-supported. The components are all open-ended, meaning these components aren't something that gets you going quickly but then becomes a burden when you need to do something they don't support. The architecture you will learn will be able to scale, not only in terms of complexity and performance requirements, but also in terms of the number of developers working on it.

Through hands-on examples and real-world scenarios, you'll learn how to integrate a suite of technologies to create a seamless development experience. 

By the end of this course, you will end up with a solid structure and understanding of how to solve the various challenges that come with building a full-stack application.

### Understanding the Challenges

This course aims to address several challenges in web app development, particularly as the codebase expands. Some of the challenges that this course focuses on include:

- **Minimizing errors**: The course emphasizes type safety, which helps ensure that variables and expressions align with their designated types, reducing the likelihood of errors.
- **Setting up a deployable app**: The course focuses on providing a foundation of infrastructure that is easy to build on, aiming to clear out most of the capital expenditures (CAPEX) involved in setting up an app for deployment.
- **Database as the source of truth**: The course guides you in using the database as the foundational source of truth for your application, allowing for advanced features and retaining type safety.
- **Simplifying the development process**: By using a monolithic architecture and avoiding unnecessary microservices, the course aims to simplify the development process and avoid premature complexity.
- **Providing a starting point for various SaaS applications**: While the course focuses on building a booking system, the architectural concepts and foundations covered can be applied to various SaaS applications.


### Why This Course?

- **Focus on Type Safety**: Learn the importance of type safety in reducing errors and building reliable software.
- **Database as Source of Truth**: Utilize advanced features of PostgreSQL to manage your application's data effectively.
- **Modern Technologies**: Get up to speed with current best practices using technologies like React, tRPC, and Tailwind CSS.
- **Scalability and Maintenance**: Prepare your applications for growth with scalable architecture and maintainable code.
- **Testing**: You will see how to set up testing of all parts of the stack, using a real database and type safety to ensure your application works as expected.

### Course Content Overview
Course Overview: Total Modules: **8** Total Lessons: **30** Total Video Runtime: **4 hours, 30 minutes**

### Summary
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.

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

Create a robust and scalable application architecture, from the backend data layer to the frontend UI. This comprehensive full-stack development course covers monorepos, TypeScript, Tailwind CSS, tRPC, and PostgreSQL, empowering you to build modern, scalable applications. Learn how to build a SaaS with type safety end-to-end, from the database through the backend and to the UI, with TypeScript, PostgreSQL, NodeJS and React. Using advanced features of PostgreSQL, you'll also learn how to achieve a high level of data integrity and performance, and how to manage database migrations using raw SQL for maximum control.

Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL

Master monorepos for streamlined development workflows and enhanced collaboration

Implementing type safety across your entire stack, starting from the database schema

Utilize Tailwind CSS to create consistent and beautiful user interfaces

Employ tRPC for seamless data exchange between frontend and backend

Managing database migrations using raw SQL for maximum control

Integrating modern back-end and front-end technologies

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

Setting up automated testing that utilizes type safety and a real database

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

The last bit of setup you need to do is what can be considered the very foundation: The database schema. Instead of using an ORM (Object Relational Mapper), you will be migrating the database with raw SQL and accessing it using knex.js, a query builder.

As the course name states, you will be using PostgreSQL. If you don't have it installed, it's trivial to start an instance using Docker:

$ docker run --name easybooking_db -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=easybooking -p 5432:5432 -d postgres

This will start a PostgreSQL instance on port 5432 (the default port for PostgreSQL), with a database called 

. The password may not look very safe which is on purpose as this is just the development database.

 file for the schema. We will use that folder as a place to keep the migrations and to generate types from the database.

 to create the migrations and to access the database. Let's install it, along with the PostgreSQL driver:

const path = require('path');

/**
 * @type { Object.<string, import("knex").Knex.Config> }
 */
module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: '127.0.0.1',
      user: 'postgres',
      password: 'postgres',
      database: 'easybooking',
      charset: 'utf8',
    },
    migrations: {
      directory: path.resolve(__dirname, 'migrations'),
    },
  },
};

Knex will use this file to know how to connect to the database. We need it only so that the Knex CLI works with your development database. When the backend is running, it will connect using environment variables that you orchestrate.

 folder and add a migration to create the 

$ mkdir migrations
$ npx knex migrate:make initial_setup

 folder for your migration. The name is prefixed with a timestamp, making sure they are always processed in order. You'll be adding the code to the migration file in a later lesson.

You might complain that the config file and the new migration file are JavaScript rather than TypeScript. While Knex supports TypeScript, I prefer to keep these files in JavaScript. There are a few advantages to this that we will get into later.

Let's have the migration create a minimal 

exports.up = function (knex) {
  return knex.raw(`
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) NOT NULL,
      password VARCHAR(255) NOT NULL
    );
  `);
};

exports.down = function (knex) {
  return knex.raw(`
    DROP TABLE users;
  `);
};

If you are not familiar with database migrations, you can think of them as a sort of ledger for the database schema. You basically add new files every time the schema should be updated. Each of these migration files is prefixed with a timestamp because it's important that they are applied in order.

Knex migrations (and most migrations with other engines) consist of an 

 part is what makes the changes we want. The 

 part is used mostly during development if you want to change your migration. It's supposed to undo all the changes that the 

 part did. Now obviously, this is not necessarily possible as changes could be destructive. It should always be possible to restore the 

 to the previous state though, which is often good enough for development.

I don't recommend using down-migrations for anything in production. I know that some people disagree with this but in my experience, it's safer to create another migration that reverts the changes, carefully constructed to deal with the discovered issue.

As you can see, we are using raw SQL to create the table. Thus, the fact that the file is JavaScript is of no consequence.
Make sure your development database is running and then run the migration:

Lastly, we want to generate TypeScript types from the schema. We will be using 

 plugin which offers some convenience functions for filtering out types that we don't want to generate. Install both:

const { knexTypeFilter } = require('kanel-knex');
const { generateIndexFile } = require('kanel');

const knexConfig = require('./knexfile');

const outputPath = './src/models';

/** @type {import('kanel').Config} */
module.exports = {
  connection: knexConfig.development.connection,

  outputPath,
  preDeleteOutputFolder: true,

  typeFilter: knexTypeFilter,
  preRenderHooks: [generateIndexFile],
};

Try running it and see what it generates:

 file in it. This file contains the type for the 

 file that exports this model for our convenience. You will want to export these from the root, so create another file 

This way, you'll be exporting your models from the schema package.

You can view the complete code for this lesson in the Replit below:

---
title: Schema
description: Setting up the database schema and migrations
isPublicLesson: true
privateVideoUrl: https://stream.mux.com/Y8NS00vLs00tuhMZlYmWl00Eube009It00KTuu01BOHLe00i8o.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_01/lesson_01.06?ref_type=heads
useMdx: true
---

## Schema

The last bit of setup you need to do is what can be considered the very foundation: The database schema. Instead of using an ORM (Object Relational Mapper), you will be migrating the database with raw SQL and accessing it using knex.js, a query builder.

As the course name states, you will be using PostgreSQL. If you don't have it installed, it's trivial to start an instance using Docker:

```bash
$ docker run --name easybooking_db -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=easybooking -p 5432:5432 -d postgres
```

This will start a PostgreSQL instance on port 5432 (the default port for PostgreSQL), with a database called `easybooking`. The password may not look very safe which is on purpose as this is just the development database.

You've already created a folder and `package.json` file for the schema. We will use that folder as a place to keep the migrations and to generate types from the database.

## Knex and Migrations

You're going to use [knex.js](https://knexjs.org/) to create the migrations and to access the database. Let's install it, along with the PostgreSQL driver:

```bash
$ cd packages/schema
$ npm i knex pg
```

Now, create a `knexfile.js` file in the root of the `packages/schema` folder:

```js
const path = require('path');

/**
 * @type { Object.<string, import("knex").Knex.Config> }
 */
module.exports = {
  development: {
    client: 'pg',
    connection: {
      host: '127.0.0.1',
      user: 'postgres',
      password: 'postgres',
      database: 'easybooking',
      charset: 'utf8',
    },
    migrations: {
      directory: path.resolve(__dirname, 'migrations'),
    },
  },
};
```

Knex will use this file to know how to connect to the database. We need it only so that the Knex CLI works with your development database. When the backend is running, it will connect using environment variables that you orchestrate.

Create the `migrations` folder and add a migration to create the `users` table:

```bash
$ mkdir migrations
$ npx knex migrate:make initial_setup
```

There will now be a file in the `migrations` folder for your migration. The name is prefixed with a timestamp, making sure they are always processed in order. You'll be adding the code to the migration file in a later lesson.

You might complain that the config file and the new migration file are JavaScript rather than TypeScript. While Knex supports TypeScript, I prefer to keep these files in JavaScript. There are a few advantages to this that we will get into later.

Let's have the migration create a minimal `users` table:

```js
exports.up = function (knex) {
  return knex.raw(`
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) NOT NULL,
      password VARCHAR(255) NOT NULL
    );
  `);
};

exports.down = function (knex) {
  return knex.raw(`
    DROP TABLE users;
  `);
};
```

If you are not familiar with database migrations, you can think of them as a sort of ledger for the database schema. You basically add new files every time the schema should be updated. Each of these migration files is prefixed with a timestamp because it's important that they are applied in order.

:::note
Knex migrations (and most migrations with other engines) consist of an `up` and a `down` part. The `up` part is what makes the changes we want. The `down` part is used mostly during development if you want to change your migration. It's supposed to undo all the changes that the `up` part did. Now obviously, this is not necessarily possible as changes could be destructive. It should always be possible to restore the _schema_ to the previous state though, which is often good enough for development.

I don't recommend using down-migrations for anything in production. I know that some people disagree with this but in my experience, it's safer to create another migration that reverts the changes, carefully constructed to deal with the discovered issue.
:::

As you can see, we are using raw SQL to create the table. Thus, the fact that the file is JavaScript is of no consequence.
Make sure your development database is running and then run the migration:

```bash
$ npx knex migrate:latest
```

## Generating Types

Lastly, we want to generate TypeScript types from the schema. We will be using [Kanel](github.com/kristiandupont/kanel) for this. We'll also use the `kanel-knex` plugin which offers some convenience functions for filtering out types that we don't want to generate. Install both:

```bash
$ npm i -D kanel kanel-knex
```

Configure Kanel by creating a `.kanelrc.js` file in the root of the `packages/schema` folder:

```js
const { knexTypeFilter } = require('kanel-knex');
const { generateIndexFile } = require('kanel');

const knexConfig = require('./knexfile');

const outputPath = './src/models';

/** @type {import('kanel').Config} */
module.exports = {
  connection: knexConfig.development.connection,

  outputPath,
  preDeleteOutputFolder: true,

  typeFilter: knexTypeFilter,
  preRenderHooks: [generateIndexFile],
};
```

Try running it and see what it generates:

```bash
$ npx kanel
```

You should now have a `src/models` folder with a `Users.ts` file in it. This file contains the type for the `users` table. There should also be an `index.ts` file that exports this model for our convenience. You will want to export these from the root, so create another file `/packages/schema/src/index.ts` with the following line:

```ts
export * from './models';
```

This way, you'll be exporting your models from the schema package.

You can view the complete code for this lesson in the Replit below:

<ReplitEmbed src ="https://replit.com/@newlineauthors2/lesson0106" />
