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

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.

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

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


Setting up the database schema and migrations

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

Schema