I have my private key in a Keepass database, and with a keypress Keepass(XC) loads it into my agent. After a time that you specify, it unloads it automatically.
I use a U2F key now, and that key I just have in ~/.ssh. It's useless without my U2F fob anyway. (Right?)
Yes, you are correct that it is useless without your physical Security Key.
Technically what's happening is that the valuable private key is in a sense in that file you're not worried about. But, you're safe not worrying about it, because it was encrypted by the Security Key, and only the Security Key knows how to decrypt it, so even though in one sense it's the private key and very important, because it's encrypted it's not a big deal if anybody learns it, as they couldn't possibly decrypt it. The contents of the file are given back to the Security Key when you use SSH to connect to (say) GitHub and in fact it will decrypt them to discover your private key, then use it, and then forget it again immediately, but it could (hypothetically) instead store a library of all credentials and use the random contents of the file to just look up the right credentials in the huge library. That would cost $$$ and Security Keys are (relatively) cheap.
It's OK to completely forget that technical description, the designers of Security Keys specifically intended that you needn't care how the magic is done, I have explained it only to reassure anyone puzzling how this could possibly work.
I use a U2F key now, and that key I just have in ~/.ssh. It's useless without my U2F fob anyway. (Right?)