How do people come to find these drugs then? If you don't have a theory about how something works, what would make you test whether it works?
Once actual chemistry broke open our ability to analyze that drug, related chemicals could be synthesized and studied, which led to biguanidines, of which metformin is one, which were tested in animals.
Many drugs are found this way by looking at chemicals related to ones we already know about, and then testing on animals and humans. Self testing of new drugs by researchers used to be very common.
How did folk medicine come to know the effects of guanidine? Dunno.
The same way we know a lot about other things in the chemistry domain - somebody tasted it, just to see what happens. "Mendeleyev's Dream" is a great book that contains many fascinating anecdotes of people going "let me just lick this, just to try". (Or, if you want to go more modern, the history of hallucinogens is pretty fun too)
Humans seem to have a strong tendency to stuff unknown things into their mouth, just because they can.
Summary: a chemist is interested in psychedelics so he starts creating candidate psychoactive substances, and he then tests them on himself and friends. He wrote a lovely book about his journey.
It's actually the other way around. They didn't have very serious theories about the etiology of depression back then, the only clue they had was from the pharmacology of these drugs, which led to the situation where for many years the leading theory on depression was that it was caused by low levels of monoamine neurotransmitters(serotonin, norepinephrine and dopamine). The main evidence for this initially was the fact that these drugs seemed to work, and that they increased the levels or effects of monoamines. This theory has since become discredited as we've learned more, although the connection between monoamines and depression is still strong. It's just not the whole picture.
You take a cell culture model for the disease and see if the compounds affect the cells.
Then try it in animals, then humans.
Repeat this process until you have good safe drugs for all of the diseases.