This repository contains the sql-action GitHub Action for deploying changes to Azure SQL Database, Azure SQL Managed Instance, or SQL Server in a dacpac, SQL scripts, or an SDK-style SQL project. With the Azure SQL Action for GitHub, you can automate your workflow to deploy updates to Azure SQL or SQL Server.
Get started today with a free Azure account!
Looking to develop with SQL for free, locally and offline, before deploying with GitHub? Check out the Azure SQL local emulator and SQL Server Developer Edition!
The definition of this GitHub Action is in action.yml. Learn more in the user guide.
- uses: azure/[email protected]
with:
# required, connection string incl the database and user authentication information
connection-string:
# required, path to either a .sql, .dacpac, or .sqlproj file
path:
# optional when using a .sql script, required otherwise
# sqlpackage action on the .dacpac or .sqlproj file, supported options are: Publish, Script, DeployReport, DriftReport
action:
# optional additional sqlpackage or go-sqlcmd arguments
arguments:
# optional SqlPackage executable location, overrides default locations
sqlpackage-path:
# optional additional dotnet build options when building a database project file
build-arguments:
# optional, set this to skip checking if the runner has access to the server. Default is false.
skip-firewall-check:Note: The database project must use the Microsoft.Build.Sql SDK.
# .github/workflows/sql-deploy.yml
on: [push]
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: azure/[email protected]
with:
connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
path: './Database.sqlproj'
action: 'publish'
build-arguments: '-c Release' # Optional build options passed to dotnet build
arguments: '/p:DropObjectsNotInSource=true' # Optional properties and parameters for SqlPackage Publish# .github/workflows/sql-deploy.yml
on: [push]
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: azure/login@v1 # Azure login required to add a temporary firewall rule
with:
creds: ${{ secrets.AZURE_CREDENTIALS }}
- uses: azure/[email protected]
with:
connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
path: './sqlscripts/*.sql'# .github/workflows/sql-deploy.yml
on: [push]
jobs:
build:
runs-on: windows-latest
steps:
- uses: actions/checkout@v3
- uses: azure/[email protected]
with:
connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
path: './Database.dacpac'
action: 'publish'
arguments: '/p:DropObjectsNotInSource=true' # Optional properties parameters for SqlPackage PublishThe v1.x version of sql-action supports SQL authentication only in the connection string. Starting in v2, AAD Password, AAD Service Principal, and AAD Default authentications are also supported. Read more about implementing sql-action with different authentication methods in the connection guide.
sql-action supports passing arguments to SqlPackage, go-sqlcmd, and dotnet build.
- SqlPackage: SqlPackage publish properties are passed to the SqlPackage utility from the
argumentsproperty. More information on these properties is available in the SqlPackage publish documentation. SqlPackage parameters that do not impact the source or target setting are also valid, including/Profile:for a publish profile,/DeployReportPath:for a deployment report, and/Variables:to set SQLCMD variable values. - go-sqlcmd: go-sqlcmd parameters are passed to the go-sqlcmd utility from the
argumentsproperty. This enables SQLCMD variables-vto be passed to scripts as seen in the go-sqlcmd documentation. Note: sql-action uses go-sqlcmd for SQL script deployments, which contains some breaking changes from the ODBC-based sqlcmd utility. - dotnet build: dotnet build options are passed to the SQL project build step from the
build-argumentsproperty. More information on options is available in the dotnet build documentation.
sql-action is supported on both Windows and Linux environments. The default images include the prerequisites:
- sqlpackage (for sqlproj or dacpac deployment)
- dotnet (for sqlproj build)
On Windows, sql-action attempts to locate SqlPackage as a dotnet tool, in the default MSI install location C:\Program Files\Microsoft SQL Server\160\DAC\bin, and as a last resort for versions installed by Visual Studio. On Linux, sql-action attempts to locate SqlPackage as a dotnet tool before falling back to PATH. In both operating systems, the input sqlpackage-path can be used to override the default locations. For custom images installing SqlPackage as a dotnet tool is recommended.
sql-action installs go-sqlcmd at runtime for SQL script deployments.
Note
This Firewall Rules section of the document is specific to Azure SQL Database. For Azure SQL Managed Instance and SQL Server it is recommended to review the connection guide.
If you can use the option Allow Azure Services and resources to access this server on Azure SQL Database, you are all set and you don't need to to anything else to allow GitHub Action to connect to your Azure SQL Database.
If you cannot use the aforementioned option on Azure SQL Database, the action can automatically add and remove a SQL server firewall rule specific to the GitHub Action runner's IP address. Without the firewall rule, the runner cannot communicate with Azure SQL Database. Read more about this in the connection guide.
To enable the action to automatically add/remove a firewall rule, add an Azure/login step before the sql-action step. Also, the service principal used in the Azure login action needs to have elevated permissions, i.e. membership in SQL Security Manager RBAC role, or a similarly high permission in the database to create the firewall rule. Read more about this and other authentication methods in the connection guide.
Paste the output of the below az cli command as the value of secret variable, for example AZURE_CREDENTIALS.
az ad sp create-for-rbac --role contributor --sdk-auth --name "sqldeployserviceprincipal" \
--scopes /subscriptions/{subscription-id}/resourceGroups/{resource-group}Replace {subscription-id}, {resource-group} with the subscription ID and resource group of the Azure SQL server
The command should output a JSON object similar to this:
{
"clientId": "<GUID>",
"clientSecret": "<GUID>",
"subscriptionId": "<GUID>",
"tenantId": "<GUID>",
// ...
}
All the above examples use {{secrets.AZURE_SQL}} syntax for sensitive information, where content such as connection strings are stored in GitHub secrets. To create secrets in GitHub, navigate within your repository to Settings and then Secrets. Be careful to check the connection string which you copy from Azure SQL as the connection string has this Password={your_password} and you will need to supply the correct password for your connection string.
- Follow the tutorial Azure SQL Quickstart to create a single database
- Copy the below template and paste the contents in
.github/workflows/in your project repository assql-workflow.yml.
# .github/workflows/sql-workflow.yml
on: [push]
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: azure/[email protected]
with:
connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
path: './Database.sqlproj'
action: 'publish'- Place the connection string from the Azure Portal in GitHub secrets as
AZURE_SQL_CONNECTION_STRING. Connection string format is:Server=<server.database.windows.net>;User ID=<user>;Password=<password>;Initial Catalog=<database>. - Copy the below SQL project template and paste the content in your project repository as
Database.sqlproj.
<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
<Sdk Name="Microsoft.Build.Sql" Version="0.1.3-preview" />
<PropertyGroup>
<Name>reactions</Name>
<DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>
<ModelCollation>1033, CI</ModelCollation>
</PropertyGroup>
</Project>- Place any additional SQL object definitions in the project folder or in subfolders. An example table to get you started is:
CREATE TABLE [dbo].[Product](
[ProductID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[ProductNumber] [nvarchar](25) NOT NULL,
[Color] [nvarchar](15) NULL,
[StandardCost] [money] NOT NULL,
[ListPrice] [money] NOT NULL,
[Size] [nvarchar](5) NULL,
[Weight] [decimal](8, 2) NULL,
[ProductCategoryID] [int] NULL,
[ProductModelID] [int] NULL,
[ModifiedDate] [datetime] NOT NULL
)- Commit and push your project to GitHub repository, you should see a new GitHub Action initiated in Actions tab.
- For further use of SQL projects in VS Code and Azure Data Studio, check out http://aka.ms/azuredatastudio-sqlprojects for more information.
- Create a dacpac from an existing SQL database with either SSMS, Azure Data Studio or SqlPackage CLI. Place the dacpac file at the root of your repository.
- Follow the tutorial Azure SQL Quickstart to create a single database
- Copy the below template and paste the contents in
.github/workflows/in your project repository assql-workflow.yml, changing the dacpac file name as appropriate.
# .github/workflows/sql-workflow.yml
on: [push]
jobs:
build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v3
- uses: azure/[email protected]
with:
connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
path: './PreviousDatabase.dacpac'
action: 'publish'- Place the connection string from the Azure Portal in GitHub secrets as
AZURE_SQL_CONNECTION_STRING. Connection string format is:Server=<server.database.windows.net>;User ID=<user>;Password=<password>;Initial Catalog=<database>. - Commit and push your project to GitHub repository, you should see a new GitHub Action initiated in Actions tab.
For more information on contributing to this project, please see Contributing.