Can a malware that infected your local computer compromise a KeePass database stored locally in any way?
If yes, what's the point for KeePass to have such strong security mechanisms if it cannot resist to this scenario? If you keep your DB locally, isn't this scenario the main threat that exists?
Yes - if the malware included a keylogger, for example, it could simply read the master password when you typed it in. The only real way to prevent that would be to have KeePass be the only software running, on a verified secure computer (e.g. no hardware keyloggers, no VMs, etc.) which would somewhat defeat the purpose of having passwords to hand.
However, the same malware would also be able to read the master password for a cloud based database, when you opened it. It's not specific to local database files. In fact, it could take a less invasive piece of malware to do that - if you've installed a dodgy browser extension and view your passwords through a web site, it would be able to see what you typed in the browser, but (probably) not what you typed into any other program.
If you're using a system which has malware on, assume it can do anything you can do - if you can read a file, so can the malware. If you can contact a specific server, so can the malware. If you can disable your AV software, so can the malware. It might need to get details off you in order to do it (e.g. passwords), but the key is to avoid getting it on the system, rather than trying to ensure that every single piece of software detects the presence of it.
When your computer is compromised, pretty much any defense on it should be considered broken. Security threat models consider this as an out-of-scope scenario - barring very few exceptions (e.g., the very purpose of your software is to be the last-line of defense).
That said, password managers (like KeePass) that store the password database locally do resist a compromise to some extent - by encrypting the database using strong measures. If you configure it as intended (e.g., use a strong master password + a keyfile that you don't store locally) it will resist bruteforce and dictionary attacks. It will even resist capture of passwords from clipboard if you only use the auto-type feature. The clipboard exposure can also be limited to single use and within just a few seconds.
However, it cannot resist keyloggers because that is an environmental problem outside KeePass. The environment is given to KeePass - not something it can control. It is possible for a keylogger to capture the master password as you type it and call home to report it - along with the KDB / KDBX file and any associated key file.
I wouldn't expect every software to resist every possible attack (somewhat like looking for a panacea). I would consider password managers - especially KeePass / KeePassX as very good for what they do.
It's worth mentioning that there are settings inside KeePass to help restrict what malware (and you) can do. This lets you, for instance, prevent malware from simply exporting your entire database by injecting a few keystrokes.
It's not a failsafe, but it gives you more of a buffer between when you get infected and when you actually realize you've been infected.
(Screenshot is from KeePass 2.36)
Can a malware that infected your local computer compromise a KeePass database stored locally by any way?
If you don't open it, they can only snatch the database file and try to brute-force the password (not easy) – unless, of course, they manage to obtain the master password in some other way.
If you do open the file, they can try to obtain the master password when you are typing it in to unlock the database. There's been at least one piece of malware that did just that.
Furthermore, it is possible for malware to inject itself into the KeePass process and extract the passwords stored in the database. A proof-of-concept piece of malware that does this was released in 2015.
If yes, what's the point for KeePass to have so strong security mechanisms if it cannot resist to this scenario?
Well, defending against all other possible scenario's in which your passwords can become compromised of course! Because, what's the alternative to a password manager? Writing down passwords in a notebook? Re-using the same password for multiple accounts because you can't remember hundreds of passwords? Coming up with some 'clever' password creation scheme that results in different, but entirely predictable passwords for every account?
Yes, it's theoretically possible to gain access to your password database in these ways, but because of the protections KeePass offers, it's only possible to compromise your passwords in these ways. (Okay, there's another way.)
But there are plenty of ways your passwords won't be compromised, such as:
If you keep your DB in local, isn't this scenario the main threat that exists?
No. I'm positive that, of the scenario's above, there's multiple that are far more likely to occur than malware going specifically after KeePass. Risk #1 is probably re-using a password across websites and that site getting compromised. (For example, there are currently 3,805,757,030 breached accounts listed in Have I been pwned?.)
Although, as KeePass' developer agrees, there's ultimately no way to stop against attacks specifically targeting KeePass, it is certainly possible to raise the bar for an attacker. For example, there's a feature to defend against keyloggers obtaining the master password when unlocking the database, a feature for locking your password database when you're not using it et cetera.
It's worth investigating if you want to enable some of these features, but I'd not recommend just turning on some or all of these without thinking; there's a non-negligible impact on usability, and the potential benefits might be small, depending on what you're up against. Mind that an attacker won't necessarily have to get your password from KeePass, but can also choose to attack the program that you're entering the password in.
A password manager won't defend you against everything… because it can't. To quote Law #1 from the Ten Immutable Laws Of Security,
If a bad guy can persuade you to run his program on your computer, it's not your computer anymore.
The things a password manager does defend against, however, still mean that using one is a very good idea.
To complement @matthew answer: the point of having an encrypted password database is to protect it against offline attacks. For example, it can protect the database if you put it on a USB key and lose it, or if your powered off computer is stolen. Also, you can safely use a backup service or cloud-base synchronization service with your password database. (This applies only if you use a strong passphrase and do not synchronize your key file.)
