SQL in Docker via Node

create-docker-sql.js

import { Docker } from "docker-cli-js";
import path from "path";
import shell from "shelljs";
import mkdirp from "mkdirp";

const delay = ms => new Promise(resolve => setTimeout(resolve, ms));
const isWin = process.platform === "win32";
const docker = new Docker();

const exec = str => {
    // console.log(str.replace(/(\s*[\r\n?]+\s*)+/g, " ").trim());
    // return;

    const { code, stderr } = shell.exec(
        str.replace(/(\s*[\r\n?]+\s*)+/g, " ").trim()
    );
    if (code !== 0) throw Object.assign(new Error("Unexpected error."), { code });
};

export const clearContainer = async name => {
    const containers = await docker.command("ps --all");
    const container = containers.containerList.filter(c => c.names === name)[0];
    if (container) {
        if (/^up/i.test(container.status)) {
            console.log(`Stopping SQL Server (${name})`);
            await docker.command(`kill "${name}"`);
        }
        console.log(`Removing SQL Server (${name})`);
        await docker.command(`rm "${name}"`);
    }
};

export const waitForSqlAvailability = async (name, sapwd) => {
    let done = false;
    await delay(1000);
    process.stdout.write(`\nWaiting for ${name} to start.`);
    while (!done) {
        await delay(1500);
        process.stdout.write(".");
        const result = shell.exec(
            `docker exec -i "${name}" /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "${sapwd}" -Q "SELECT Name FROM sys.Databases" > ${
            isWin ? "nul" : "/dev/null"
            } 2>&1`
        );
        done = result.code === 0;
    }
};

export default async function createDockerSql(name, port, sapwd = "Let_Me_In") {
    await clearContainer(name);

    console.log(`Creating New SQL Server (${name})`);
    var volMnt = path.join(process.env.HOME || process.env.USERPROFILE, "./data");
    mkdirp.sync(volMnt);
    exec(
        `
      docker run 
        -m 2GB 
        --restart unless-stopped 
        --name "${name}" 
        -h "${name}" 
        -e "ACCEPT_EULA=Y" 
        -e "MSSQL_SA_PASSWORD=${sapwd}" 
        -p ${port}:1433 
        -v "${volMnt}":"/data"
        -d "microsoft/mssql-server-linux:2017-latest"
    `
    );
    await waitForSqlAvailability(name, sapwd);
    console.log(
        `\nStarted SQL Server (${name}) on localhost,${port} user:sa, pass:${sapwd}`
    );
    await delay(100);
}

docker-net.js

import { Docker } from "docker-cli-js";
const docker = new Docker();

export const createNetIfNotExists = async network => {
  const networks = await docker.command("network ls");
  const net = networks.network.filter(n => n.name === network)[0];
  if (!net) {
    console.log(`Creating docker network (${network})`);
    await docker.command(`network create ${network}`);
  }
};

export const removeNet = async network => {
  const networks = await docker.command("network ls");
  const net = networks.network.filter(n => n.name === network)[0];
  if (net) {
    console.log(`Removing docker network (${network})`);
    await docker.command(`network rm ${network}`);
  }
};

export const connectNetwork = async (network, container) => {
  await createNetIfNotExists(network);
  const result = await docker.command(`network inspect ${network}`);
  const net = result.object[0];
  if (
    net &&
    !Object.values(net.Containers).filter(c => c.Name === container).length
  ) {
    console.log(`Adding ${container} to ${network}`);
    await docker.command(`network connect ${network} ${container}`);
  }
};

export const disconnectNetwork = async (network, container) => {
  await createNetIfNotExists(network);
  const result = await docker.command(`network inspect ${network}`);
  const containers =
    (result && result.object[0] && result.object[0].Containers) || {};
  const c =
    containers[container] ||
    Object.values(containers).filter(c => c.Name === container)[0];

  if (c) {
    console.log(`Removing ${container} from ${network}`);
    await docker.command(`network disconnect ${network} ${container}`);
  }
};

export const removeNetwork = async network => {
  const result = await docker.command(`network inspect ${network}`);
  const containers = Object.keys(
    (result &&
      result.object &&
      result.object[0] &&
      result.object[0].Containers) ||
      {}
  );
  for (const c of containers) {
    await disconnectNetwork(network, c);
  }
  await removeNet(network);
};

reset.js

import path from "path";
import shell from "shelljs";
import { Docker } from "docker-cli-js";
import createDockerSql from "./create-docker-sql";
import { connectNetwork } from "./docker-net";

const docker = new Docker();

const delay = ms => new Promise(resolve => setTimeout(resolve, ms));

// copies and runs a local sql script file inside the docker container's instance
const runSqlScript = scriptPath => {
  console.log(`Running SQL Script (${scriptPath})`);

  // copy the local file into the container
  shell.exec(`docker cp "${scriptPath}" myapp:/tmp/temp.sql`);

  // execute the file in the container on sql
  shell.exec(
    `docker exec -i myapp /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "Let_Me_In" -i /tmp/temp.sql -j -I`
  );

  // remove the file inside the container
  shell.exec(`docker exec myapp rm /tmp/temp.sql`);
};

async function main(skip) {
  if (skip) return;

  await createDockerSql("CHANGEME", 1401, "Let_Me_In");
  await connectNetwork("myapp", "CHANGEME");

  runSqlScript(path.join(__dirname, "../db/full/CHANGEME.sql"));

  // initial/test data script
  runSqlScript(path.join(__dirname, "../db/test-data/initialize.sql"));
}

main(!!module.parent)
  .then(_ => delay(0))
  .then(_ => process.exit())
  .catch(err => {
    console.error(err);
    process.exit(err.code || 666);
  });

sql-builld.js

import path from "path";
import shell from "shelljs";
import fs from "mz/fs";

import createDockerSql, { clearContainer } from "./create-docker-sql";

const delay = ms => new Promise(resolve => setTimeout(resolve, ms));

const exec = str => {
    // console.log(str.replace(/(\s*[\r\n?]+\s*)+/g, " ").trim());
    // return;

    const { code, stderr } = shell.exec(
        str.replace(/(\s*[\r\n?]+\s*)+/g, " ").trim()
    );
    if (code !== 0) throw Object.assign(new Error("Unexpected error."), { code });
};

const getMsBuildPath = async () => {
    var msbuilds = [
        "C:/Program Files (x86)/Microsoft Visual Studio/2019/Professional/MSBuild/Current/Bin/msbuild.exe",
        "C:/Program Files (x86)/Microsoft Visual Studio/2017/Professional/MSBuild/15.0/Bin/msbuild.exe",
        "C:/Program Files (x86)/Microsoft Visual Studio/2017/BuildTools/MSBuild/15.0/Bin/msbuild.exe"
    ];

    for (var fp of msbuilds) {
        if (await fs.exists(fp)) return fp;
    }

    throw new Error("MSBUILD.EXE Not Found.");
};

const getSqlPackage = async () => {
    const packages = [
        "c:/Program Files (x86)/Microsoft Visual Studio/2019/Professional/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/150/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2017/Professional/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/150/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2017/BuildTools/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/150/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2019/Professional/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/140/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2017/Professional/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/140/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2017/BuildTools/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/140/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2019/Professional/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/130/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2017/Professional/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/130/sqlpackage.exe",
        "c:/Program Files (x86)/Microsoft Visual Studio/2017/BuildTools/Common7/IDE/Extensions/Microsoft/SQLDB/DAC/130/sqlpackage.exe"
    ];

    for (var fp of packages) {
        if (await fs.exists(fp)) return fp;
    }

    throw new Error("SQLPACKAGE.EXE Not Found.");
};

async function main(skip) {
    if (!!skip) return;

    if (process.platform !== "win32") {
        console.log("Not a windows enviroment, skipping database build.");
        return;
    }

    const tempDir = path.join(process.cwd(), "./build");
    const outDir = path.join(process.cwd(), "./scripts/db/full");

    const sqlport = 61000 + Math.floor(Math.random() * 1000);
    await createDockerSql(`build-${sqlport}`, sqlport);

    shell.rm("-rf", tempDir);

    // Generate DACPAK
    const [msbuild, sqlpackage] = await Promise.all([
        getMsBuildPath(),
        getSqlPackage()
    ]);
    console.log("Build Using: ", { msbuild, sqlpackage });
    exec(
        ` "${msbuild}"
      "CHANGEME/CHANGEME.sln"
      /WarnAsError
      /nologo
      /nr:false
      /t:"Clean"
      /t:build
      /p:CmdLineInMemoryStorage=True
      /p:Configuration=Debug
      /p:OutputPath="${tempDir}"
      /p:UpdateDatabase=False
    `
    );

    // GENERATE CHANGEME SQL Script
    console.log("\n\n----------");
    exec(
        `
      "${sqlpackage}"
      /Action:Script
      /SourceFile:"${tempDir}/CHANGEME.dacpac"
      /DeployScriptPath:"${outDir}/CHANGEME.sql"
      /TargetDatabaseName:CHANGEME
      /TargetServerName:"localhost,${sqlport}"
      /TargetUser:sa
      /TargetPassword:Let_Me_In
      /p:CreateNewDatabase=True
    `
    );

    await clearContainer(`build-${sqlport}`);
}

main(!!module.parent)
    .then(_ => delay(0))
    .then(_ => process.exit())
    .catch(err => {
        console.error(err);
        process.exit(err.code || 666);
    });