[little pet]

As a former nurse, I've given countless enema's. Of course, everything was aimed at minimizing embarrassment and making the experience as comfortable possible for the patients.
So, if you use regular over the counter products and follow their advice, it's not going to be very painful. There will be discomfort, because of the cramping.
I don't have experience with them in a kinky setting, but it makes sense that adding certain ingredients, in a different position might make it a very different, potentially painful experience.

I'd urge you to read up on the proper way of administering an enema first and the risks involved. If done without caution, you could cause damage to the intestines.