A few questions about databases and Laravel

Docker Engine Compose
1

Hello,
I have a sql file that is related to a website and contains user information such as username, password, etc. I have some questions:

1- I uploaded this sql file to the database using docker compose as follows:

db:
    image: mariadb:latest
    container_name: db
    restart: unless-stopped
    environment:
      MARIADB_ROOT_PASSWORD: rootsecret
      MARIADB_DATABASE: laravel_db
      MARIADB_USER: laravel_user
      MARIADB_PASSWORD: secret
    volumes:
      - mariadb_data:/var/lib/mysql
      - ./mysql/my.cnf:/etc/mysql/my.cnf
      - ./file.sql:/docker-entrypoint-initdb.d/file.sql
    ports:
      - "3306:3306"

I logged into the container and viewed the tables using the SHOW TABLES; command. I deleted the line that imported file.sql and stopped and restarted the container. All the data was deleted:

# mariadb -ularavel_user -psecret laravel_db
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 11.7.2-MariaDB-ubu2404 mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [laravel_db]> SHOW TABLES;
Empty set (0.001 sec)
MariaDB [laravel_db]>

Why?

2- In the database.php file for Laravel I saw the following information:

 'mariadb' => [
            'driver' => 'mariadb',
            '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', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => env('DB_CHARSET', 'utf8mb4'),
            'collation' => env('DB_COLLATION', 'utf8mb4_unicode_ci'),
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

As you can see, the database name is laravel. Do I also need to change laravel_db to laravel in the compose file?

3- Suppose I have 10 Laravel projects with different databases. If I use the mariadb:latest image in the compose file of each of these projects, will there be any conflict? Does each project have its own image?

Thank you.

2

The data of the database is persisted in a volume mariadb_data, not in a bind mount (folder on host). So if you deleted the volume in the process, then also your data is gone. I prefer bind-mounts, with those its harder to accidentally delete the data.

DB_HOST is the IP or service name of the database instance. You can not use 127.0.0.1, as that is only localhost within the container. You should use the Docker service name (db) or container name to connect to the database.

DB_DATABASE is the name of the database within the database container instance. You can have multiple databases within the same single mariadb container, for example for different applications (laravel_app_a, laravel_app_b), but all need to be populated individually with data.

You can have multiple mariadb containers running, one in each project compose file without conflict. If you don’t publish to the same port with ports, which is not required at all within a compose project. They will resolve correctly to their service name within their compose file, as long as they don’t share a network across multiple compose projects.

Personally I would not recommend to run multiple database containers, as it takes up more CPU and RAM, and it’s harder to keep multiple containers up to date than a single one.

1 Like
3

Hi,
Thank you so much for your reply.
1- So should the line - ./file.sql:/docker-entrypoint-initdb.d/file.sql always be there? If new data is added to the database and the container is reset, will the new data be lost?

2- The .env file looks like this:

DB_CONNECTION=mysql
DB_HOST=db
DB_PORT=3306
DB_DATABASE=laravel_db
DB_USERNAME=laravel_user
DB_PASSWORD=secret

I changed the value of 127.0.0.1 to db in the database.php file. I have a question about the following two lines:

'database' => env('DB_DATABASE', 'laravel'),
'username' => env('DB_USERNAME', 'root'),

Do I also need to change laravel_db to laravel in the compose and .env files?

4

It all depends on what you want to do.

Usually you would want your database to keep all data. As stated, I would use bind-mount folder to reduce the chances of deleting the data.

For the initial setup, we usually handle that inside the application, not with a SQL file in the database container. (get user count, if 0 create initial user and send email with password)

For database and user, again it depends on what you want to do. You can set DB_DATABASE, DB_USERNAME and DB_PASSWORD to whatever you want, as long as you create the database (and tables) and user/pass before starting the application.

1 Like
5

Thank you so much.
Can you show me an example of bind-mount folder?
I got the following error:

In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[HY000] [2002] Connection refused (Connection: mysql, SQL: select *  
   from `permissions` where `permissions`.`deleted_at` is null)                
                                                                               

In Connector.php line 66:
                                             
  SQLSTATE[HY000] [2002] Connection refused  
                                             


In Connection.php line 825:
                                                                               
  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'laravel_db.permi  
  ssions' doesn't exist (Connection: mysql, SQL: select * from `permissions`   
  where `permissions`.`deleted_at` is null)                                    
                                                                               

In Connection.php line 407:
                                                                               
  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'laravel_db.permi  
  ssions' doesn't exist
6

Hi,
I found that the problem is that the backend container starts before the db container. How do I prioritize?

7

Look at the healthcheck property for your service in your yaml file. Then second thing to look for is the depends_on key.

Here a tutorial : Understanding the depends_on condition in Docker compose files | Christophe Avonture

1 Like