Picking the best container for a patio water garden depends what kind of pot would match your garden decor and what kind of plants and fish you want to add. The requirement that it has to be water tight is only partially true. A small leak means you will have to add water more often, which can be a good maintenance tool as it keeps the water fresh. Plastic liners can be added to wooden half-barrels and waterproof coatings can be painted or sprayed into other pots to make them hold water.
It doesn't have to look like a flower pot to be used as a water garden container. Old bath tubs, galvanized wash tubs, horse water troughs, kid swimming pools, old rubber boots, and other household items have all been turned into water gardens. Use your imagination.
If you want to use a terra cotta or ceramic flower pot that already has a drain hole, you will need to try to plug it with a rubber or cork stopper. Try coating the plug with silicone sealant to help fill the gaps and to create a good watertight seal.