How to set up connection to MySQL database through SSH Tunnel in Laravel Sail

I recently ran into a situation, where I had to connect to MySQL database from within a Laravel app in Laravel Sail via SSH tunnel. Specifically, in the app I'm working on (legaldocsforge.com), I am caching prompts to Chat GPT API in the database shared between production and local development. There are no "update" or "delete" operations in that database. I'm simply adding new rows, and reading them.

The app is a creator of legal documents. Having cached prompts allows me to locally test & seed the database without paying extra for API usage. Also, if I create some legal document, or a bunch of them locally, I can use the same document creation prompt in production without hitting OpenAI API. Until this week, I've been using a hobby plan at PlanetScale to cache those prompts, but since they're retiring that plan on April 8, 2024, I moved to a self-hosted MySQL instance spun up via Laravel Forge, which steers you into "SHH tunnel" connection by default (I didn't want to play around with "allowed IPs" addresses). And so, I had to figure out to connect to that DB from Laravel Sail container, that I use for local development.

I don't know if this is the optimal way to do it, but it works for me. I guess this is quite generic solution you could use in any Docker app, but at this time the only way I'm interacting with Docker is via Laravel Sail, so can't say that for sure.

Setting up MySQL connection through SSH tunnel

The setup is pretty simple. I assume that you know how to connect to MySQL via SSH tunnel in general, and know what Laravel Sail is. I also assume you already have SSH key, and that you can access the database from your machine using that key. The only thing I'm focusing on here is how to replicate in the container what already works on the host system.

To get the connection going, you need to add another service to docker-compose.yaml, that is on the same network as the web service. The name of the web service is laravel.test`, but I renamed it `legaldocsforge.test` in my app. Connection parameters can also be defined in the .env file to make them easier to swap.

I need to add that besides "cached prompts" MySQL database, I'm using another ("main") MySQL database for the rest of the app. The "main" database has different state on local and production. The "cached prompts" database shares the state between environments.

Changes in docker-compose.yml

Add the following new service in docker-compose.yaml:

    prompts-db-ssh-tunnel:
        image: alpine
        volumes:
            - '~/.ssh:/root/.ssh'
        ports:
            - '${FORWARD_PROMPTS_DB_PORT:-3306}:3306'
        command: >
            sh -c "
                # install openssh-client inside the container
                apk add --no-cache openssh-client;
                # Start the SSH tunnel
                ssh -4 -L 0.0.0.0:3306:${PROMPTS_DB_HOST}:${PROMPTS_DB_PORT:-3306} forge@${PROMPTS_DB_HOST} -N -o ExitOnForwardFailure=yes -o ServerAliveInterval=60 -o ServerAliveCountMax=3;
                # Keep the container running after the SSH command is initiated
                while true; do sleep 30; done;"
        networks:
            - sail

Let's walk step by step to understand what this service does:

• prompts-db-ssh-tunnel : is just the name of our service. It can be anything you want.

• image: alpine : we're using a lightweight Linux container

• volumes : we're mapping here local directory ~/.ssh to /root/.ssh inside the container. I assume, you have your SSH keys in ~/.ssh on your local machine (it's the default location)

• ports : FORWARD_PROMPTS_DB_PORT specifies the port via which you'll be able to connect to prompts-db-ssh-tunnel service. The default value is 3306, and you can override it by setting the value of FORWARD_PROMPTS_DB_PORT in Laravel's .env file. We're hardcoding 3306 as the tunneling port within the service, as we don't really need to customize it - the container's only purpose is to provide the SSH tunnel for MySQL connection.

• command : please see the comments in the docker-compose.yaml excerpt above for general info.

• ssh -4 -L 0.0.0.0:3306:${PROMPTS_DB_HOST}:${PROMPTS_DB_PORT:-3306} forge@${PROMPTS_DB_HOST} -N : we're using -4 flag is to force connection via IPv4 (it didn't work for me without that flag). -L flag specifies that we're setting up forwarding. 0.0.0.0 is special value of "bind address" that allows you to connect to the container from other containers (it didn't work for me without explicitely specifying this value). 3306 is a hardcoded value for local port via which we're tunneling the connection to remote DB port (see "ports" section above"). PROMPTS_DB_HOST PROMPTS_DB_PORT are a hostname and port of your remote DB. -N flag tells ssh that we only want to set up the forwarding, and we don't need interactive session with remote host. If you need more details, run man ssh to check ssh manual.

• networks : we're making sure that our service is on the same network as our web service.

We also need to make a small adjustment to our web service, by adding prompts-db-ssh-tunnel as a dependency. You need to add it to the depends_on key, as in:

services:
    legaldocsforge.test:
        build:
            context: ./vendor/laravel/sail/runtimes/8.3
            dockerfile: Dockerfile
            args:
                WWWGROUP: '${WWWGROUP}'
        image: sail-8.3/app
        extra_hosts:
            - 'host.docker.internal:host-gateway'
        ports:
            - '${APP_PORT:-80}:80'
            - '${VITE_PORT:-5173}:${VITE_PORT:-5173}'
        environment:
            WWWUSER: '${WWWUSER}'
            LARAVEL_SAIL: 1
            XDEBUG_MODE: '${SAIL_XDEBUG_MODE:-off}'
            XDEBUG_CONFIG: '${SAIL_XDEBUG_CONFIG:-client_host=host.docker.internal}'
            IGNITION_LOCAL_SITES_PATH: '${PWD}'
        volumes:
            - '.:/var/www/html'
        networks:
            - sail
        depends_on:
            ## We're adding the below line
            - prompts-db-ssh-tunnel
            - mysql
            - redis
            - mailpit

After you make these changes, you need to rebuild your container with sail build (in my case, adding -- no-cache flag was not necessary, which I'm happy about, since it saves a few minutes of waiting).

Changing values in your .env file:

Since I have 2 MySQL connections, I added PROMPTS_ prefix to the keys used by default in .env file.

PROMPTS_DB_CONNECTION=mysql
PROMPTS_DB_HOST=XXX.XXX.XXX.XXX # host of your remote database on Forge
PROMPTS_DB_PORT=3306 # port of remote database on Forge
PROMPTS_DB_SSH_TUNNEL_SERVICE=prompts-db-ssh-tunnel # service name of our forwarding container 
FORWARD_PROMPTS_DB_PORT=3306 # port on our forwarding container
PROMPTS_DB_DATABASE=cached_prompts # DB name on forge
PROMPTS_DB_USERNAME=my_username # DB username on forge
PROMPTS_DB_PASSWORD=my_password # password on forge

I then refer to these values in my config/database.php as follows:

// ...
    'connections' => [
        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            // ...
        ],
        'prompts' => [
            'driver' => 'mysql',
            'url' => env('PROMPTS_DATABASE_URL'),
            'host' => env('PROMPTS_DB_SSH_TUNNEL_SERVICE'),
            'port' => env('FORWARD_PROMPTS_DB_PORT'),
            'database' => env('PROMPTS_DB_DATABASE'),
            'username' => env('PROMPTS_DB_USERNAME'),
            'password' => env('PROMPTS_DB_PASSWORD'),
            // ...
        ],
       // ...

Final thoughts

To sum up, the steps outlined above offer a pretty straightforward method for connecting to a MySQL database via SSH tunnel within a Laravel Sail environment. This method, while applied here in the context of caching Chat GPT API prompts for legaldocsforge.com, is broadly applicable and could benefit various Docker-based applications. Adjustments in docker-compose.yml and .env files are key to this setup, ensuring a seamless connection through an SSH tunnel.

With this setup, everything should be working properly. If you're running into any issues, please let me know in the comments below. Your thoughts, alternatives, or questions on this setup are welcome for further refinement and learning :)