Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL in Docker via Node
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);
}
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);
};
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);
});
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);
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment