Open cfritzlar opened 2 years ago
I'd like to run a repeatable script before the version scripts to do USE SCHEMA {{ SCHEMA }}
Hello @cfritzlar, @FelipeLema
Thank you for raising the issue. Could you check out Issue #110 and see if you can leverage the -f
and -m
options to control the order of execution of the scripts using jinja templating.
Please let us know if this is helpful.
thx for the tip, @sfc-gh-tmathew , although it is not something I could use.
Let me explain
the use-case for me would be to have a single command say alias company-dev-run-migrations=...
that runs a command like schemachange --pre-run=some/file/pre.sql
this way, when running make run-migrations
I'll know that before running any script, the script some/file/pre.sql
will be run as a setup for each of the actual migration scripts.
since I'm using a one-liner to set the schema, using -f
or -m
would not change much my situation ("having this one-liner at the top of every script is a must")
alternatively, I could use something like --snowflake-schema=SOME_SCHEMA
added as a flag (like --snowflake-account
and --snowflake-user
Hello @FelipeLema
Let me see if I understood your requirement
if your file modules/pre.sql
has the following code
SET START_TS=CURRENT_TIMESTAMP();
USE ROLE MY_DEPLOYMENT_ROLE;
Say your root folder -f deploy
and -m modules
that contains the scripts are
(project_root)
|
|-- deploy
|-- V1.1.1__first_change.sql
|-- V1.1.2__second_change.sql
|-- modules
|-- pre.sql
Can you not have the following in the first line of each sql file to ensure it is executed at the beginning of each file?
Contents of V1.1.1___first_change.sql
{% include modules/pre.sql %}
SELECT $START_TS, CURRENT_ROLE();
OR is your requirement that you need the pre.sql to run once before looping through each SQL files in the -f
location?
Could you provide an example of the kind of statements you will place in the pre.sql call ?
Also, are looking for a feature like the one in dbt docs.
If nothing I attempted above is what you intended, could you review #164 to see how far is it aligned with your use case as well ? Appreciate your thoughts and feedback.
OR is your requirement that you need the pre.sql to run once before looping through each SQL files in the -f location?
this is my requirement. #164 sounds very much to what I am requesting
The development for this functionality is already complete and is available for review with https://github.com/Snowflake-Labs/schemachange/pull/173. This additional feature allows the user to specify if certain files should be executed at the beginning of every deployment. Additionally, this function can be turned off at the command line, so deployment scripts can be turned on for lower environments, but turned off for production (e.g. cloning operations).
I'd like to run a repeatable script before the version scripts to do
USE SCHEMA {{ SCHEMA }}
@FelipeLema I'm a bit late to this party, but I have a PR ready to enable supplying schema as part of the configuration: #210 . Assuming this gets accepted, you'll be able to accomplish this by simply supplying schema the same way you supply DB and warehouse.
In our development process we currently face the issue from time to time that in a versioned script (V) we are referencing a view or stored procedure that is introduced or updated in the same change via a repeatable script. But since versioned scripts always run before repeatable scripts we need to include the new view / stored procedure definition also in the versioned script. This should be avoided since it's error prone and contradicts the DRY principle. I'm proposing to make this configurable in schemachange by i.e. adding a certain string in the V script name like "post_deploy__" that enables a different order of execution for that specific script.