How to "synchronize" multiple schemas in the same database? And about diff.

[f3rn4nd0-c354r]

1. I have 2 schemas in my local database. One is used for development and the other to run automatic tests (they have different names). How should I go about using skeema on both of them (since I can't use --schema on diffcommand; in fact the schema is "hardcoded" in a file).

2. What is the reasoning behind having 2 different commands (diff and push), where one is destructive (push) and another is not (diff), but you still have to use the allow-unsafe option if you just want to use the non-destructive one?

[evanelias]

1: The exact solution will depend on your overall db topology, but generally this can be solved by moving the schema name to an environment-section of the .skeema file.

How many production schemas do you have? If just one, I recommend starting your repo using skeema init --schema=... pointing at production. Using --schema on init will automatically "flatten" the dir structure to put host and schema in the same .skeema file. You can then manually edit that .skeema file as needed, to reflect the dev/test environments. For example you may end up with something like this:

[production]
host=some.prod.db.hostname
schema=myapp

[development]
host=127.0.0.1
schema=devapp

[testing]
host=127.0.0.1
schema=testing

Then you can specify environment names on the CLI to interact with the right thing: skeema push testing will push from the filesystem to the testing environment; skeema pull development will pull from the development environment into the filesystem; etc. If you omit an environment name from the command-line, the default is "production", so for example skeema diff is equivalent to skeema diff production.

Any configuration placed above the first environment (above the "[production]" line in the example here) applies to all environments, but individual environments may still override these as needed. There's a longer example in the examples doc.

If you have multiple different logical production schemas -- perhaps representing storage for different microservices -- the same general method of "move the schema name to an environment section" still applies. The overall setup may be a bit trickier in this case though, since you can't use a single flat directory hierarchy with multiple logical schemas, so the host names need to be configured one directory level up. If your dev/test locations don't follow the same colocation pattern as prod, this can get messy, but may still be solvable with some combination of host-level ignore-schema and/or skip-new-schemas.

2: This is explained in the docs for allow-unsafe and also mentioned in passing in the FAQ. Please let me know if this doesn't make sense though, I can improve the docs in this area if it still seems unclear/confusing. Thanks!

[f3rn4nd0-c354r]

1. I had tried this, but my .schema file was not inside the folder with the sql files, but one above. There was another .skeema file inside that folder witch was messing my settings. Now it's working correctly.

2. Ok. I think I understand the reasoning. Maybe is just not my favorite way of thinking. I'll try keep this in mind.

Thanks for the help!

[evanelias]

1: Yes, the default layout created by skeema init depends on whether or not you use the --schema option for it.

If you don't use --schema on init, Skeema uses a tiered directory layout of instance/schema/*.sql, which supports instances that have multiple schemas (databases). The .skeema file in the instance-level directory will define instance-level settings (host, port or socket, etc) and the .skeema file in the schema directory will define schema-specific settings (including the schema name). Options in .skeema "cascade" down the directory hierarchy, so options set in the instance-level .skeema still affect things in the schema-level directory, unless overridden there.

If you do use --schema on init, Skeema assumes that only the specified schema is relevant, and "flattens" the directory structure to define the instance and schema settings in the same .skeema file.

In any case, you can freely move things around in the .skeema files. Everyone uses a different database topology, and Skeema attempts to be flexible enough to support any arbitrary layout. But the cost of this is it can sometimes be complex to configure.

2: Just for context, this functionality was largely derived from trial and error. Very early on, skeema diff with unsafe changes did not require --allow-unsafe, and honestly this seemed more confusing. It was weird to have skeema diff report no errors/problems, but then have skeema push fail on an unsafe change. It also makes automation more cumbersome, since with that setup you can't programmatically examine the exit code of skeema diff to predict whether a subsequent skeema push will succeed.