database

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

Pull production schema with supabase db dump

To set up declarative schemas on an existing project, run supabase db dump > supabase/schemas/prod.sql to pull in your production schema. You can then break down the schema into smaller files and generate migrations incrementally as you make changes.

Custom schema order in config.toml

To specify a custom order for applying schemas, declare them explicitly in the db.migrations section of supabase/config.toml using the schema_paths option. Any glob patterns are evaluated, deduplicated, and sorted in lexicographic order. For example: [db.migrations] schema_paths = ["./schemas/employees.sql", "./schemas/*.sql"]

Schema files executed in lexicographic order by default

Schema files in supabase/schemas/ are run in lexicographic order by default. This order is important when you have foreign keys between tables, as the parent table must be created first.

Append new columns to end of table in declarative schema

When adding new columns to a table in declarative schema files, always append them to the end of the table definition. Some entities like views and enums require columns to be declared in a specific order, and appending to the end avoids messy diffs.

Rolling back migrations in preview branches

To roll back changes from a migration, push the latest changes, then delete the preview branch in Supabase and reopen it. The new preview branch is reseeded from the ./supabase/seed.sql file by default. Any additional data changes made on the old preview branch are lost. This is equivalent to running 'supabase db reset' locally. All migrations are rerun in sequential order.

Debugging failed migrations

When migrations fail, ensure your migration files contain valid SQL, check if migrations depend on objects that don't exist, and verify the migration doesn't require superuser privileges. Test migrations locally first using 'supabase db reset' and check migration logs in the dashboard by navigating to Branches > Your Branch > View Logs.

Seed data file location and loading issues

Ensure the seed.sql file is located in the ./supabase/ directory. Check for SQL syntax errors in the seed file and verify that seed data doesn't reference non-existent tables.

Migration timestamp and order requirements

Migrations must run in the correct order. Ensure migration files have unique timestamps, later migrations depending on earlier ones, and timestamps don't get out of order after Git rebase. Rename migration files to fix timestamp order, for example: 'mv 20240101000000_old.sql 20240102000000_old.sql'. Reset local database to test using 'supabase db reset'.

Apply migrations with db reset

Use 'supabase db reset' to reset the database to the current migrations. This applies all migration files and also runs the seed data from supabase/seed.sql if it exists.

Database migrations concept and workflow

Database migrations are a common way of tracking changes to your database over time. You can develop locally using the Supabase CLI to run a local Supabase stack, use the integrated Studio Dashboard to make changes, capture changes in schema migration files saved in version control, or write your own migrations and push them to the local database for testing.

Create migration file command

To create a new migration file, run 'supabase migration new <migration_name>'. This creates a new migration file at supabase/migrations/<timestamp>_<migration_name>.sql.

Migration file example: create table

Migration example to create an employees table: create table employees (id bigint primary key generated always as identity, name text, email text, created_at timestamptz default now());

Pull remote schema before pushing if remote has unpulled changes

If your remote database already has schema changes that aren't in your local migrations (for example, tables created directly in the Dashboard), run 'supabase db pull' to write those changes to a <timestamp>_remote_schema.sql migration file so your local and remote histories line up. Then run 'supabase db reset' to re-apply your migrations locally to confirm they're consistent. This step can be skipped for brand-new remote projects.

Database diff command to capture dashboard changes

Use 'supabase db diff --schema public' to view the SQL that corresponds to changes made in the Dashboard. This shows the SQL that will be run to create tables and columns. You can copy this SQL into a new migration file and run 'supabase db reset' to apply the changes.

Seed data script location and usage

Create a seed script at supabase/seed.sql to populate sample data. The seed file is automatically executed when running 'supabase db reset'. This allows you to reset to a known state with sample data at any time.

Seed data example

Example seed script in supabase/seed.sql: insert into public.employees (name) values ('Erlich Bachman'), ('Richard Hendricks'), ('Monica Hall');

Migration file example: alter table

Migration example to add a column to employees table: alter table if exists public.employees add department text default 'Hooli';

Sync specific schema with --schema option

Use 'supabase db pull --schema <schema_name>' to synchronize your database with a specific schema. If the local supabase/migrations directory is empty, the db pull command will ignore the --schema parameter. To fix this, run 'supabase db pull' first, then 'supabase db pull --schema <schema_name>'.

Set PostgreSQL sslmode to require for Laravel

Set sslmode to 'require' in the PostgreSQL configuration in app/config/database.php. By default, Laravel ships with sslmode set to 'prefer', which sends data in plaintext if the encrypted attempt fails. Setting it to 'require' ensures the connection fails rather than falling back to plaintext. This can be complemented by enforcing SSL on the database side.

Laravel schema configuration with search_path

Change the default Laravel schema from 'public' by modifying the search_path variable in app/config/database.php. Supabase exposes the public schema as a data API, so it is recommended to use a different schema. The schema must exist on Supabase and can be created from the Table Editor. Set search_path to a custom schema name like 'laravel'.

Laravel PostgreSQL configuration in database.php

The PostgreSQL configuration in app/config/database.php for the 'pgsql' driver includes: driver set to 'pgsql', url from DB_URL environment variable, host defaults to 127.0.0.1, port defaults to 5432, database defaults to 'laravel', username defaults to 'root', password empty by default, charset defaults to 'utf8', prefix and prefix_indexes set for index naming, search_path set to 'laravel' (or custom schema), and sslmode set to 'require'.

Laravel database connection configuration with Supabase

Configure the Postgres connection in .env file with: DB_CONNECTION=pgsql and DB_URL=postgres://postgres.[PROJECT-REF]:[YO••••••D]@aws-[REGION].pooler.supabase.com:5432/postgres. Use the Session Pooler connection string from the Supabase dashboard Connect section, replacing the password with your saved database password. If in an IPv6 environment or with the IPv4 Add-On, use the direct connection string instead of Supavisor in Session mode.

Run Laravel database migrations for authentication

Run php artisan migrate to execute database migration files that set up required tables for Laravel Authentication and User Management. Note that Laravel does not use Supabase Auth but implements its own authentication system.

Database connection URI format for Supabase projects

Postgres connection details follow this schema: postgresql://postgres:[DB••••••D]@db.[REF].supabase.co:5432/postgres. Note that database password cannot be retrieved via the Management API, so for existing projects you must collect it from the user.

Check and install extensions in Supabase

To check available extensions in Supabase, run `SELECT name, comment FROM pg_available_extensions ORDER BY name;`. Compare with source database extensions using `SELECT extname FROM pg_extension ORDER BY extname;`. Install needed extensions with `CREATE EXTENSION IF NOT EXISTS extension_name;`

Restore database with psql no encryption

To restore a database backup without Vault or column encryption, run: psql --single-transaction --variable ON_ERROR_STOP=1 --file roles.sql --file schema.sql --command 'SET session_replication_role = replica' --file data.sql --dbname [CONNECTION_STRING]. Replace [CONNECTION_STRING] with your new project's connection string.

Session pooler vs direct connection strings

For database connections, use the Session pooler connection string by default: postgresql://postgres.[PROJECT-REF]:[YO••••••D]@aws-0-us-east-1.pooler.supabase.com:5432/postgres. Use the direct connection string postgresql://postgres.[PROJECT-REF]:[YO••••••D]@db.[PROJECT-REF].supabase.com:5432/postgres if your network supports IPv6 or you have the IPv4 add-on enabled.

Preserve migration history in restored database

To preserve Supabase CLI migration history when restoring to a new project, use three commands: (1) supabase db dump --db-url "$OLD_DB_URL" -f history_schema.sql --schema supabase_migrations, (2) supabase db dump --db-url "$OLD_DB_URL" -f history_data.sql --use-copy --data-only --schema supabase_migrations, (3) psql --single-transaction --variable ON_ERROR_STOP=1 --file history_schema.sql --file history_data.sql --dbname "$NEW_DB_URL"

Copy root key with curl example

Example curl commands to copy pgsodium root key: ```bash export OLD_PROJECT_REF="<old_project_ref>" export NEW_PROJECT_REF="<new_project_ref>" export SUPABASE_ACCESS_TOKEN="<personal_access_token>" curl "https://api.supabase.com/v1/projects/$OLD_PROJECT_REF/pgsodium" \ -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" | curl "https://api.supabase.com/v1/projects/$NEW_PROJECT_REF/pgsodium" \ -H "Authorization: Bearer $SUPABASE_ACCESS_TOKEN" \ -X PUT --json @- ```

Copy encryption root key between projects

When restoring a database that uses Supabase Vault or pgsodium (column encryption), you must copy the root encryption key from the old project to the new project before or immediately after restoration. Use the API endpoint at https://api.supabase.com/v1/projects/{PROJECT_REF}/pgsodium with a Personal Access Token. First retrieve the key from the old project, then PUT it to the new project. The endpoint returns and expects a 64-character hex root key. Do this before pausing or deleting the old project, as the API only returns the key for active projects.

Restore auth and storage schema changes separately

If you modified the auth and storage schemas in your old project (such as adding triggers or Row Level Security policies), restore them separately. Use: supabase link --project-ref "$OLD_PROJECT_REF" followed by supabase db diff --linked --schema auth,storage > changes.sql to identify and save the changes, then apply them manually to the new project.

session_replication_role prevents double encryption

Setting session_replication_role to 'replica' during database restore disables triggers, which prevents columns from being encrypted twice during the migration process.

Custom login roles need manual password reset

Custom database roles created with the LOGIN attribute must have their passwords manually set in the new project after restoration. Use the SQL command: alter user "YOUR_USER" with password 'SOME_NEW_PASSWORD';

Fix supabase_admin permission errors during restore

If you encounter permission errors related to supabase_admin during restore, open schema.sql and comment out any lines containing: ALTER ... OWNER TO "supabase_admin"

Fix cli_login_postgres role grant error

If you encounter the error "ERROR: permission denied to grant role \"postgres\"" during restore, open roles.sql and comment out the line: GRANT "postgres" TO "cli_login_postgres" WITH INHERIT FALSE GRANTED BY "supabase_admin";

Resolve cli_login_postgres role issues after cloning

If restoring a cloned database fails with error "role \"postgres\" is a member of role \"cli_login_postgres\"", drop the custom cli_login_postgres role so the CLI can recreate it correctly: DROP ROLE IF EXISTS cli_login_postgres;

Wrong password error during restore

Error 'psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.216.29.125), port 5432 failed: error received from server in SCRAM exchange: Wrong password' may occur if the database password was recently reset. Wait a few minutes for the password reset to take effect and try again.

Database password reset latency

It can take a few minutes for the database password reset to take effect, especially if multiple password resets are done in succession.

GSSAPI negotiation error with psql

Error 'psql: error: connection to server at "aws-0-us-east-1.pooler.supabase.com" (44.216.29.125), port 5432 failed: received invalid response to GSSAPI negotiation' indicates you are using psql and Postgres version 15 or lower. Completely remove the Postgres installation and install the latest version to resolve this issue.

Connection string format for restoring backups

Session pooler connection string format: postgresql://postgres.[PROJECT-REF]:[YO••••••D]@aws-0-us-east-1.pooler.supabase.com:5432/postgres. Direct connection string format: postgresql://postgres.[PROJECT-REF]:[YO••••••D]@db.[PROJECT-REF].supabase.com:5432/postgres. Use the Session pooler connection string by default. If your ISP supports IPv6 or you have the IPv4 add-on enabled, use the direct connection string.

Check available Postgres extensions on self-hosted instance

To see which extensions are available on your self-hosted Postgres version, run: select * from pg_available_extensions;. This helps identify whether an extension that caused a restore failure is supported on your self-hosted Postgres version.

pgmq.metrics and pgmq.metrics_all retrieve queue statistics

pgmq provides two functions to retrieve queue metrics: **pgmq.metrics(queue_name text)** - Returns metrics for a specific queue. Function signature: pgmq.metrics(queue_name text) RETURNS TABLE(queue_name text, queue_length bigint, newest_msg_age_sec integer, oldest_msg_age_sec integer, total_messages bigint, scrape_time timestamp with time zone) Parameters: - queue_name (text) - the name of the queue Return columns: - queue_name (text) - the queue name - queue_length (bigint) - number of messages currently in the queue - newest_msg_age_sec (integer or null) - age of the newest message in seconds - oldest_msg_age_sec (integer or null) - age of the oldest message in seconds - total_messages (bigint) - total messages passed through the queue over all time - scrape_time (timestamp with time zone) - current timestamp **pgmq.metrics_all()** - Returns metrics for all existing queues. Function signature: pgmq.metrics_all() RETURNS TABLE(queue_name text, queue_length bigint, newest_msg_age_sec integer, oldest_msg_age_sec integer, total_messages bigint, scrape_time timestamp with time zone) Returns the same columns as metrics() but for every queue in the system.

Setting log_min_messages in Postgres

By default, the database's log_min_messages configuration is set to 'fatal' in docker-compose.yml to prevent redundant logs generated by Realtime. You can configure log_min_messages using any of the Postgres Severity Levels.

Give your agent this brain