[Laravel] Initial Database Setup and Debugging

Initial Setup

Database connection information is found in two files mainly. These values are created during application creation. However, it is often necessary to check and fix those values. Any sensitive values like credentials are supposed to go into /.env file. It looks like:

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306

DB_DATABASE=alpha
DB_USERNAME=waterbae
DB_PASSWORD="securepassword"

Another file with database connection information is /config/database.php. It can have multiple configurations for many databases and it look like.

       'mysql' => [
            'driver' => 'mysql',
            'url' => env('DB_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'laravel'),
            'username' => env('DB_USERNAME', 'root'),
            'password' => env('DB_PASSWORD', ''),
            ...
            ]) : [],
        ],

In case you are building Laravel application with existing table, make sure your migrations files, /database/migrations/*.php , for creating tables exists in migrations table without .php and batch field has “1” as value.

Debugging

It is not working! What do I do??? It is most common issue we encounter after setting the server up for the first time. It is time to talk to system admin and DBA, but there are some steps we can take before starting a war.

First, check if your application server can talk to your db server using netcat.

nc -vz db.server.com 3306

If the connection is rejected talk to your DBA and System admin. If it can be connected, you can try the next step by troubleshooting with Tinker by typing:

php artisan tinker

Inside Tinker, you can double check if DB related environment variables and configurations are correct and run simple SQL to check.

config('database.connections.mysql.host');
DB::connection('mysql')->select('SELECT 1');

Keep double check with your setting and Tinker output to get desirable results for your server configuration.

Tip1: If you are working with existing databases and using “kitloong/laravel-migrations-generator”, you may generate those migration files with batch value 1.

php artisan migrate:generate --connection="mydb2"
php artisan migrate:generate --connection="mydb3"

Tip2: You may clear any configs and/or cache to see the results of you changes right away.

php artisan config:clear
php artisan cache:clear

Sail Configuration for multiple DB

To use more than one DB with Sail, each DB docker containers must set to use each ports for your machine side. So, it will look like as below in docker-compose.yml file.

    mysql:
        image: 'mysql/mysql-server:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3306}:3306'
        ...
        networks:
            - sail
        ...
    mysql.db2:
        image: 'mysql/mysql-server:8.0'
        ports:
            - '${FORWARD_DB_PORT:-3307}:3306'
        ...
        networks:
            - sail
        ...

However, you need to set /config/database.php up to use port 3306 because that is the port Sail talk to inside your DB docker container. Do not get lost here 🙂