Lot of cool history and good food in both places, just stay out of the shitty areas.
Downtown Providence and the east side of Providence are mostly where you should stick to there, south Providence is a pit. Federal Hill is a super Italian area with great Italian food, south of Federal Hill is the south Providence area though. Bristol and Barrington are nearby and pretty nice areas, can't say whether I've been to Warren or not. From Bristol you can get to the Portsmouth/Middletown/Newport island and from there Jamestown. Johnston, Pawtucket, Central Falls and North Providence are all pretty shitty, stay away from there (some nice historical parts of all, some decent spots, but by and large they are shitty and depressing). Cranston is very close by and most of it is pretty nice, western Cranston (west of the highway) is very suburban, easy to shop at, and has some nice wooded areas (wooded suburban not rural). Pretty Italian, but not guido or stereotype Italian, but it's not a dominant ethnicity. Parts of east Cranston outside of the Pawtuxet area (shared with Warwick, also largely the setting of the Lovecraft story The Case of Charles Dexter Ward) can be pretty shitty, even near the Roger Williams Park Zoo (right near the Cranston/Providence border there's an area that has these amazing large old historical houses filled with opioid abusers that's nicknamed "heroin highway"; how they let a nice area get that bad I'll never know, but I had to work there before, salt of the earth junkies though. Warwick is also pretty close by (south to Cranston) and about as suburban as you can get (also the dividing line between the more Italian Providence area with the more Irish most of the rest of Rhode Island, but no one ethnicity really dominates anywhere there), was developed a bit too much in a few areas (usually near the airport and Jefferson Blvd., there's also Route 2/Quaker Lane which is lined with shops, but doesn't look industrial or anything, just developed where lots of trees used to be, but right behind the shops usually have a bunch of trees), but you'll rarely have to travel to shop. Also has the only real airport in the state. No shitty areas or anything, just some a bit overdeveloped and so missing the natural look it used to have, but those spots never look urban or anything like that. Funnily enough reading some H.P. Lovecraft stories will give you great descriptions of Providence. Those are pretty much the areas to avoid or live in and around Providence from what I can recall.
I mostly worked in the shitty areas of Boston, so I can't give you advice where the best areas are there, I mostly go there as needed. Despite being a small area with a compact population most New Englanders (at least Southern New England) find traveling anywhere over 10-15 miles a bit inconvenient (traffic combined with population density doesn't help), myself included, so I never went out of the way to get acquainted with the Boston area that's multiple times over 15 miles away. Best bet would be living outside the area but not too far outside to be inconvenient. North Eastern Mass. has some really nice quaint old areas, the type that they always show in films to represent New England, not sure how expensive they are though.
If you had to move to Connecticut, stick to the eastern half, low population areas, or northwest mostly. Unfortunately urban migration from NY/NJ from the 70's and on really messed with parts of Connecticut from Hartford and west to it. People wonder what happens to the former populations of now gentrified areas, well those parts of Connecticut tell you what happens to them. They didn't exactly learn their lessons.