Something as simple as a database password definitely doesn't fly as far as I know based on reading through the GDPR. Maybe it's legal if you apply enough tricks, you should consult a lawyer if you want to know your workaround is sufficient.
However, by default, storing PII of EU citizens (+UK citizens, I believe, they've implemented the GDPR before they left) with American companies is not legal. I can see how in theory a remote disk drive with fully end-to-end encrypted traffic (encrypted inside the EU, merely stored abroad, the decryption key never leaving the EU) may be allowed, but if the data gets decrypted on the American end I'm pretty sure you're out of luck. Otherwise, any form of TLS would be enough to avoid the GDPR, and that's definitely not the case.
Encryption at rest doesn't protect you. In fact, may even be legally required, regardless of where you store your data. The GDPR doesn't specify any exact security measures, but you do have to try your hardest to secure any PII you may process or store and encryption at rest is one of the easiest steps you can take to do so. You should make a conscious decision of what data may leak to where, the impact of the leak, and ways to counteract such problems.
The problem with these kinds of questions is that the GDPR does not define any threat models, it only mentions "proper processes" and "adequate safeguards". Whether active subversion (by law or by greed) by your service provider should be included in your data loss exposure/risk assessments is very much an open question. At my company we decided to exclude such questions from the GDPR compliance process, and only include these scenario's in the threat models for our security assessments (note: that's not to say they are treated in isolation -- the results from our security assessments do inform our GDPR decisions like which data can be hosted where, but we do not repeat those same risk assessments in the GDPR survey).
To give a more direct answer to your question: I would consider encryption-at-rest a minimal requirement for a company hosting our internal data. Regardless of whether they're inside or outside the EU, and whether we're looking to host internal data, sales data or customer data; not being able to offer encryption at rest would mean my company won't use your hosting services for non-public data. For us, this specific ability is a supplier maturity test: if you haven't given serious thought about securing your customer's data, maybe we shouldn't be in business together.
But that decision is driven more by a defense-in-depth strategy about overall data security than by a specific GDPR requirement.
But if said US hosting company doesn't have the DB password then would this also apply? Do you think it would change anything if the data was encrypted at rest?