`supabase-drizzle`

Overview

supabase-drizzle lets you manage Postgres RLS policies with Drizzle-like syntax, just as you manage your Postgres schema.

Active Development Notice

supabase-drizzle is a new library that is under active development. It is not fully battle-tested yet, but Im working on improvements to make it production-ready. In the meantime, there could be breaking changes as I continue refining it. Feel free to contribute or open issues as the project progresses.

Motivation

Drizzle ORM makes managing your database schema in TypeScript and handling migrations simple. However, managing RLS policies still requires manual SQL or using the Supabase dashboard, which can slow down your workflow.

supabase-drizzle aims to streamline this process, making RLS policy management as easy and intuitive as managing your schema with Drizzle, so you can move faster.

Installation

To install supabase-drizzle, simply run:

yarn add supabase-drizzle
# or
npm install supabase-drizzle

Peer Dependencies

Make sure you have the following peer dependencies installed in your project:

yarn add drizzle-kit drizzle-orm
# or
npm install drizzle-kit drizzle-orm

Usage

Step 1

Follow the Drizzle setup guide for Supabase, then come back here and carry on to step 2.

Step 2

Add a file policies.config.ts in the same directory as your drizzle.config.ts file.

Step 3

In drizzle.config.ts, import defineConfig from supabase-drizzle instead of drizzle-kit:

- import { defineConfig } from 'drizzle-kit';
+ import { defineConfig } from 'supabase-drizzle';

Your config file will now support a policies option. This should point to the policies file policies.config.ts you made in step 2:

// drizzle.config.ts

import { defineConfig } from 'supabase-drizzle';
import { config } from 'dotenv';

config({ path: '.env' });

export default defineConfig({
  schema: 'drizzle/schema.ts',
  out: 'drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
  schemaFilter: ['public'],
  policies: 'drizzle/policies.ts'
});

Step 4

Define RLS policies for your tables in policies.config.ts. This is a basic example, see examples below for user role and multi-tenant configurations:

// policies.config.ts

import { rlsPolicyBuilder } from 'supabase-drizzle';
import * as schema from './schema';

const { tables, own, rls, authenticated, everyone } = rlsPolicyBuilder(schema, {});

const profiles = rls(tables.profiles, {
  insert: authenticated(), // User can create their profile row if authenticated
  update: own(), // User can update their own profile row
  select: everyone(), // Everyone can select all profile rows
});

const tenantUserRoles = rls(tables.todos, {
  insert: authenticated(), // User can create a todo row if authenticated
  update: own(), // User can update their own todo row
  select: own(), // User can select their own todo row
  delete: own(), // User can delete their own todo row
});

// Make sure your policies are exported from the file!
export { profiles, tenantUserRoles };

Step 5

Generate your RLS policy migration:

# Make sure to point to the correct config file location for your project

npx supabase-drizzle generate --config drizzle/drizzle.config.ts

supabase-drizzle generate outputs the location of the generated SQL migration file for your RLS policies. I suggest you manually check this for errors.

Step 6

Run the migration using drizzle:

# Make sure to point to the correct config file location for your project

npx drizzle-kit migrate --config drizzle/drizzle.config.ts

Step 7

For ease of use, consider adding these scripts to your package.json (Make sure to point to the correct config file location for your project):

{

  "scripts": {
    "drizzle-generate-schema": "npx drizzle-kit generate --config drizzle/drizzle.config.ts",
    "drizzle-generate-rls": "npx supabase-drizzle generate --config drizzle/drizzle.config.ts",
    "drizzle-migrate": "npx drizzle-kit migrate --config drizzle/drizzle.config.ts",
  }
}

Then you can sync your local schema and RLS policies to Supabase in one go using:

yarn drizzle-generate-schema && drizzle-generate-rls && yarn drizzle-migrate
#or
npm run drizzle-generate-schema && npm run drizzle-generate-rls && npm run yarn drizzle-migrate

Usage

`rlsPolicyBuilder(schema, rlsConfig)`

Returns functions that allow you to define RLS policies for your tables.

// policies.config.ts

import { rlsPolicyBuilder } from 'supabase-drizzle';
import * as schema from './schema';

const {
  tables, // Strongly typed table names object based on your schema
  roles, // Strong typed user roles object based on your RLS config
  rls, // Function to define RLS policies for a table
  own, // RLS policy to allow access where the row's user_id matches the user's uid
  authenticated // RLS policy to allow access to all authenticated users
  hasRole, // RLS policy to allow access if a user has a role
  everyone // RLS policy that allows access to all users
} = rlsPolicyBuilder(
  schema, // Your drizzle schema (only single file schema supported)
  {
    tenantsTable: schema.tenants, // Optional - used for multi-tenant setups
    userRolesTable: schema.userRoles, // Optional - used for RLS policies with user roles
    userRoles: { // Optional - used for RLS policies with user roles
      owner: {},
      admin: {},
      member: {}
    }
  }
);

`rls()`

Function to define RLS policies for a table.

// policies.config.ts

const profiles = rls(tables.profiles, {
  insert: authenticated(),
  update: own(),
  select: everyone(),
});

export { profiles }

`own()`

RLS policy to allow access where the row's user_id matches the user's uid. Tables must have a user_id column to use this policy.

// policies.config.ts

const todos = rls(tables.todos, {
  all: own() // User has access to all methods where their uid matches the row's user_id
});

export { profiles }

`authenticated()`

RLS policy to allow access to authenticated users.

// policies.config.ts

const profiles = rls(tables.profiles, {
  insert: authenticated(),
});

export { profiles }

`hasRole(userRole: string | string[])`

RLS policy to allow access if a user has a role. User roles are defined in your rlsConfig passed to rlsPolicyBuilder.

// policies.config.ts

const profiles = rls(tables.profiles, {
  insert: authenticated(),
  select: everyone(),
  update: own(),
  delete: hasRole(['admin', 'owner']) // Or simply `hasRole('admin')`
});

export { profiles }

`everyone()`

RLS policy to allow access to all users. Proceed with caution.

// policies.config.ts

const profiles = rls(tables.profiles, {
  select: everyone(),
});

export { profiles }

Examples

Limitations

Currently only a single file Drizzle schema is supported.
I have only tested this lib on MacOS. I expect it will work on *nix systems but not Windows. If there is enough interest I might look into it.
Only Typescript Drizzle Kit config files are supported. Plain JavaScript will not work. Again, if there's enough interest I might look into it.

Contributing

Contributions are welcome. I am a single developer who built this to solve a problem I had, so if there's a feature you specifically want feel free to open a pull request.

Disclaimers

supabase-drizzle is not an official library affiliated with the Supabase or Drizzle teams.
I make no guarantees that this lib is bug free.
I strongly suggest that you manually review generated SQL for RLS migrations before running them.
Don't use this lib anywhere near production databases until it's more stable and battle tested.