mssql.Script
Explore with Pulumi AI
Allows execution of arbitrary SQL scripts to check state and apply desired state.
Note This resource is meant to be an escape hatch for all cases not supported by the provider’s resources. Whenever possible, use dedicated resources, which offer better plan, validation and error reporting.
Example Usage
import * as pulumi from "@pulumi/pulumi";
import * as mssql from "@pulumi/mssql";
import * as mssql from "@pulumiverse/mssql";
const test = mssql.getDatabase({
name: "test",
});
const cdc = new mssql.Script("cdc", {
databaseId: test.then(test => test.id),
readScript: test.then(test => `SELECT COUNT(*) AS [is_enabled] FROM sys.change_tracking_databases WHERE database_id=${test.id}`),
deleteScript: test.then(test => `ALTER DATABASE [${test.name}] SET CHANGE_TRACKING = OFF`),
updateScript: Promise.all([test, test]).then(([test, test1]) => `IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id=${test.id}) = 0
ALTER DATABASE [${test1.name}] SET CHANGE_TRACKING = ON
`),
state: {
is_enabled: "1",
},
});
import pulumi
import pulumi_mssql as mssql
import pulumiverse_mssql as mssql
test = mssql.get_database(name="test")
cdc = mssql.Script("cdc",
database_id=test.id,
read_script=f"SELECT COUNT(*) AS [is_enabled] FROM sys.change_tracking_databases WHERE database_id={test.id}",
delete_script=f"ALTER DATABASE [{test.name}] SET CHANGE_TRACKING = OFF",
update_script=f"""IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id={test.id}) = 0
ALTER DATABASE [{test.name}] SET CHANGE_TRACKING = ON
""",
state={
"is_enabled": "1",
})
package main
import (
"fmt"
"github.com/pulumi/pulumi/sdk/v3/go/pulumi"
"github.com/pulumiverse/pulumi-mssql/sdk/go/mssql"
)
func main() {
pulumi.Run(func(ctx *pulumi.Context) error {
test, err := mssql.LookupDatabase(ctx, &mssql.LookupDatabaseArgs{
Name: "test",
}, nil)
if err != nil {
return err
}
_, err = mssql.NewScript(ctx, "cdc", &mssql.ScriptArgs{
DatabaseId: pulumi.String(test.Id),
ReadScript: pulumi.Sprintf("SELECT COUNT(*) AS [is_enabled] FROM sys.change_tracking_databases WHERE database_id=%v", test.Id),
DeleteScript: pulumi.Sprintf("ALTER DATABASE [%v] SET CHANGE_TRACKING = OFF", test.Name),
UpdateScript: pulumi.Sprintf("IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id=%v) = 0\n ALTER DATABASE [%v] SET CHANGE_TRACKING = ON\n", test.Id, test.Name),
State: pulumi.StringMap{
"is_enabled": pulumi.String("1"),
},
})
if err != nil {
return err
}
return nil
})
}
using System.Collections.Generic;
using System.Linq;
using Pulumi;
using Mssql = Pulumi.Mssql;
using Mssql = Pulumiverse.Mssql;
return await Deployment.RunAsync(() =>
{
var test = Mssql.GetDatabase.Invoke(new()
{
Name = "test",
});
var cdc = new Mssql.Script("cdc", new()
{
DatabaseId = test.Apply(getDatabaseResult => getDatabaseResult.Id),
ReadScript = $"SELECT COUNT(*) AS [is_enabled] FROM sys.change_tracking_databases WHERE database_id={test.Apply(getDatabaseResult => getDatabaseResult.Id)}",
DeleteScript = $"ALTER DATABASE [{test.Apply(getDatabaseResult => getDatabaseResult.Name)}] SET CHANGE_TRACKING = OFF",
UpdateScript = Output.Tuple(test, test).Apply(values =>
{
var test = values.Item1;
var test1 = values.Item2;
return @$"IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id={test.Apply(getDatabaseResult => getDatabaseResult.Id)}) = 0
ALTER DATABASE [{test1.Name}] SET CHANGE_TRACKING = ON
";
}),
State =
{
{ "is_enabled", "1" },
},
});
});
package generated_program;
import com.pulumi.Context;
import com.pulumi.Pulumi;
import com.pulumi.core.Output;
import com.pulumi.mssql.MssqlFunctions;
import com.pulumi.mssql.inputs.GetDatabaseArgs;
import com.pulumi.mssql.Script;
import com.pulumi.mssql.ScriptArgs;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.io.File;
import java.nio.file.Files;
import java.nio.file.Paths;
public class App {
public static void main(String[] args) {
Pulumi.run(App::stack);
}
public static void stack(Context ctx) {
final var test = MssqlFunctions.getDatabase(GetDatabaseArgs.builder()
.name("test")
.build());
var cdc = new Script("cdc", ScriptArgs.builder()
.databaseId(test.applyValue(getDatabaseResult -> getDatabaseResult.id()))
.readScript(String.format("SELECT COUNT(*) AS [is_enabled] FROM sys.change_tracking_databases WHERE database_id=%s", test.applyValue(getDatabaseResult -> getDatabaseResult.id())))
.deleteScript(String.format("ALTER DATABASE [%s] SET CHANGE_TRACKING = OFF", test.applyValue(getDatabaseResult -> getDatabaseResult.name())))
.updateScript("""
IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id=%s) = 0
ALTER DATABASE [%s] SET CHANGE_TRACKING = ON
", test.applyValue(getDatabaseResult -> getDatabaseResult.id()),test.applyValue(getDatabaseResult -> getDatabaseResult.name())))
.state(Map.of("is_enabled", "1"))
.build());
}
}
resources:
cdc:
type: mssql:Script
properties:
databaseId: ${test.id}
readScript: SELECT COUNT(*) AS [is_enabled] FROM sys.change_tracking_databases WHERE database_id=${test.id}
deleteScript: ALTER DATABASE [${test.name}] SET CHANGE_TRACKING = OFF
updateScript: |
IF (SELECT COUNT(*) FROM sys.change_tracking_databases WHERE database_id=${test.id}) = 0
ALTER DATABASE [${test.name}] SET CHANGE_TRACKING = ON
state:
is_enabled: '1'
variables:
test:
fn::invoke:
Function: mssql:getDatabase
Arguments:
name: test
Create Script Resource
Resources are created with functions called constructors. To learn more about declaring and configuring resources, see Resources.
Constructor syntax
new Script(name: string, args: ScriptArgs, opts?: CustomResourceOptions);
@overload
def Script(resource_name: str,
args: ScriptArgs,
opts: Optional[ResourceOptions] = None)
@overload
def Script(resource_name: str,
opts: Optional[ResourceOptions] = None,
database_id: Optional[str] = None,
read_script: Optional[str] = None,
state: Optional[Mapping[str, str]] = None,
update_script: Optional[str] = None,
create_script: Optional[str] = None,
delete_script: Optional[str] = None)
func NewScript(ctx *Context, name string, args ScriptArgs, opts ...ResourceOption) (*Script, error)
public Script(string name, ScriptArgs args, CustomResourceOptions? opts = null)
public Script(String name, ScriptArgs args)
public Script(String name, ScriptArgs args, CustomResourceOptions options)
type: mssql:Script
properties: # The arguments to resource properties.
options: # Bag of options to control resource's behavior.
Parameters
- name string
- The unique name of the resource.
- args ScriptArgs
- The arguments to resource properties.
- opts CustomResourceOptions
- Bag of options to control resource's behavior.
- resource_name str
- The unique name of the resource.
- args ScriptArgs
- The arguments to resource properties.
- opts ResourceOptions
- Bag of options to control resource's behavior.
- ctx Context
- Context object for the current deployment.
- name string
- The unique name of the resource.
- args ScriptArgs
- The arguments to resource properties.
- opts ResourceOption
- Bag of options to control resource's behavior.
- name string
- The unique name of the resource.
- args ScriptArgs
- The arguments to resource properties.
- opts CustomResourceOptions
- Bag of options to control resource's behavior.
- name String
- The unique name of the resource.
- args ScriptArgs
- The arguments to resource properties.
- options CustomResourceOptions
- Bag of options to control resource's behavior.
Constructor example
The following reference example uses placeholder values for all input properties.
var scriptResource = new Mssql.Script("scriptResource", new()
{
DatabaseId = "string",
ReadScript = "string",
State =
{
{ "string", "string" },
},
UpdateScript = "string",
CreateScript = "string",
DeleteScript = "string",
});
example, err := mssql.NewScript(ctx, "scriptResource", &mssql.ScriptArgs{
DatabaseId: pulumi.String("string"),
ReadScript: pulumi.String("string"),
State: pulumi.StringMap{
"string": pulumi.String("string"),
},
UpdateScript: pulumi.String("string"),
CreateScript: pulumi.String("string"),
DeleteScript: pulumi.String("string"),
})
var scriptResource = new Script("scriptResource", ScriptArgs.builder()
.databaseId("string")
.readScript("string")
.state(Map.of("string", "string"))
.updateScript("string")
.createScript("string")
.deleteScript("string")
.build());
script_resource = mssql.Script("scriptResource",
database_id="string",
read_script="string",
state={
"string": "string",
},
update_script="string",
create_script="string",
delete_script="string")
const scriptResource = new mssql.Script("scriptResource", {
databaseId: "string",
readScript: "string",
state: {
string: "string",
},
updateScript: "string",
createScript: "string",
deleteScript: "string",
});
type: mssql:Script
properties:
createScript: string
databaseId: string
deleteScript: string
readScript: string
state:
string: string
updateScript: string
Script Resource Properties
To learn more about resource properties and how to use them, see Inputs and Outputs in the Architecture and Concepts docs.
Inputs
In Python, inputs that are objects can be passed either as argument classes or as dictionary literals.
The Script resource accepts the following input properties:
- Database
Id string - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - Read
Script string - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - State Dictionary<string, string>
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - Update
Script string - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- Create
Script string - Delete
Script string - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- Database
Id string - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - Read
Script string - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - State map[string]string
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - Update
Script string - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- Create
Script string - Delete
Script string - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- database
Id String - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - read
Script String - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state Map<String,String>
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update
Script String - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create
Script String - delete
Script String - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- database
Id string - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - read
Script string - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state {[key: string]: string}
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update
Script string - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create
Script string - delete
Script string - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- database_
id str - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - read_
script str - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state Mapping[str, str]
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update_
script str - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create_
script str - delete_
script str - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- database
Id String - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - read
Script String - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state Map<String>
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update
Script String - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create
Script String - delete
Script String - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
Outputs
All input properties are implicitly available as output properties. Additionally, the Script resource produces the following output properties:
- Id string
- The provider-assigned unique ID for this managed resource.
- Id string
- The provider-assigned unique ID for this managed resource.
- id String
- The provider-assigned unique ID for this managed resource.
- id string
- The provider-assigned unique ID for this managed resource.
- id str
- The provider-assigned unique ID for this managed resource.
- id String
- The provider-assigned unique ID for this managed resource.
Look up Existing Script Resource
Get an existing Script resource’s state with the given name, ID, and optional extra properties used to qualify the lookup.
public static get(name: string, id: Input<ID>, state?: ScriptState, opts?: CustomResourceOptions): Script
@staticmethod
def get(resource_name: str,
id: str,
opts: Optional[ResourceOptions] = None,
create_script: Optional[str] = None,
database_id: Optional[str] = None,
delete_script: Optional[str] = None,
read_script: Optional[str] = None,
state: Optional[Mapping[str, str]] = None,
update_script: Optional[str] = None) -> Script
func GetScript(ctx *Context, name string, id IDInput, state *ScriptState, opts ...ResourceOption) (*Script, error)
public static Script Get(string name, Input<string> id, ScriptState? state, CustomResourceOptions? opts = null)
public static Script get(String name, Output<String> id, ScriptState state, CustomResourceOptions options)
Resource lookup is not supported in YAML
- name
- The unique name of the resulting resource.
- id
- The unique provider ID of the resource to lookup.
- state
- Any extra arguments used during the lookup.
- opts
- A bag of options that control this resource's behavior.
- resource_name
- The unique name of the resulting resource.
- id
- The unique provider ID of the resource to lookup.
- name
- The unique name of the resulting resource.
- id
- The unique provider ID of the resource to lookup.
- state
- Any extra arguments used during the lookup.
- opts
- A bag of options that control this resource's behavior.
- name
- The unique name of the resulting resource.
- id
- The unique provider ID of the resource to lookup.
- state
- Any extra arguments used during the lookup.
- opts
- A bag of options that control this resource's behavior.
- name
- The unique name of the resulting resource.
- id
- The unique provider ID of the resource to lookup.
- state
- Any extra arguments used during the lookup.
- opts
- A bag of options that control this resource's behavior.
- Create
Script string - Database
Id string - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - Delete
Script string - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- Read
Script string - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - State Dictionary<string, string>
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - Update
Script string - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- Create
Script string - Database
Id string - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - Delete
Script string - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- Read
Script string - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - State map[string]string
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - Update
Script string - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create
Script String - database
Id String - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - delete
Script String - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- read
Script String - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state Map<String,String>
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update
Script String - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create
Script string - database
Id string - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - delete
Script string - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- read
Script string - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state {[key: string]: string}
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update
Script string - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create_
script str - database_
id str - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - delete_
script str - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- read_
script str - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state Mapping[str, str]
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update_
script str - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
- create
Script String - database
Id String - ID of database. Can be retrieved using
mssql.Database
orSELECT DB_ID('<db_name>')
. - delete
Script String - SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
- read
Script String - SQL script returning current state of the DB. It must return single-row result set where column names match the keys of
state
map and all values are strings that will be compared againststate
to determine if the resource state matches DB state. - state Map<String>
- Desired state of the DB. It is arbitrary map of string values that will be compared against the values returned by the
read_script
. - update
Script String - SQL script executed when the desired state specified in
state
attribute does not match the state returned byread_script
Package Details
- Repository
- mssql pulumiverse/pulumi-mssql
- License
- Apache-2.0
- Notes
- This Pulumi package is based on the
mssql
Terraform Provider.