Migrations and Environment management

[rbkayz]

Hello community,

As we began moving our entire project to a staging and prod environment, we struggled with establishing the right repo structure, processes etc. to enable a smooth CI / CD workflow.

Hoping some of our learnings help. Would love to get your thoughts

---

Supabase - A suggestive guide for devops incl. repo & environment management

Devs,

As we are building hashmail, supabase powers a large part of our backend. Consequently, as we began moving our entire project to a staging and prod environment, we struggled with establishing the right repo structure, processes etc. to enable a smooth CI / CD workflow.

This guide is a recommendation based on what worked for us. It imbibes a lot of the guides written by the supabase team, as well as the github & discord community. As you implement this (or other similar systems in your teams), we hope to learn from you on how can we make this better

This guide will cover a few topics:

• repo structure
• environments for dev, staging, prod
• CI/CD setup
• useful tools and libraries

And is structured in a sequence of steps to improve your devops with supabase

Step 1 - Setup your repo structure

If you're using the postgres db, postgrest APIs, storage and edge functions, you'll have a lot of code in various languages (some function queries, triggers, RLS policies in psql, functions in deno, etc.)

To maintain it in the same repo, do the following:

1. Setup a new project repo in VSCode (the editor of choice for me) and initialize git.

2. Install the supabase CLI

3. Link your supabase project to the repo (this will create a folder called supabase, write your config.toml file, and setup the functions folder) - https://supabase.com/docs/reference/cli/usage

4. This folder will contain your edge functions (under ./supabase/functions) and migrations under (./supabase/migrations) - we cover migrations later

5. In the main project repo, create two new folders - .github (for your CI/CD workflows) and .vscode (for your settings.json file).

6. Finally, create a folder for your db queries (we will maintain all your function queries, triggers, RLS policies, etc. in this folder)

Step 2 - (OPTIONAL) Install some extensions / software

This step is purely to enhance your experience. Here are some of the extensions we prefer:

1. PostgreSQL explorer - https://marketplace.visualstudio.com/items?itemName=ckolkman.vscode-postgres
   This is so that you can run your SQL queries from vscode by connecting your supabase postgres db. (Connection details are in settings > database, on the studio)

2. Deno - https://marketplace.visualstudio.com/items?itemName=denoland.vscode-deno
   Installs the language server client for Deno, and has an inbuilt linter (removes the pesky lint errors if you use a standard Typescript linter)

3. pgFormatter or Prettier SQL VSCode - Optional linters for SQL

4. pgAdmin - I strongly recommend this. Is also my primary choice for diffing your environments (there's a CLI version that supabase launched, but I prefer this, since the diffing is not perfect in either cases, and a GUI helps).

Step 3 - Setting up your migrations

At this point, presumably, you have written some code, or are primed to do so. A good process we've found that works for us is this. In your dev branch, setup your code as follows:

• DB functions, written in psql, are placed in the queries folder (along with enums, triggers, RLS policies, and any other code in psql)
• Edge Functions in ts, are written in the functions folder (generated using supabase functions new 'function name')
• Migrations in SQL, are written in the migrations folder
• Github actions are written as yaml files in the .github/workflows folder

Once you are ready to move to a production environment, create a new supabase project (or two if you need staging). Do read this from the supabase team as well - https://supabase.com/docs/guides/cli/cicd-workflow

1. Create a new migration by calling supabase migration new <migration-name> in your terminal. This should create a new empty sql file in your migration called timestamp_migration_name.sql
2. If you go to your project studio, and see the schema supabase_migrations, you will see a single table, with a timestamp on each row. These refer to the migrations that are already applied.
3. Call supabase db push, and supabase will apply the new empty migration in your dev project. To confirm, you can check in the table that the latest timestamp has been added.
4. Now, we can begin the process of diffing your dev db to your staging db. To do this, I recommend using the schema diff tool on pgadmin.
5. Connect your dev and staging db servers to pgadmin. In the schema diff tool, run the schema diff (preferably one schema at a time - you can even avoid schemas like graphql, extensions or net, if the initial settings are the same across projects)
6. IMPORTANT! Once the diffing is complete, it isn't very intelligent. You need to manually reorder the generated SQL, so that it makes sense when applied in order
7. For example, you cannot create a RLS policy that refers to a function unless the function is already created. The recommended order is, tables > types > constraints > functions > RLS policies > triggers > anything else.
8. Paste all the SQL code in your newly created migration file. Now we're ready to setup our CI/CD pipeline

Step 4 - Enabling CI / CD workflows

Our setup is such that when we push a code to our 'staging' branch, it automatically triggers the latest migrations, and builds all the edge functions again.

To enable this,

1. Enable github actions on your repo
2. Setup your project secrets (project id, and db password) in github
3. Create these files in your repo under ./.github/workflows

name: CI

on:
  pull_request:
  workflow_dispatch:

jobs:
  test:
    runs-on: ubuntu-22.04
    steps:
      - uses: actions/checkout@v3

      - uses: supabase/setup-cli@v1
        with:
          version: 1.0.0

      - name: Start Supabase local development setup
        run: supabase start

      - name: Verify generated types are up-to-date
        run: |
          supabase gen types typescript --local > types.ts
          if [ "$(git diff --ignore-space-at-eol types.ts | wc -l)" -gt "0" ]; then
            echo "Detected uncommitted changes after build. See status below:"
            git diff
            exit 1
          fi

name: Deploy Migrations to Production
on:
  push:
    branches:
      - main
  workflow_dispatch:

jobs:
  deploy:
    runs-on: ubuntu-22.04

env:
  SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
  SUPABASE_DB_PASSWORD: ${{ secrets.PRODUCTION_DB_PASSWORD }}
  PRODUCTION_PROJECT_ID: abcdefghijklmnopqrst

steps:
  - uses: actions/checkout@v3

  - uses: supabase/setup-cli@v1
    with:
      version: 1.0.0

  - run: |
      supabase link --project-ref $PRODUCTION_PROJECT_ID
      supabase db push
      supabase functions deploy function1
      supabase functions deploy function2

4. Make sure you have added your function names to the production runner file. If you have another staging project, create a separate file for that

5. Now everytime you create a commit to the main branch, it will run this code, which will link your prod project, apply any unapplied migrations, and redeploy all functions.

6. To confirm that everything is okay, I recommend doing the following actions

7. Run your postman collection with all your APIs on your prod environment to check that all APIs are working

8. Run a diff again between your dbs, to make sure there is no missed changes

Other random remarks

If you have any recommendations or improvements, please do DM

The CLI setup by supabase for diffing is also great, especially if you use migra (gives very concise results)

When diffing in pgAdmin, you can choose ignore owner and whitespace, else you'll get a lot of random diffs that are not needed

When supabase brings out environments within a project, all of this will become redundant, so hoping for that day soon

[henningko]

Thanks for sharing this. Do you know of any way to only deploy functions when their code has actually been altered? Currently, every push will redeploy all functions, and using something like `on “/functions/“ will limit to pushes that include changes to the functions, but not a specific function.

[rbkayz]

Ah yes, I think you can write custom yaml for that. I haven't done that yet, but will check it out

[hamanuha]

Thank you for the insight into your workflow.
How do you handle the database functions from the queries folder? Do you copy them manually to the database?

[rbkayz]

To deploy the rpc functions on my dev db, I use our postgres extension on vscode, so directly deploy them

For the staging and prod db, I exclusively use diffs and migrations, so it's easy to rollback if needed

[hamanuha]

Ok, so based on the current function code you create a migration file.
So there is some duplicated code, right? Not ideal but then you know always how the current function looks like without searching the latest migration.

[rbkayz]

There is quite a bit of duplicated code.

But my source of truth for all the current state of the db is always pgadmin.

Things like RLS for non public schemas, types, enums, constraints, indexes, etc., are not yet available on the supabase admin console.

[scopethis]

Hey @rbkayz , thanks for the write up! Very useful.

Am I right to assume that you don't use Supabase Local (on development machines) as part of this setup?
Or do you all run local versions (for testing etc) before deploying to staging?

If not, was there a reason you chose not to include it?
(Just working out a good solution for our team)

Thanks.

[rbkayz]

Hey @scopethis ,

We really don't run the local setup. Mostly because we want fidelity of the seed data in the Dev db for testing

So our FE is nextjs deployed on vercel

The way we've setup is, all local deployments of the FE is linked to our Dev db environment

This way, even when I deploy the branch on a hosted domain on vercel, the data remains the same. Very useful for testing

Once tested on Dev db, we migrate to staging for a full qa.

And then finally to prod

[scopethis]

Data fidelity is a good, and sometimes ignored, aspect to consider. Thanks for highlighting.

[groyDoorvest]

Copying and updating the Deploy To Prod workflow yield the follow error

Invalid workflow file

No steps defined in steps and no workflow called in uses for the following jobs: deploy

[groyDoorvest]

Fixed the erroneous indentation from the example above and now it works.