How to connect a lambda to a database accessible locally on Mac's localhost when using sam

Background

• I have a lambda that is connected to a RDS database. The RDS database and lambda are in a VPC. The db is only accessible to developers via a Bastion instance.
• During development we can test the lambda using sam. This works fine for APIs that don't depend on the database.
• For APIs that depend on the database, I would ideally like to connect to the database instance running in our Gamma stage. However, we can't directly connect to it because it is in a VPC.

What I have tried

• To get around this, we can use the SSM agent on the bastion instance with port forwarding so that the database is accessible on our Mac's localhost. See instructions. Sample code below:

aws ssm start-session --target <instance-id> --document-name AWS-StartPortForwardingSessionToRemoteHost --parameters host="mydb.example.us-east-2.rds.amazonaws.com",portNumber="3306",localPortNumber="3306"

• I can now connect to this locally at http://127.0.0.1:3306/ via CLI or a GUI like PSequel. No need to use SSH.
• However, if I try to get the lambda to connect to http://127.0.0.1:3306/, I get the error Connection refused.
• My understanding is that this is because 127.0.0.1 resolves to the docker container's localhost rather than my machine's localhost.
• According to docker docs, host.docker.internal ... resolves to the internal IP address used by the host
• However, if I try to get the lambda to connect to http://host.docker.internal:3306/, I get the error Name or service not known.

Minimal Working Example

I have created a MWE at https://github.com/bluprince13/sam-app-connect-to-host-localhost. Instead of trying to connect to a database, we can just run a Python server locally, and try to get the lambda to connect to it.

Question

• How to connect a lambda to a database accessible locally on Mac's localhost when using sam?
• I'm open to any alternatives for testing our lambda locally. Deploying to AWS is too much of a pain even with cdk hotswap.

References

• [Question] Is it possible to connect to a database running on localhost? #2272
• Cannot call API running on separate localhost port #260
• How to connect RDS instance when running SAM locally?

The MWE provided looks correctly configured. The issue is with docker configuration. As OP could figure out, there was a dns override in the configuration. (Docker -> Preferences -> Docker Engine) was overridden. After removing it, everything worked fine with host.docker.internal.

In general, to connect to the localhost of your host machine from a container you have to use host.docker.internal in mac and windows. Refer the SO post for configurations in other platforms. Specific to SAM in macOS, it is advisable to have the following, to avoid hard coding of parameters:

1. Create an Environment variable in template.yaml under your resource property.

  Properties:
     Environment:
       Variables:
         DB_HOST: *your_database_url*

2. Create an env.json file with following configuration.

   {
    "*Logical_ID of your resource*": {
        "DB_HOST": "host.docker.internal"
     }
   }    

3. Run your SAM with the env.json as sam local invoke --env-vars env.json.