His 6-3 7-6 (9-7) win sealed the title, with Roberto Bautista Agut returning to the team three days after his father's death to put the hosts 1-0 up.
Bautista Agut was in tears after beating 19-year-old Felix Auger-Aliassime 7-6 (7-3) 6-3 in the opener.
Nadal's win gave Spain an unassailable 2-0 lead and a sixth Davis Cup title.
The world number one has won all eight of his rubbers this week to help Spain to a first title since 2011 and ensure local fans were celebrating at the end of the inaugural edition of the competition's revamped version.
"When you have this moment it is difficult to describe with words - so many feelings, so many emotions you have never felt," Spain captain Sergi Bruguera told Eurosport.
"Unbelievable. Imagine Roberto yesterday was in the funeral of his father, now he is here giving everything - the mentality, the spirit, giving everything for his team.
"Rafa, he is out of this world, I don't know if he is an alien or what. Not one day we went to sleep before 3am this week."
The new format has featured 18 teams playing in a week-long event in the Spanish capital, with a round-robin group stage followed by knockout rounds. Under the old system, teams played home and away ties through the year.
- Follow live coverage of Spain v Canada - radio & text
- Live scores, schedule and results
- Alerts: Get tennis news sent to your phone
'I could not be happier' - Nadal caps successful year with team joy
Rafael Nadal had not lost a Davis Cup singles match since 2004 and it was always going to be a tough task for Shapovalov to send the tie to a deciding doubles rubber.
Nadal, who this year won the French Open and US Open to take his total to 19 Grand Slam titles, dominated the first set before the 20-year-old Shapovalov got himself back into the match to force a second-set tie-break.
The youngster saved two match points with blistering back-to-back winners and fashioned a set point of his own, but finally succumbed when he netted a forehand.
Nadal dropped to the ground before being mobbed by his team-mates. When he finally got to his feet, he held aloft Bautista Agut in a tight embrace as the cheers rang out.
"An amazing week, a lot of things we went through - the father of Roberto passed away... a lot of things happened," Nadal said.
"I could not be happier. It has been an unforgettable moment in this amazing stadium; we can't thank the crowd enough. Our team spirit prevails."
Bautista Agut puts aside grief to deliver for Spain
While Nadal's victory settled the title, it was Bautista Agut's performance so soon after his bereavement that heightened the emotions.
Bautista Agut went home on Thursday after his father was taken ill. The Spanish federation later announced the player's dad had died.
But the world number nine returned to Madrid to watch his team-mates beat Great Britain in the semi-finals on Saturday before being recalled for the final.
The 31-year-old, whose mother died last year, pointed to the sky after claiming victory when Auger-Aliassime went wide on the first of three match points.
He hugged his captain Sergi Bruguera before leaping into the arms of his delighted team-mates in the stands.
"It was very special feeling on the court and I just could go out to try my best, give my best," he told Eurosport.
"I am very happy I could win the first point for Spain."
Auger-Aliassime was playing his first match at the revamped tournament this week after an ankle injury, with Canada reaching their first final by using just the same two players in all of their singles and doubles matches - Shapovalov and Vasek Pospisil.
Inaugural event ends on a high
While the new format - the brainchild of Barcelona footballer Gerard Pique - experienced some teething problems this week, the event drew to a close on a high, partly helped by home success.
There had been empty seats at some ties during the week but at Sunday's final there was a packed and noisy arena, where Spain's King Felipe VI was in attendance and singer Shakira - the partner of Pique - performed before the tie started.
Scheduling issues had meant there were some late-night/early-morning finishes but Nadal showed no sign of fatigue as he put in another convincing performance that underlined why he was named most valuable player of the tournament.
His Spanish team will pocket 2.1m euros (£1.8m) and can look forward to next year where Madrid will once again host the event.
Analysis
BBC tennis correspondent Russell Fuller
Rafael Nadal was Spain's standout player - producing eight wins out of eight at a stage of the year when his body usually rebels at the length of the season.
And Roberto Bautista Agut's presence, let alone victory, was awe-inspiring. As understated as ever, he played with poise and fortitude just three days after the death of his father.
Spain benefited from home advantage, but proved themselves the best team. They may boast the world number one, but all five members of their squad played at least one match.
Next year we hope to see more partisan fans, and fewer late finishes.
And, in future, wouldn't it be wonderful to see an expanded World Cup of tennis, featuring both men and women, and played at a more suitable time of the year?
Compromise and collaboration between tennis' varied vested interests is urgently required.