Part 1 — Configuring and testing a supabase database table and policies

Learn how to set up a Supabase database like a pro! This tutorial is the perfect starting point for mastering the basics, including creating tables, policies, and test cases.

Join me on a journey to create a dynamic Next.js web app that will easily run background jobs for Node.js. We’ll be using a powerful combo of Next.js, Inngest, Supabase, and Vercel to bring your vision to life. Stick around and let’s get started!

About Me

As a tech creator, I build web2 and web3 products for a range of industries. I’m experienced in financial services, natural resources, medical tech, and blockchain. I’m a founding member of multiple companies, operating as CTO, product manager, and developer. I use Medium as a way to teach about technology and discuss business challenges beyond tech.

Follow me on Twitter or join my Telegram group to brainstorm and build together.

Supabase

Supabase is an open-source, cloud-based platform for building scalable and secure applications. It simplifies database management, authentication, and APIs with its intuitive interface and pre-built components, including real-time subscription, webhooks, and user management. Supabase’s real-time capabilities enable lightning-fast reactions to database changes, and it seamlessly integrates with popular frameworks and libraries like React, Vue.js, and Next.js. With Supabase, developers can build modern, scalable, and secure applications quickly and efficiently.

Topics Covered

• Configuring Supabase for local development
• Configuring a database table on Supabase
• Testing a database table on Supabase
• Configuring four basic policies on Supabase
• Testing database policies on Supabase

Configuring Supabase for local development

Running Supabase locally is easy to set up and will enable you to develop and manage your persistence layers (database and storage) in record time.

First, you will need to create an account on supabase and generate an access token at https://app.supabase.com/account/tokens.

You will also need to have docker installed.

yarn init
yarn add --dev supabase
npx supabase login
npx supabase init
npx supabase start

Note: to shutdown supabase, use npx supabase stop

Once it is started, you should have the following in the Terminal:

Started supabase local development setup.

API URL: http://localhost:54321
DB URL: postgresql://postgres:postgres@localhost:54322/postgres
Studio URL: http://localhost:54323
Inbucket URL: http://localhost:54324
JWT secret: super-secret-jwt-token-with-at-least-32-characters-long
anon key: eyJhb...
service_role key: eyJhb...

Configuring a database table on Supabase

We are going to create a table called images. Our table will consist of the following attributes:

id — we will use a UUID for this, I prefer them over integer ids. An id will automatically be generated each time a row is added.

original_path — varchar of the path where the original image is stored.

thumbnail_path — varchar of the path where the thumbnail image is stored.

user_id — a reference to the owner of the row. The user_id will automatically be added at creation.

Let’s go to the Table Editor at http://localhost:54323/project/default/editor

Create a new table with the following inputs:

Supabase table: example configuration

For the user_id, we will use a foreign key relation. Click on the icon to the right of the name field, and configure the following inputs:

Supabase Table: configuring user as a foreign key

Congratulations, you have created your first table in Supabase! Now it’s time to create some policies!

Testing a database table on Supabase

Create a new file /supabase/tests/database/images.test.sql in the supabase folder and add the following code:

begin;
select plan(7); -- only one statement to run

select has_table('images');

select has_column('images', 'id');
select has_column('images', 'created_at');
select has_column('images', 'original_path');
select has_column('images', 'thumbnail_path');
select has_column('images', 'user_id');

select col_is_pk('images', 'id');

select * from finish();
rollback;

Run the tests with the command

npx supabase test db

Expected result:

All tests successful.
Files=1, Tests=7,  0 wallclock secs ( 0.01 usr  0.00 sys +  0.01 cusr  0.00 csys =  0.02 CPU)
Result: PASS

Configuring four basic policies on Supabase

We will configure the following four policies for our table:

• Enable read access for all users
• Enable insert for authenticated users only
• Enable update for users based on user id
• Enable delete for users based on user id

Head over to http://localhost:54323/project/default/auth/policies and you should see a reference to our images table. Click on the New Policy button on the right side.

1. Enable read access for all users

Create a new policy and enter the following:

Supabase Policy: Enable read access to everyone

The corresponding SQL statement should be:

CREATE POLICY "Enable read access for all users" ON "public"."images"
AS PERMISSIVE FOR SELECT
TO public
USING (true)

2. Enable insert for authenticated users only

Create a new policy and enter the following:

Supabase Policy: Enable insert access for authenticated users only

The corresponding SQL statement should be:

CREATE POLICY "Enable insert for authenticated users only" ON "public"."images"
AS PERMISSIVE FOR INSERT
TO authenticated

WITH CHECK (true)

3. Enable update for users based on user id

Create a new policy and enter the following:

The corresponding SQL statement should be:

CREATE POLICY "Enable update for users based on user id" ON "public"."images"
AS PERMISSIVE FOR UPDATE
TO public
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id)

4. Enable delete for users based on their user id

Create a new policy and enter the following:

The corresponding SQL statement should be:

CREATE POLICY "Enable delete for users based on user id" ON "public"."images"
AS PERMISSIVE FOR DELETE
TO public
USING (auth.uid() = user_id)

Congratulations! You have set up four basic policies for your table. Now let’s test them :)

Testing database policies on Supabase

Create a new file /supabase/tests/database/images.policies.test.sql in the supabase folder and add the following code:

begin;
select plan( 1 );

select policies_are(
  'images',
  ARRAY [
    'Enable read access for all users',
    'Enable insert for authenticated users only',
    'Enable update for users based on user id',
    'Enable delete for users based on user id'
  ]
);

select * from finish();
rollback;

Run the tests with the command

npx supabase test db

Expected result:

All tests successful.
Files=2, Tests=8,  0 wallclock secs ( 0.00 usr  0.00 sys +  0.02 cusr  0.00 csys =  0.02 CPU)
Result: PASS

Final Tip

Before committing any code, make sure you have a .gitignore file that ignores the node_modules folder.

The code if here.

This was the first part of a series of articles that will discuss how to use Next.js, Inngest, Supabase, and Vercel to build a serverless application that runs background jobs.

In our next session, we will build a Next.js app that uses Supabase to authenticate users. Stay tuned!

If this article was helpful, join our Telegram group! It is a space to discuss new ideas, build projects, and troubleshoot code and business challenges.

I build web2 and web3 products for a range of industries. I’m a founding member of multiple companies, operating as CTO, product manager, and developer. I use Medium as a way to teach about technology and discuss business challenges beyond tech.

Follow me on Twitter.