Vercel + PlanetScale CI/CD pipeline using Github Actions for T3 Stack NotesApp (Next.js + tRPC+ Prisma + Tailwind) — Part 2: Implementation

11 min readSep 5, 2023

Introduction

The introduction, which discusses the tech stack and describes the desired system behaviour, can be found in Part 1 of this series.

Github repository and T3 app initialization

Code changes will be provided in the article, but you can also refer to my notes-app repository.

Start by creating and cloning the Github repository. I’ll name my repository “notes-app”.
Position inside your repository directory using terminal and create a T3 app with the required parameters :

pnpm create t3-app@latest

I’ll enter “notes-app” as app name (same as my repo name).
Choose “typescript” as the language.
Select “prisma,” “tailwind,” and “trpc” in the include options.
Skip creating a git repository
Skip “pnpm install”
Skip creating aliases

3. Move the app files (regular and hidden ones) from the generated folder to the root of your repository and remove the generated folder:

mv notes-app/* .
mv notes-app/.* .
rm -r notes-app

4. Install the project dependencies, start the development server and access your app on http://localhost:3000:

pnpm install
pnpm run dev

5. Commit and push your changes to the main branch.

Create Vercel project

Go to the vercel.com, create an account (in case you don’t have it) and create an organization inside of it as well. I recommend doing it via Github account because of easy integration and SSO.
Create new Vercel project:

make sure you select Next.js as framework preset
I’ll name my “notes-app” (same as my repository)
give Vercel access to your repository
select your repository and import it
add environment variable DATABASE_URL with some random URL value such as “https://example.com” (this is not important at this moment, but our env.mjs configuration requires this URL env var for initial deployment)

3. Once the app has been deployed, you can access it using one of the links that Vercel gives you (you will get more than one domain).

Create PlanetScale project

Go to the planetscale.com and create an account and an organization inside of it as well.
Create a new database (I’ll call my notes-app just like my repo) and select desired AWS region for your database and your plan. I’ll go with the us-east-2 region and the hobby (free) plan.

3. Once database has been initialized, you need to create a password which will allow you to connect to the database.
(Settings > Passwords > New password)
Note that the password can only be seen once after the creation, so make sure you copy it and store it somewhere safe. You can select password format as a Prisma connection string.

4. Create dev branch for your PlanetScale database.
(Branches > New branch)

5. Create and store password for dev branch just like you did it for the main branch.

6. Enable safe migrations so that we can work with deploy requests.
(Overview > Settings “wheel” button near “safe migrations” label)

7. To identify ourselves inside the PlanetScale API requests which will be used in Github Actions, we need to generate a new service token for our PlanetScale organization and store it somewhere safe (service token id and service token itself). Make sure to give permissions to the service token inside “Database access” section (you can give it all available permissions).

Configure Vercel environment variables

Once you get your connection strings, navigate to Vercel project settings and select “Environment Variables” section.

edit DATABASE_URL env var by unchecking preview and development checkboxes (we don’t want to use same database for production, development and preview deployments) and set it’s value to your PlanetScale main branch connection string

create new DATABASE_URL env var only for preview and development environments with value of your PlanetScale dev branch connection string

Configure Github Action secrets

Set repository secrets based on the values you obtained in previous steps:

PSCALE_MAIN_DATABASE_URL
PSCALE_DEV_DATABASE_URL
PSCALE_DATABASE_NAME
PSCALE_ORG (the name of your PlanetScale organization, for me it’s “maticluka999”)
PSCALE_SERVICE_TOKEN_ID
PSCALE_SERVICE_TOKEN

Github workflows

Create directory “.github” in the root of yoru project and then create “workflows” directory inside of .github directory. Here we’ll store all of our workflows so that Github can recognize them.
Create workflow “push-schema-to-dev.yml” for pushing database schema changes to PlanetScale dev branch database which will execute every time something is pushed to the dev branch (this includes merging pull requests from feat/fix branches into dev branch), but only if there are some changes inside the “schema.prisma” file:

name: push-schema-to-dev

on:
  push:
    branches: [dev]
    paths: ["prisma/schema.prisma"]

jobs:
  deploy-migrations-to-dev:
    runs-on: ubuntu-latest

    strategy:
      matrix:
        node-version: [18.16.0]
        pnpm-version: [7.29.3]

    steps:
      - name: Print event
        run: echo ${{ github.event_name }}

      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Create .env file
        run: echo DATABASE_URL=${{ secrets.PSCALE_DEV_DATABASE_URL }} > .env

      - name: Install pnpm ${{ matrix.pnpm-version }}
        uses: pnpm/action-setup@v2
        with:
          version: ${{ matrix.pnpm-version }}

      - name: Push schema changes
        run: npx prisma db push

3. Create workflow “create-deploy-request.yml” for creating PlanetScale deploy request every time there is a pull request created for merging changes into the main branch, but only if there are some changes inside the “schema.prisma” file. We are going to use PlanetScale’s action for creating deploy requests to do this:

name: create-deploy-request

on:
  pull_request:
    branches: [main]
    paths: ["prisma/schema.prisma"]

jobs:
  create-deploy-request:
    runs-on: ubuntu-latest

    steps:
      - name: Create a deploy request
        uses: planetscale/create-deploy-request-action@v1
        with:
          org_name: ${{ secrets.PSCALE_ORG }}
          database_name: ${{ secrets.PSCALE_DATABASE_NAME }}
          branch_name: dev
        env:
          PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PSCALE_SERVICE_TOKEN_ID }}
          PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PSCALE_SERVICE_TOKEN }}

4. Create workflow “review-deploy-request.yml” for deploying PlanetScale deploy request to main database branch once pull request has been merged into main branch, but only if there are some changes inside the “schema.prisma” directory and if the deploy request contains changes. In case of closing (rejecting) pull request, the corresponding deploy request will be closed as well. Since PlanetScale hasn’t yet provided the actions for deploying and canceling deploy requests, we are going to use PlanetScale API with curl:

name: review-deploy-request

on:
  pull_request:
    branches: [main]
    types: [closed]
    paths: ["prisma/schema.prisma"]

jobs:
  review-deploy-request:
    runs-on: ubuntu-latest

    steps:
      - name: Get deploy request
        id: get_deploy_request
        run: |
          response=$(curl --request GET \
          --url 'https://api.planetscale.com/v1/organizations/${{ secrets.PSCALE_ORG }}/databases/${{ secrets.PSCALE_DATABASE_NAME }}/deploy-requests?state=open&branch=dev&into_branch=main' \
          --header 'Authorization: ${{ secrets.PSCALE_SERVICE_TOKEN_ID }}:${{ secrets.PSCALE_SERVICE_TOKEN }}' \
          --header 'accept: application/json')

          number=$(echo "$response" | jq -r '.data[0].number')
          echo $number
          echo "number=$number" >> $GITHUB_OUTPUT

          deployment_state=$(echo "$response" | jq -r '.data[0].deployment_state')
          echo $deployment_state
          echo "deployment_state=$deployment_state" >> $GITHUB_OUTPUT

      - name: Deploy deploy request (if PR merged and there are schema changes)
        if: ${{ (github.event.pull_request.merged == true) && (steps.get_deploy_request.outputs.deployment_state != 'no_changes') }}
        uses: planetscale/deploy-deploy-request-action@v1
        with:
          org_name: ${{ secrets.PSCALE_ORG }}
          database_name: ${{ secrets.PSCALE_DATABASE_NAME }}
          number: ${{ steps.get_deploy_request.outputs.number }}
          wait: true
        env:
          PLANETSCALE_SERVICE_TOKEN_ID: ${{ secrets.PSCALE_SERVICE_TOKEN_ID }}
          PLANETSCALE_SERVICE_TOKEN: ${{ secrets.PSCALE_SERVICE_TOKEN }}

      - name: Close deploy request (if PR closed or there are no schema changes)
        if: ${{ (github.event.pull_request.merged == false) || (steps.get_deploy_request.outputs.deployment_state == 'no_changes') }}
        run: |
          curl --request PATCH \
          --url https://api.planetscale.com/v1/organizations/${{ secrets.PSCALE_ORG }}/databases/${{ secrets.PSCALE_DATABASE_NAME }}/deploy-requests/${{ steps.get_deploy_request.outputs.number }} \
          --header 'Authorization: ${{ secrets.PSCALE_SERVICE_TOKEN_ID }}:${{ secrets.PSCALE_SERVICE_TOKEN }}' \
          --header 'accept: application/json' \
          --header 'content-type: application/json' \
          --data '{"state":"closed"}'

5. Commit and push your changes to the main branch.

Setup local database and Prisma schema

Make sure you checkout to dev branch before this section.

You don’t have to use Docker for local database setup, however, I prefer doing it this way because by deleting containers and volumes we can easily clear everything related to our databases.

Create a Docker Compose file named “docker-compose.yml” in the root of your project for configuring MySQL database which we are going to use for local development.

version: "3.9"

services:
  mysql_db:
    image: mysql:8.0.33
    container_name: mysql_db
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: mysql_root_password
      MYSQL_DATABASE: mysql_db
      MYSQL_USER: mysql_user
      MYSQL_PASSWORD: mysql_password
    ports:
      - "33060:3306"
    volumes:
      - mysql_db_data:/var/lib/mysql

volumes:
  mysql_db_data:

2. Start the local database (using docker-compose up command).

3. Set the necessary environment variable in a .env file located in the root of your repository:

DATABASE_URL=mysql://mysql_user:mysql_password@localhost:33060/mysql_db

4. Inside the “prisma.schema” file, set relation mode to “prisma” because PlanetScale doesn’t allow foreign keys in database schema since it is built on top of Vitess (more about it here).

generator client {
    provider = "prisma-client-js"
}

datasource db {
    provider     = "mysql"
    url          = env("DATABASE_URL")
    relationMode = "prisma"
}

model Note {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    text      String   @default("")
}

5. Push schema changes to database by running the following command:

npx prisma db push

We are not using prisma migrate because, based on Prisma docs, it is not recommended when working with PlanetScale: “When you merge a development branch into your production branch, PlanetScale will automatically compare the two schemas and generate its own schema diff. This means that Prisma’s prisma migrate workflow, which generates its own history of migration files, is not a natural fit when working with PlanetScale. These migration files may not reflect the actual schema changes run by PlanetScale when the branch is merged”. You can read more about this here.

Note: You can view and edit your local database records via Prisma Studio:

npx prisma studio

6. Inside src/server/api/routers directory, delete example.ts file and create notes.ts file containing notes tRPC router:

import { z } from "zod";
import { createTRPCRouter, publicProcedure } from "~/server/api/trpc";

export const notesRouter = createTRPCRouter({
  create: publicProcedure
    .input(z.object({ text: z.string() }))
    .mutation(({ ctx, input }) => {
      const { text } = input;
      return ctx.prisma.note.create({ data: { text } });
    }),
  getAll: publicProcedure.query(({ ctx }) => {
    return ctx.prisma.note.findMany();
  }),
});

NOTE: Run npx prisma generate command and restart VS code for type recognition if you are getting type error like this:

7. Add notes router to root tRPC router:

import { notesRouter } from "~/server/api/routers/notes";
import { createTRPCRouter } from "~/server/api/trpc";

/**
 * This is the primary router for your server.
 *
 * All routers added in /api/routers should be manually added here.
 */
export const appRouter = createTRPCRouter({
  notes: notesRouter,
});

// export type definition of API
export type AppRouter = typeof appRouter;

8. Update index.tsx file by adding create example and display examples functionality:

import Head from "next/head";
import { useState } from "react";
import { api } from "~/utils/api";

export default function Home() {
  const createNoteMutation = api.notes.create.useMutation();
  const getAllNotesQuery = api.notes.getAll.useQuery();

  const [text, setText] = useState<string>();

  function createNote() {
    if (!text) {
      return;
    }

    createNoteMutation.mutate(
      { text },
      {
        async onSuccess() {
          await getAllNotesQuery.refetch();
        },
      }
    );
  }

  return (
    <>
      <Head>
        <title>NotesApp</title>
      </Head>
      <main className="flex min-h-screen flex-col items-center bg-gradient-to-b from-[#2e026d] to-[#15162c] text-white">
        <div className="container flex flex-col items-center justify-center gap-12 px-4 py-16">
          <div className="flex items-center space-x-4">
            <input
              className="w-[300px] rounded p-2 text-black"
              onChange={(e) => setText(e.target.value)}
            />
            {createNoteMutation.isLoading ? (
              <div className="w-[180px]">Inserting note...</div>
            ) : (
              <button
                className="w-[180px] rounded bg-[hsl(280,100%,70%)] p-2 hover:bg-[hsl(280,100%,70%)]/70"
                onClick={createNote}
              >
                Insert new note
              </button>
            )}
          </div>
          {getAllNotesQuery.isLoading ? (
            <div>Loading notes...</div>
          ) : (
            <>
              {getAllNotesQuery.data ? (
                <div className="flex flex-col items-center">
                  <div className="mb-3 text-2xl">Notes:</div>
                  {getAllNotesQuery.data.map((note) => (
                    <div key={note.id}>{note.text}</div>
                  ))}
                </div>
              ) : (
                <div>Could not retrieve notes.</div>
              )}
            </>
          )}
        </div>
      </main>
    </>
  );
}

9. Restart the server (pnpm run dev) and access your app at http://localhost:3000 to test inserting and reading notes.

Testing workflows

Commit and push changes to the dev branch and wait for our first workflow to execute.

After the workflow execution, we can see on the PlanetScale dev branch dashboard that we have our schema pushed to the dev database.

Also, we can open our dev environment using Vercel dashboard and test our functionality.

2. Create pull request for merging changes from dev to main branch.

After the workflow execution, on the PlanetScale dev branch dashboard we can see that we have successfully created a deploy request for deploying changes to the main branch database.

3. Merge pull request into the main branch.

After the workflow execution, on the PlanetScale main branch dashboard we can see that the deploy request has been successfully deployed to the main branch database.

Now we can open our Vercel main deployment and test our functionality.

Summary

We managed to set up a CI/CD pipeline for a NotesApp using Vercel, PlanetScale and GitHub Actions. We covered creating projects, configuring environments, establishing workflows for schema changes and deployments, setting up a local database with Docker and then we tested the entire process. Hope you enjoyed it! If you have any suggestions on how to improve this pipeline, please share them in the comment section!