Using Laravel Sail with a separate testing database

Question

I'm using Laravel Sail as my development environment. According to the docs,

when the MySQL container is starting, it will ensure a database exists whose name matches the value of your DB_DATABASE environment variable.

This works perfectly for my development environment, but not so much when it comes to testing since my .env.testing defines a separate database, and it seems this database does not get created - when I sail mysql into the container and run show databases; it is not listed. As a result, running sail test fails every test where the database is concerned.

SQLSTATE[HY000] [1045] Access denied for user ...

My .env file contains this:

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=dev

My .env.testing file contains this:

DB_CONNECTION=mysql
DB_HOST=mysql
DB_PORT=3306
DB_DATABASE=test

DB_USERNAME and DB_PASSWORD are the same in both files.

How can I create this database so that it's available when running sail test?

EDIT:

As I dug through the repository code I found that the database is being created when the mysql container image is built, but it doesn't look like there's an option for creating multiple databases.

MYSQL_DATABASE This variable is optional and allows you to specify the name of a database to be created on image startup. If a user/password was supplied (see below) then that user will be granted superuser access (corresponding to GRANT ALL) to this database.

score 8 · Accepted Answer · answered Jan 11 '21 at 21:38

8

Add the following to docker-compose.yml under the services: key and set your host in .env.testing to mysql_test:

  mysql_test:
    image: "mysql:8.0"
    environment:
      MYSQL_ROOT_PASSWORD: "${DB_PASSWORD}"
      MYSQL_DATABASE: "${DB_DATABASE}"
      MYSQL_USER: "${DB_USERNAME}"
      MYSQL_PASSWORD: "${DB_PASSWORD}"
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    networks:
      - sail

answered Jan 11 '21 at 21:38

Michael Heap

371
1
2

I have tried this however the bit I am not sure about it loading in the .env.testing var? It seems docker-compose.yml just takes the .env vars so the the sql_test db does not actually take the vars defined in env.testing to set it up right? – Dean Collins Mar 21 '21 at 23:01
.env.testing is used when you run `sail test` to tell your app which database to connect to – Michael Heap Apr 13 '21 at 10:32
@DeanCollins Right, the `.env` file is being loaded and that's what defines `${DB_DATABASE}`. You can hard-code the database name into the `mysql_test` container config, or have them use the same database name (which is fine, since they're different containers). – Ethan C Jul 14 '21 at 17:12
Solution is nice and simple but not work in my case. I make sail build --no-cache with mysql_test but after all when I go sail mysql -> show databases; it shows just one database - not two that I expected. – Adam Kozlowski Apr 05 '22 at 13:15
in your .env.testing, make sure to set DB_HOST=mysql_test, Docker Compose uses the service name as the host name for networking. – Paul Preibisch Sep 03 '22 at 22:44

score 2 · Answer 2 · answered Jun 23 '21 at 01:37

2

You should be able to create the test database alongside the original dev database on the existing mysql container:

sail mysql

mysql> CREATE DATABASE test;

I have not tested this using mysql but the same process works on postgres:

sail psql

dev=# CREATE DATABASE test;

answered Jun 23 '21 at 01:37

Nick H

433
1
3
10

1

I get "ERROR 1044 (42000): Access denied for user 'sail'@'%' to database 'test'". – gre_gor Mar 18 '22 at 17:56
@gre_gor, as per the documentation, the default user is "root", not "sail". https://laravel.com/docs/10.x/sail#mysql – Eduardo Chongkan Aug 28 '23 at 17:47
@EduardoChongkan Not on 8.x – gre_gor Aug 28 '23 at 18:26
well, it is the same for all versions. – Eduardo Chongkan Sep 02 '23 at 03:52

score 0 · Answer 3 · answered Aug 28 '23 at 18:24

TLDR: It gets created by default by "./docker/mysql/create-testing-database.sh"

Inside the docker-compose.yml, in the mysql->volumes section, you should see the following:

volumes:
            - 'sail-mysql:/var/lib/mysql'
            - './docker/mysql/create-testing-database.sh:/docker-entrypoint-initdb.d/10-create-testing-database.sh'

The settings in .env.testing are not for telling sail which name to use to create the DB, but rather to tell sail what DB it needs to use when running the tests ( it needs to be set to "testing" in order to match the default schema created by the SH script. )

Using Laravel Sail with a separate testing database

3 Answers3

Linked