1. Packages
  2. MSSQL
  3. API Docs
  4. Script
Microsoft SQL Server v0.0.8 published on Wednesday, Nov 1, 2023 by pulumiverse

mssql.Script

Explore with Pulumi AI

mssql logo
Microsoft SQL Server v0.0.8 published on Wednesday, Nov 1, 2023 by pulumiverse

    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

    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 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.String(fmt.Sprintf("SELECT COUNT(*) AS [is_enabled] FROM sys.change_tracking_databases WHERE database_id=%v", test.Id)),
    			DeleteScript: pulumi.String(fmt.Sprintf("ALTER DATABASE [%v] SET CHANGE_TRACKING = OFF", test.Name)),
    			UpdateScript: pulumi.String(fmt.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
    	})
    }
    
    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());
    
        }
    }
    
    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",
        })
    
    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",
        },
    });
    
    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

    The Script resource accepts the following input properties:

    DatabaseId string
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    ReadScript 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 against state 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.
    UpdateScript string
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    CreateScript string
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    DeleteScript string
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    DatabaseId string
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    ReadScript 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 against state 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.
    UpdateScript string
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    CreateScript string
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    DeleteScript string
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    databaseId String
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    readScript 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 against state 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.
    updateScript String
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    createScript String
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    deleteScript String
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    databaseId string
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    readScript 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 against state 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.
    updateScript string
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    createScript string
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    deleteScript 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 or SELECT 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 against state 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 by read_script
    create_script str
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    delete_script str
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    databaseId String
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    readScript 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 against state 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.
    updateScript String
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    createScript String
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    deleteScript 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.
    The following state arguments are supported:
    CreateScript string
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    DatabaseId string
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    DeleteScript string
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    ReadScript 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 against state 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.
    UpdateScript string
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    CreateScript string
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    DatabaseId string
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    DeleteScript string
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    ReadScript 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 against state 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.
    UpdateScript string
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    createScript String
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    databaseId String
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    deleteScript String
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    readScript 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 against state 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.
    updateScript String
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    createScript string
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    databaseId string
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    deleteScript string
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    readScript 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 against state 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.
    updateScript string
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script
    create_script str
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    database_id str
    ID of database. Can be retrieved using mssql.Database or SELECT 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 against state 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 by read_script
    createScript String
    SQL script executed when the resource does not exist in Terraform state. When not provided, update_script will be used to create the resource.
    databaseId String
    ID of database. Can be retrieved using mssql.Database or SELECT DB_ID('<db_name>').
    deleteScript String
    SQL script executed when the resource is being destroyed. When not provided, no action will be taken during resource destruction.
    readScript 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 against state 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.
    updateScript String
    SQL script executed when the desired state specified in state attribute does not match the state returned by read_script

    Package Details

    Repository
    mssql pulumiverse/pulumi-mssql
    License
    Apache-2.0
    Notes
    This Pulumi package is based on the mssql Terraform Provider.
    mssql logo
    Microsoft SQL Server v0.0.8 published on Wednesday, Nov 1, 2023 by pulumiverse