How can I deploy a database script with Octopus Deploy?

Hi,

I have a database project set-up in my CI server (GitLab).

I wanted to get my GitLab server to run a database script (.sql) on another server. The problem is that the local build runner can’t access the server.

So I need to use Octopus to do this instead, as it does have access and correct permissions.

Is there a way for Octopus to do this. I heard that Octopus uses packages?
I really only want to run one or maybe two scripts on the server in question.

Hi!

Thanks for the great question.

Although we do support the concept of taking a set of files from a Git repository, it’s currently limited to GitHub as they expose APIs which allow us to treat it as a feed of packages.

Unfortunately (at the time of writing) we aren’t able to do that using GitLab (though we would certainly be interested in doing so!)

The VP of Customer Success at Octopus recently took the time to update our doco on our website on different ways to deploy to SQL Server. If you are interested you can see more on that here -> https://octopus.com/docs/deployment-examples/database-deployments/sql-server#guides

I’ve also outlined a conventional option below, with some alternatives at the end.

Coventional Process


The conventional way of achieving the execution of your database scripts (.sql in your case) is typically when they are packaged up into an archive (.zip, .nupkg etc).

We would generally see a CI server such as GitLab build the package, and then you could push the package to Octopus (or another external package repository) and execute the package as part of the deployment.

The easiest way to create a package and push to Octopus is to use the Octopus CLI - we have different flavors of it available to download. We also publish it as a docker image.

The two (or possibly three) CLI commands most relevant to your scenario would be:

  1. pack - to package up your SQL files -> https://octopus.com/docs/octopus-rest-api/octopus-cli/pack
  2. push - to push the package to Octopus -> https://octopus.com/docs/octopus-rest-api/octopus-cli/push
  3. create-release - to create a release (and optionally kick off a deployment) -> https://octopus.com/docs/octopus-rest-api/examples/create-and-deploy-a-release

Deploy a package step


If you are able to package up your database scripts, you can use the deploy a package step.

In order to execute your database scripts, you can achieve this using one of the Configuration features available on the package step, namely; Custom Deployment Scripts .

You can enable this on the package step by selecting Configure Features :

image

Then tick the Custom Deployment Scripts:

image

You can then choose to execute your SQL script in the Deployment script section:

image

The obvious advantage of this is that it allows you to not have to hard code any path to the files. A handy guide to what variables are available to use can be found here.

Alternatives


If you aren’t able to build and package up your database scripts, you’d be limited to either:

  • writing a script to clone the repo and the script and executing it from the script
  • executing the script as an ad-hoc script with its contents saved in Octopus - see this post for an example.

It’s also worth taking a look at our community library which has a number of scripts already written. I have linked to 2 main groups here:

  1. Git step templates - https://library.octopus.com/listing/git
  2. SQL Server step - https://library.octopus.com/listing/sql%20server