There's only two type of spell systems I like. Either bloated ones or incredibly generic ones.
I like having uselss spells. Or rather, not useless, just sort of niche spells that have a very specific use, often revolving around a niche character build. One that you had to put a lot of effort into aquiring items for or taking the power-hit through half the game until it all came together, just so you have one of those cool, "rare", characters, amongst thousands of carbon copy cookie-cuttered trash.
I also like spells that sort of round out a game world. It's up to the devs to make them useful somehow. Not everything has to be game winning. Oh good, I can call dogs and make them my friends. Not big dogs, poodles. Happy nice ones. Cue the fluffy poodle army of doom! Regardless of how bad it is in gameplay. It might not be your main character, but you'll get a laugh out of it.
On the generic side, I love games where there's no set spells, you just construct your own. Legend and Worlds of Legend, Son of the Empire are my favourite examples of this. Buy runes, buy ingredients, then make whatever you please out of them. Some were there to get past specific "puzzles", but most were combat use. Fair enough, some of most used spells were "Missile, death, death, death, death, death", but it did give you the option to do whatever you wanted. The moment you could emulate a dragon potion, which understandably went out of stock pretty early on, made you feel like butchering all those armies was worthwhile.
I don't know why more games don't use something like this. More runes, more creativity and more lore behind it, but yeah, Legend had the best spell system ever. I'm also hoping someone does a "Syndicate" style spell system, with slider-boosters that do run out, but recharge. Pew-pew or big nuke? Wild-firing or accurate? It just depends on what you want the generic spell to do. But this isn't a suggestion thread, so.....
Specific bloat or customizable generality are the only good ways of doing spells in my opinion