database

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

C# client initialization with custom schema

Initialize the C# Supabase client with a custom schema by passing SupabaseOptions { Schema = "myschema" } to the Client constructor: var supabase = new Supabase.Client(SUPABASE_URL, SUPABASE_PUBLISHABLE_KEY, new SupabaseOptions { Schema = "myschema" }); await supabase.InitializeAsync();

Exposing custom schemas to data APIs

To expose custom database schemas via data APIs, first add the custom schema to 'Exposed schemas' in API settings at /dashboard/project/_/settings/api. Then grant permissions by running GRANT USAGE ON SCHEMA myschema TO anon, authenticated, service_role; GRANT ALL ON ALL TABLES IN SCHEMA myschema TO anon, authenticated, service_role; GRANT ALL ON ALL ROUTINES IN SCHEMA myschema TO anon, authenticated, service_role; GRANT ALL ON ALL SEQUENCES IN SCHEMA myschema TO anon, authenticated, service_role; ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON TABLES TO anon, authenticated, service_role; ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON ROUTINES TO anon, authenticated, service_role; ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA myschema GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;

Public schema is exposed by default

By default, the public schema is automatically exposed on data APIs without requiring additional configuration.

Creating custom schemas

Custom schemas are created using the SQL command CREATE SCHEMA myschema; where myschema is replaced with the desired schema name.

Dart/Flutter client initialization with custom schema

Initialize the Flutter client with a custom schema by passing postgrestOptions: const PostgrestClientOptions(schema: 'myschema') to Supabase.initialize(). Alternatively, change the schema on a per-query basis using: supabase.schema('myschema').from('todos').select()

Cron job execution options

Each cron job can run SQL snippets or database functions with zero network latency, or can make an HTTP request such as invoking a Supabase Edge Function.

Cron job performance recommendations

For best performance, no more than 8 jobs should run concurrently. Each job should run no more than 10 minutes.

Cron job management methods

Cron jobs can be managed via the Supabase Dashboard interface or by writing SQL directly. The Dashboard provides an interface to schedule jobs and monitor job runs, with the same functionality available through SQL.

Supabase Cron overview and capabilities

Supabase Cron is a Postgres module that simplifies scheduling recurring jobs using cron syntax and monitoring job runs inside Postgres. Cron jobs can be created via SQL or the Integrations -> Cron interface in the Dashboard. Jobs can run anywhere from every second to once a year depending on the use case.

pg_cron Postgres extension

Supabase Cron uses the pg_cron Postgres database extension, which is the scheduling and execution engine for jobs. The extension creates a cron schema in the database where all jobs are stored in the cron.job table, and every job run and its status is recorded in the cron.job_run_details table.

Install pg_cron extension via Dashboard

To install the Supabase Cron Postgres Module using the Dashboard, navigate to the Cron Postgres Module under Integrations in the Dashboard, then enable the pg_cron extension.

Install pg_cron extension via SQL

To install pg_cron via SQL, run: create extension pg_cron with schema pg_catalog; grant usage on schema cron to postgres; grant all privileges on all tables in schema cron to postgres;

pg_cron extension deletion is irreversible

Disabling the pg_cron extension will permanently delete all Jobs. This action cannot be undone.

Uninstall pg_cron extension

To uninstall the pg_cron extension, run: drop extension if exists pg_cron; Disabling the pg_cron extension will permanently delete all Jobs.

Cron job run history is not automatically cleaned up

The records in the cron.job_run_details table are not cleaned up automatically. They are also not removed when jobs are unscheduled, which can consume disk space in your database over time.

Example: Invoke Supabase Edge Function every 30 seconds

select cron.schedule('invoke-function-every-half-minute', '30 seconds', $$ select net.http_post(url:='https://project-ref.supabase.co/functions/v1/function-name', headers:=jsonb_build_object('Content-Type','application/json', 'apikey', 'YOUR_PUBLISHABLE_KEY'), body:=jsonb_build_object('time', now()), timeout_milliseconds:=5000) as request_id; $$); This requires the pg_net extension to be enabled.

Caution: System maintenance cron jobs can have unintended consequences

Be extremely careful when scheduling cron jobs for system maintenance tasks. For example, scheduling pg_terminate_backend(pid) to terminate idle connections can disrupt critical background processes like nightly backups. Often, existing Postgres settings like idle_session_timeout can perform these common maintenance tasks more safely. Contact Supabase Support if unsure.

Cron job names are case sensitive and cannot be edited

Job names are case sensitive. Once created, a job name cannot be edited. Attempting to create a second Job with the same name and case will overwrite the first Job.

Schedule a cron job with SQL

Use the cron.schedule() function to create a cron job. The syntax is: select cron.schedule('job-name', 'schedule-expression', 'command'); The job name is permanent and cannot be edited once created. The schedule can be a standard cron expression (e.g., '30 3 * * 6' for Saturday at 3:30AM GMT) or sub-minute intervals like '30 seconds'.

Activate or deactivate a cron job

Use cron.alter_job() with the active parameter to toggle a job's status: select cron.alter_job(job_id := (select jobid from cron.job where jobname = 'job-name'), active := true); to activate or active := false to deactivate.

Example: Call database function every 5 minutes

select cron.schedule('call-db-function', '*/5 * * * *', 'SELECT hello_world()');

Example: Run VACUUM every day

select cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');

Delete a cron job with SQL

Use cron.unschedule('job-name') to permanently delete a job from the cron.job table. Note that unscheduling does not remove the job's run history from the cron.job_run_details table.

Cron syntax specification

Cron expressions use five fields: (1) minute (0-59), (2) hour (0-23), (3) day of month (1-31), (4) month (1-12), (5) day of week (0-6, where 0 and 7 are Sunday and 6 is Saturday). You can also use [1-59] seconds syntax (e.g., '30 seconds') to schedule sub-minute jobs on Postgres 15.1.1.61 or later.

Alter a cron job with SQL

Use the cron.alter_job() function to modify a job. The full signature is: cron.alter_job(job_id bigint, schedule text default null, command text default null, database text default null, username text default null, active boolean default null). You can find the job_id by querying the cron.job table. Alternatively, use cron.schedule() with the same job name to replace the job via upsert.

Example: Delete old data every week

select cron.schedule('saturday-cleanup', '30 3 * * 6', $$ delete from events where event_time < now() - interval '1 week' $$);

Example: Call database stored procedure

select cron.schedule('call-db-procedure', '*/5 * * * *', 'CALL my_procedure()');

Query cron job run history

Query the cron.job_run_details table to inspect job runs. Example: select * from cron.job_run_details where jobid = (select jobid from cron.job where jobname = 'job-name') order by start_time desc limit 10;

Database migration pull command

Run `supabase db pull --db-url <db_connection_string>` to pull database changes locally. The database connection string uses the Session pooler connection string format: postgres://postgres.xxxx:password@xxxx.pooler.supabase.com:5432/postgres. This connection string can be found in your project dashboard under Connect > Session pooler.

IPv6 environment database connection

If you are in an IPv6 environment or have the IPv4 Add-On, you can use the direct connection string instead of Supavisor in Session mode.

Migrations automatic execution

The migrations in the migrations subdirectory of your Supabase directory are automatically run when branches are synced.

Migration execution order in branches

Migrations are run in sequential order. Each migration builds upon the previous one. The preview branch maintains a record of which migrations have been applied, and only applies new migrations for each commit.

Preview branch seeding behavior

Preview branches are seeded with sample data using the same behavior as local seeding. The database is only seeded once when the preview branch is created. To rerun seeding, delete the preview branch and recreate it by closing and reopening the pull request.

Migration file ordering

Migration files are applied in timestamp order. When multiple developers push migrations concurrently from different machines, this can cause conflicts.

Migration tracking table

Supabase tracks which migrations have been applied to each database in a table named `supabase_migrations.schema_migrations`. When running `supabase db push`, the CLI compares local migration files against this table and applies only the ones not yet applied, in timestamp order.

Example migration to add column

The following SQL adds a department column to an existing employees table with a default value: alter table if exists public.employees add department text default 'Hooli';

Pull remote state to local migration

Use `supabase db pull` to create a new migration file capturing the current remote database schema. This is useful when changes were made directly to the remote database to bring local migrations back in sync.

Example schema diff migration output

When running `supabase db diff -f create_cities_table` after creating a table in the Dashboard, the generated migration file contains the full CREATE TABLE statement: create table "public"."cities" ( "id" bigint primary key generated always as identity, "name" text, "population" bigint );

Lock timeout configuration for migrations

If a lock timeout error occurs during migrations, increase the `lock_timeout` setting in your migration file to allow more time for schema changes to complete.

Example migration to create employees table

The following SQL creates a simple employees table with id, name, email, and created_at columns: create table if not exists employees ( id bigint primary key generated always as identity, name text not null, email text, created_at timestamptz default now() );

Apply migrations locally

Use the CLI command `supabase migration up` to apply pending migrations to your local database.

Never modify remote database schema directly

Once you are using migrations, all schema changes to your remote database should only go through migration files. Making schema changes directly on the remote database via the SQL editor or Table Editor bypasses the migration history and causes `db push` to fail with sync errors.

Seed data configuration

Create a seed script at supabase/seed.sql containing SQL INSERT statements to populate tables with initial data. Running `supabase db reset` reapplies all migrations and executes the seed script.

Dashboard schema changes only on local database

Only use the Dashboard to make schema changes on your local database, then capture them with `supabase db diff`. Schema changes made directly on the remote database via the Dashboard bypass migration history.

Migration repair only updates tracking table

`supabase migration repair` updates only the migration history tracking table in `supabase_migrations.schema_migrations`. It does not apply or revert any SQL statements. Use it only to correct the history record when you know the actual database state is correct.

Mark migration as applied without running

Use `supabase migration repair --status applied <migration-timestamp>` to mark a migration as applied in the tracking table without re-running the SQL. This is useful when a migration's schema change is already in place but was applied manually, and only the history record needs to be corrected.

Generate schema diff from Dashboard changes

Use `supabase db diff -f <migration-name>` to automatically generate a migration file capturing changes made via the Dashboard on your local database. This allows you to use the Dashboard for schema changes locally, then convert those changes to migration files.

Database migrations overview

Database migrations are SQL statements that create, update, or delete existing database schemas. They are used to track changes to your database over time.

Team workflow for migrations

When multiple developers work on the same Supabase project, each developer should create migration files on their own branch locally, reset their local database to test changes, commit migration files to git, pull new migrations from teammates, and coordinate so only one person runs `db push` at a time to avoid conflicts.

Mark migration as reverted

Use `supabase migration repair --status reverted <migration-timestamp>` to mark a migration as reverted in the tracking table. This is useful when a migration is recorded as applied but was never actually run.

Reset local database

Use `supabase db reset` to reset your local database, reapplying all migrations in order and executing the seed.sql script if it exists.

Add database indexes for common query patterns

Ensure that you have suitable indexes to cater to common query patterns. The pg_stat_statements tool can help identify hot or slow queries.

Use Performance Advisor to check database performance

Check and review issues in your database using Performance Advisor in the Supabase Dashboard.

Enable replication on sensitive data with RLS and policies

Enable replication on tables containing sensitive data by enabling RLS and setting row security policies in the Database > Policies section of the Supabase Dashboard. Manage replication tables in the Database > Publications section of the dashboard.

Use Security Advisor to check database security

Check and review issues in your database using Security Advisor in the Supabase Dashboard.

Supabase uses Postgres, not NoSQL

Supabase is built on PostgreSQL as its core database rather than a NoSQL store. This choice was deliberate, as Postgres offers the functionality required to compete with Firebase while maintaining the scalability to go beyond it.

Supabase core architecture components

Each Supabase project consists of multiple services fronted by an Envoy API gateway: Postgres (database), Studio (dashboard), GoTrue (Auth), PostgREST (API), Realtime (WebSocket engine), Storage API (S3-compatible object storage), Deno (Edge Functions), postgres-meta (database management API), Supavisor (connection pooler), and Envoy (API gateway).

postgres-meta provides Postgres management API

postgres-meta is a RESTful API for managing Postgres databases, allowing users to fetch tables, add roles, and run queries. Source code is at github.com/supabase/postgres-meta, written in Node.js/TypeScript, and licensed under Apache 2.0.

Supavisor is a cloud-native Postgres connection pooler

Supavisor is a cloud-native, multi-tenant Postgres connection pooler. Source code is at github.com/supabase/supavisor, written in Elixir, and licensed under Apache 2.0.

Supabase design principle: everything works in isolation

Each Supabase system must work as a standalone tool with minimal moving parts. The test is whether a user can run the product with nothing but a Postgres database.

Give your agent this brain