I'm also surprised at the majority view. buying and selling is stressful and expensive enough without all the uncertainty we've had in the past year. And with a new baby on the way, just sounds like a recipe for major stress.
When we bought our house, there were many things about it that didn't fit what we wanted. It is mid terraced, has no front garden at all and is a tiny cottage (we are a large family) that has meant sharing bedrooms all round. But I, like you mentioned, immediately had a connection to it. And it ticked other boxes for us. It is close to our kids schools, good transport links, a short, picturesque walk in to town...
Sure, you can't make the garden bigger or sunnier, but there are many ways to add interest and beauty to even the smallest of gardens - we used to live in an apartment with a balcony and I was able to turn it into a lovely outdoor space with flowers and vegetables and seating.
look for climbing flowers, hang planters on the fence to grow trailing plants or vegetables, grow things in lovely pots. focus on plants that smell and feel nice, along with edibles - a sort of sensory garden approach - to make it fun for the kids Get a folding table and chair set that can be put aside when not in use. Hang up festoon lights and use rugs/floor cushions for decor. Maybe you could fit a swing in? A sand table could do double duty as an eating table maybe?
My youngest kids don't really use our garden much at all except for in the summer as we spend most of our time out at parks, in the woods etc. The older ones barely ever use it except to get out the back.
I suggest you give it some time, because you did mention many positives.