How to integrate Sqitch the CI tools?

[Ivan Nunes]

In a context of continuous integration or continuous delivery. How can I integrate sqitch with these tools?

Use cases, flow tips, are welcome.

Thank U.

[David E. Wheeler]

On Mar 30, 2015, at 2:29 PM, Ivan Nunes <ivanels...@gmail.com> wrote:

> In a context of continuous integration or continuous delivery. How can I integrate sqitch with these tools?
>
> Use cases, flow tips, are welcome.

Hi Ivan,

We have a bunch of Jenkins jobs that build RPMs for our Sqitch projects. As part of the RPM building, the %check section of the RPM spec file looks like this:

%check
psql -U postgres -c 'DROP DATABASE IF EXISTS configuration_test'
createdb -U postgres configuration_test
sqitch deploy db:pg://postgres@/configuration_test
pg_prove -U postgres -d configuration_test test/*.sql
sqitch revert db:pg://postgres@/configuration_test
dropdb -U postgres configuration_test

So it makes sure the deploy works, that all the unit tests pass, and the revert works. Works quite well. You could also just do this in a script block in Jenkins or in the relevant sections of a .travis.yml. Just need to make sure you have a database server you can use. Or write a shell script that does it and just call that.

HTH,

David

[Mark Mzyk]

Since we make use of sqitch at Chef, we have a cookbook to install it on the systems where we need it. The cookbook is open source and anyone is free to use it.

https://github.com/opscode-cookbooks/sqitch

Within Chef (the configuration management tool, not the company) you can then use and configure this cookbook to ensure sqitch is installed on a system and then you can access sqitch from within other Chef cookbooks and recipes you might write.

That cookbook also gives you access to a resource provider that we wrote, so then you can call sqitch in your other cookbooks like this example:

https://github.com/opscode-cookbooks/opscode-bifrost/blob/master/recipes/database.rb#L69

There are details that I've left out on how exactly to set this all up, but if you use Chef this should all be familiar to you. So this is one way to make use of sqitch within a configuration management environment which often gets used in the context of CI and CD environments.

Thanks,

Mark Mzyk

David E. Wheeler

April 2, 2015 at 6:22 PM

Hi Ivan,

We have a bunch of Jenkins jobs that build RPMs for our Sqitch projects. As part of the RPM building, the %check section of the RPM spec file looks like this:

%check
psql -U postgres -c 'DROP DATABASE IF EXISTS configuration_test'
createdb -U postgres configuration_test
sqitch deploy db:pg://postgres@/configuration_test
pg_prove -U postgres -d configuration_test test/*.sql
sqitch revert db:pg://postgres@/configuration_test
dropdb -U postgres configuration_test

So it makes sure the deploy works, that all the unit tests pass, and the revert works. Works quite well. You could also just do this in a script block in Jenkins or in the relevant sections of a .travis.yml. Just need to make sure you have a database server you can use. Or write a shell script that does it and just call that.

HTH,

David

Ivan Nunes

March 30, 2015 at 5:29 PM

In a context of continuous integration or continuous delivery. How can I integrate sqitch with these tools?

Use cases, flow tips, are welcome.

Thank U.

--
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 post to this group, send email to sqitch...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[David E. Wheeler]

On Apr 6, 2015, at 11:36 AM, Mark Mzyk <mm...@chef.io> wrote:

> Since we make use of sqitch at Chef, we have a cookbook to install it on the systems where we need it. The cookbook is open source and anyone is free to use it.
>
> https://github.com/opscode-cookbooks/sqitch

Ooh, very cool, thank you!

David

[Ivan Nunes]

I have an old database. In this context, I will add the DDL in an existing git repository. After that, I do integration with Sqitch? I'm thinking correctly?

As the Sqitch determines the order of script execution?

[Tony Lukasavage]

I'm using sqitch successfully with travis ci + postgres right now. Happy to offer tips if anyone is interested. 

[Ivan Nunes]

Hi Tony,

I'm still in baby steps. I do not know Travis-CI. I'm still in the "Hello Jenkins."

My environment using git and postgres and ansible. I have doubts. How to integrate each of these tools now ?!

[Sameer Srinivas]

Few concerns in the context of CI:

> By deleting and re-creating a test database, we loose sqitch registry tables too. So, when we run deploy, wouldn't it deploy from the begining again when in fact we wanted to just check the new changes pushed/deployed ?

> To solve above scenario, I thought to clone the master db to the test db so that sqitch knows what is deployed and what to deploy. Looks like, database name is being used as part of the key which sqitch used to determine if a particular script has been deployed or not. What happened was, it ended up deploying everything from the beginning again. Is there a way to work around this issue? 

     

If you could answer your take on how you handled above scenarios in your current projects, that would be great too. Thanks.  

[David E. Wheeler]

On Jan 15, 2020, at 21:24, Sameer Srinivas <sameer....@li.me> wrote:

> Few concerns in the context of CI:
>
> > By deleting and re-creating a test database, we loose sqitch registry tables too. So, when we run deploy, wouldn't it deploy from the begining again when in fact we wanted to just check the new changes pushed/deployed ?

Yep. That’s how I prefer to do it, to make sure the whole damn thing works. My CI practice is to start from nothing, so that I have no external dependencies from previous builds or anything else. Whole lot cleaner that way.

> > To solve above scenario, I thought to clone the master db to the test db so that sqitch knows what is deployed and what to deploy. Looks like, database name is being used as part of the key which sqitch used to determine if a particular script has been deployed or not. What happened was, it ended up deploying everything from the beginning again. Is there a way to work around this issue?

It does not rely on the database name, so this should work. What error are you seeing?

Best,

David

[Sameer Srinivas]

No error. Seemed like it redeployed the changes again. But, I might be wrong since I didn't do thorough testing on this. I'll give it a try. Thank you!  

[Paul Williams]

Hi David,

Amazing tool, thanks for your patience when I wrote a couple of things for Sqitch. Using it successfully at a new company and have a couple of things to chime in with.

> psql -U postgres -c 'DROP DATABASE IF EXISTS configuration_test'
> createdb -U postgres configuration_test
> sqitch deploy db:pg://postgres@/configuration_test
> pg_prove -U postgres -d configuration_test test/*.sql
> sqitch revert db:pg://postgres@/configuration_test
> dropdb -U postgres configuration_test

I appreciate this response was five years ago so things might have changed since then, but I've also added another `sqitch deploy` after the revert.

This catches occurrences where users miss adding to the revert properly (or not at all) as deploying over entities that still exist fails.

I've also recently had to add a change to the CI job to prevent people adding Sqitch changes out-of-order which affected deployment on production. This was caused by a dev with a long lived branch sneaking in a MR that I didn't vet with changes before the end of the plan.

It's not foolproof but it looks like:

- fetch target branch
- check out just the plan from that branch
- deploy from that plan
- check out source plan (git revert to tip)
- deploy from that plan

This helps catch stray changes working their way into the wrong position in the plan based on latest sqitch plan (that might be in production).

It's not fool proof as there's nothing stopping someone from creating a MR a week ago that passed at the time but the plan has changed since then, but other processes should kick in then to try and prevent that.

Cheers,
Paul

[David E. Wheeler]

On Feb 29, 2020, at 01:55, Paul Williams <kwakwa...@gmail.com> wrote:

> I appreciate this response was five years ago so things might have changed since then, but I've also added another `sqitch deploy` after the revert.
>
> This catches occurrences where users miss adding to the revert properly (or not at all) as deploying over entities that still exist fails.

Oh, smart!

> I've also recently had to add a change to the CI job to prevent people adding Sqitch changes out-of-order which affected deployment on production. This was caused by a dev with a long lived branch sneaking in a MR that I didn't vet with changes before the end of the plan.
>
> It's not foolproof but it looks like:
>
> - fetch target branch
> - check out just the plan from that branch
> - deploy from that plan
> - check out source plan (git revert to tip)
> - deploy from that plan
>
> This helps catch stray changes working their way into the wrong position in the plan based on latest sqitch plan (that might be in production).

Also a nice idea. Thanks for the tip

> It's not fool proof as there's nothing stopping someone from creating a MR a week ago that passed at the time but the plan has changed since then, but other processes should kick in then to try and prevent that.

Are you not able to do the same thing for every fresh push to the MR?

Best,

David

[Paul Williams]

> Are you not able to do the same thing for every fresh push to the MR?

Yeah, it would run when the source branch goes into the target branch too, but if this is `master` the CI job would fail, but there's nothing stopping a deploy happy infrastructure bod from deploying the latest and greatest. So kind of process thing - where I am currently at least.

Hmm. I should probably check to see if gitlab supports periodically rerunning the CI jobs for MRs that are still open. Seems like a sensible feature so surely it exists. *reads*