I am selling my house, which is connected to the mains drainage. My notary is asking me to have a collective sanitation diagnosis carried out. Is it mandatory?

It depends on where your property is located.
The diagnosis is legally mandatory for non-collective sanitation facilities (art. L.1311-11-1 Public Health Code).

However, the municipalities can impose a control of the quality of the works necessary to bring the waste water to the public part of the connection and of their proper functioning order (Rep. Min. of March 17, 2015).