feat(mysql): try creating database if not exist

[thurt]

refer to: mattes/migrate#279

I created an issue for this because I'm interested in having this feature. Thoughts?

[dhui]

I'm not convinced of the value of migrate creating the DB is worth the cost but think this is an issue worth discussing.

I see the following issues with migrate creating the DB:

1. Compatibility with other DB drivers. For consistency, every driver would also need to implement a similar setup and the referenced PR only implements the change for MySQL.
2. What is the expected state of the DB for the first migration and what role does migrate play? Currently, migrate doesn't change the state of the DB so the first migration starts w/ a "clean slate" and any necessary DB configuration is done out of band.
3. Elevated permissions - Generally creating DBs and Tables require different permission levels
   • For postgres, the user needs to be a superuser or have the CREATEDB privilege: https://www.postgresql.org/docs/10/static/sql-createdatabase.html
   • For MySQL, the user needs a high priv_level and the CREATE privilege
     • https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_create
     • https://dev.mysql.com/doc/refman/8.0/en/grant.html

The value I see is:

1. Makes setting up a DB environment slightly easier
   • There are better tools like docker and terraform to do this for you

[thurt]

Thanks for your thoughts! The permissions point is something I hadn't thought about much as I am accustomed to thinking about access control defined by the application rather than in the database.

I think it's just an easier development process to have db schema changes (including db creation) organized around one tool. But I do see the fine line here about db creation being out of scope for migrate.

Currently, I create a database with a Dockerfile which just has a single COPY instruction to move /docker-entrypoint-initdb.d/createDatabase.sql into the mysql base image (see header Initializing a fresh instance). That is not as nice as just being able to always use the base mysql image and drive the schema changes in code with a tool like migrate.

[murphybytes]

I'd be interested in having this feature for postgres as well. The reason is mig.Drop doesn't work for databases that have functions, triggers and the like. It only drops the tables and if you have interdependencies in your database code. It doesn't work. This makes it impossible to perform unit tests starting with a clean slate base for every unit test. With postgres, I've been forced to execute a query killing all open connections before the test, drop the test table if it exists, and then re-create it.

[dhui]

@murphybytes Do your up migrations which add the functions and triggers have corresponding down migrations which drop the functions and triggers?