Connection my Express API to Mysql (Container) In CodeBuild

To connect your Express API to a MySQL container in CodeBuild, you need to ensure that the MySQL container is properly set up and accessible from your CodeBuild environment. Here are the steps to resolve the ECONNREFUSED 127.0.0.1:3306 error:

1. Check Docker Configuration in CodeBuild:

Ensure that you have configured your buildspec.yml file to start the MySQL container before running your tests. Here is an example of how you might set it up:

version: 0.2

phases:
  install:
    runtime-versions:
      nodejs: 14
    commands:
      - echo Installing Docker
      - apt-get update
      - apt-get install -y docker.io
      - service docker start
      - docker info
      - echo Docker installed

  pre_build:
    commands:
      - echo Starting MySQL container
      - docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=testdb -p 3306:3306 -d mysql:latest
      - docker ps
      - echo Waiting for MySQL to start
      - until docker exec mysql-container mysqladmin ping --host 127.0.0.1 --port 3306 --user=root --password=root --silent &> /dev/null ; do
          echo -n "." ; sleep 1 ; 
        done
      - echo MySQL is up and running

  build:
    commands:
      - echo Running tests
      - npm install
      - npm test

  post_build:
    commands:
      - echo Cleaning up
      - docker stop mysql-container
      - docker rm mysql-container

2. Ensure MySQL Container is Running:

Make sure that the MySQL container is running and accessible. You can check the logs of the MySQL container to ensure that it has started properly:

docker logs mysql-container

3. Update Connection Settings in Your Express API:

Ensure that your Express API is configured to connect to the MySQL container correctly. Here is an example using the mysql2 package in Node.js:

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: '127.0.0.1',
  port: 3306,
  user: 'root',
  password: 'root',
  database: 'testdb'
});

connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected to MySQL as id', connection.threadId);
});

module.exports = connection;

4. Ensure Network Configuration:

If your CodeBuild environment has network restrictions, ensure that Docker containers can communicate with each other and that there are no firewall rules blocking the connection.

5. Environment Variables: Instead of hardcoding the database credentials, you can use environment variables to make your setup more flexible. In your buildspec.yml, you can define environment variables like this:

version: 0.2

env:
  variables:
    DB_HOST: 127.0.0.1
    DB_PORT: 3306
    DB_USER: root
    DB_PASSWORD: root
    DB_NAME: testdb

phases:
  install:
    runtime-versions:
      nodejs: 14
    commands:
      - echo Installing Docker
      - apt-get update
      - apt-get install -y docker.io
      - service docker start
      - docker info
      - echo Docker installed

  pre_build:
    commands:
      - echo Starting MySQL container
      - docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=$DB_PASSWORD -e MYSQL_DATABASE=$DB_NAME -p 3306:3306 -d mysql:latest
      - docker ps
      - echo Waiting for MySQL to start
      - until docker exec mysql-container mysqladmin ping --host $DB_HOST --port $DB_PORT --user=$DB_USER --password=$DB_PASSWORD --silent &> /dev/null ; do
          echo -n "." ; sleep 1 ; 
        done
      - echo MySQL is up and running

  build:
    commands:
      - echo Running tests
      - npm install
      - npm test

  post_build:
    commands:
      - echo Cleaning up
      - docker stop mysql-container
      - docker rm mysql-container

6. Adjust Your Application to Use Environment Variables:

In your Express API, use the environment variables for configuration:

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME
});

connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected to MySQL as id', connection.threadId);
});

module.exports = connection;

By following these steps, you should be able to connect your Express API to the MySQL container in the CodeBuild environment successfully. If you continue to face issues, please provide more details about the error messages or logs.