Implementing UPSERT/MERGE with Supabase

[Dima_Semensky]

I have pretty simple need - I have a source in a file with ID and VALUE fields and I have a target Supabase table with ID and VALUE

I would like to update the VALUE field of existing records based on the ID field and insert new records for new IDs.

I’ve tried LOOPS.. MERGE…

using n8n cloud

[solomon]

That feature is currently not available on the Supabase node.
But since Supabase uses Postgres, you can connect directly to the database using a Postgres node.

You are gonna have to create a Postgres credential to connect to your supabase database directly.

1. To do that click the connect button on the top of your Supabase dashboard:
   

2. Scroll down to “Transaction pooler” and click “view parameters”
   

   

3. Paste all that information into a new Postgres credential, in n8n
   

   

4. To find your database password, go back to Supabase in Project Settings > Database
   

   
   
   
   

5. Click “Reset Password” to be able to copy the password and paste that into the n8n credential.
   

   

6. Create a new Postres node in your workflow and select the credential. Now you have the “Upsert” option for you database
   

   

.

If my reply answers your question, please remember to mark it as a solution.

[Dima_Semensky]

This is great - thanks!
But what I need to lookup in a separate data source?

[solomon]

I don’t understand your challenge.

In your first post you asked about how to update a record in the database based on its ID.

And if the ID doesn’t exist you create a new record.

What I showed you is exactly how to do that.

[Dima_Semensky]

Sorry here is what I;m looking for:

Source 1 has ID and Value;

I want to lookup each record in Source 2 (any database) by ID and depending if it was found, update Source 3 or create a record in Source 3.

[solomon]

I see. What are the sources?

Each source will have a different way of searching them.
If they are also Supabase databases, you just need to create more credentials and use each credential in a separate node.

[Dima_Semensky]

It does not matter - my issue is that after I do a lookup - if the item is not found - the flow stops the execution.

[solomon]

I see. With limited information, I also have a limited capacity of helping you.

But there’s an option in all nodes that enables it to return items even when the result is empty:

You can enable that and then use an IF node to use the condition:

• IF empty, do this
• IF not empty, do that

Hope this helps.
If my reply answers your question, please remember to mark it as a solution.

[Bojan]

Just to add to what Solomon is saying, if you have your lookup1 going straight into lookup2 then even with the always output data you won’t get 1 output from lookup2 for each item in lookup1. Example: lookup1 has 5 items but none are in lookup2 then even with always output enabled lookup2 will have just 1 empty item as its output.

The simple fix for that is to add a for each loop between lookup1 and lookup2.

[Dima_Semensky]

here is my not working workflow:

hope this helps you help me:)

[Jasono]

hi, im local deploy supabase, so i could not find connect button and Transaction pooler; Do you have other ways to solve?

[solomon]

You should have your credentials in you config file or environment variables, then.
Direct connection to the Postgres database.

[Najib_El_Alam]

the trick is on you look up node, Always Output Data checked, by doing this, even if the row doesn’t exists will run all the loop. then in the if out of the loop, check if the id from database item exits…