Supabase SQL

Supabase SQL

Star on Github

Add Constraint

Template for adding a constraint.

Automatically update timestamps

Update a column timestamp on every update.

Basic Aggregrate Functions

Set of functions to perform calculation on a set of values. The return is a single summary value. (Except for "round()")

CRON job with pg_cron

Schedule PostgreSQL commands directly from the database.

Change Column Type

Template to modify a column type. Make sure to change the name and type.

Create View

Basic view template. Change according to your preference.

Create basic table

Basic table template. Change "table_name" to the name you prefer.

Create function

Template to create a simple function.

Create function that return integer

Template to create a function that return integer like count of rows.

Create function that return table

Template to create a function a function that return set of table.

Create table with constraints

Table with constraints example.

Create table with foreign key

Table with foreign key (fk) template. Change "table_name" to the name you prefer.

Delete Storage Item

Delete items in Storage using SQL

Drop Constraint

Template for dropping / removing constraint.

Drop Function

Template for dropping / removing function.

Drop RLS

Template for dropping / removing row level security.

Drop Trigger

Template for dropping / removing trigger from table.

Full Text Search

How to use full text search in PostgreSQL.

Generate Youtube-like short ID

Generate YouTube-like short IDs as Postgres Primary Keys.

Handle New User - Function & Trigger

Insert new user data into another table when event new user signup using Supabase Auth.

Increment field value

Update a field with incrementing value using stored procedure.

Javascript

Javascript snippets.

List all constraints

List all constraints and their tables.

List all foreign keys

List all foreign keys (FKs) and their columns.

List all functions

List all the functions in (predefined/user-defined).

List all primary keys

List all primary keys (PKs) and their columns.

List all table size

List all table data size.

List all triggers

List all the triggers in (predefined/user-defined).

RLS Advanced policies

Row level security with advanced/complicated policies.

RLS Policies with joins

Row level security that requires table joins .

RLS Policies with security definer functions

Row level security that make use of security definer functions .

RLS Read Access

Row level security for read access.

RLS Restrict updates

Row level security for restrict updates.

RLS Time to live for rows

Row level security policies to implement TTL.

RLS Verifying email domains

Row level security that verify email domains.

Rename Constraints

Template to rename any constraints you have.

Sequence operations

Define a sequence generator.

Show Postgres version

Check your database version.

Stripe subscriptions

Starter template for the Next.js Stripe Subscriptions Starter.

Todo List

Build a todo list with Row Level Security.

Update Constraint

Template for updating a constraint.

RLS Advanced policies

Row level security with advanced/complicated policies.

  1. Creates a table called profiles in the public schema (default schema).
  2. Enables Row Level Security.
  3. Creates a policy which allows logged in users to update their own data.
create table posts (
  id            serial    primary key,
  creator_id    uuid      not null     references auth.users(id),
  title         text      not null,
  body          text      not null,
  publish_date  date      not null     default now(),
  audience      uuid[]    null -- many to many table omitted for brevity
);

create table comments (
  id            serial    primary key,
  post_id       int       not null     references posts(id)  on delete cascade,
  user_id       uuid      not null     references auth.users(id),
  body          text      not null,
  comment_date  date      not null     default now()
);

create policy "Creator can see their own posts"
on posts
for select
using (
  auth.uid() = posts.creator_id
);

create policy "Logged in users can see the posts if they belong to the post 'audience'."
on posts
for select
using (
  auth.uid() = any (posts.audience)
);

create policy "Users can see all comments for posts they have access to."
on comments
for select
using (
  exists (
    select 1 from posts
    where posts.id = comments.post_id
  )
);
Edit this script Updated at Wed, Jan 18, 2023