Yuri for Supabase

Posted on Aug 8, 2023 • Edited on Feb 7 • Originally published at supabase.com

Supabase Local Dev: migrations, branching, and observability

One of our goals at Supabase is to make Postgres development delightful. To do this, we need to simplify the experience between our CLI, your code editor, and the remote Postgres database powering your applications.

We received feedback recently about our local development experience, encouraging us to improve. This iteration introduces many new features to address that feedback. Let’s jump into a few of the features we’re launching today.

Postgres Language Server

One of the unique features of Supabase is the ability to access your Postgres database directly from a browser or mobile app through our Serverless APIs. This means that developers are writing more PL/pgSQL.

While code editors have great support for most programming languages, SQL support is underwhelming. We want to make Postgres as simple as Python. Our recently announced Postgres Language Server takes us a step in that direction - eventually it will provide first-class support for Postgres in your favorite code editor including Linting, Syntax Highlighting, Migrations Parsing, SQL Auto-complete, and Intellisense.

The Postgres Language Server is not ready for Production just yet. The majority of work is still ahead, but we've verified that the technical approach works and we're making it public now so that we can develop it in the open with input from the community. We’re already receiving amazing feedback and support.

Follow the progress of the Postgres Language Server on GitHub

Observability tools for Postgres

We’ve added debugging utilities to our CLI to identify production issues via the supabase inspect db command. This interface includes 19 different commands to help you solve everything from slow queries to redundant indexes.

A lot of the credit for this belongs to Heroku’s pg-extras feature, an amazingly useful set of functionality. We’ve adapted the work they started, added a few additional commands, and made it available for any Postgres database. Simply append the --db-url param to use these commands with your own Postgres database.

This is just a starting point for the Supabase inspector. We’ll grow this feature to become an essential part of your Postgres toolkit.

supabase inspect db --help
Tools to inspect your Supabase database

Usage:
  supabase inspect db [command]

Available Commands:
  bloat                Estimates space allocated to a relation that is full of dead tuples
  blocking             Show queries that are holding locks and the queries that are waiting for them to be released
  cache-hit            Show cache hit rates for tables and indices
  calls                Show queries from pg_stat_statements ordered by total times called
  index-sizes          Show index sizes of individual indexes
  index-usage          Show information about the efficiency of indexes
  locks                Show queries which have taken out an exclusive lock on a relation
  long-running-queries Show currently running queries running for longer than 5 minutes
  outliers             Show queries from pg_stat_statements ordered by total execution time
  replication-slots    Show information about replication slots on the database
  role-connections     Show number of active connections for all database roles
  seq-scans            Show number of sequential scans recorded against all tables
  table-index-sizes    Show index sizes of individual tables
  table-record-counts  Show estimated number of rows per table
  table-sizes          Show table sizes of individual tables without their index sizes
  total-index-size     Show total size of all indexes
  total-table-sizes    Show total table sizes, including table index sizes
  unused-indexes       Show indexes with low usage
  vacuum-stats         Show statistics related to vacuum operations per table

Easier backups

We’ve made it even easer to backup and migrate your database, using supabase db dump. Under the hood, this simply uses pg_dump (it's just Postgres, after all). However we also handle a few of the hairy issues that you might need to navigate on your own, like object permissions.

supabase db dump --help
Dumps data or schemas from the remote database

Usage:
supabase db dump [flags]

Flags:
--data-only Dumps only data records.
-f, --file string File path to save the dumped contents.
--keep-comments Keeps commented lines from pg_dump output.
--role-only Dumps only cluster roles.
--use-copy Uses copy statements in place of inserts.

Improved Database Migrations

We’ve extended the CLI migration feature and added Dashboard support. Database migrations give you a way to update your database using version-controlled SQL files. We’ve built a lot of tooling around our migrations, including reparation, migration cleanup using the squash command, and diffing (using migra) to generate a new migration or to detect schema drift.

With the new Postgres Language Server, we hope to make it as easy to write Postgres migrations as it is to develop applications in TypeScript, Go, Python, or Rust.

Finally, we’ve added a Migrations view in the dashboard to track your migration history to improve the discoverability of migrations.

supabase migration
Manage database migration scripts

Usage:
  supabase migration [command]

Available Commands:
  list        List local and remote migrations
  new         Create an empty migration script
  repair      Repair the migration history table
  squash      Squash migrations to a single file
  up          Apply pending migrations to local database

Test and lint your database

We’ve simplified the database testing experience, with supabase test. Runnning supabase test new stubs out a pgTAP test for you to fill with testing logic. The CLI includes pg_prove and the TAP harness, so all you need to do is run
supabase test db.

To make life even easier, our friends at Basejump have created an entire suite of Supabase Test Helpers which make it simple to create users, run tests as an authenticated user, and test your RLS policies.

Finally, while you wait for us to make progress on the Language Server, we’ve added support for linting through the excellent plpgsql_check extension.

supabase test
Run tests on local Supabase containers

Usage:
  supabase test [command]

Available Commands:
  db          Tests local database with pgTAP
  new         Create a new test file

supabase db lint
Checks local database for typing error

Usage:
  supabase db lint [flags]

Flags:
  -h, --help                        help for lint
      --level [ warning | error ]   Error level to emit. (default warning)
      --linked                      Lints the linked project for schema errors.
  -s, --schema strings              List of schema to include. (default all)

Database seeding

Seeding is the process of populating a database with initial data, typically used to provide sample or default records for testing and development purposes. This gives you a reproducible development environment across your entire team.

We’ve added support for seeding to populate your local databases with data whenever you run supabase start or supabase db reset.

We’ve also worked with our friends at Snaplet to generate seed data directly from your database:

npx snaplet generate --sql > supabase/seed.sql

Type generators

Type generators introspect a PostgreSQL schema and automatically generate TypeScript definitions. This gives you end-to-end type safety from the database to the browser.

In the past month, we've added relationship detection in supabase-js. Foreign keys are now included in the generated types so that supabase-js can detect whether a foreign table should be an array (one-to-many) or an object (many-to-one). We've also added Helper Types to improve the developer experience for common scenarios, like short-hand accessors:

// Before
let movie: Database['public']['Tables']['movies']['Row'] = // ...

// After
let movie: Tables<'movies'> = // ...

supabase gen types
Generate types from Postgres schema

Usage:
  supabase gen types [command]

Available Commands:
  typescript  Generate types for TypeScript

Official GitHub Action

We’ve developed an official GitHub Action which leverages the CLI. You can generate types on every PR, or run your tests on every commit.

Local Logging and Debugging

Logs are now accessible locally in the Dashboard. Last launch week we released an open source logging server, with support for BigQuery. In the past few months we’ve added Postgres support to this server. This means that all of your local logs are accessible with no additional config - simply run supabase start and then visit the local dashboard to start debugging.

Stable releases

We’ve moved the Supabase CLI to a fortnightly stable-release cycle. Every 2 weeks, we will update the latest tag on npm, the supabase/tap for homebrew, and the supabase scoop bucket. You can find the binary downloads in our GitHub latest release.

For the adventurous feature hunters, we’ve added a beta release channel for the CLI, with new releases on every PR merged. You can follow this guide to install Supabase CLI (beta).

Branching and Preview Environments

And finally, probably our most anticipated feature - branching:

We’ve made major improvements to our local development with the features above - but we have bigger ambitions. For several months we’ve been developing Supabase branching and today we're opening it up for alpha testers.

Supabase isn’t simply a database, it’s an entire backend - everything from your Postgres database to your 50GB videos. Branching improves the experience of managing environments so that developers and teams spend less time on DevOps and more time building.

Supabase branching is hard

Every project is a Postgres database, wrapped in a suite of tools like Auth, Storage, Edge Functions, Realtime and Vectors, and encompassed by API middleware and logs.

A good branching solution requires each tool to provide multi-tenancy support so that:

Data can be isolated from production for security.
Compute can be isolated from each other to avoid noisy-neighbors.

How does branching work?

We use Git to bridge the gap between your local development environment and your hosted database. For now, we’ve focused on GitHub.

Every time you open a new Pull Request on GitHub, a corresponding “Preview Environment” is created. Each preview branch is an isolated Firecracker instance that pauses automatically after a period of inactivity. Every time a change is pushed to GitHub, the migrations within the ./supabase/migrations folder are run against the Preview Branch so that your entire team is working from the same source of truth.

When you hit merge on your Pull Request we run the migrations on your Production database.

What about data?

We’re starting with seed data. Any SQL with ./supabase/seed.sql will populate your Preview Branch. This provides your entire team with an isolated and reproducible environment, safe from any data privacy concerns.

Importantly, we aren’t cloning production data until we find something appropriate for data security. We know that copy-on-write is an available option, and with the appropriate anonymization techniques it seems like a promising way to provide a “production-like” test environment.

We’ll also need to figure out what this means for large files in Supabase Storage. Do you need to anonymize your photos and videos? This is a work in progress and we’re open to feedback.

Either way, we want to support both seed data and anonymized production data, so that teams can choose their preference based on their risk profile. It makes sense to start with a seed.

Is it available yet?

For the next few months branching is in closed alpha. We’re looking for development partners for Supabase Branching. If you’re interested in testing the new features, sign up for early access.