A few days ago, I’ve pushed a new project on AppVeyor. AppVeyor is a continuous integration service and it’s free for open-source projects. It’s not the first time that I configure a project on this CI platform but it was the first time that I wanted to execute tests on a custom database (part of the project).
My solution is composed of three distinct projects. The main library project, another project with the tests and finally a SSDT project with the database. Most of the tests need to connect to the database and execute queries (they are not unit tests but integration tests).
The tasks list is not complex:
- Build the three projects
- Deploy the database to an instance of SQL Server
- Run the tests
It’s extremely easy to build the solution on AppVeyor, you just need the sln file. First task done!
Deploying the database sounded also relatively easy. AppVeyor comes with a step “Deploy” where you can ask for the deployment of a dacpac in just a few clicks. Unfortunately the “Deployment” step is executed after the “Run tests” step … and it’s not what we’re expecting here. It’s also impossible to change the order of the steps. We’ll need to use some hacks to perform this.
First, we need to provision an instance of SQL Server by clicking on “Environment” and “Add Service”. This instance will be hosted on the same instance than the build agent and supports a SQL Server Login named “sa” with its password set to “Password12!”. A few pages of AppVeyor’s documentation also mention the support of “Integrated Security=True” but it hasn’t work on my side. The name of the instance is “(local)\SQL2014”.
Now that the SQL Server instance is provisioned, we need to deploy the database before running the tests. As previously said, impossible to use the “Deployment” step because it will occur after the “Tests” step. On the other hand, AppVeyor supports to configure events “before the tests”. We’ll use this feature to request the deployment of our dacpac on the previously provisioned SQL Server instance.
The trick is to use the full path for SqlPackage.exe (The software effectively managing the deployment of your dacpac to an instance of SQL Server). This path is the same on all build agents of AppVeyor.
"C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\SqlPackage.exe" /Action:Publish /SourceFile:"YourProject.Database\bin\Debug\\YourProject.Database.dacpac" /TargetDatabaseName:YourProject
When done, we still need to adapt the connectionString of our tests to redirect to the correct instance/database on AppVeyor environment. Naturally on my own laptop, the connection-string is different and on my own build machine same issue: the connection-string is also different. Personally, my solution is to provide a generic file connectionString.config and a few other files such as connectionString.appVeyor.config Each of these files contains the correct connectionString for a specific environment. The trick is to override the file connectionString.config by another one (in this case connectionString.appVeyor.config) after the build. You can configure after-build events in your Visual Studio project but this event is only required when building on appVeyor, so I prefer to setup it directly on the platform.
The following CMD commands will do the job: if file ConnectionString.config exists then delete it, rename “ConnectionString.appVeyor.config” to “ConnectionString.config”. Note that all path are relative to the root of your solution.
if exist "YourProject.Testing\bin\Debug\ConnectionString.config" del "YourProject.Testing\bin\Debug\ConnectionString.config" copy "YourProject.Testing.Testing\bin\Debug\ConnectionString.appVeyor.config" "YourProject.Testing.Testing\bin\Debug\ConnectionString.config"
It’s done, you now have a correctly configured CI on AppVeyor executing your database-related tests.