I’ve been using Supabase for my recent project, and it works great for all things related to PostgreSQL. However, I have an existing MongoDB database that stores certain non-relational data which I’d like to integrate with my Supabase-powered application.

What I’m Trying to Achieve:

Utilize Supabase for authentication and some relational data storage.
Continue using MongoDB for non-relational data.
Seamlessly integrate the two databases within my Next.js application.

What I Have So Far:

Supabase project set up with authentication and PostgreSQL.
MongoDB Atlas cluster up and running.
Basic Next.js project configured.

Specific Questions:

How can I connect to both Supabase and MongoDB within a Next.js application?
What are the best practices for managing data flow between a relational (Supabase/PostgreSQL) and a non-relational (MongoDB) database?
Are there any existing libraries or frameworks that facilitate this kind of integration?
Any potential pitfalls or performance issues I should be aware of when integrating these two databases?

// Supabase setup
import { createClient } from '@supabase/supabase-js';
const supabaseUrl = 'https://xyzcompany.supabase.co';
const supabaseAnonKey = 'public-anon-key';
const supabase = createClient(supabaseUrl, supabaseAnonKey);

// MongoDB setup
import { MongoClient } from 'mongodb';
const mongoUri = 'mongodb+srv://username:[email protected]/myDatabase?retryWrites=true&w=majority';
const client = new MongoClient(mongoUri, { useNewUrlParser: true, useUnifiedTopology: true });

export default async function handler(req, res) {
    try {
        await client.connect();
        console.log('Connected to MongoDB');

        // Example query from Supabase
        const { data, error } = await supabase
            .from('my_table')
            .select('*');

        if (error) {
            console.error('Supabase error:', error);
        } else {
            console.log('Supabase data:', data);
        }

        // Example query from MongoDB
        const database = client.db('myDatabase');
        const collection = database.collection('myCollection');
        const mongoData = await collection.find({}).toArray();
        console.log('MongoDB data:', mongoData);

        res.status(200).json({ supabaseData: data, mongoData: mongoData });
    } catch (err) {
        console.error('Error connecting to databases:', err);
        res.status(500).json({ error: 'Failed to connect to databases' });
    } finally {
        await client.close();
    }
}

My application involves handling user profiles, where relational data is stored in Supabase, and activity logs which are non-relational are stored in MongoDB.
I'm looking for efficient ways to query and aggregate data across both databases.

Any guidance, examples, or references to relevant documentation would be greatly appreciated!