Repository: dbeatty10/dbt-mysql (language: python)

Issue:
docker compose
Provide a `docker-compose.yml` file to launch database services for testing.
Provide instructions to bring those services up, shut them down, and use them in between.


Discussion / hints:

Fix patch:
diff --git a/.env.example b/.env.example
new file mode 100644
index 0000000..07c8a34
--- /dev/null
+++ b/.env.example
@@ -0,0 +1,6 @@
+DBT_MYSQL_SERVER_NAME=localhost
+DBT_MYSQL_USERNAME=root
+DBT_MYSQL_PASSWORD=dbt
+DBT_MYSQL_80_PORT=3306
+DBT_MYSQL_57_PORT=3307
+DBT_MARIADB_105_PORT=3308
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 72e1cf9..b8ef2dc 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,7 +1,10 @@
 ## Unreleased (TBD)
+### Features
 - Support dbt v1.1 ([#100](https://github.com/dbeatty10/dbt-mysql/pull/100))
+### Under the hood
+- docker compose MySQL and MariaDB database services for local testing ([#9](https://github.com/dbeatty10/dbt-mysql/issues/9), [#104](https://github.com/dbeatty10/dbt-mysql/pull/104))
 
-## Unreleased (March 13, 2022)
+## dbt-mysql 1.0.0 (March 13, 2022)
 - Support dbt v1.0 ([#90](https://github.com/dbeatty10/dbt-mysql/pull/90))
 
 ## dbt-mysql 0.21.1 (March 13, 2022)
diff --git a/docker-compose.yml b/docker-compose.yml
new file mode 100644
index 0000000..bd091ff
--- /dev/null
+++ b/docker-compose.yml
@@ -0,0 +1,29 @@
+version: "3.5"
+services:
+  mysql-server-8-0:
+    image: mysql:8.0
+    environment:
+      MYSQL_ROOT_PASSWORD: ${DBT_MYSQL_PASSWORD}
+    ports:
+      - "${DBT_MYSQL_80_PORT}:3306"
+
+  mysql-server-5-7:
+    platform: linux/x86_64
+    image: mysql:5.7
+    environment:
+      MYSQL_ROOT_PASSWORD: ${DBT_MYSQL_PASSWORD}
+    command: [--explicit-defaults-for-timestamp=ON]
+    ports:
+      - "${DBT_MYSQL_57_PORT}:3306"
+
+  mariadb-server-10-5:
+    image: mariadb:10.5
+    environment:
+      MYSQL_ROOT_PASSWORD: ${DBT_MYSQL_PASSWORD}
+    command: [--explicit-defaults-for-timestamp=ON]
+    ports:
+      - "${DBT_MARIADB_105_PORT}:3306"
+
+networks:
+  default:
+    name: dbt-net
diff --git a/tox.ini b/tox.ini
index 6c11e3e..8c82fb6 100644
--- a/tox.ini
+++ b/tox.ini
@@ -20,7 +20,7 @@ deps =
 [testenv:integration-mysql-8.0]
 basepython = python3.8
 commands = /bin/bash -c '{envpython} -m pytest test/integration/mysql-8.0.dbtspec'
-passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME
+passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME DBT_MYSQL_80_PORT
 deps =
    -r{toxinidir}/requirements.txt
    -r{toxinidir}/requirements-dev.txt
@@ -29,7 +29,7 @@ deps =
 [testenv:integration-mysql-5.7]
 basepython = python3.8
 commands = /bin/bash -c '{envpython} -m pytest test/integration/mysql-5.7.dbtspec'
-passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME
+passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME DBT_MYSQL_57_PORT
 deps =
    -r{toxinidir}/requirements.txt
    -r{toxinidir}/requirements-dev.txt
@@ -38,7 +38,7 @@ deps =
 [testenv:integration-mariadb-10.5]
 basepython = python3.8
 commands = /bin/bash -c '{envpython} -m pytest test/integration/mariadb-10.5.dbtspec'
-passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME
+passenv = DBT_INVOCATION_ENV DBT_MYSQL_SERVER_NAME DBT_MARIADB_105_PORT
 deps =
   -r{toxinidir}/requirements.txt
   -r{toxinidir}/requirements-dev.txt


Test patch:
diff --git a/test/README.md b/test/README.md
index 857c0ae..728d45d 100644
--- a/test/README.md
+++ b/test/README.md
@@ -22,12 +22,43 @@ pytest test/mysql.dbtspec
 ### Environment variables
 
 Create the following environment variables (e.g., `export {VARIABLE}={value}` in a bash shell or via a tool like [`direnv`](https://direnv.net/)):
-    * `DBT_MYSQL_SERVER_NAME`
-    * `DBT_MYSQL_USERNAME`
-    * `DBT_MYSQL_PASSWORD`
+* `DBT_MYSQL_SERVER_NAME`
+* `DBT_MYSQL_USERNAME`
+* `DBT_MYSQL_PASSWORD`
+* `DBT_MARIADB_105_PORT`
+* `DBT_MYSQL_57_PORT`
+* `DBT_MYSQL_80_PORT`
+
+`.env.example` has a listing of environment variables and values. You can use it with Docker by configuring a `.env` file with appropriate variables:
+
+```shell
+cp .env.example .env
+$EDITOR .env
+```
+
+By default, [Docker will automatically load environment variables](https://docs.docker.com/compose/env-file/) from a file named `.env`.
 
 ### Docker
 
+#### Easiest
+
+This command will launch local databases for testing:
+```shell
+docker-compose up -d
+```
+
+Skip to down below and follow the instructions to ["Run tests"](#run-tests).
+
+When finished using the containers:
+```shell
+docker-compose down
+```
+
+#### Harder
+
+<details>
+  <summary>More complicated docker setup commands</summary>
+
 [Here](https://medium.com/@crmcmullen/how-to-run-mysql-in-a-docker-container-on-macos-with-persistent-local-data-58b89aec496a) is one guide on "How to Run MySQL in a Docker Container on macOS with Persistent Local Data".
 
 In the docker commands below, the default MySQL username is `root` and the default server name is `localhost`. If they are used unaltered, then you should set the following environment variable values:
@@ -52,11 +83,21 @@ sql_mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID
 
 `docker run --name mysql5.7 --net dev-network -v /Users/YOUR_USERNAME/Develop/mysql_data/5.7:/var/lib/mysql -v /Users/YOUR_USERNAME/Develop/mysql_data/5.7/my.cnf:/etc/my.cnf -p 3307:3306 -d -e MYSQL_ROOT_PASSWORD=$DBT_MYSQL_PASSWORD mysql:5.7`
 
+</details>
+
 ### Run tests
 
 Run the test specs in this repository:
-```
+```shell
 pytest -v test/integration/mariadb-10.5.dbtspec && \
 pytest -v test/integration/mysql-5.7.dbtspec && \
 pytest -v test/integration/mysql-8.0.dbtspec
 ```
+
+Or run all the tests via tox:
+```shell
+tox -e flake8,unit && \
+tox -e integration-mariadb-10.5 && \
+tox -e integration-mysql-5.7 && \
+tox -e integration-mysql-8.0
+```
diff --git a/test/integration/mariadb-10.5.dbtspec b/test/integration/mariadb-10.5.dbtspec
index a847ea3..1f04248 100644
--- a/test/integration/mariadb-10.5.dbtspec
+++ b/test/integration/mariadb-10.5.dbtspec
@@ -1,7 +1,7 @@
 
 target:
   type: mariadb
-  port: 3306
+  port: "{{ env_var('DBT_MARIADB_105_PORT', '3306') | as_number }}"
   database: dbt_test_{{ var('_dbt_random_suffix') }}
   schema: dbt_test_{{ var('_dbt_random_suffix') }}
   server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
diff --git a/test/integration/mysql-5.7.dbtspec b/test/integration/mysql-5.7.dbtspec
index ca5bfe7..8ff7e81 100644
--- a/test/integration/mysql-5.7.dbtspec
+++ b/test/integration/mysql-5.7.dbtspec
@@ -1,7 +1,7 @@
 
 target:
   type: mysql5
-  port: 3306
+  port: "{{ env_var('DBT_MYSQL_57_PORT', '3306') | as_number }}"
   database: dbt_test_{{ var('_dbt_random_suffix') }}
   schema: dbt_test_{{ var('_dbt_random_suffix') }}
   server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
diff --git a/test/integration/mysql-8.0.dbtspec b/test/integration/mysql-8.0.dbtspec
index 189dc6a..07bc658 100644
--- a/test/integration/mysql-8.0.dbtspec
+++ b/test/integration/mysql-8.0.dbtspec
@@ -1,7 +1,7 @@
 
 target:
   type: mysql
-  port: 3306
+  port: "{{ env_var('DBT_MYSQL_80_PORT', '3306') | as_number }}"
   database: dbt_test_{{ var('_dbt_random_suffix') }}
   schema: dbt_test_{{ var('_dbt_random_suffix') }}
   server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
diff --git a/test/mysql.dbtspec b/test/mysql.dbtspec
index cd158b6..18b51f9 100644
--- a/test/mysql.dbtspec
+++ b/test/mysql.dbtspec
@@ -1,9 +1,10 @@
 
 target:
   type: mysql
+  port: "{{ env_var('DBT_MYSQL_80_PORT', '3306') | as_number }}"
   server: "{{ env_var('DBT_MYSQL_SERVER_NAME', 'localhost') }}"
-  username: "{{ env_var('DBT_MYSQL_USERNAME', 'dbt_username') }}"
-  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt_password') }}"
+  username: "{{ env_var('DBT_MYSQL_USERNAME', 'root') }}"
+  password: "{{ env_var('DBT_MYSQL_PASSWORD', 'dbt') }}"
   schema: dbt_test_{{ var('_dbt_random_suffix') }}
 
 sequences: