My favorite would have to have been the better dungeons (A.K.A Chocolate Quest) mod. It added SO much to the game in terms of exploration and challenge and could keep me playing singleplayer for hours. I used to create my own custom modpacks and that mod was a staple in all of them. Sadly the creator stopped updating it so it is stuck in the past.
I never play singleplayer, but I did play it a few times back in Beta 1.6. I used the ''NOW modpack'' and my favorite mod there was definitely Millionaire. Advanced villages with NPCs that gave quests to expand the village and such.
My favorite mod of all time would have to be the betterdungeons mod, AKA chocolate quest. It was a mod that added huge amounts of dungeons into minecraft. It added pirate ships with actual pirates to vast underground citadels filled with enemies to giant castle towns. The dungeons were scaled in difficulty and the harder ones had bosses, which even with full diamond and end game gear were difficult to defeat. I often made my own custom modpacks and would use this mod as my centerpiece in all of them.
I used to have a compilation of a bunch of mods, one of them was really neat, basically you place a block down and an npc spawns out of the block and whatever it is(axe, pickaxe, hoe) It will do that for you, but you have to supply the tool.