search

rubenv / sql-migrate

SQL schema migration tool for Go.
MIT License
3.24k stars 280 forks source link

My "USE" statements break the utility #91

Open stephen-kruger opened 7 years ago

stephen-kruger commented 7 years ago

If my migration script looks like this :

-- +migrate Up

CREATE DATABASE /!32312 IF NOT EXISTS/ "apollo" /!40100 DEFAULT CHARACTER SET latin1 /;

USE "apollo"; DROP TABLE IF EXISTS "items";

I get these errors on run : Migration failed: Error 1146: Table 'apollo.gorp_migrations' doesn't exist handling 000000-apollo.sql_

If I remove the "USE " statement, it works fine.

How are we to create multiple databases? Surely that should not be a manual step? Does the sql-migrate only work on pre-existing databases? I had a look at the undocumented "schema" config parameter, but it just kept generating an sql error when executing :

sql-migrate status -config=migrations/apollo/dbconfig.yml Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table if not exists apollo.apollo-migrations (id varchar(255) not nul' at line 1)

any help much appreciated.

rubenv commented 7 years ago

Create your database outside of sql-migrate. That's usually considered part of your infrastructure, not your app.

stephen-kruger commented 7 years ago

Any other common sql statements that are not supported that I should steer clear of? Disagree database is part of the infrastructure. Maybe in 1980. But the ability to spin up arbitrary containers (databases OR tables) should be common for CI usage. Why treat them differently? Even worse, why not throw errors when a "CREATE DATABASE" is detected?

rubenv commented 7 years ago

Disagree database is part of the infrastructure. Maybe in 1980. But the ability to spin up arbitrary containers (databases OR tables) should be common for CI usage.

Believe it or not, but you're actually reducing flexibility by doing this (packaging DB provisioning in the app). In some environments you may not have the permissions to do CREATE DATABASE. By adding that to your migrations, you're forcing people to allow that. So in effect you've made it impossible for people to use your application in a high-security environment.

For arbitrary containers: just do a CREATE DATABASE when starting the container, then start your app. Always pass in the configuration parameters of a pre-configured database.

opensas commented 5 years ago

For arbitrary containers: just do a CREATE DATABASE when starting the container, then start your app. Always pass in the configuration parameters of a pre-configured database.

It makes sense, it would be great to add this sort of reasonings in the readme.