https://pulumi.com logo
#dotnet
Title
# dotnet
l

limited-carpenter-34991

06/10/2020, 7:58 PM
Is there a way to deploy ef core migrations during a pulumi progamm? I want to deploy database changes after azure sql database initialization inside my stack.
b

better-rainbow-14549

06/17/2020, 10:05 AM
i wrote a dynamic provider to do just that, but it only works in typescript
last time i checked dotnet pulumi didnt support dynamic providers, maybe thats changed
happy to share the code tho if it helps
l

limited-carpenter-34991

06/19/2020, 10:45 AM
yes please share, maybe i can addept someting.
b

better-rainbow-14549

06/26/2020, 12:35 PM
Sorry I've not been back on slack so didn't see your reply.
Copy code
import { all, dynamic, ID, Input, output } from "@pulumi/pulumi";
import * as azure from "@pulumi/azure";
import * as child_process from "child_process";
import * as path from "path";
import * as fs from "fs";
import * as crypto from "crypto";

export type DbSchemaProps = {
    readonly schemaName?: Input<string>;
    readonly serverUri: Input<string>;
    readonly databaseName: Input<string>;
    readonly schemaFileName: Input<string>;
};

type DbSchemaOutProps = Required<DbSchemaProps> & {
    schemaFileHash: string;
};

export class DbSchemaProvider implements dynamic.ResourceProvider {

    private static command = (serverUri: string, databaseName: string, command: string) => {
        const result = child_process.spawnSync("sqlutil", [
            "-t", <string>process.env.ARM_TENANT_ID,
            "-m", "ServicePrincipal",
            "-A", <string>process.env.ARM_CLIENT_ID,
            "-P", <string>process.env.ARM_CLIENT_SECRET,
            "-c", `Server=tcp:${serverUri};Database=${databaseName}`,
            "--stdin"
        ], {
            input: command
        });

        return ({
            idPrefix: `${serverUri}::${databaseName}`,
            success: result.status == 0,
            output: result.stdout.toString("UTF-8"),
            error: result.stderr.toString("UTF-8")
        });
    };

    private static getSchemaFile = (filePath: string): { schema: string, hash: string } => {
        const schema = fs.readFileSync(path.resolve(filePath)).toString("UTF8");
        const hash = crypto.createHash('sha256').update(schema).digest('hex');

        return ({
            schema: schema,
            hash: hash
        });
    };

    check: (olds: DbSchemaOutProps, news: DbSchemaProps) => Promise<dynamic.CheckResult>;
    create: (inputs: DbSchemaOutProps) => Promise<dynamic.CreateResult>;
    diff: (id: ID, olds: DbSchemaOutProps, news: DbSchemaOutProps) => Promise<dynamic.DiffResult>;
    update: (id: ID, olds: DbSchemaOutProps, news: DbSchemaOutProps) => Promise<dynamic.UpdateResult>;
    delete: (id: ID, props: DbSchemaOutProps) => Promise<void>;

    constructor() {

        if (process.env.ARM_TENANT_ID == undefined) throw new Error("ARM_TENANT_ID is not configured.");
        if (process.env.ARM_CLIENT_ID == undefined) throw new Error("ARM_CLIENT_ID is not configured.");
        if (process.env.ARM_CLIENT_SECRET == undefined) throw new Error("ARM_CLIENT_SECRET is not configured.");

        this.check = (_olds: DbSchemaOutProps, props: DbSchemaProps) => new Promise<dynamic.CheckResult>((resolve) => {
            all([
                props.schemaName,
                props.serverUri,
                props.databaseName,
                props.schemaFileName
            ]).apply(([
                schemaName,
                serverUri,
                databaseName,
                schemaFileName
            ]) => {
                resolve({
                    inputs: {
                        schemaName: schemaName,
                        serverUri: serverUri,
                        databaseName: databaseName,
                        schemaFileName: schemaFileName
                    },
                    failures: fs.existsSync(path.resolve(schemaFileName)) ? undefined : [{ property: "schemaFileName", reason: `File not found: ${path.resolve(schemaFileName)}` }]
                });
            })
        });

        this.create = (props: DbSchemaOutProps) => new Promise<dynamic.CreateResult>((resolve, reject) => {
            all([
                props.schemaName,
                props.serverUri,
                props.databaseName,
                props.schemaFileName
            ]).apply(([
                schemaName,
                serverUri,
                databaseName,
                schemaFileName
            ]) => {
                const script = DbSchemaProvider.getSchemaFile(schemaFileName);
                let result = DbSchemaProvider.command(serverUri, databaseName, script.schema);
                if (result.success) {
                    resolve({
                        id: `${result.idPrefix}::DbSchema::${schemaName}`,
                        outs: {
                            schemaName: schemaName,
                            serverUri: serverUri,
                            databaseName: databaseName,
                            schemaFileName: schemaFileName,
                            schemaFileHash: script.hash
                        }
                    });
                } else {
                    reject({
                        message: `Failed: (${serverUri}/${databaseName}) Deploy ${schemaFileName}\n${result.error}`
                    });
                }
            });
        });

        this.diff = (_: ID, olds: DbSchemaOutProps, news: DbSchemaOutProps) => new Promise<dynamic.DiffResult>((resolve, _) => {
            output(news.schemaFileName).apply(schemaFileName => {
                const script = DbSchemaProvider.getSchemaFile(schemaFileName);

                let props: string[] = [];
                if (schemaFileName != olds.schemaFileName) props.push("schemaFileName");
                if (script.hash != olds.schemaFileHash) props.push("schemaFileHash");

                resolve({
                    replaces: undefined,
                    stables: [],
                    changes: props.length !== 0
                });
            })
        });

        this.update = (_id: string, _: DbSchemaOutProps, news: DbSchemaOutProps) => new Promise<dynamic.UpdateResult>((resolve, reject) => {
            all([
                news.schemaName,
                news.serverUri,
                news.databaseName,
                news.schemaFileName
            ]).apply(([
                schemaName,
                serverUri,
                databaseName,
                schemaFileName
            ]) => {
                const script = DbSchemaProvider.getSchemaFile(schemaFileName);
                let result = DbSchemaProvider.command(serverUri, databaseName, script.schema);
                if (result.success) {
                    resolve({
                        outs: {
                            schemaName: schemaName,
                            serverUri: serverUri,
                            databaseName: databaseName,
                            schemaFileName: schemaFileName,
                            schemaFileHash: script.hash
                        }
                    });
                } else {
                    reject({
                        message: `Failed: (${serverUri}/${databaseName}) Deploy ${schemaFileName}\n${result.error}`
                    });
                }
            });
        });

        this.delete = (_: ID, props: DbSchemaProps) => new Promise<void>((_, reject) => {
            output(props.schemaName).apply(schemaName => {
                reject({
                    message: `Can't roll back a migration of schema ${schemaName}.`
                });
            });
        });
    }
}

export class DbSchema extends dynamic.Resource {
    constructor(name: string, inputs: DbSchemaProps) {
        super(new DbSchemaProvider(), `DbSchema-${name}`, {
            serverUri: inputs.serverUri,
            databaseName: inputs.databaseName,
            schemaName: inputs.schemaName || name,
            schemaFileName: inputs.schemaFileName
        });
    }
}
sqlutil binary is a custom tool that just executes the script. it's using the azure credentials it gets from ARM_ env variables - clientSecret isnt available from azure.getClientConfig() if you use the other methods of setting up the azure provider it looks for the file during the 'check' step, hashes it and only stores that hash inside the pulumi state to avoid having a huge sql blob in there. it's been working fine for us changing the file and having it redeploy the schema on top, but the schema script has to be idempotent for that to work. it doesn't try and redeploy if the hash matches though.