Not really, it is said once or twice to young children as they are put into bed. It is supposed to be light-hearted and a little funny. However, it is very overused, old-fashioned and, although it is meant kindly, I would not advise using it.
The version I know: Night night, sleep tight, mind the bugs don't bite.
And it's absolutely infuriating to many people, despite not being meant to be. "Twee" is the best description of how it comes across. I'd say it's OK for use with small children who haven't heard it before, but agree that it's best avoided at all costs with adults.