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

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 cashing 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 basically need to add the 2 entries to your docker-compose.yaml in the web service section (laravel.test by default, and in my case legaldocsforge.test), and then set the connection parameters in your .env file.

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 2 entires to your docker-compose.yml:

  1. Volume mapping of your local .ssh directory to the same directory in Sail container '~/.ssh:/home/sail/.ssh'

  2. Command executed when you start the continer command: ssh -4 -L 3307:XXX.XXX.XXX.XXX:3306 forge@XXX.XXX.XXX.XXX -N. Some notes about this command: -4 flag is used to force connection via IPv4 (it didn't work for me without that flag). XXX.XXX.XXX.XXX needs to be replaced with your IP address. forge is my username on forge, so if you have a different one, you need to adjust accordingly. 3307 is a port within Laravel Sail app. This is something that you'll be referring to in your .env file. You can also use 3307, or choose something else, for example 33060, 3308 etc. It doesn't really matter what number you pick, as long as the port is not already taken by another service in Sail container. `3306` is the DB port on the remote database on Laravel forge. Unless you customized the defaults, it should be 3306 in your case as well. -N flag tells ssh not to open the interactive connection, but simply set up a tunnel that will run in the background. You can review the details by running man ssh in terminal.

Here's how the changes look like in the context of your docker-compose.yml.

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'
            # 1. Map key folder on host to the container
            - '~/.ssh:/home/sail/.ssh'
        # 2. Open SSH tunner when you start a container
        command: ssh -4 -L 3307:XXX.XXX.XXX.XXX:3306 forge@XXX.XXX.XXX.XXX -N
        networks:
            - sail
        depends_on:
            - mysql
            - redis
            - mailpit

# The rest of your docker-compose.yml

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=host.docker.internal # equivalent of 127.0.0.1 in docker 
PROMPTS_DB_PORT=3307 # DB port in your Sail container (the tunnel 
# takes care of forwarding it to port 3306 on your DB server)
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_HOST'),
            'port' => env('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 :)