database

128 notes in this subject, read out of this brain and free to use. This is page 1 of 3.

Ways to work with Supabase database

You can work with your project's database in three ways: visually using the Table Editor section of the Dashboard, with query syntax using the SQL Editor section of the Dashboard, or programmatically using a variety of different methods.

Supabase provides full Postgres database

Every Supabase project gets a full Postgres database, not a Postgres abstraction. This database is the foundation that Auth, Storage, Realtime, and Edge Functions are built on.

Supabase database backups and recovery

Supabase manages daily database backups and offers point-in-time recovery on paid plans.

SET DEFAULT foreign key delete behavior

When SET DEFAULT is specified, deleting a row from the parent table sets the values of the foreign key columns in the child tables to their default values.

Five foreign key constraint delete options

PostgreSQL supports five options for foreign key constraint deletes: CASCADE, RESTRICT, SET NULL, SET DEFAULT, and NO ACTION. These are specified using the ON DELETE clause in a foreign key constraint definition.

CASCADE foreign key delete behavior

When a row is deleted from the parent table with CASCADE specified, all related rows in the child tables are deleted as well.

NO ACTION foreign key delete behavior

NO ACTION prevents deletion of a row in the parent table if there are related rows in a child table. Unlike RESTRICT, NO ACTION can be deferred to the end of a transaction using INITIALLY DEFERRED. When deferred, other cascading deletes can run first, and the constraint only raises an error if referenced data remains at the end of the transaction.

RESTRICT vs NO ACTION difference

Both RESTRICT and NO ACTION prevent deletion if child rows exist, but RESTRICT checks immediately while NO ACTION can defer the check. RESTRICT always raises an error immediately. NO ACTION with INITIALLY DEFERRED defers the check until the end of the transaction, allowing other cascading deletes to execute first and potentially resolve the constraint violation.

NO ACTION is the default for foreign keys

NO ACTION is the default behavior when no delete option is explicitly specified in a foreign key constraint.

SET NULL foreign key delete behavior

When SET NULL is specified, deleting a row from the parent table sets the values of the foreign key columns in the child tables to NULL.

RESTRICT foreign key delete behavior

When RESTRICT is specified, attempting to delete a row from the parent table will abort the delete operation and raise an error if there are any related rows in the child tables. The database will not delete, update or set to NULL any rows in the referenced tables.

Foreign key cascade delete SQL syntax

To create a foreign key constraint with CASCADE delete, use the ON DELETE CASCADE clause in an ALTER TABLE statement. Example: ALTER TABLE child_table ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent_table (id) ON DELETE CASCADE;

NO ACTION INITIALLY DEFERRED allows cascades to take precedence

When NO ACTION INITIALLY DEFERRED is used, if a child table has a CASCADE delete constraint on another foreign key column pointing to the same parent table, the CASCADE delete will execute first. If CASCADE removes the referencing rows, the deferred NO ACTION constraint will not raise an error because there are no remaining references at the end of the transaction.

Change database timezone

To change the timezone of a Supabase database, use the SQL command: alter database postgres set timezone to 'America/New_York'; where the timezone value is replaced with the desired timezone.

Default timezone in Supabase databases

Every Supabase database is set to UTC timezone by default. Supabase recommends keeping it this way, even if your users are in a different location, because it makes it much easier to calculate differences between timezones if everything in the database is in UTC time.

Query available timezones in Postgres

To get a full list of timezones supported by your database, use the query: select name, abbrev, utc_offset, is_dst from pg_timezone_names() order by name; This returns columns: name (time zone name), abbrev (time zone abbreviation), utc_offset (offset from UTC, positive means east of Greenwich), and is_dst (true if currently observing daylight savings).

Search for specific timezone

To search for a specific timezone, use case-insensitive search with ilike: select * from pg_timezone_names() where name ilike '%york%';

Restricted roles cannot use wildcard operator

Roles with restricted column-level privileges cannot use the wildcard operator (*) on affected tables. Instead of SELECT * FROM <restricted_table>, column names must be specified explicitly.

Revoke column-level UPDATE privilege

To restrict a specific column from being updated by a role, revoke the column-level UPDATE privilege on that column. Example: revoke update (title) on table public.posts from authenticated; After the table-level UPDATE is already revoked, only the column-level revoke is needed.

Manage column privileges via migrations

Column-level privileges can be managed through database migrations using SQL commands (REVOKE and GRANT). A migration is created using 'supabase migration new <name>' and SQL commands are added to the generated migration file to define column privileges.

Two types of Postgres privileges: table-level and column-level

Table-level privileges grant a privilege on all columns in the table. Column-level privileges grant a privilege on a specific column in the table. Both types can exist on the same table simultaneously. If both are present and the column-level privilege is revoked, the table-level privilege remains in effect.

Column privilege revocation consequences

When a column privilege is turned off, that column becomes inaccessible for all operations (insert, update, delete) and using select * will fail on the affected table.

RLS policies enforce WHERE clause on queries

RLS policies work by adding a WHERE clause to every query executed on a table. For example, a policy can restrict updates to only rows where the user_id matches the authenticated user, like: create policy "Allow update for owners" on posts for update using ((select auth.uid()) = user_id);

Column Level Security definition

Column Level Security in Postgres allows you to restrict access to specific columns within rows of a table. It complements Row Level Security (RLS) which controls row access. Column-level privileges are managed separately from RLS policies.

Column Level Security is an advanced feature

Column-level privileges are an advanced feature that Supabase does not recommend for most users. Instead, RLS policies in combination with a dedicated table for handling user roles are recommended for common access control needs.

Migration example with row and column-level security

Example migration creating a posts table with both row and column-level security: create table posts (id bigint primary key generated always as identity, user_id text, title text, content text, created_at timestamptz default now(), updated_at timestamptz default now()); create policy "Allow update for owners" on posts for update using ((select auth.uid()) = user_id); revoke update (title) on table public.posts from authenticated;

Running database queries in Supabase

You can execute queries in Supabase using the SQL Editor in the Supabase Dashboard, or via psql if connecting directly to the database.

Drop all tables in a schema with CASCADE

To drop all tables in a Postgres schema, execute this query: do $$ declare r record; begin for r in (select tablename from pg_tables where schemaname = 'my-schema-name') loop execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade'; end loop; end $$;. Replace 'my-schema-name' with the actual schema name. In Supabase, the default schema is 'public'. This query iterates through all tables in the schema and drops each one with CASCADE to remove dependent objects.

Default schema in Supabase

The default schema in Supabase is 'public'.

Backup before dropping all tables

Before dropping all tables in a schema, ensure you have a recent backup. Dropping all tables deletes all associated data and cannot be easily recovered without a backup.

Only postgres user can create event triggers in Supabase

Only the postgres user can create event triggers in Supabase. Authentication as the postgres user is required before creating an event trigger.

Event trigger performance consideration

Event triggers run for each DDL command and can consume resources which may cause performance issues if not used carefully.

Drop event trigger syntax

Event triggers can be deleted using the DROP EVENT TRIGGER command: DROP EVENT TRIGGER trigger_name;

Disable event trigger syntax

Event triggers can be disabled using the ALTER EVENT TRIGGER command: ALTER EVENT TRIGGER trigger_name DISABLE;

Prevent table drops with event trigger

This example creates a function that prevents any table from being dropped by raising an exception when a table drop is detected via pg_event_trigger_dropped_objects(). The trigger can be temporarily disabled with ALTER EVENT TRIGGER dont_drop_trigger DISABLE;

Event trigger composition

Event triggers consist of two parts: a PL/pgSQL function which will be executed when the triggering event occurs, and the actual Event Trigger object with parameters around when the trigger should be run.

Event trigger firing events

Event triggers can be triggered on four types of events: ddl_command_start (occurs before a DDL command for almost all objects within a schema), ddl_command_end (occurs after a DDL command for almost all objects within a schema), sql_drop (occurs before ddl_command_end for any DDL commands that DROP a database object, and altering a table can cause it to be dropped), and table_rewrite (occurs before a table is rewritten using the ALTER TABLE command).

Event trigger example code - prevent drops

CREATE OR REPLACE FUNCTION dont_drop_function() RETURNS event_trigger LANGUAGE plpgsql AS $$ DECLARE obj record; tbl_name text; BEGIN FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects() LOOP IF obj.object_type = 'table' THEN RAISE EXCEPTION 'ERROR: All tables in this schema are protected and cannot be dropped'; END IF; END LOOP; END; $$; CREATE EVENT TRIGGER dont_drop_trigger ON sql_drop EXECUTE FUNCTION dont_drop_function();

Event trigger definition and purpose

An event trigger in Postgres is triggered by database level events rather than row-level events like regular triggers. Event triggers are usually reserved for superusers. With Supabase's Supautils extension, the postgres user can create and manage event triggers.

Event trigger use cases

Event triggers are useful for capturing Data Definition Language (DDL) changes to database schema, and for enforcing, monitoring, or preventing actions such as preventing tables from being dropped in production or enforcing RLS on all new tables.

Event trigger helper functions

Within each event trigger, helper functions exist to view the objects being modified or the command being run. For example, pg_event_trigger_dropped_objects() returns the object(s) being dropped. For a comprehensive overview, refer to the official Postgres event trigger definition documentation.

Unsupported SQL operations in Supabase

The following operations that typically require superuser privileges are not available in Supabase: COPY ... FROM PROGRAM and ALTER USER ... WITH SUPERUSER.

Default postgres role without superuser privileges

Supabase provides the default postgres role to all instances deployed, but superuser access is not given because it allows destructive operations to be performed on the database.

postgres user privileges on Supabase

The postgres user in Supabase is granted additional privileges beyond the standard role to allow it to run some operations that are normally restricted to superusers, compensating for the lack of full superuser access.

Small deletes with DELETE statement

For tables with less than a few thousand rows, use a DELETE operation. DELETE acquires a ROW EXCLUSIVE lock, which allows other SELECT, INSERT, UPDATE, and DELETE statements to run concurrently. For small row counts, the operation completes with minimal impact.

Find table dependencies with pg_depend

Use system catalog tables pg_class, pg_constraint, and pg_depend to identify dependencies. Query pg_depend with a WHERE clause matching the referenced object to find all objects that depend on a table. For example: SELECT d.classid::regclass as dependent_object, d.objid::regclass as dependent_object_id, d.refclassid::regclass as referenced_object, d.refobjid::regclass as referenced_object_id FROM pg_depend d WHERE d.refobjid = 'public.logs'::regclass;

Example: DROP COLUMN with timeout

SET LOCAL lock_timeout = '5s'; ALTER TABLE users DROP COLUMN IF EXISTS legacy_field;

DROP INDEX lock behavior

Dropping a regular index takes an ACCESS EXCLUSIVE lock on the index but not on the table, so reads and writes to the table continue uninterrupted.

Example: DROP INDEX

DROP INDEX IF EXISTS idx_users_legacy_field;

TRUNCATE for deleting all data

To delete all data from a table, use TRUNCATE instead of DELETE. TRUNCATE is much faster because it does not generate individual row-level WAL entries, does not scan the table, and also resets any auto-incrementing sequences.

Example: TRUNCATE table

TRUNCATE TABLE logs;

Example: Soft delete implementation

ALTER TABLE orders ADD COLUMN deleted_at timestamptz; UPDATE orders SET deleted_at = now() WHERE id = 42; CREATE VIEW active_orders AS SELECT * FROM orders WHERE deleted_at is null;

DROP TABLE lock behavior

DROP TABLE acquires an ACCESS EXCLUSIVE lock, which blocks all other operations on the table, including reads. On a busy table, this can queue up behind long-running queries. Always use IF EXISTS in migrations to avoid errors.

Example: DELETE recent data

Delete rows created more than 90 days ago: DELETE FROM logs WHERE created_at < now() - interval '90 days';

Prepare before deleting data

Before deleting rows or dropping objects, test in a staging environment, ensure a recent backup exists, confirm table dependencies and foreign key constraints, drop dependent objects explicitly (use CASCADE with caution), choose a low traffic time, run operations inside a migration, and set timeouts such as lock_timeout and statement_timeout.

Example: DROP TABLE safely

DROP TABLE IF EXISTS old_analytics;

Soft delete pattern

Instead of permanently deleting data, add a timestamptz column like deleted_at to mark rows as deleted. Update the column to now() instead of deleting. Exclude soft-deleted rows in queries using WHERE deleted_at is null or via views. Combine with a scheduled hard-delete job using pg_cron to permanently remove old soft-deleted rows during low-traffic periods.

Large deletes with batch deletion

Deleting millions of rows in a single statement holds locks for a long time, generates WAL traffic, and impacts replication. Instead, delete in batches to control runtime and minimize impact. Example: DELETE FROM logs WHERE id IN (SELECT id FROM logs WHERE created_at < now() - interval '90 days' LIMIT 5000);

DROP COLUMN lock behavior

Dropping a column is a metadata-only operation in Postgres that does not rewrite the table. However, it requires an ACCESS EXCLUSIVE lock. Since the lock is brief, this is generally safe on tables with many concurrent transactions. Use a lock timeout to avoid waiting indefinitely.

Find unused indexes with Supabase CLI

Use the Supabase CLI inspect command to identify unused indexes: supabase inspect db index-stats

Give your agent this brain