While more can always be written, you may be interested in this previous answer of mine to the question: "How did colonies such as New York grow so large while others like Salem and Plymouth are still small communities?"

The TL;DR version is: geography. Salem and other small communities in the early Plymouth and Massachusetts Bay colonies didn't become major seaports because Boston did instead. And Boston did instead because of the geographical advantages of its harbor, lying in deep water at the mouth of the much more extensive Charles River system, versus the Crane River which Salem is adjacent to, or the Town Brook which Plymouth is adjacent to.

You may also be interested in this other answer of mine that goes more in depth into why New York developed like it did. Again, the geographic advantages of the Hudson River system played a, and perhaps the, major part in its early development.

I cannot say for sure why New London did not develop, though it stands to reason the geographic disadvantages of the relatively short Thames River system is a cause - Boston Harbor to the east and New York Harbor to the west are much more advantageous to seafaring traffic. But maybe someone else can provide a more dedicated answer for that part of your question.

And from that perspective, then, why didn't relatively long river systems in New England like the Connecticut River or the Merrimack River foster major seaports like the Hudson River and the Charles River did?

The book The Connecticut River from the Air: An Intimate Perspective of New England’s Historic Waterway by Jerry Roberts (Rowman & Littlefield, 2018) explains that the Connecticut River and its harbor are too shallow for many ocean-going vessels:

The Connecticut River is unique in that it is one of the few waterways in the developed world without a major industrial city at its mouth....The reason for this anamoly among rivers [in the region] is the great Saybrook Bar, a massive sand barrier that until the establishment of a dredged channel in the mid-1800s prevented all but relatively small coastal veseels from passing upriver. There was no deep-water anchorage or natural harbor where ocean-going ships could encourage the evolution of a major port city....

Although the mouth is a mile wide and apparently easily navigable by any size vessel, there is only about eight to ten feet of water at low tide in most places.

The book Capitalism, Politics, and Railroads in Jacksonian New England by Michael J. Connolly (University of Missouri Press, 2003) explains the relative lack of growth along the Merrimack River system along similar lines. The Newburyport Harbor at the mouth of the river is too shallow:

At a disadvantage with Boston, New York, and the other deeper American ports, Newburyport harbor was also too shallow to accommodate the larger clippers and frigates. More "piers and beacons" were built with state and federal help after the [War of 1812] to make the harbor safer, but "foreign vessels were not thus to be baited to [the] warves." In 1829, Congress appropriated $30,000 to build a breakwater outside the harbor to alter silt and sediment and deepen the passage. However, the nineteen-hundred-foot structure failed completely and collapsed into the ocean during an 1851 storm. Most importantly, the building of the Middlesex Canal between Pawtucket Falls (soon to be Lowell) and Boston in 1803 diverted the "country trade" along the Merrimack River valley southward and left Newburyport isolated at the river's mouth.

BONUS:

/u/uncovered-history has written an answer to the question: "Why did Jamestown never become a major settlement?"

And both /u/MrDowntown and /u/amp1212 have answered the question: "Why is there no large settlement at the junction of the Mississippi and Ohio Rivers in the US?"

In all of these answers, geography explains a lot of why early U.S. cities developed the way they did.