Proposal: v2 of sql-action

[dzsquared]

The team has been discussing improvements to sql-action that would also require some breaking changes. As we prepare to implement changes we wanted to gather feedback and suggestions by sharing the proposed changes here. Please feel free to drop comments on this thread and if any topic gets detailed we might move it over to "discussions" for better thread support.

Issues mentioned below will be tagged with a v2-candidate tag soon.

---

1. Azure Active Directory support

We'd like to support AAD auth for username/password, service principal, and managed identity. (#60, #78, #93)

To do this smoothly, connectivity testing and script execution would be done directly through node-mssql instead of invoking sqlcmd within the action. The workflow would need to provide a connection string to the action, but any valid connection string supported by Tedious would work.

This change has a side benefit of reducing confusion around the connection string components (#72, #79) and we'd remove the confusing (and no longer used) server name parameter.

This change would remove the ability to use sqlcmd arguments during sql-file script execution.

2. Login credentials that do not have access to master

We'd like to allow sql-script execution by database logins with less access. (#89) This can be incorporated into the change from sqlcmd to node-mssql.

3. Additional actions

There's been interest voiced to have a subset of functionality from SqlPackage (#55) as well as the ability to build but not deploy a SQL project.

---

Proposed yml interface

Within reason, we need to make some changes to the action.yml definition to support additional actions and remove the unused server-name parameter.

name: 'Azure SQL Deploy'
description: 'Deploy a database project, dacpac, or a SQL script to Azure SQL database'
inputs:
  connection-string:
    description:  'The connection string, including authentication information, for the Azure SQL Server database.'
    required: true
  path:
    description: 'Path to file or files to use. Wildcard supported in path for .sql files.'
    required: true
  action:
    description: 'Selection for action to perform on SQL scripts, project file, or dacpac. Options: Script, Publish, PublishScript, PublishReport, NoDeploy'
    required: true
  arguments:
    description: 'In case of dacpac or sql project files are selected, additional SqlPackage arguments that will be applied. When script action is selected, additional go-sqlcmd arguments that will be applied.'
    required: false
  build-arguments:
    description: 'In case of sql project file is selected, additional arguments that will be applied to dotnet build when building the database project.'
    required: false
runs:
  using: 'node12'
  main: 'lib/main.js'

---

Breaking Changes

1. server-name parameter is proposed to be completely removed
2. sqlcmd arguments are proposed to no longer be available for script execution

---

Sound off!

Here's some ideas:

• Will these changes benefit your SQL deployments?
• Are we removing anything you use? (sqlcmd arguments)
• What are we not tackling that you would like to see?

[github-actions]

This issue is idle because it has been open for 14 days with no activity.

[dzsquared]

I’ve been involved in some discussions where its come up that it would be really helpful to run some queries/TSQL commands that are generated during the pipeline without going through the step of writing it to file. This is part of what we’ve taken away from being able to pass sqlcmd variables in, although even that wasn’t especially intuitive.
Could the addition of a file-type value along the lines of sql-query (or interactive-query, sql-command, run-command) be helpful for your use cases?

[yorek]

Following on what @dzsquared proposed, maybe we can just use the word input-type instead of file-type so that it can be a bit more open to what it accepts as values, and then we can use sql-command as an option to indicate that the SQL query is provided right in the yaml file, in a new dedicated property, for example: command.

 input-type:
    description: 'The input type. Options: sql-file, project-file, dacpac, sql-command'
    required: true
 sql-command:
    description: 'In case input type is sql-command, the sql command that will be executed'
    required: false

[github-actions]

This issue is idle because it has been open for 14 days with no activity.

[dzsquared]

As work as been underway on v2, the proposed interface is revised to simplify and allow the potential for extension to additional sqlpackage commands.

• The action parameter has become required and the file-type parameter is removed from the proposal.
• The arguments option is specified as sqlpackage-arguments

(this is being updated in the post above as well)

[github-actions]

This issue is idle because it has been open for 14 days with no activity.

[zijchen]

As work as been underway on v2, the proposed interface is revised to simplify and allow the potential for extension to additional sqlpackage commands.

• The action parameter has become required and the file-type parameter is removed from the proposal.
• The arguments option is specified as sqlpackage-arguments

(this is being updated in the post above as well)

After further discussion and user feedback, we have replaced the script action with go-sqlcmd in #113. We are keeping the arguments input from v1 to be for additional sqlpackage arguments when Publishing to database, and be for additional sqlcmd arguments when Script action is used. This has been updated in the description above.

There are some breaking changes from the go-sqlcmd arguments that may require changes to your yaml files still: https://docs.microsoft.com/en-us/sql/tools/go-sqlcmd-utility?view=sql-server-ver16#breaking-changes-from-sqlcmd

[dzsquared]

Work for v2 is nearing completion and is available for testing through https://github.com/azure/sql-action/tree/v2

Prior to release at https://github.com/Azure/sql-action/releases the v2 work can be used for evaluation and we appreciate you reporting any issues you find.

There are a few items to finish up in the v2 milestone and we anticipate the release will be ready in September.

[github-actions]

This issue is idle because it has been open for 14 days with no activity.