variable substitution?

[Dan Lynch]

Found a question on twitter, maybe somebody can help this gentleman out or is there a workaround?

https://twitter.com/EyMaddis/status/1309047660138110978

""Does it allow variable substitution? We use our migrations for multiple clients/environments which use different role names and such""

Dan Lynch
(734) 657-4483

[Dimitriy Khlevnyak]

https://github.com/sqitchers/sqitch/blob/develop/lib/sqitch-deploy.pod

deploy command has --set argument

Set a variable name and value for use by the database engine client, if it supports variables. The format must be name=value, e.g., --set defuser='Homer Simpson'. Overrides any values loaded from "configuration Variables".

--
You received this message because you are subscribed to the Google Groups "Sqitch Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqitch-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqitch-users/CA%2B_muLE8k7o4eM6eh3%2BZmwByiQOkLNqyp64kO5BvRE1DSr1V%2Bg%40mail.gmail.com.

--

Regards,
Dmytro Kh.

[Andrew Mason]

I asked about this a very long time ago and it's one of the only things I find most limiting when using sqitch.

AFAIK it still doesn't support variable substitution so I've ended up using a wrapper around the sqitch commands that first templates out the files to a temp dir, then runs sqitch over that.

Mainline support for even basic templates would be fantastic.

[Andrew Mason]

Correct me if I'm wrong but I, I don't think this is the same thing.

What I think the person wants (i.e what I am looking for) is something that allows this

-- dev.yml

db_name: db_name_provided_by_ops_team

foo_api_user:  test_user

foo_api_pwd: this_will_get_and_env_override_at_run_time

foo_api_privs: select,update 

-- deploy/create_users.sql

use {{ db_name }}

grant {{ privs }} on {{ db_name }} to {{ foo_api_user }} identified by '{{ foo_api_password }}'

To view this discussion on the web visit https://groups.google.com/d/msgid/sqitch-users/CAOvQPWHM3ke8112Q%2Bgo%3DAVJ%3Dst2BcTiW-hBDj3wJtMtD0%2BGU7A%40mail.gmail.com.

[David E. Wheeler]

On Sep 24, 2020, at 20:21, Andrew Mason <and...@nocturnal.net.au> wrote:

> Correct me if I'm wrong but I, I don't think this is the same thing.
>
> What I think the person wants (i.e what I am looking for) is something that allows this
>
> -- dev.yml
>
> db_name: db_name_provided_by_ops_team
>
> foo_api_user: test_user
>
> foo_api_pwd: this_will_get_and_env_override_at_run_time
>
> foo_api_privs: select,update
>
>
>
> -- deploy/create_users.sql
>
> use {{ db_name }}
>

> grant {{ privs }} on {{ db_name }} to {{ foo_api_user }} identified by '{{ foo_api_password }}’

Sqitch supports only the variable substitution provided by the database client (psql, mysql, snowsql, etc.). It does not provide support for any additional variable support. This is part of its design: it lets you write your scripts native to the database platform client.

If one wanted to provide a single variable substitution implementation around any script, the best way is to write a wrapper, as it sounds like you have done. Hav you released it for others to use, by chance?

Best,

David

[Andrew Mason]

> Sqitch supports only the variable substitution provided by the database client (psql, mysql, snowsql, etc.). It does not provide support for any additional variable support. This is part of its design: it lets you write your scripts native to the database platform client.
>
> If one wanted to provide a single variable substitution implementation around any script, the best way is to write a wrapper, as it sounds like you have done. Hav you released it for others to use, by chance?
>
> Best,
>
> David
>

My wrapper is actually just an ansible playbook calling a jinja template
task over the directories. Currently these projects are only for me and
I have ansible installed already for deployment. I don't think this is
ideal as I have to run manually before I deploy but it works.

Is there a pre-deploy hook or something in sqitch which could run and
perform some task like templating. If so I could publish a small binary
with something like Go which people could easily add without lots of
dependencies.

I am curious why you don't see this has something sqitch could provide ?
particularly when the rest of sqitch is geared up to support multiple
environments. I understand the support for native variable substitution
is there but I don't think I could use @db_name in a 'use' statement can
I ?

Additionally, isn't adding this kind of templating  a line or so of perl
? =)

kind regards

Andrew

[David E. Wheeler]

On Sep 28, 2020, at 09:40, Andrew Mason <and...@nocturnal.net.au> wrote:

> My wrapper is actually just an ansible playbook calling a jinja template task over the directories. Currently these projects are only for me and I have ansible installed already for deployment. I don't think this is ideal as I have to run manually before I deploy but it works.

Bummer. I mean, glad it works for you!

> Is there a pre-deploy hook or something in sqitch which could run and perform some task like templating. If so I could publish a small binary with something like Go which people could easily add without lots of dependencies.

That’d be cool. Sadly there isn’t, just a long-standing feature request:

https://github.com/sqitchers/sqitch/issues/96

> I am curious why you don't see this has something sqitch could provide ? particularly when the rest of sqitch is geared up to support multiple environments. I understand the support for native variable substitution is there but I don't think I could use @db_name in a 'use' statement can I ?

Actually, Sqitch ships with a templating system. It’s used to generate the default scripts, and you can customize them, too. Not well documented in the app or on the site, but I blogged about the approach a while back.

https://justatheory.com/2013/09/sqitch-templating/
https://justatheory.com/2014/01/templating-tests-with-sqitch/

So one could, in theory, add support for running deploy/verify/revert scripts through Template::Tiny or Template Toolkit, reading variable settings from a YAML or JSON file. I guess it would read in the scripts, run them through the template engine, write out a temporary file, have the database client run the temp file, then delete it. Probably not a bad feature request. So here you go:

https://github.com/sqitchers/sqitch/issues/548

Anyone want to take a stab at implementing it? (I have very few TUITs on hand, sadly.)

>
> Additionally, isn't adding this kind of templating a line or so of perl ? =)

Pbbbbt!

Best,

David