The article doesn't seem to be claiming that unit testing is bad, rather that religious promotion of it is bad, and the benefits and drawbacks should be weighted up like any other decision.

Obviously SQLite has got a tremendous amount of value out of unit testing. But SQLite is quite abnormal in many respects. It's used in aviation, so the standards of testing are ridiculously high. It's also a library, and libraries tend to be very easy to test. It's also a long-term project with no deadline to speak of.