Managing Environments

Manage multiple environments using Database Migrations and GitHub Actions.

This guide shows you how to set up your local Supra development environment that integrates with GitHub Actions to automatically test and release schema changes to staging and production Supra projects.

Set up a local environment#

The first step is to set up your local repository with the Supra CLI:


_10supra init

You should see a new supra directory. Then you need to link your local repository with your Supra project:


_10supra login
_10supra link --project-ref $PROJECT_ID

You can get your $PROJECT_ID from your project's dashboard URL:


_10https://supra.com/dashboard/project/<project-id>

If you're using an existing Supra project, you might have made schema changes through the Dashboard. Run the following command to pull these changes before making local schema changes from the CLI:


_10supra db pull

This command creates a new migration in supra/migrations/<timestamp>_remote_schema.sql which reflects the schema changes you have made previously.

Now commit your local changes to Git and run the local development setup:


_10git add .
_10git commit -m "init supra"
_10supra start

You are now ready to develop schema changes locally and create your first migration.

Create a new migration#

There are two ways to make schema changes:

Manual migration: Write DDL statements manually into a migration file
Auto schema diff: Make changes through Studio UI and auto generate a schema diff

Manual migration#

Create a new migration script by running:


_10supra migration new new_employee

You should see a new file created: supra/migrations/<timestamp>_new_employee.sql. You can then write SQL statements in this script using a text editor:


_10create table public.employees (
_10  id integer primary key generated always as identity,
_10  name text
_10);

Apply the new migration to your local database:


_10supra db reset

This command recreates your local database from scratch and applies all migration scripts under supra/migrations directory. Now your local database is up to date.

tip

The new migration command also supports stdin as input. This allows you to pipe in an existing script from another file or stdout:

supra migration new new_employee < create_employees_table.sql

Auto schema diff#

Unlike manual migrations, auto schema diff creates a new migration script from changes already applied to your local database.

Create an employees table under the public schema using Studio UI, accessible at localhost:54323 by default.

Next, generate a schema diff by running the following command:


_10supra db diff -f new_employee

You should see that a new file supra/migrations/<timestamp>_new_employee.sql is created. Open the file and verify that the generated DDL statements are the same as below.


_24-- This script was generated by the Schema Diff utility in pgAdmin 4
_24-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated
_24-- and may require manual changes to the script to ensure changes are applied in the correct order.
_24-- Please report an issue for any failure with the reproduction steps.
_24
_24CREATE TABLE IF NOT EXISTS public.employees
_24(
_24    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
_24    name text COLLATE pg_catalog."default",
_24    CONSTRAINT employees_pkey PRIMARY KEY (id)
_24)
_24
_24TABLESPACE pg_default;
_24
_24ALTER TABLE IF EXISTS public.employees
_24    OWNER to postgres;
_24
_24GRANT ALL ON TABLE public.employees TO anon;
_24
_24GRANT ALL ON TABLE public.employees TO authenticated;
_24
_24GRANT ALL ON TABLE public.employees TO postgres;
_24
_24GRANT ALL ON TABLE public.employees TO service_role;

You may notice that the auto-generated migration script is more verbose than the manually written one. This is because the default schema diff tool does not account for default privileges added by the initial schema.

Commit the new migration script to git and you are ready to deploy.

tip

Alternatively, you may pass in the --use-migra experimental flag to generate a more concise migration using migra. Without the -f file flag, the output is written to stdout by default.

supra db diff --use-migra

Deploy a migration#

In a production environment, we recommend using a CI/CD pipeline to deploy new migrations with GitHub Actions rather than deploying from your local machine.

This example uses two Supra projects, one for production and one for staging.

Prepare your environments by:

Creating separate Supra projects for staging and production
Pushing your git repository to GitHub and enabling GitHub Actions

caution

You need a new project for staging. A project which has already been modified to reflect the production project's schema can't be used because the CLI would reapply these changes.

Configure GitHub Actions#

The Supra CLI requires a few environment variables to run in non-interactive mode.

supra_ACCESS_TOKEN is your personal access token
supra_DB_PASSWORD is your project specific database password
supra_PROJECT_ID is your project specific reference string

We recommend adding these as encrypted secrets to your GitHub Actions runners.

Create the following files inside the .github/workflows directory:

.github/workflows/ci.yml


_28name: CI
_28
_28on:
_28  pull_request:
_28  workflow_dispatch:
_28
_28jobs:
_28  test:
_28    runs-on: ubuntu-latest
_28
_28    steps:
_28      - uses: actions/checkout@v3
_28
_28      - uses: supra/setup-cli@v1
_28        with:
_28          version: latest
_28
_28      - name: Start Supra local development setup
_28        run: supra start
_28
_28      - name: Verify generated types are checked in
_28        run: |
_28          supra gen types typescript --local > types.gen.ts
_28          if ! git diff --ignore-space-at-eol --exit-code --quiet types.gen.ts; then
_28            echo "Detected uncommitted changes after build. See status below:"
_28            git diff
_28            exit 1
_28          fi

The full example code is available in the demo repository.

Commit these files to git and push to your main branch on GitHub. Update these environment variables to match your Supra projects:

supra_ACCESS_TOKEN
PRODUCTION_PROJECT_ID
PRODUCTION_DB_PASSWORD
STAGING_PROJECT_ID
STAGING_DB_PASSWORD

When configured correctly, your repository will have CI and Release workflows that trigger on new commits pushed to main and develop branches.

Open a PR with new migration#

Follow the migration steps to create a supra/migrations/<timestamp>_new_employee.sql file.

Checkout a new branch feat/employee from develop , commit the migration file, and push to GitHub.


_10git checkout -b feat/employee
_10git add supra/migrations/<timestamp>_new_employee.sql
_10git commit -m "Add employee table"
_10git push --set-upstream origin feat/employee

Open a PR from feat/employee to the develop branch to see that the CI workflow has been triggered.

Once the test error is resolved, merge this PR and watch the deployment in action.

Release to production#

After verifying your staging project has successfully migrated, create another PR from develop to main and merge it to deploy the migration to the production project.

The release job applies all new migration scripts merged in supra/migrations directory to a linked Supra project. You can control which project the job links to via PROJECT_ID environment variable.

Troubleshooting#

Sync production project to staging#

When setting up a new staging project, you might need to sync the initial schema with migrations previously applied to the production project.

One way is to leverage the Release workflow:

Create a new branch develop and choose main as the branch source
Push the develop branch to GitHub

The GitHub Actions runner will deploy your existing migrations to the staging project.

Alternatively, you can also apply migrations through your local CLI to a linked remote database.


_10supra db push

Once pushed, check that the migration version is up to date for both local and remote databases.


_10supra migration list

Permission denied on db pull#

If you have been using Supra hosted projects for a long time, you might encounter the following permission error when executing db pull.


_10Error: Error running pg_dump on remote database: pg_dump: error: query failed: ERROR:  permission denied for table _type
_10
_10pg_dump: error: query was: LOCK TABLE "graphql"."_type" IN ACCESS SHARE MODE

To resolve this error, you need to grant postgres role permissions to graphql schema. You can do that by running the following query from Supra dashboard's SQL Editor.


_10grant all on all tables in schema graphql to postgres, anon, authenticated, service_role;
_10grant all on all functions in schema graphql to postgres, anon, authenticated, service_role;
_10grant all on all sequences in schema graphql to postgres, anon, authenticated, service_role;

Permission denied on db push#

If you created a table through Supra dashboard, and your new migration script contains ALTER TABLE statements, you might run into permission error when applying them on staging or production databases.


_10ERROR: must be owner of table employees (SQLSTATE 42501); while executing migration <timestamp>

This is because tables created through Supra dashboard are owned by supra_admin role while the migration scripts executed through CLI are under postgres role.

One way to solve this is to reassign the owner of those tables to postgres role. For example, if your table is named users in the public schema, you can run the following command to reassign owner.


_10ALTER TABLE users OWNER TO postgres;

Apart from tables, you also need to reassign owner of other entities using their respective commands, including types, functions, and schemas.

Rebasing new migrations#

Sometimes your teammate may merge a new migration file to git main branch, and now you need to rebase your local schema changes on top.

We can handle this scenario gracefully by renaming your old migration file with a new timestamp.


_10git pull
_10supra migration new dev_A
_10# Assume the new file is: supra/migrations/<t+2>_dev_A.sql
_10mv <time>_dev_A.sql <t+2>_dev_A.sql
_10supra db reset

In case reset fails, you can manually resolve conflicts by editing <t+2>_dev_A.sql file.

Once validated locally, commit your changes to Git and push to GitHub.

Edit this page on GitHub

SupraDOCS