I don’t use a pihole, but I have a pi with my favorite distro acting as server, and I use dnsmasq for what you mention. It allows to set the machine as the nameserver for all your machines (just use its IP in your router DNS conf, DHCP will automatically point connected machines to it), and then you can just edit /etc/hosts to add new names, and it will be picked up by the nameserver.

Note that dnsmasq itself does not resolve external names (eg when you want to connect on google.com), so it needs to be configured to relay those requests to an other nameserver. The easy way is to point it to your ISP nameservers or to public nameservers like those from Cloudflare and Google (I would really recommend against letting them know all domains you’re interested in), or you can go the slightly more difficult way as I did, and install an other nameserver (like bind9) that runs locally. Gladly, dnsmasq allowed to configure its relay nameserver to be on something else than port 53, which is quite rare in dns world. Of course, if you’re familiar with bind9, you could just declare new zones in it. I just find it (slightly 😂) more pleasant to work with /etc/hosts.