A gobo is a thin, metal template that fits inside a lighting instrument. A gobo rotator, also called a twinspin, uses two gobos and then spins them to create the illusion of movement. They are used quite often to simulate rainfall or running water. A DMX cable sends a signal to control the speed. It may take a few attempts to properly position the gobos in the rotator to achieve your desired effect, but with some trial and error you'll have the desired mood.
Gels are the sheets of colored material used to change the color of the beam of light. By using blue or aquamarine gels, you can create a very watery look. Rosco, Lee, and GAM are the main suppliers of gel in the United States. They will provide a swatch book of colors for designers to test and determine what color makes the best look for their project.
A flickering light can effectively give the feel of a dank subterranean cave or stream. You can buy flickering lamps if you have small units, like a wall sconce. A program can be written on your light board to create a flicker effect. This is done by using auto-follow cues to create a loop to make this illusion as well.
Footlights or up lights, especially when covered with blue gel, can create the feeling that the actors on stage are surrounded by water. Since light naturally comes from above, it is disorienting to see it come from below. This can create very creative shadows and give an eerie and otherworldly feel.
The best water effects will consist of a combination of these and it will depend on the style of production you are doing. More dramatc scenes may use darker blues and harsher up light. Comedic ones may prefer a lighter blue with less flickering. Trial and error will reveal the best solution for your performances.