The long and the short of it was that Germany invaded the USSR in June 1941 without much provocation and with the expressed aim of wiping the Soviet state off the map for all intents and purposes. The details of the Third Reich's genocidal plans towards the regions' Slavs was not readily apparent at the time, German propaganda was unambiguous that the Soviet invasion was a war to eliminate "Judeo-Bolshevism" and there was no place for the Soviets in a German-dominated order.
As for Stalin's "good relations" with Hitler prior to June 1941, they were only good in the most qualified of senses. Although Stalin's long-term motives in the Molotov-Ribbentrop Pact are still unclear, there is much evidence to suggest that he did not see the Pact as the basis for a lasting peace with Hitler. Directives to Comintern and Soviet ambassadors in 1939/40 often stressed that supporting fascism in this was was a means to hasten the final collapse of the bourgeois economic order. Whether or not Stalin believed this line is unknown, but it does show he was deeply concerned about the optics of aligning with a self-declared ideological foe of communism. Likewise, Stain's overtures towards the Tripartite Pact were more likely part of a wider attempt to suss out Hitler's overall strategic intentions after the Fall of France. Stalin's instructions to Molotov in Autumn 1940 did not really authorize the Foreign Minister to make Soviet commitments to the Tripartite Pact and Molotov had very little leeway to propose Soviet conditions to joining. He relayed Ribbentrop's proposals for Soviet joining the Pact, but they became a dead letter in the Kremlin.
Whether or not Stalin was serious about the Tripartite Pact was a moot point since Hitler had already decided that summer to attack the USSR. The German dictator was far from alone in this decision as a number of German military chiefs were already thinking along those lines after the French campaign. Soviet economic assistance gave the USSR a lever over Germany's war effort that few German military and political chiefs relished. Moreover, the Molotov-Ribbentrop Pact itself did not eliminate the decades-worth of anticommunist thought within Germany's military elite. Hitler's political system also encouraged this anticommunism to be wedded to various racialist precepts as well as dreams of a massive Eastern empire. A number of the Wehrmacht's military chiefs had been involved in the last war's Ober-Ost command which likewise envisioned a vast Germanic empire carved out of the Russian sphere.
In short, Stalin had no real option but to be on the side of the Allies in World War II and the Western Allies had no need to refuse his partnership. They all shared a common enemy that attacked and declared war on them.