Overview

You may run into a situation where your app attempts to access the database before it is ready to accept connections, resulting in a failure.

In this article, we will discuss strategies you can use in your CircleCI build in this situation.

Option 1: Sleep

Some users may opt to use the sleep command to wait for N seconds (e.g., `sleep 60` to wait for 1 minute).

This option is not preferred, since it is hard to be sure that the wait is consistent for every build every time.
Builds may sometimes need a longer wait.
For example, you may have added more initialization scripts to run for your Postgres database in a new build.

Option 2: Dockerize

Dockerize is a useful tool that comes with a `-wait` option.
Given an address, Dockerize will poll the address until the service can be reached or when we exceed the timeout specified.

As an example, we use Dockerize to wait on our Postgres database below:

version: 2.1

jobs:
  build:
    docker:
      # NOTE: cimg/base:current comes with Dockerize pre-installed
      - image: cimg/base:current
      - image: postgres:9.6.2-alpine
        environment:
          POSTGRES_USER: "your_postgres_user"
          POSTGRES_DB: "your_postgres_database"
    steps:
      - checkout
      - run:
          name: Wait for DB
          command: dockerize -wait tcp://localhost:5432 -timeout 1m

Note that Dockerize is not limited to just databases.
We can use it to check up on other services like Redis or an web server for example:

# Redis
dockerize -wait tcp://localhost:6379 -timeout 1m

# application server on port 8080
dockerize -wait tcp://localhost:8080 -timeout 1m

However, when it comes to databases, a successful Dockerize wait only means the database server can be reached.
It does not mean that the Database server can accept connections from then.

Option 3: Ping via SQL query

This last option takes advantage of making a simple `SELECT 1;` SQL query to ensure the database server can accept connections.

Here is an example with a MySQL database:

version: 2.1

jobs:
  build:
    docker:
      - image: cimg/base:current
      - image: mysql:latest
        environment:
          # as a trivial example; not recommended
          MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    steps:
      - checkout
      - run: |
          sudo apt-get update && sudo apt-get install -y mysql-client
      - run:
          name: Wait for DB
          command: |
            N=20
            while [ $N -gt 0 ]
            do
              if $(mysql -h 127.0.0.1 -u root -e "/* ping */ SELECT 1" > /dev/null); then
                echo "Connected to MySQL!"
                exit 0
              fi
              echo "Not connected; retrying"
              N=$(( $N - 1 ))
              sleep 1
            done
            exit 1
      - run:
          name: Verify
          command: |
            mysql -h 127.0.0.1 -u root -e "SHOW DATABASES;"