MS SQL Server Module

Microsoft SQL Server is a relational database management system developed by Microsoft. It provides a platform for efficiently storing, managing, and retrieving structured data. MSSQL offers features for data storage, retrieval, manipulation, and analysis, making it a key component in various applications ranging from small-scale projects to enterprise-level systems.

Install

npm install @testcontainers/mssqlserver --save-dev

Examples

EULA Acceptance

Due to licencing restrictions you are required to accept an EULA for this container image. To indicate that you accept the MS SQL Server image EULA, call the acceptLicense() method.

Please see the [`microsoft-mssql-server` image documentation](https://hub.docker.com/_/microsoft-mssql-server#environment-variables) for a link to the EULA document.

Connect and execute query:

it("should connect and return a query result", async () => {
  await using container = await new MSSQLServerContainer(IMAGE).acceptLicense().start();

  const sqlConfig: config = {
    user: container.getUsername(),
    password: container.getPassword(),
    database: container.getDatabase(),
    server: container.getHost(),
    port: container.getPort(),
    pool: {
      max: 1,
      min: 0,
      idleTimeoutMillis: 30000,
    },
    options: {
      trustServerCertificate: true,
    },
  };

  const connection = await sql.connect(sqlConfig);

  const { recordset } = await connection.query`SELECT 1;`;
  expect(recordset).toStrictEqual([{ "": 1 }]);

  await connection.close();
});

⋯

  await using container = await new MSSQLServerContainer(IMAGE).acceptLicense().start();

  const connectionString = container.getConnectionUri();
  const connection = await sql.connect(connectionString);

  const { recordset } = await connection.query`SELECT 1;`;
  expect(recordset).toStrictEqual([{ "": 1 }]);

  await connection.close();

  ⋯

  await using container = await new MSSQLServerContainer(IMAGE).acceptLicense().withPassword("I!@M#$eCur3").start();

  const connectionString = container.getConnectionUri();
  const connection = await sql.connect(connectionString);

  const { recordset } = await connection.query`SELECT 1;`;
  expect(recordset).toStrictEqual([{ "": 1 }]);

  await connection.close();

Connect and execute query using URI:

it("should connect and return a query result with database URI", async () => {
  await using container = await new MSSQLServerContainer(IMAGE).acceptLicense().start();

  const connectionString = container.getConnectionUri();
  const connection = await sql.connect(connectionString);

  const { recordset } = await connection.query`SELECT 1;`;
  expect(recordset).toStrictEqual([{ "": 1 }]);

  await connection.close();
});

Connect and execute query using a valid custom password:

it("should connect and return a query result with valid custom password", async () => {
  await using container = await new MSSQLServerContainer(IMAGE).acceptLicense().withPassword("I!@M#$eCur3").start();

  const connectionString = container.getConnectionUri();
  const connection = await sql.connect(connectionString);

  const { recordset } = await connection.query`SELECT 1;`;
  expect(recordset).toStrictEqual([{ "": 1 }]);

  await connection.close();
});

⋯

it("should throw error with invalid password", async () => {
  const container = new MSSQLServerContainer(IMAGE).acceptLicense().withPassword("password");
  await expect(container.start()).rejects.toThrow(
    Error('Log stream ended and message "/.*Recovery is complete.*/" was not received')
  );
});

Throw error with an invalid password:

it("should throw error with invalid password", async () => {
  const container = new MSSQLServerContainer(IMAGE).acceptLicense().withPassword("password");
  await expect(container.start()).rejects.toThrow(
    Error('Log stream ended and message "/.*Recovery is complete.*/" was not received')
  );
});

Use a different edition:

it("should start db with express edition", async () => {
  await using container = await new MSSQLServerContainer(IMAGE)
    .withWaitForMessage(/.*Attribute synchronization manager initialized*/)
    .acceptLicense()
    .withEnvironment({ MSSQL_PID: "Express" })
    .start();

  const { output, exitCode } = await container.exec([
    "/opt/mssql-tools/bin/sqlcmd",
    "-S",
    container.getHost(),
    "-U",
    container.getUsername(),
    "-P",
    container.getPassword(),
    "-Q",
    "SELECT @@VERSION;",
  ]);

  expect(exitCode).toBe(0);
  expect(output).toContain("Express Edition");
});