Basically what the title says. I've only played two games like this so far (Silent Hill 2 and OMORI) and am looking for more.
Could be games, ROM hacks, mods of existing games, etc.
EDIT: So many great suggestions! Getting a bit overwhelmed by how many there are, so I'm going to need to find a way to put this in some kind of list with an order to play the games in. By all means, keep 'em coming, though!
It might not fit entirely in, but check out Spec Ops: The Line. It's more or less linear shooter, but the story (and ending) makes it quite unique and very different from any other game I've played. Could too get pretty emotional at times.
It's one of the few games I've played where I was speechless at the ending. Maybe not crying-level emotional, but still hit me like a truck. Another one would be probably Dear Esther, which was already mentioned here.
The last or second to last collectable is still haunting to me year later. The note from father to son.
There are a crazy amount of emotions in that game. Also absolutely not something children should play, it's a totally different level than your average M rated game.