How to write 001.sql for existing environments?

[stephen-kruger]

Hi all,

Is there a best practice for the post-fit of this system for existing environments? For example, initial state will be taken from our prod env. So the 001 script will recreate the entire schema for qa, stage etc, but ideally it would not be run on prod (as the schema would already be correct).

I suspect the answer would be in a different script for each env, but this means maintaining a script for every env, I would prefer to have a single set for all envs.

Any suggestions would be welcome.

[rubenv]

You could take the existing schema but use CREATE TABLE IF NOT EXISTS etc, making it a no-op on production.

[stephen-kruger]

It seems that there is no way in sql to write the equivalent of : IF NOT TABLE EXISTS tablename BEGIN --do initial migration END

Similarly doing a CREATE DATABASE IF NOT EXISTS doesn't let me skip adding all the schema (or not) depending on if it was created or not.

Some docs point at using stored procedures to achieve something similar, but it seems clunky. Could the syntax support some basic exists() commands for db's and tables?

It seems the use of an existing production schema being backported to upstream environments is a common scenario, and it would be nice to support introducing a versioning system which can "clone" production for stagin envs, and going forward maintain it (starting at version 2 for example).

[rubenv]

It seems the use of an existing production schema being backported to upstream environments is a common scenario,

I highly doubt it, as this sounds like horrible engineering practice.

In your case, since you only have one production instance and it's already been hand-tweaked in the past (why else would you need to backport it), I'd just recommend to make a 001-initial.sql (or similar) and manually add a record to the migrations table in the production environment to indicate that it's already been applied.

[stephen-kruger]

So basically everyone on the planet currently using mysql in production who is NOT using sql-migrate, should not use it? That's quite a large user base you're writing off there...

[rubenv]

No, not at all!

You can pull an existing production system into the fold by:

1. dumping the existing schema into a 001-initial.sql migration
2. Manually inserting a row for that migration on the existing environment, so it won't get executed there

No need to build anything special for this case, right?

[stephen-kruger]

1. Agreed 2.No. I don't think users should be fiddling around in the undocumented internal tables of the sql-migrate. Of course I could log in and try and figure it out, but why not simply add a command like : sql-migrate skip --level=x Furthermore, on a new system the sql-migrate table would not exist yet, and it would not be simply adding the (undocumented) value, but also creating the table (again, undocumented, and for-internal-use-only).

[rubenv]

No. I don't think users should be fiddling around in the undocumented internal tables of the sql-migrate.

You're already in a situation where you've been live editing a production schema, who cares about committing a few more engineering crimes? :-)

why not simply add a command like : sql-migrate skip --level=x

Sure, I'm cool with that