

As a NextJS enthusiast, I often rely on it for most of my projects. However, recently, I have been exploring other frameworks like Remix for web and SwiftUI for iOS. It's got me thinking how cool it would be to leverage Basejump more as a headless starter kit, that way you could bring your own front end.

Unfortunately, when the first version of Basejump was created, Supabase did not support developing with multiple Edge Functions at once. It made working with it locally nearly impossible. But since [launch week 7](https://supabase.com/launch-week), Supabase now provides full support for local Edge Functions. I decided to take the weekend and migrate Basejump over so that I could begin the process of making a headless version. Here's what I learned.

You can follow along the [Basejump code changes from this article here](https://github.com/usebasejump/basejump/pull/31).

## What we'll cover

- [Creating and running Edge Functions locally](#local)
- [Calling Edge Functions from your front end](#calling)
- [Sharing code between Edge Functions](#sharing)
- [Setting up your IDE for Deno support](#deno)
- [Setting up your functions to support CORS](#cors)
- [Working with dependencies and import maps](#dependencies)
- [Using environment variables locally and in production](#env)
- [Deploying Edge Functions to Supabase](#deploying)
- [Additional Resources](#resources)

## <a id="local">Creating and running Edge Functions locally</a>
Creating Edge Functions locally is pretty straight forward.

1. Make sure you have the latest version of the [Supabase CLI](https://supabase.com/docs/guides/cli) installed
2. Run `supabase functions new my-function-name`.  
3. Your function will be created inside the `./supabase/functions/my-function-name` folder.

Your functions will be run automatically when you run `supabase start`.  You can also run them manually with `supabase functions serve`. Running them manually allows you to pass additional configuration such as local environment variables or an import map.

## <a id="calling">Calling Edge Functions from your front end</a>
When you create a new Edge Function, Supabase will automatically create a new route for you.  Those routes are callable via the API, but there are also some nice convenience functions in the SDK's that wire up the authentication for you.

```ts

import { supabase } from "./supabaseClient";

const {data, error} = supabase.functions.invoke('my-function-name', {param1: 'value1', param2: 'value2'});

```

## <a id="sharing">Sharing code between Edge Functions</a>
Your Edge Functions will all be located inside of your `./supabase/functions` folder. Because functions are written in Deno, you'll want to maintain a `_shared` folder for any shared code leveraged between functions.  This is particularly useful for things like your supabase client.  Here's an example of a service/admin client

```ts
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
import { Database } from "./types/supabase.ts";

const supabaseAdmin = createClient<Database>(
  Deno.env.get("SUPABASE_URL") as string,
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY") as string
);

export default supabaseAdmin;
```

## <a id="deno">Setting up your IDE for Deno support</a>
Supabase functions are written in Deno (or recently Dart, but this guide focuses primarily on Deno). Leveraging Deno gives the Supabase team some nice security benefits out of the box - unfortunately it also creates a bit of a learning curve for folks new to Deno.  Likely the first thing you'll notice is that your IDE will show all sorts of lint issues - this is particularly the case if you're sharing a project root between a frontend client and your Supabase folder.

#### Setting up VS Code for Deno
1. Install the [Deno plugin for VS Code](https://marketplace.visualstudio.com/items?itemName=denoland.vscode-deno).
2. Update your local project file to configure explicit paths for Deno.  This will allow you to use normal JS/Typescript linting in the rest of your project

```json
// .vscode/settings.json
{
    "deno.enable": true,
    "deno.unstable": true,
    "deno.enablePaths": [
        "supabase/functions"
    ]
}
```

#### Setting up Webstorm for Deno
1. Install the [Deno plugin for Webstorm](https://plugins.jetbrains.com/plugin/14382-deno).
2. Update your local project file to configure explicit paths for Deno.  This will allow you to use normal JS/Typescript linting in the rest of your project
```json
// Preferences -> Languages & Frameworks -> Deno -> Init command
{
  "lint": true,
  "unstable": true,
  "importMap": "import_map.json",
  "config": "deno.json",
  "enablePaths": ["./supabase/functions"]
}
```
3. Enable Deno support for your project
4. Restart Webstorm

## <a id="cors">Setting up your functions to support CORS</a>
By default, Supabase functions do not support CORS. The JS client, however, makes cross domain requests - so you'll need to get that handled before you can start working with your functions. You'll also want to make sure to set a suitable CORS cache header to avoid unnecessary requests.

```ts
// supabase/functions/_shared/cors.ts
export const corsHeaders = {
  "Access-Control-Allow-Origin": "*",
  "Access-Control-Allow-Headers": "authorization, x-client-info, apikey, content-type",
  "Access-Control-Max-Age": 86400
};
```

Next, you'll import this into each of your functions and leverage it for `OPTIONS` requests

```ts
// supabase/functions/my-function-name/index.ts
import { corsHeaders } from "../_shared/cors.ts";

serve(async (req) => {
  if (req.method === "OPTIONS") {
    return new Response("ok", { headers: corsHeaders });
  }
  // the rest of your function
});
```

## <a id="dependencies">Working with NPM dependencies</a>
Deno has no package manager, instead it imports dependencies directly from the web. That can cause some challenges when working with NPM modules which depend on your `node_modules` folder to resolve dependencies.

There are some CDNs that attempt to resolve this, but I've had mixed results. Ideally, it's best to avoid using external dependencies as much as possible. But when you do need to, there are some options.

The most popular CDN for importing npm modules as Deno packages is esm.sh. You can import any package from npm using the following format:

```ts
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
```

#### Working with a local import map
To clean up some of your inputs and help manage versions a bit better, you can create an import_map.json file located at `./supabase/functions/import_map.json`.  This will allow you to import your dependencies like so:

```json
// supabase/functions/import_map.json
{
  "imports": {
    "@supabase/supabase-js": "https://esm.sh/@supabase/supabase-js@2"
  }
}
```


```ts
import { createClient } from "@supabase/supabase-js";
```

Your import_map.json file will be automatically loaded by Supabase as long as it's located in the `./supabase/functions` folder. If you have a custom location, you can pass it in as a flag when starting your functions locally or deploying them.

```bash
supabase functions serve --import-map ./my-custom-location/import_map.json
supabase functions deploy my-function-name --project-ref MY-PROJECT-ID --import-map ./my-custom-location/import_map.json
```

## <a id="env">Using environment variables locally and in production</a>
By default, Supabase will inject some useful environment variables into your Edge Functions. [You can check them out here](https://supabase.com/docs/guides/functions/secrets#default-secrets).  To add your own, create a file at `./supabase/.env` in the following format:

```bash
MY_FUNCTION_SECRET=supersecret
```

Next, startup your functions with `supabase functions serve --env-file ./.env`.  You can now access your environment variables in your functions with `Deno.env.get("MY_FUNCTION_SECRET")`.

#### Using environment variables in production
To use custom environment variables in production, add them to your linked project by running `supabase secrets set MY_FUNCTION_SECRET=supersecret`.

## <a id="deploying">Deploying Edge Functions to Supabase</a>
To deploy your functions, run `supabase functions deploy my-function-name --project-ref MY-PROJECT-ID`.  You can [get your project ID from the Supabase dashboard](https://app.supabase.com/project/_/settings/general). Functions must be deployed one at a time.

## <a id="resources">Additional Resources</a>
I found the following guides helpful when working through the migration - particularly the documentation and Youtube series.  Feel free to [ping me on Twitter](https://twitter.com/tiniscule) If you have more to add!

- [Edge Functions Documentation and examples](https://supabase.com/docs/guides/functions)
- [Edge Runtime to Self Host Deno Functions](https://supabase.com/blog/edge-runtime-self-hosted-deno-functions)
- [Edgy Edge Functions playlist](https://www.youtube.com/playlist?list=PL5S4mPUpp4OulD3olUW8Eq1IYKpUbk5Ob)

Working with Supabase Edge Functions for Basejump


> This guide is a work in progress as I learn more about creating database.dev extensions, but should be useful as-is. If you have feedback or find any issues, beat me up on [Twitter](https://twitter.com/tiniscule).

Supabase recently [announced dbdev and database.dev](https://supabase.com/blog/dbdev), an exciting new package manager that allows you to install trusted extensions to Postgres without requiring access to the filesystem. It does this by leveraging [pg_tle](https://github.com/aws/pg_tle), an extension from AWS that provides the installation functionality for extensions written using an approved subset of typical Postgres extension functionality.

This was an awesome fit for my [Supabase test helpers](https://github.com/usebasejump/supabase-test-helpers) library, so I decided to take some time to convert the helper into a dbdev extension.  This guide is a quick overview of what I've learned and how you can create your own extensions as well.

## What we'll cover

- [What is the difference between pg_tle and dbdev](#difference)
- [What languages can I use?](#languages)
- [Writing functions for your extension](#writing-tests-for-supabase)
    - [Things I've learned writing extension functions](#function-learnings)
- [Converting your functions into a pg_tle extension](#pg_tle)
- [How can users install my extension?](#installing)
- [Getting extensions listed on database.dev](#database-dev)
  - [Adding a new extension](#database-dev-new-extension)
  - [Updating an existing extension](#database-dev-update-extension)
- [Testing your extension](#testing)
  - [Testing with Supabase](#testing-supabase)
  - [Testing without Supabase](#testing-non-supabase)
- [Automating pg_tle and database.dev extensions with Github Actions](#github-action)

## <a id="difference">What is the difference between pg_tle and dbdev?</a>
#### TLDR
pg_tle allows external Postgres extensions to be installed without filesystem access. dbdev makes it way easier to do so.

#### Longer version
pg_tle is the underlying technology that allows external extensions to be installed in Postgres without requiring access to the file system. If you've worked with managed Postgres instances often, you'll know this is a massive deal. As long as the database has pg_tle installed, you'll have the ability to install "most" extensions on your own.  I say "most" because the format is still fairly new, and while I expect it to get popular quite fast, it will take some time for all our favorites to get ported over.

dbdev and database.dev are a layer on top of pg_tle that provide a trusted extension repository and remote installation. For an example on how this works, you can check out my [supabase_test_helpers pg_tle function](https://github.com/usebasejump/supabase-test-helpers/commit/c2c0cfbbdeeca9eb5d779d41bee2607a7ca2bf49#diff-2c4e0c2e1667f265ecc45bc32d8805716b5ef02b200055ea6b445ca4684d0c16). You'll notice the `pgtle.install_extension` call at the top containing all the extension functionality.  That's awesome, but cumbersome.  Once approved on database.dev, that installation would become `dbdev.install_extension('basejump-supabase_test_helpers').

## <a id="languages">What languages can I use?</a>
At launch time, pg_tle supports SQL, PL/pgSQL, pl/v8 (javascript) and pl/perl.  Support for pl/rust is coming soon.

I suspect more languages get added over time.

## <a id="functions">Writing functions for your extension</a>
Extensions are effectively a collection of functions. Since the details here will vary based on what you're building, I won't go into specific functionality too much.  Instead, I'll give a quick example of some functions and mention a few random things I've learned while building supabase_test_functions.

#### Example function

```sql
-- Create a schema for your extension
CREATE SCHEMA IF NOT EXISTS my_extension;

-- Create a generic function for my extension
CREATE OR REPLACE FUNCTION my_extension.my_function(value text)
RETURNS text
AS $$
    SELECT value;
$$ LANGUAGE SQL;
```

#### <a id="function-learnings">Things I've learned creating extension functions</a>
- I like to create all of my functions as `CREATE OR REPLACE` so that it's easy to update them without having to drop them first
- It can be a good idea to create a specific schema for your functions. That allows finer grained control over who has access. For supabase users, that also keeps the functions from being exposed through the API. For example, in [supabase_test_helpers](https://github.com/usebasejump/supabase-test-helpers) I use:
```sql
CREATE SCHEMA IF NOT EXISTS tests;

-- anon and authenticated should have access to tests schema
GRANT USAGE ON SCHEMA tests TO anon, authenticated;
-- Don't allow public to execute any functions in the tests schema
ALTER DEFAULT PRIVILEGES IN SCHEMA tests REVOKE EXECUTE ON FUNCTIONS FROM public;
-- Grant execute to anon and authenticated for testing purposes
ALTER DEFAULT PRIVILEGES IN SCHEMA tests GRANT EXECUTE ON FUNCTIONS TO anon, authenticated;
```
- You can overload your functions by assigning default values to some of your function inputs.  For example:
```sql

CREATE OR REPLACE FUNCTION  my_function(value text default 'hello world') RETURNS text AS $$
    SELECT value;
$$ LANGUAGE SQL;

SELECT my_function(); -- returns 'hello world'
SELECT my_function('hello'); -- returns 'hello'
```

- By default, functions are executed with the current user role. In the context of Supabase, that'll typically mean either your `authenticated` user or `anon` for anonymous requests. You can get around that by defining your function as `SECURITY DEFINER`, which gets run with elevated permissions. You almost always want to avoid `SECURITY DEFINER`. If you need to use it, make sure you set a `search_path` as well, typically to `public`.  [Supabase talks about this more here](https://supabase.com/docs/guides/database/functions#security-definer-vs-invoker).

## <a id="pg_tle">Converting your functions into a pg_tle extension</a>
Now that your functions are created, converting them into a pg_tle extension is super straight forward.  You'll just wrap your function in the pgtle.install_extension function.

```sql
SELECT pgtle.install_extension
(
 'my_extension',
 '0.1',
  'This is a quick description of your extension',
$_pg_tle_$
  -- YOUR EXTENSION FUNCTIONS GO HERE
$_pg_tle_$
);
```

For example:

```sql
SELECT pgtle.install_extension
(
 'my_extension',
 '0.1',
  'This is a quick description of your extension',
$_pg_tle_$
    -- Create a schema for your extension
    CREATE SCHEMA IF NOT EXISTS my_extension;

    -- Create a generic function for my extension
    CREATE OR REPLACE FUNCTION my_extension.my_function(value text)
    RETURNS text
    AS $$
        SELECT value;
    $$ LANGUAGE SQL;
$_pg_tle_$
);
```

## <a id="installing">How can users install my extension?</a>
Extensions were meant to be shared. Out of the box, your users will need to enable pg_tle on their database and then run your above `pgtle.install_extension` function in its entirety to leverage your code.  It's a bit cumbersome, and that's where `dbdev` and [database.dev](https://database.dev) come in. But we'll tackle that in a bit, to start, here's how your users can install unapproved extensions.

```sql
CREATE EXTENSION IF NOT EXISTS pgtle;

SELECT pgtle.install_extension
(
 'my_extension',
 '0.1',
  'This is a quick description of your extension',
$_pg_tle_$
    -- Create a schema for your extension
    CREATE SCHEMA IF NOT EXISTS my_extension;

    -- Create a generic function for my extension
    CREATE OR REPLACE FUNCTION my_extension.my_function(value text)
    RETURNS text
    AS $$
        SELECT value;
    $$ LANGUAGE SQL;
$_pg_tle_$
);

CREATE EXTENSION my_extension;

SELECT my_extension.my_function('hello world'); -- returns 'hello world'
```

## <a id="database-dev">Getting extensions listed on database.dev?</a>
If you want to get your extension listed on [database.dev](https://database.dev), you'll need to get it approved by the Supabase team.  

> They're working on an API for extension submission, but for now it requires opening up a PR on the [database.dev repo](https://github.com/supabase/dbdev).

1. First, create an account at [database.dev](https://database.dev)

2.  Extensions are maintained as a list of migration files into the database.dev database.  The format is:

```sql

-- First we upsert the extension into the app.packages table
insert into app.packages(
    handle,
    partial_name,
    control_description,
    control_relocatable,
    control_requires
)
values (
    'your_database_dev_handle',
    'your_extension_name',
    'Your extension description',
    false, -- set to true if your extension is relocatable
    '{}' -- set to a json array of extension names that your extension requires
) on conflict (handle, partial_name) do update set
    control_description = excluded.control_description,
    control_relocatable = excluded.control_relocatable,
    control_requires = excluded.control_requires;

insert into app.package_versions(package_id, version_struct, sql, description_md)
values (
(select id from app.packages where package_name = 'your_database_dev_handle-your_extension_name'), -- handle-partial_name
(1,0,0), -- your semver version number
$pkg$ -- start of your extension
-- YOUR EXTENSION FUNCTIONS GO HERE
$pkg$, -- end of your extension

$description$ -- start of your description
# Markdown text content goes here
$description$ -- end of your description
);
```

3. Submit your extension to the Supabase team by opening a PR to the [database.dev repo](https://github.com/supabase/dbdev).  Check out some examples [here](https://github.com/supabase/dbdev/tree/master/supabase/migrations).  If you want to see an example submission, [checkout the supabase_test_helpers PR here](https://github.com/supabase/dbdev/pull/51).

## <a id="testing">Testing your extension</a>
It's a good idea to test your extension using [pgTAP](https://pgtap.org/) before you publish it. I have a [full guide on creating tests for Supabase here](https://usebasejump.com/blog/testing-on-supabase-with-pgtap). You can also view some example tests from my [supabase-test-helpers project](https://github.com/usebasejump/supabase-test-helpers/tree/main/tests).

#### <a id="testing-supabase">Testing for Supabase installations</a>
Supabase comes with pgTAP already configured and ready to go, making it easy to generate your tests. I like to create a sample supabase project outside of my extension directory.

1. Make sure you have the latest version of the [Supabase CLI](https://supabase.com/docs/guides/cli) installed
2. Next, create a new project using the CLI and start it
```bash
mkdir supabase-test-project && cd supabase-test-project && supabase init
supabase start
```
3. Create your tests in `./supabase/tests/your-test.sql`
4. Activate the pg_tle extension using psql
```bash
PGASSWORD=postgres psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -c "CREATE EXTENSION pg_tle;"
```
5. Install your extension using psql
```bash
PGASSWORD=postgres psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -f ./my-extension-pglet.sql
```
6. Run your tests
```bash
supabase test db
```

#### <a id="testing-non-supabase">Testing on a non Supabase installation</a>
If you're not using Supabase, you can still use pgTAP to test your extension.  You'll just need to install it yourself using the [instructions here](https://pgtap.org). Once installed on your local database, you can perform the same steps as above. When it comes to running your tests, just make sure you run them using `pg_prov` instead of `supabase test db`.

## <a id="github-action">Automating pg_tle and database.dev extensions with Github Actions</a>
> I'm working on some automation to make it easier to generate pg_tle and database.dev extensions.  I'll update this section once I have something to share. Just waiting to get through the process a bit more before I make them public.

#### Automating your extension tests
Assuming you've located your tests in a project root `tests` directory, here's a quick Github Action that will run them for you using the Supabase CLI.

```yaml
name: PGTap Tests
on:
  pull_request:
    branches: [ main ]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: supabase/setup-cli@v1
        with:
          version: 1.50.4
      - name: Supabase Start
        run: supabase init && supabase start
      - name: Enable pgtle
        run: psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -c "CREATE EXTENSION pg_tle;"
        env:
          PGPASSWORD: postgres
      - name: Install supabase_test_helpers extension
        run: psql -v ON_ERROR_STOP=1 -U postgres -d postgres -h localhost -p 54322 -f ./your-pg_tle-extension.sql
        env:
          PGPASSWORD: postgres
      - name: Move files into supabase tests directory
        run: mkdir ./supabase/tests && mv ./tests/* ./supabase/tests/
      - name: Run Tests
        run: supabase test db
```

Creating Supabase & Postgres extensions for database.dev


One of the benefits of using Supabase is their out-of-the-box support for common authentication methods like Google, Github and email/sms authentication. Coupled with RLS (Row level security) and NextJS middleware, it lets you get up and running quickly with a robus authentication/authorization system. But there are a few gotchas worth knowing about to create a seamless experience.

## How authenticating with Supabase works
If you check out the [Supabase docs](https://supabase.com/docs/guides/auth/server-side-rendering#understanding-the-authentication-flow), you'll see that when you call the `signIn` method from your client, Supabase will detect the auth method desired, perform the required steps, and then redirect the authenticated user back to your app. Unfortunately, the redirected URL is sent with the authentication tokens as hash params - which are not immediately accessible from the server. Instead, the browser client will detect the hash tokens after the render is complete and set the auth cookies then.

## Stuck in a loop
Because the NextJS middleware does not have access to the hash tokens until the client has detected them and set the cookie, the user experiences the following flow:

![Auth flow](/images/blog/supabase-oauth-with-nextjs-middleware/auth-flow.png)

1. Anonymous hits a private route on your app, the NextJS middleware will detect this and force them to a `/login` page.
2. User authenticates using oauth such as Google
3. Supabase redirects the user back to your app with the authentication tokens in the URL hash
4. The NextJS middleware does _not_ have access to the tokens, so it forces the user back to `/login` again
5. Client side detects the hash tokens, authenticates, and sets the cookie
6. The user could now access the private route, but they don't know this because they're still stuck on the `/login` page

## A not-so-great solution
I haven't been able to find a decent way to avoid this issue - the NextJS middleware doesn't have any access to the hash tokens, so it won't have a way to detect the user has just authenticated until the client loads. Some folks recommended [using a specific post-authentication page to listen for the authentication to load](https://github.com/supabase/auth-helpers/issues/341#issuecomment-1319597309), but the issue there is that you'd need to evolve that over time to handle auth failure conditions. 

Instead, I've opted to perform this check on my `login` and `signup` pages directly. This way, failure states are easy to recover for the user since they're already on the login page.

Here's my [use-auth-check.ts](https://github.com/usebasejump/basejump/blob/main/src/utils/use-auth-check.ts) hook

```typescript
import { useRouter } from "next/router";
import { useSessionContext } from "@supabase/auth-helpers-react";
import { useEffect } from "react";

const useAuthCheck = (redirectedFrom?: string) => {
  const { replace } = useRouter();
  const { session } = useSessionContext();

  useEffect(() => {
    if (session && redirectedFrom) {
      replace(redirectedFrom as string);
    }
  }, [session, redirectedFrom]);
};

export default useAuthCheck;

```

And the [middleware.ts](https://github.com/usebasejump/basejump/blob/main/src/middleware.ts) file, which blocks unauthenticated requests from reaching protected routes

```typescript
import { createMiddlewareSupabaseClient } from "@supabase/auth-helpers-nextjs";
import type { NextRequest } from "next/server";
import { NextResponse } from "next/server";

export async function middleware(req: NextRequest) {
  const res = NextResponse.next();
  const supabase = await createMiddlewareSupabaseClient({ req, res });

  const {
    data: { session },
  } = await supabase.auth.getSession();

  // Check auth condition
  if (session) {
    // Authentication successful, forward request to protected route.
    return res;
  }

  // Auth condition not met, redirect to home page.
  const redirectUrl = req.nextUrl.clone();
  redirectUrl.pathname =
    req.nextUrl.pathname === "/invitation" ? "/signup" : "/login";
  redirectUrl.searchParams.set(`redirectedFrom`, req.url);
  return NextResponse.redirect(redirectUrl);
}

export const config = {
  matcher: ["/dashboard/:path*", "/invitation"],
};
    
 ```

Finally, the [login](https://github.com/usebasejump/basejump/blob/main/src/pages/login.tsx) and [signup](https://github.com/usebasejump/basejump/blob/main/src/pages/signup.tsx) pages get the following added to detect the authenticated session and redirect the user to the final destination.

```typescript
const router = useRouter();
const { redirectedFrom } = router.query;

useAuthCheck(redirectedFrom as string);
```

## Remaining issues
There are a few issues with this approach, but the most annoying one is that there is a flicker on the landing page where the user will see a login screen for a brief moment before getting redirected.  That delay is caused by waiting for the supabase client to load, detect the session, and then trigger the route change. I'm still thinking through ways to improve that, but for now users can at least complete an invitation link auth flow without confusion.

Oauth and magic links with Supabase and NextJS middleware


Testing Supabase applications can be a bit intimidating at first glance. You get pgTAP out of the box, but there isn't a ton of guidance on how to property test your app - particularly RLS policies and authentication.  Here's a quick rundown of what I've learned building out the tests for Basejump.

If you're a code first kinda person, you can check out the [Basejump tests on Github instead](https://github.com/usebasejump/supabase-test-helpers/tree/main/tests) instead.

## What we'll cover

- [Setup and run tests](#installing)
- [Supabase test helpers](#test-helpers)
- [Writing tests for Supabase](#writing-tests-for-supabase)
- [Testing authenticated requests](#testing-authenticated)
- [Testing anonymous requests](#testing-anonymous)
- [Ensuring RLS is enabled on all tables](#checking-rls)
- [Testing your RLS Policies](#rls-testing)
  - [Testing Insert Policies](#rls-insert)
  - [Testing Update Policies](#rls-update)
  - [Testing Delete Policies](#rls-delete)
  - [Testing Select Policies](#rls-select)
- [Running your tests as a Github action](#github-action)

## <a id="installing">Setup and run tests</a>
Supabase has always included pgTAP by default as an extension, you'll just want to enable it in your project. Great news is they now support the pg_prove test runner by default as well! Just make sure you have the [latest CLI installed](https://supabase.com/docs/guides/cli).

Once installed, tests should be located in your  `supabase/tests` folder with a `.sql` extension.  You can run tests using the following command:

```bash
supabase test db
```

## <a id="test-helpers">Supabase test helpers</a>
While not required, we've created some [Supabase test helpers](https://github.com/usebasejump/supabase-test-helpers) to make testing your Supabase database a bit easier.  You can find them on [Github](https://github.com/usebasejump/supabase-test-helpers).  The helpers provide you useful functionality like authenticating as a user, creating test users and verifying RLS policies.

Some example methods in the helper library are:
* `create_supabase_user(identifier, email, phone)` - Creates a test user in your Supabase project
* `get_supabase_user(identifier)` - Returns information on the test user
* `authenticate_as(identifier)` - Authenticates you as the user and sets all the required JWT values
* `clear_authentication()` - logs out of any session and puts you in an `anon` state

## <a id="writing-tests-for-supabase">Writing tests for Supabase</a>
To write your first tests, you'll want to add a new file to your `supabase/tests/database` folder with a `.sql` extension. I recommend naming them using the convention `XX-feature-being-tested.sql`.  For example, `01-account-creation.sql`.

Once you've created your test file, you can begin writing test.  All tests should take the following format:

```sql
-- begin the transaction, this will allow you to rollback any changes made during the test
BEGIN;

-- plan your test in advance, this ensures the proper number of tests have been run.
select plan(1);

-- run your test

select ok(true, 'test passed');

-- check the results of your test
select * from finish();

-- rollback the transaction, completing the test scenario
ROLLBACK;
```

### <a id="testing-authenticated">Testing authenticated requests</a>
By default, all tests are run as the `postgres` user, meaning you have full access to the database.  That can be useful for some things, but doesn't provide very comprehensive test scenarios. You can change that by authenticating as a specific user:

The following examples leverage the [Supabase test helpers](https://github.com/usebasejump/supabase-test-helpers).

```sql
BEGIN;

select plan(2);

-- create users for testing
select tests.create_supabase_user('test_owner');
select tests.create_supabase_user('test_reader');

-- create an example post for testing
insert into posts (title, body, user_id) values ('test post', 'this is a test post', tests.get_supabase_uid('test_owner'));

-- authenticate as reader
select tests.authenticate_as('test_reader');

-- verify that we cannot update the post
SELECT
    is_empty(
            $$ update posts set content = 'Post updated by viewer' returning content $$,
            'Post viewers cannot update posts'
        );

-- verify that the reader can view the post
select results_eq(
               $$ select title from posts $$,
               $$ VALUES('test post') $$,
               'Readers can view posts'
           );

-- finish out the test scenario
select * from finish();

ROLLBACK;
```

### <a id="testing-anonymous">Testing anonymous requests</a>
Similarly, you can test anonymous requests by clearing the authentication:

```sql
BEGIN;

select plan(2);

-- create users for testing
select tests.create_supabase_user('test_owner');

-- create an example post for testing
insert into posts (title, body, user_id) values ('test post', 'this is a test post', tests.get_supabase_uid('test_owner'));

-- clear out authentication
select tests.clear_authentication();

-- verify that we cannot update the post
SELECT
    is_empty(
            $$ update posts set content = 'Post updated by viewer' returning content $$,
            'Post viewers cannot update posts'
        );

-- verify that the reader can view the post
select is_empty(
               $$ select title from posts $$,
               'Anon users cannot view posts'
           );

-- finish out the test scenario
select * from finish();

ROLLBACK;
```

### <a id="checking-rls">Checking for missing RLS on tables</a>
It's a good practice to make sure all tables have RLS policies defined.  The test helpers have a convenience method to make that easy

```sql

BEGIN;

select plan(1);

select tests.rls_enabled('public');

select * from finish();

ROLLBACK;
```

### <a id="rls-testing">Testing your RLS Policies</a>
It can be a bit challenging to know how to test RLS policies specifically. Here are a few examples I've found helpful:

#### <a id="rls-insert">Insert policies</a>
Inserting policies that are protected by RLS will throw an error when blocked. You can test them as follows:

```sql
BEGIN;

select plan(1);

-- ensure you're anon
select tests.clear_authentication();

SELECT
    throws_ok(
            $$ insert into posts (content) values ('Post created by anon') $$,
            'new row violates row-level security policy for table "posts"'
        );

select * from finish();

ROLLBACK;
```

#### <a id="rls-update">Update policies</a>
Blocked update requests will NOT throw an error.  I've found it's useful to return a fake value and check for it instead

```sql
BEGIN;

select plan(1);

-- create users for testing
select tests.create_supabase_user('test_owner');

-- create an example post for testing
insert into posts (title, body, user_id) values ('test post', 'this is a test post', tests.get_supabase_uid('test_owner'));


-- ensure you're anon
select tests.clear_authentication();

SELECT
    is_empty(
            $$ update posts set content = 'Post updated by anon' returning content $$,
            'anon users cannot update posts'
        );

select * from finish();

ROLLBACK;
```

#### <a id="rls-delete">Delete policies</a>
Similar to updates, delete requests that are blocked will not throw an error. The same format will work, however:
```sql
BEGIN;

select plan(1);

-- create users for testing
select tests.create_supabase_user('test_owner');

-- create an example post for testing
insert into posts (title, body, user_id) values ('test post', 'this is a test post', tests.get_supabase_uid('test_owner'));


-- ensure you're anon
select tests.clear_authentication();

SELECT
    is_empty(
            $$ delete from posts returning 1 $$,
            'Anon cannot delete posts'
        );

select * from finish();

ROLLBACK;
```

#### <a id="rls-select">Select policies</a>
Select policies are a bit tricky. They'll filter out any rows that are not a match for your policy. When testing these, you'll want to make sure you have a clean dataset to work with:

```sql
BEGIN;

select plan(1);

-- create users for testing
select tests.create_supabase_user('test_owner');

-- create an example post for testing
insert into posts (title, body, user_id) values ('test post', 'this is a test post', tests.get_supabase_uid('test_owner'));


-- ensure you're anon
select tests.clear_authentication();

SELECT
    is_empty(
            $$ select * from posts $$,
            'Anon cannot select posts'
        );

select * from finish();

ROLLBACK;
```

### <a id="github-action">Running your tests automatically using Github Actions</a>
Once your tests are ready to go, you can configure them to run on every PR using Github Actions.  Note that right now the Supabase github action defaults to v1 of the CLI, so you need to specify version `1.11.4` or greater. Here's an example workflow file:

`.github/workflows/pgTAP.yml`

```yaml
name: PGTap Tests
on:
  pull_request:
    branches: [ main ]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: supabase/setup-cli@v1
        with:
          version: 1.11.4
      - name: Supabase Start
        run: supabase start
      - name: Run Tests
        run: supabase test db
```

A Guide to testing on Supabase using pgTAP


We've just released an upgrade to Basejump that bumps NextJS to version 13.  As part of the release, Vercel added support for automatic social image generation using edge functions.  That means you'll get a nice image pulled in whenever you post content to Twitter, Facebook, etc... It seemed pretty cool, so I wanted to give it a whirl for Basejump.

The starter kit now includes a basic social image generator for both documentation and blog posts. I recommend customizing it a bit for your use case, here's what I did for our content site (generated live):

![Generate Social Image for Basejump](https://usebasejump.com/api/og?title=There's%20always%20money%20in%20the%20banana%20stand%20)

It's pretty straight forward to do this, so here's a quick rundown.

## Create an image generating edge function
First up, you'll need to get the api path setup for generating images.  To do this, we'll pull in the `@vercel/og` package:

```bash
yarn add @vercel/og
```

Next, create your API route.  I made mine at `/pages/api/og.tsx`.  Note the `.tsx` extension, we'll be generating HTML here so that's important.

```typescript
import { ImageResponse } from "@vercel/og";
import { NextRequest } from "next/server";

export const config = {
  runtime: "experimental-edge",
};

export default function (req: NextRequest) {
  try {
    const { searchParams } = new URL(req.url);

    const hasTitle = searchParams.has("title");
    const title = hasTitle
      ? searchParams.get("title")?.slice(0, 100)
      : "Basejump";
    return new ImageResponse(
      (
        <div
          style={{
            fontSize: 80,
            color: "white",
            fontWeight: "bolder",
            background:
              "linear-gradient(117deg, rgba(2,0,36,1) 0%, rgba(9,9,121,1) 100%)",
            width: "100%",
            height: "100%",
            display: "flex",
            padding: 60,
            flexDirection: "column",
            justifyContent: hasTitle ? "space-between" : "center",
            alignItems: "center",
          }}
        >
          {hasTitle && (
            <div
              style={{
                fontWeight: "bolder",
              }}
            >
              {title}
            </div>
          )}
          <div
            style={{
              width: "100%",
              display: "flex",
              fontWeight: 800,
              justifyContent: hasTitle ? "flex-end" : "center",
              alignItems: "center",
              alignContent: hasTitle ? "flex-end" : "center",
            }}
          >
            <img
              width="150"
              height="150"
              src={`https://your-url-here.com/images/logo.png`}
              style={{
                borderRadius: 128,
                marginRight: "1rem",
              }}
            />
            Basejump
          </div>
        </div>
      ),
      {
        width: 1200,
        height: 600,
      }
    );
  } catch (e) {
    console.log(`${e.message}`);
    return new Response(`Failed to generate an image`, {
      status: 500,
    });
  }
}

```

A couple things to point out here.  

First, I'm accepting a title tag as a URL param.  When one is included, it'll format the image with a logo on the bottom right and the title centered up top.  If a title is not sent, it'll just center the logo in the middle of the image.

Next, I've added a quick CSS gradient for the background. The `@vercel/og` package gives you access to most CSS functionality, so there's a lot you can do here.  I can't remember how to generate CSS gradients to save my soul, so I used [cssgradient.io](https://cssgradient.io/).

## Adding your social images
Once that's done, you'll just need to pull in your social images on relevant pages.  To do that, you'll add the following meta tags to your header:

```typescript
<Head>
  <meta name="twitter:card" content="summary_large_image" />
  <meta name="description" content={description} key="desc" />
  <meta property="og:title" content={title} />
  <meta name="twitter:title" content={title} />
  <meta property="og:description" content={description} />
  <meta name="twitter:description" content={description} />
  <meta property="og:image" content={`https://your-url-here/api/og?title=${title}`} />
  <meta name="twitter:image" content={`https://your-url-here/api/og?title=${title}`} />
</Head>
```

That's it, all done!

Generating Social Images with Next 13


Basejump started as the first three weeks of another project I was working on. I'd decided to use it for learning [Supabase](https://supabase.com/), but very quickly realized that there aren't a lot of guides on how to 
leverage it for more serious projects.  In particular, things like team accounts, permissions and testing are fairly poorly documented.

At first, I planned on abstracting the basic SaaS setup into a starter kit and charging for it. Since then, I've learned a ton about RLS and some of the gotchas there for more complex permissions. Also, pgTAP is great once you get the hang of it, but can be pretty intimidating to get going. The end result can be some pretty poor permission logic that can lead to security bugs.

As a result, I've decided instead to release Basejump as an open source project. That way more folks can benefit from some of my mistakes, contribute improvements and hopefully avoid some headache.

I have additional plans for the project, but think it's reached a decent enough spot to be useful, so here it is!

You can check out [Basejump on Github](https://github.com/usebasejump/basejump).


Working with Supabase Edge Functions for Basejump

Creating Supabase & Postgres extensions for database.dev

Oauth and magic links with Supabase and NextJS middleware

A Guide to testing on Supabase using pgTAP

Generating Social Images with Next 13

Introducing Basejump

Basejump