This has endless potential. The most obvious effect would be happy thoughts from the drugs, and occasionally, in the case of hallucinogenic drugs, an unhappy thought.
UristMcAcidDropper has been unhappy lately. He had a pretty decent drink lately. He slept on rocks lately. He was frightened by hallucinations lately.
As to how hallucinations work, I think it'd be easy enough to modify a dorf's perception by altering what objects and creatures they think they see. For example, they might get the notion that soap looks kind of like food and it would be marked as such, they would path to it and attempt to eat it. They might try to walk through walls if it's bad enough and fail to notice certain hazards like water/magma/cliffs. One of your elite wrestlers might think one of your dorfs looks sorta like a goblin for some reason and tear it apart, or the opposite could happen by someone getting frightened by something that's not there, or is something other than what they think it is.
But that could get annoying fast. Let's assume that doesn't commonly happen. I'd love this to be a type of "insanity," some dwarves, instead of going melancholy would consume a bunch of their favorite drug, and would keep doing so until they're in a drug induced stupor/frenzy/sleep, eventually dying from an OD.
There could also be an illegal, highly addictive and life destroying drug. I can imagine 1 in every 30 immigrants bringing it in for personal use, and trading it in secret once the economy kicks in. Dwarves addicted from the stuff would suffer all of the real life problems: Though they'd get considerably happy thoughts when using it, they'd require more of it to stay happy once addicted and would get very unhappy thoughts from withdrawals. Dwarves caught selling the drug would get capital punishment.