Alright, first things first. You need to set up two separate Supabase projects. Think of each project as a different database. Don't forget to jot down the credentials for both databases. You'll need them later.

Next, head over to the settings for each project to set up external database connections. You can usually find this in the database settings section within the Supabase dashboard for each project.

Now, in your main Supabase project (the one where you'll be running your queries), you need to allow connections from the secondary database. Go to the database settings and tweak the pg_hba.conf file to include the IP addresses of the secondary database. This file controls which IPs can connect to your database.

Time to get a bit technical. Use the SQL editor in the Supabase dashboard for your main database to install the foreign data wrapper extension. Just run this command:

CREATE EXTENSION postgres_fdw;

Next, you need to define the foreign server that connects to your secondary database. Run this command:

CREATE SERVER secondary_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'SECONDARY_DATABASE_HOST', dbname 'SECONDARY_DB_NAME', port '5432');

Replace SECONDARY_DATABASE_HOST with the host of your secondary database and SECONDARY_DB_NAME with its name.

Now, map the user for the foreign server with the credentials of the secondary database. Use this command:

CREATE USER MAPPING FOR CURRENT_USER
SERVER secondary_server
OPTIONS (user 'SECONDARY_DB_USER', password 'SECONDARY_DB_PASSWORD');

Just swap out SECONDARY_DB_USER and SECONDARY_DB_PASSWORD with the right credentials.

Time to bring in the schema or specific tables from the secondary database. Run this:

IMPORT FOREIGN SCHEMA public
FROM SERVER secondary_server
INTO foreign_schema;

This will import all tables from the public schema of the secondary database into a new schema named foreign_schema in your main database.

Now you can query the imported tables just like they were local tables. For example:

SELECT * FROM foreign_schema.table_name;

Just make sure to specify the schema in your queries to access tables from the secondary database.

Remember, the foreign data wrapper setup doesn't automatically sync data between databases. You'll need to create custom scripts or use a task scheduling mechanism to handle data synchronization as needed.

Finally, make sure only authorized users can access the cross-database query functionality. Set the appropriate permissions in both the primary and secondary databases.

And there you have it! These steps will help you handle cross-database queries in Supabase effectively while keeping everything secure and connected.