Old '08 D40 owner here, wandering past 200k miles and still really happy with the truck, though the newer 3.8l and 9-speed make me a little jealous-- Part of the charm of the Frontier is it is simple, durable and ages well.
Really solid, old-school 4x4 system (though it engages on the fly up to 60mph), all the guts tuck up above the fully boxed frame, good sight-lines, power, and (as I'm so fond of saying) the payload is the same in lbs as kg (?!?). The breaks could be better and so could the turn radius.
The reliability of my particular truck has been stellar, which is good as the dealers near me have been lousy and I appreciate never having to take it there. A basic OBD scanner has let me replace the sensors that have aged-out and triggered the CEL, now I'm changing-out door seals and other rubber parts.
They are really capable off-road right out of the box with no mods, but tires and suspension upgrades will always make a difference. It isn't as well supported by the aftermarket as Tacomas, but there is so much less that needs fixing/modding, so that's understandable.
After more than a decade I noticed that my manual calls for 87 octane, and avoiding 85-86 actually gave me a slight improvement in mileage... but running the cheaper stuff did no harm. They like regular fluid changes and long drives on the beach (or ancient seabeds), and country road speed limits--it's still shaped like a brick and wind resistance is a real thing.
The D41 seems quieter, a little more powerful, slightly worse visibility and better mileage and lots of driver-assistance I couldn't care less for. Make sure you get the color you'll be happy to see for the next decade or so--