Creating a GitHub Action to Monitor Column/Table Deletions in Prisma

The Project Origin

Here at Premier Octet, we have a particular fondness for certain technology stacks we trust, Prisma is one of them. This ORM allows us to efficiently manage our database, but during a recent code review, an important detail escaped us.

A migration generated by Prisma had provoked the deletion of a column in one of our tables, without it being immediately visible.

Yet, Prisma always generates a small comment like this one:

You are about to drop the column ... on the ... table. All the data in the column will be lost.

Unfortunately, in a voluminous pull request (PR), such modifications might fly under the radar. An error on our part drove us to seek an automated solution, to bring to light these sometimes critical deletions in our Prisma migrations and ensure the stability of our projects.

Objective

Having never created a GitHub Action before, I was inspired by a similar project called prisma-migration-warning-action by IanMitchell. With a bit of research, trials, and the help of a colleague whom we'll call Claude, I began to work on a simple GitHub Action. It should automatically check in the modifications of a PR if there are Prisma migrations that delete tables or columns, if this is the case, it should then alert the developers.

Development

The process to create this action is quite simple:

1. Recover the modified files: The first step is checking the files modified in a pull request. This is done via a diff between the base branch (e.g., main) and the pull request branch.
2. Filter the Prisma migration files: From all the modified files, we focus only on those that are located in the Prisma directory and end with .sql.
3. Detect table/column deletion: Then, we analyse the content of the migration files to detect a precise sequence of characters. Here, "drop" + ("column" or "table") + "All the data in the column will be lost".
4. Warning and notifications: If a deletion is detected, the GitHub Action posts a warning comment on the PR.

Here is an example of the main code to illustrate the logic:

async function run(): Promise<void> {
    const octokit = getOctokitClient();

    const path = core.getInput('path');
    const message = core.getInput('message');
    const warning = core.getBooleanInput('warning');

    const modifiedFiles = getModifiedFiles();

    const migrationFiles = modifiedFiles.filter(
      (file) => file.startsWith(path) && file.endsWith('.sql')
    );

    const hasTableOrColumnDrop = checkForDropsInMigrationsFiles(
      migrationFiles,
      detectTableOrColumnDrop
    );

    if (hasTableOrColumnDrop) {
      core.warning('A table or column drop has been detected.');
      if (warning) {
        await warnWithCommentOnPR(octokit, message);
      }
    } else {
      core.info('No drop detected.');
    }
}

await run().catch(() => {
  console.error('Error executing the action');
  process.exit(1);
});

Function Details

The code is divided into several utility functions that simplify the verification process. Here's a summary of the main ones:

• getModifiedFiles(): Retrieves the list of files modified in the PR using git diff.
• detectTableOrColumnDrop(): Checks if a file contains a column or table deletion.
• checkForDropsInMigrationsFiles(): Analyses all the migration files to detect column or table deletions, and launches a callback function on each file content (here detectTableOrColumnDrop).
• warnWithCommentOnPR(): If a deletion is detected, this function sends a warning comment in the PR via the GitHub API.

Conclusion

This GitHub Action project has been an excellent means to strengthen our code review by automating the detection of critical deletions in our Prisma migrations. It's a good example of how with a little coding and the tools GitHub provides, we can enhance our development workflow.

The Action is now available on the GitHub Actions Marketplace! If you use Prisma and want to secure your migrations, you can easily integrate it into your workflow. Here's a configuration example:

name: Check Prisma migrations
on:
  pull_request:
    branches:
      - main
jobs:
  check-migrations:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v3
        with:
          fetch-depth: 0
          ref: ${{ github.event.pull_request.head.sha }}
      - name: Check Prisma migrations
        uses: premieroctet/prisma-drop-migration-warning@v1.0.1
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}  # Set the GitHub token to access API
        with:
          main-branch: 'main' # Main branch of the repository
          path: 'prisma' # Path to Prisma migration files
          message: 'Potential drop detected in Prisma migration files.' # Comment message
          warning: true # Set to false to disable warning

The source code of the action is available on our GitHub repository, don't hesitate to try it and give us your feedback!