deployment

428 notes in this subject, read out of this brain and free to use. This is page 5 of 8.

Migrate from Amazon RDS to Supabase overview

Supabase provides a free and open source option that encompasses backend features including a Postgres database, authentication, instant APIs, edge functions, real-time subscriptions, and storage. Supabase's core is Postgres, enabling the use of row-level security and providing access to over 40 Postgres extensions.

Retrieve Supabase host credentials steps

To retrieve your Supabase host: (1) If you're new to Supabase, create a project and make a note of your password, which you can reset in database settings if forgotten, (2) On your project dashboard, click Connect, (3) Under the Session pooler, click on View parameters under the connect string, and note your Host (SUPABASE_HOST).

Retrieve Supabase Session pooler connection string

To get your Supabase project's connection string for database migration, navigate to your project dashboard and click Connect. Use the Session pooler connection string. You must replace [YOUR-PASSWORD] in the connection string with your actual database password. You can reset your database password on the Database Settings page if needed.

psql command to import database to Supabase

Use the following psql command to import the exported database to your Supabase project: psql -d "$YOUR_CONNECTION_STRING" -f heroku_dump.sql

pg_dump command to export Heroku database

Use the following pg_dump command to export your Heroku database to a file: pg_dump --clean --if-exists --quote-all-identifiers -h $HEROKU_HOST -U $HEROKU_USER -d $HEROKU_DATABASE --no-owner --no-privileges > heroku_dump.sql

Heroku database credentials to retrieve before migration

Before migrating from Heroku, retrieve and save these credentials from your Heroku Postgres database: Host (stored as $HEROKU_HOST), Database (stored as $HEROKU_DATABASE), User (stored as $HEROKU_USER), and Password (stored as $HEROKU_PASSWORD). Access these by logging into Heroku, selecting your project, clicking Resources, selecting your Heroku Postgres database, clicking Settings, and then clicking View Credentials.

Migrate Heroku Postgres to Supabase using pg_dump and psql

To migrate a Heroku Postgres database to Supabase, use pg_dump to export the database and psql to import it. The pg_dump and psql CLI tools are installed automatically as part of the complete Postgres installation package.

Heroku to Supabase migration tool alternative

As an alternative to manually using pg_dump and psql, you can use the Heroku to Supabase migration tool at https://migrate.supabase.com/ to migrate your database in a few clicks.

pgloader migration command

Run pgloader migration with the command: pgloader config.load

pgloader MSSQL migration configuration

To migrate from MSSQL using pgloader: install pgloader, create a configuration file (e.g., config.load) with the migration settings. Use your Supabase connection string as the destination with connection pooling enabled and Session mode. Set the configuration with: LOAD DATABASE FROM mssql://USER:PASSWORD@HOST/SOURCE_DB INTO postgres://postgres.xxxx:password@xxxx.pooler.supabase.com:5432/postgres; ALTER SCHEMA 'public' OWNER TO 'postgres'; set wal_buffers = '64MB', max_wal_senders = 0, statement_timeout = 0, work_mem to '2GB';

MSSQL database credentials needed for migration

Before migrating, collect the following details from your MSSQL database provider: hostname or IP address, database name, username, and password.

Custom hook example: add numeric key

This custom hook adds a unique numeric key to each record: module.exports = (collectionName, doc, recordCounters, writeRecord) => { doc.unique_key = recordCounters[collectionName] + 1; return doc }

writeRecord function for flattening Firestore data

Use the writeRecord function within a custom hook to write data to separate JSON files for flattening nested Firestore documents into related SQL tables. writeRecord takes three parameters: name (name of the JSON file to write to), doc (the document to write), and recordCounters (the recordCounters object passed to the hook).

Custom hook file structure for Firestore migration

Create a .js file with the same name as your Firestore collection (e.g., users.js) to define custom hooks. The basic format is: module.exports = (collectionName, doc, recordCounters, writeRecord) => { // modify the doc here; return doc }. Parameters are: collectionName (name of collection being processed), doc (current JSON object), recordCounters (object tracking records processed per collection), writeRecord (function to write data to other JSON files).

Import JSON file to Supabase command

Run 'node json2supabase.js <path_to_json_file> [<primary_key_strategy>] [<primary_key_name>]' to import a JSON file to Supabase Postgres. path_to_json_file is the full path to the JSON file created by firestore2json.js. primary_key_strategy (optional) is one of: none (default, no primary key), smallserial (autoincrementing 2-byte integer), serial (autoincrementing 4-byte integer), bigserial (autoincrementing 8-byte integer), uuid (randomly generated UUID with gen_random_uuid()), or firestore_id (uses existing firestore_id text as key). primary_key_name (optional) is the name of the primary key column, defaults to 'id'.

Dump Firestore collection to JSON command

Run 'node firestore2json.js <collectionName> [<batchSize>] [<limit>]' to dump a Firestore collection to JSON file. batchSize is optional and defaults to 1000. limit is optional and defaults to 0 (no limit). The output filename is <collectionName>.json.

List Firestore collections command

Run 'node collections.js' to list all Firestore collections during migration.

Firebase private key generation for migration

To generate a Firebase private key: log in to Firebase Console, open your project, click the gear icon next to Project Overview and select Project Settings, click Service Accounts and select Firebase Admin SDK, click Generate new private key, and rename the downloaded file to firebase-service.json.

Firestore to Supabase data migration tool

Supabase provides tools to convert data from a Firebase Firestore database to a Supabase Postgres database. The process copies the entire contents of a single Firestore collection to a single Postgres table. The Firestore collection is 'flattened' and converted to a table with basic columns of one of the following types: text, numeric, boolean, or jsonb. If the structure is more complex, you can write a program to split the newly-created JSON file into multiple related tables before importing to Supabase.

Custom hook example: add timestamp

This custom hook adds a timestamp of when the record was dumped from Firestore: module.exports = (collectionName, doc, recordCounters, writeRecord) => { doc.dump_time = new Date().toISOString(); return doc }

Custom hook example: flatten nested arrays

This example flattens a Firestore collection with nested arrays into separate related tables. For a users collection with a weapons array, the users.js hook file iterates through doc.weapons, writes each weapon as a separate record to a 'weapons' file using writeRecord, then deletes the weapons array from the original doc before returning it. This creates two separate JSON files: users.json (with uid, name, score) and weapons.json (with uid, weapon pairs for each weapon).

firebase-to-supabase repository setup

Clone the firebase-to-supabase repository from https://github.com/supabase-community/firebase-to-supabase.git to begin the migration process.

Supabase database connection configuration file

In the /firestore directory, create a file named supabase-service.json with the following JSON structure: host (database server hostname), password (Postgres password), user (Postgres user), database (database name), port (connection port, typically 5432). Replace Host and User values from the Session pooler connection parameters shown in the project dashboard Connect view.

Migrate using Supabase Colab notebook steps

To migrate using Google Colab: (1) Select the database engine from the source database dropdown. (2) Set environment variables in the notebook: HOST, USER, SOURCE_DB, PASSWORD, SUPABASE_URL, and SUPABASE_PASSWORD. (3) Run the first two steps in order (first step sets engine and installs necessary files). (4) Run the third step to start migration (takes a few minutes).

Enterprise migration support

For organizations needing additional help migrating projects to Supabase, contact the enterprise support team at https://forms.supabase.com/enterprise.

Supabase connection pooling configuration for pgloader

When using pgloader for MySQL migration to Supabase, enable connection pooling in the destination connection string with the mode set to Session. The connection string can be obtained from Database Settings in the Supabase dashboard.

Migrate from MySQL with pgloader configuration

To migrate MySQL with pgloader: (1) Install pgloader. (2) Create a configuration file (e.g., config.load) with: source database connection string as mysql://user:password@host/source_db, destination as postgres://postgres.xxxx:password@xxxx.pooler.supabase.com:5432/postgres (with connection pooling enabled and mode set to Session), alter schema owner to postgres, and set parameters: wal_buffers='64MB', max_wal_senders=0, statement_timeout=0, work_mem='2GB'. (3) Run migration with: pgloader config.load

Supabase host retrieval for migration

To retrieve your Supabase host for migration: (1) Create a new Supabase project and save your password, or reset it in dashboard/project/_/database/settings if forgotten. (2) Click Connect on the project dashboard. (3) Under Session pooler, click 'View parameters under the connect string' and note your Host ($SUPABASE_HOST).

MySQL database credentials to collect before migration

Before migrating to Supabase, collect the following details from your MySQL database provider: hostname or IP address, database name, username, and password.

Migrate Render database using CLI tools with pg_dump and psql

Export your Render database using pg_dump with the command: pg_dump --clean --if-exists --quote-all-identifiers -h $RENDER_HOST -U $RENDER_USER -d $RENDER_DATABASE --no-owner --no-privileges > render_dump.sql. Then import to Supabase using psql with: psql -d "$YOUR_CONNECTION_STRING" -f render_dump.sql

Retrieve Render database credentials process

Log in to your Render account and select the project to migrate. Click Dashboard in the menu and click your Postgres database. Scroll down in the Info tab. Click on PSQL Command and edit it to get the connection command which includes the password, host, user, and database name.

Retrieve Supabase connection string process

Create a new Supabase project and save the password. On the project dashboard, click Connect. Under Session pooler, copy the connection string and replace the password placeholder with your database password. If in an IPv6 environment or with the IPv4 Add-On, you can use the direct connection string instead of Supavisor in Session mode.

Migrate Render database using Google Colab

Set environment variables (PSQL_COMMAND, SUPABASE_HOST, SUPABASE_PASSWORD) in the Colab notebook. Run the first two steps in order: the first sets the variables and the second installs PSQL and the migration script. Run the third step to start the migration, which takes a few minutes.

Backup and Restore using CLI guide

To migrate from one Supabase project to another using SQL backup files with *.sql format, follow the Backup and Restore using the CLI guide.

Project transfer for organization changes

Project transfer allows you to move your project to a different organization without touching the infrastructure. This is different from project migration which is used for changing regions or upgrading to new major versions.

Restore to another project for Paid Plan backups

If you are on a Paid Plan and have physical backups enabled, use the Restore to another project feature instead of other migration methods.

Database migration methods in Supabase

There are three ways to migrate between Supabase projects: using a backup file from the dashboard (*.backup format), using SQL backup files (*.sql format), or using project transfer for moving to a different organization without infrastructure changes.

Restore dashboard backup guide

To migrate from one Supabase project to another using a backup file created in the dashboard with *.backup format, follow the Restore dashboard backup guide.

Specific regions available as alternative to general regions

You can choose an exact AWS region for your Supabase project if you prefer, as an alternative to using general region groupings. This provides more precise control over data location.

Region choice affects primary data storage location

The region you choose for a Supabase project determines where your primary project data is stored. If your data residency requirements call for data to stay within a specific jurisdiction, you should choose a specific region rather than a general region grouping, as general regions deploy to an available AWS region within that broader area which may not match a specific jurisdiction.

Each Supabase project deploys to one primary region

Each Supabase project is deployed to one primary region. The best practice is to choose the location closest to your users for the best performance.

General regions may not match specific jurisdictions

General regions deploy to an available AWS region within that broader area, which may not match a specific jurisdiction. For example, the Europe general region includes London and Zurich, which are not EU member states. Region selection is a data-location control, not proof of regulatory compliance.

General regions not yet supported for read replicas or API management

General regions are not yet supported for read replicas or management via the API. Only specific regions support these features.

Expose pgmq_public schema for Queues in local Supabase CLI

When running Supabase locally with Supabase CLI, update your project's config.toml file. Locate the [api] section and add pgmq_public to the list of schemas. The schemas array should include at minimum: public, graphql_public, and pgmq_public. Then restart the local Supabase stack using: supabase stop && supabase start

Expose pgmq_public schema for Queues in Docker Compose

When running self-hosted Supabase with Docker Compose, locate the PGRST_DB_SCHEMAS variable inside your .env file and add pgmq_public to it. The variable should be set to: PGRST_DB_SCHEMAS=public,graphql_public,pgmq_public. This environment variable is passed to the rest service inside docker-compose.yml. Restart containers with: docker compose down && docker compose up -d

pgmq_public schema prerequisite for exposing Queues

Before exposing the pgmq_public schema through the API, the pgmq_public schema must first exist. This schema is created by completing the step 'Expose queues to client-side consumers' from the Queues Quickstart guide. Only then can it be added to the list of exposed schemas.

Default exposed schemas in local and self-hosted Supabase

By default, local and self-hosted Supabase instances expose only core schemas: public and graphql_public. The pgmq_public schema is not exposed by default and must be manually added to the configuration.

Disabling Queues exposure

To stop exposing the pgmq_public schema, remove it from your configuration. For Supabase CLI, remove pgmq_public from the [api] schemas list in config.toml. For Docker Compose, remove pgmq_public from the PGRST_DB_SCHEMAS variable in your .env file. Then restart your containers for the changes to take effect.

Manual exposure requirement for Queues

Manual exposure of the pgmq_public schema is only required when running Supabase locally with the Supabase CLI or self-hosting using Docker Compose. Other deployment methods may have different requirements.

Production readiness resources

Supabase provides a production checklist guide for preparing an app for production, SOC 2 compliance documentation, and HIPAA compliance documentation that outline roles and responsibilities for building a secure and compliant app.

Restart containers after email template configuration

After configuring custom email templates in docker-compose.yml, restart the containers using the command: sh run.sh recreate auth templates-server

Custom email templates in self-hosted Supabase

When running a self-hosted Supabase instance, you can fully customize emails sent by Supabase Auth. Supabase Auth expects each template to be available at a URL that returns a valid HTML template. The URL does not need to be public but must be reachable from the auth service and must return a valid Golang HTML template.

Email template service requirements

To provide templates to Supabase Auth, you need a service that serves static HTML files. This can be any server of your choice. The only requirement is that the auth service must be able to reach it via an HTTP GET request.

Custom email template fallback behavior

If Supabase Auth cannot fetch the template or if the fetched template is invalid, it falls back to the default template.

Setting up custom email templates with Caddy in docker-compose

To set up custom email templates in a self-hosted Supabase instance using Caddy, create a templates directory inside volumes (volumes/templates/), add your HTML template files to it, and update the docker-compose.yml file. The auth service should depend on a templates-server service with the configuration: services: auth: depends_on: db: condition: service_healthy templates-server: condition: service_started environment: GOTRUE_MAILER_TEMPLATES_INVITE: 'http://templates-server/invite.html' GOTRUE_MAILER_SUBJECTS_INVITE: 'You have been invited' templates-server: image: caddy:2-alpine command: ['caddy', 'file-server', '-r', '/templates', '--listen', ':80'] volumes: - ./volumes/templates:/templates The templates-server service runs on the same docker network, keeps templates private to the Docker network (no published ports), and allows the auth service to fetch templates via http://templates-server/<template>.html.

Restart Supabase after configuration changes

After updating the docker-compose.yml configuration to use the postgres role, restart Supabase by running `sh run.sh recreate` from the project directory.

Remove superuser access from self-hosted Supabase

In late 2022, Supabase removed superuser access from the dashboard SQL editor and shifted ownership of user-created database objects away from supabase_admin toward the postgres role. This security change was automatically applied to hosted projects but not to self-hosted instances. Self-hosted instances may still have objects owned by supabase_admin, exhibit different behavior from the Supabase platform, and experience failed migrations when run as postgres.

Reassign database object ownership in self-hosted Supabase

To reassign ownership of database objects in the public schema from supabase_admin to postgres, run the script `sh utils/reassign-owner.sh` from the project directory containing docker-compose.yml. This script only updates objects in the public schema; Supabase-managed and custom schemas are not affected.

Configure Studio to use postgres role in docker-compose.yml

In the docker-compose.yml configuration, uncomment the line POSTGRES_USER_READ_WRITE: postgres under the studio service environment variables to use the postgres role for read/write operations.

Update PG_META_DB_USER in docker-compose.yml meta service

In the meta service environment variables in docker-compose.yml, change the PG_META_DB_USER environment variable from supabase_admin to postgres. This change is needed for backward compatibility and consistency, as Studio uses its own credentials to access Postgres via postgres-meta.

Give your agent this brain