Testing Your Database
To ensure that queries return the expected data, RLS policies are correctly applied and etc., we encourage you to write automated tests. There are essentially two approaches to testing:
-
Firstly, you can write tests that interface with a Supabase client instance (same way you use Supabase client in your application code) in the programming language(s) you use in your application and using your favorite testing framework.
-
Secondly, you can test through the Supabase CLI, which is a more low-level approach where you write tests in SQL.
Testing through the Supabase client
A good starting point are the tests written for the Supabase client itself. For example, here are the tests for the Supabase JS client written with TypeScript and Jest.
Keep in mind that the tests (which you'll likely integrate into your CI) need to run with the same DB setup that you're running locally. That means you need to make all changes to the database exclusively through migrations (not manually).
Testing using the Supabase CLI
You can use the Supabase CLI to test your database. The minimum required version of the CLI is v1.11.4. To get started:
- Install the Supabase CLI on your local machine
Creating a test
Create a tests folder inside the supabase folder:
_10mkdir -p ./supabase/tests/database
Create a new file with the .sql
extension which will contain the test.
_10touch ./supabase/tests/database/hello_world.test.sql
Writing tests
All sql
files use pgTAP as the test runner.
Let's write a simple test to check that our auth.users
table has an ID column. Open hello_world.test.sql
and add the following code:
_12begin;_12select plan(1); -- only one statement to run_12_12SELECT has_column(_12 'auth',_12 'users',_12 'id',_12 'id should exist'_12);_12_12select * from finish();_12rollback;
Running tests
To run the test, you can use:
_10supabase test db
This will produce the following output:
_10$ supabase test db_10supabase/tests/database/hello_world.test.sql .. ok_10All tests successful._10Files=1, Tests=1, 1 wallclock secs ( 0.01 usr 0.00 sys + 0.04 cusr 0.02 csys = 0.07 CPU)_10Result: PASS