apps/www/_blog/2023-08-08-supabase-local-dev.mdx
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.
<div className="video-container"> <iframe className="w-full" src="https://www.youtube-nocookie.com/embed/N0Wb85m3YMI" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; fullscreen; gyroscope; picture-in-picture; web-share" allowfullscreen ></iframe> </div>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.
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.
<BlogCollapsible title="See full command"> ```markdown supabase inspect db --help Tools to inspect your Supabase databaseUsage:
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
```
We’ve made it even easier 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.
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.
```
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.
<BlogCollapsible title="See full command"> <div> ```markdown supabase migration Manage database migration scriptsUsage:
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
```
We’ve simplified the database testing experience, with supabase test. Running 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.
<BlogCollapsible title="See full command"> <div> ```markdown supabase test Run tests on local Supabase containersUsage:
supabase test [command]
Available Commands:
db Tests local database with pgTAP
new Create a new test file
````
```markdown
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)
```
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 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 referenced 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'> = // ...
Usage:
supabase gen types [command]
Available Commands:
typescript Generate types for TypeScript
```
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.
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.
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).
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.
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:
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.
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.
We are rolling out access and we'll be onboarding organizations in batches over the next few weeks. You can still sign up for access.
</Admonition>Jump into our updated Local Development documentation to get started with the CLI.
If you’re an existing user simply update your CLI and check out the full command reference for all the latest commands.