FWIW, here are a few things about automated testing that I’ve found to be true very generally.

Other things being equal, testing is good and automated testing is more valuable than manual testing, for the same reasons that having any systematic, repeatable process is generally more efficient and reliable than doing the same things manually and hoping to avoid human error. Many of the following notes are just reasons why other things aren’t always equal and sometimes you might prefer other priorities.

Automated test suites get diminishing returns beyond a certain point. Even having a few tests to make sure things aren’t completely broken when you make a change can be a great time-saver. On the other hand, writing lots of detailed tests for lots of edge cases takes a lot of time and only helps if you break those specific cases. For most projects, a middle ground will be better than an extreme. But remember that as a practical reality, an absurdly high proportion of bugs are introduced in those quick one-line changes in simple functions that couldn’t possibly go wrong, so sometimes testing even simple things in key parts of the code can be worthwhile.

Automated test suites have an opportunity cost. Time you spend writing and maintaining tests is time you’re not spending performing code reviews, or formally proving that a key algorithm does what you think it does, or conducting usability tests, or taking another pass over a requirements spec to make sure it’s self-consistent and really does describe what you want to build. These things can all help to develop better software, too.

Automated test suites do not have to be automated unit test suites. For example, higher-level functional or integration tests can be very useful, and for some projects may offer better value than trying to maintain a comprehensive unit test suite.

Unit tests tend to work best with pure code that has no side effects. As soon as you have any kind of external dependency, and you start talking about faking a database or file access or network stack or whatever other form of I/O, unit testing tends to become messy and much more expensive, and often you’re not even testing the same set-up that will run for real any more.

A corollary to the above is that separating code that deals with external interactions from code that deals with any serious internal logic is often a good idea. Different testing strategies might be best for the different parts of the system. (IME, this kind of separation of concerns is also helpful for many other reasons when you’re designing software, but those are off-topic here.)

Modifying your software design just to support unit tests can have serious consequences and can harm other valuable testing/quality activities. For example, mechanics that you introduce just to support unit testing might make language-level tools for encapsulation and modular design less effective, or might split up related code into different places so code reviews are more difficult or time-consuming.

Automated testing is there to make sure your code is working properly. It is not a substitute for having robust specifications, writing useful documentation, thinking about the design of your system, or, most importantly of all, understanding the problem you’re trying to solve and how you’re trying to solve it.

No-one really only writes code that is necessary to pass their tests. Even if they religiously adhere to writing tests first, at some point they generalise the underlying code because that’s what makes it useful, and the test suite didn’t drive that generalisation or verify that it was correct. In TDD terms, the tests only drive the red/green part, not the refactor part.

For similar reasons, just because someone has a test suite, that does not mean they can safely refactor at will without thinking. This may be the most dangerous illusion in all of TDD advocacy, but unfortunately it seems to be a widespread belief.

A lot of “evidence” cited in support of various test strategies and wider development processes is fundamentally flawed. Read critically, and be sceptical of conclusions that over-generalise.

And finally, what works for someone else’s project might not be a good choice for yours, and something that didn’t fit for someone else might still be useful for you. If you’re experimenting, it can be very informative just to keep even basic records of roughly how much time you’re really spending on different activities and what is really happening with interesting things like speed of adding new features or how many bugs are getting reported in released code. You’re allowed to change your mind and try a different approach if whatever you’re doing right now isn’t paying off.