testcontainers
Implement withDatabase() in MSSQLServerContainer
Hello,
First of all, thanks for your great work!
I have a requirement to set READ_COMMITED_SNAPSHOT ON in the MSSQLServer database, and due to a MSSQLServer limitation that can't be applied to the default master database:
ALTER DATABASE master SET READ_COMMITTED_SNAPSHOT ON
throws
SQL Error [5058] [S0002]: Option 'READ_COMMITTED_SNAPSHOT' cannot be set in database 'master'.
This setting prevents read locks from being created and is the default setting in AzureSQL DB - in my case with testContainers my integration tests intermittently fail with CouldNotAquireLockException because of that.
For this to work OOTB for me I think the MSSQLServerContainer class would have to have the withDatabaseName() method implemented, and add the logic for creating the database before letting the application code connect to it.
I managed to get by by subclassing the MSSQLServerContainer, but it would be great to have it OOTB.
I can provide a PR if that's helpful.
Thanks!
That seems like not a bad idea, although maintainers might want to have it implemented across all db containers, not just for MSSQL... Anyway, let's wait for their opinion.
Other databases provide withDatabaseName to set an env var which is offered by the official image. In MSSQL, there is no such env var, see all env var available here. I've found this cmd /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Yukon900 -d master -i setup.sql where setup.sql contains
CREATE DATABASE DemoData;
GO
USE DemoData;
GO
CREATE TABLE Products (ID int, ProductName nvarchar(max));
GO
wondering if sqlcmd -S localhost -U sa -P Yukon900 -d master -q "create database DemoData" works.
@tomazfernandes can you share more details about your implementation, please? Let's discuss it first :)
Hi @alxxyz and @eddumelendez, thanks for looking into this.
I came up with a simple workaround by subclassing MSSQLServerContainer.java and overriding a few methods.
It's been a while - not sure how the codebase looks these days. But basically I added a hook in the waitUntilContainerStarted method that creates the table after the connection has been stablished if a databaseName was provided.
I added it there because it's where I had access without changing the codebase - probably there's a better place for it, or as @eddumelendez suggested, perhaps something using the CLI.
Here's the resulting class:
public class DatabaseCreatingMSSQLServerContainer extends MSSQLServerContainer<DatabaseCreatingMSSQLServerContainer> {
private String databaseName;
public DatabaseCreatingMSSQLServerContainer(DockerImageName dockerImageName) {
super(dockerImageName);
}
@Override
public DatabaseCreatingMSSQLServerContainer withDatabaseName(String dbName) {
this.databaseName = dbName;
return self();
}
@Override
public String getJdbcUrl() {
return createJdbcUrl(true);
}
private String createJdbcUrl(boolean addDatabaseName) {
String additionalUrlParams = constructUrlParameters(";", ";");
String url = "jdbc:sqlserver://" + getHost() + ":" + getMappedPort(MS_SQL_SERVER_PORT);
if (addDatabaseName && this.databaseName != null) {
url += addDatabaseName();
}
return url + additionalUrlParams;
}
private String addDatabaseName() {
return databaseName != null ? ";database=" + databaseName : "";
}
@Override
public String getDatabaseName() {
return databaseName;
}
@Override
protected void waitUntilContainerStarted() {
logger().info("Waiting for database connection to become available at {} using query '{}'", createJdbcUrl(false), getTestQueryString());
// Repeatedly try and open a connection to the DB and execute a test query
long start = System.currentTimeMillis();
try {
while (System.currentTimeMillis() < start + (1000 * 240)) {
try {
if (!isRunning()) {
Thread.sleep(100L);
continue; // Don't attempt to connect yet
}
try (Connection connection = createConnection(() -> createJdbcUrl(false))) {
boolean testQuerySucceeded = connection.createStatement().execute(this.getTestQueryString());
if (testQuerySucceeded) {
maybeCreateDatabase();
break;
}
}
} catch (NoDriverFoundException e) {
// we explicitly want this exception to fail fast without retries
throw e;
} catch (Exception e) {
// ignore so that we can try again
logger().debug("Failure when trying test query", e);
Thread.sleep(100L);
}
}
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
throw new ContainerLaunchException("Container startup wait was interrupted", e);
}
}
private Connection createConnection(Supplier<String> jdbcUrlSupplier) throws SQLException, NoDriverFoundException {
final Properties info = new Properties();
info.put("user", this.getUsername());
info.put("password", this.getPassword());
final String url = jdbcUrlSupplier.get();
final Driver jdbcDriverInstance = getJdbcDriverInstance();
SQLException lastException = null;
try {
long start = System.currentTimeMillis();
// give up if we hit the time limit or the container stops running for some reason
while (System.currentTimeMillis() < start + (1000 * 240) && isRunning()) {
try {
return jdbcDriverInstance.connect(url, info);
} catch (SQLException e) {
lastException = e;
Thread.sleep(100L);
}
}
} catch (InterruptedException e) {
Thread.currentThread().interrupt();
}
throw new SQLException("Could not create new connection", lastException);
}
private void maybeCreateDatabase() {
if (this.databaseName == null) {
return;
}
try {
Connection connection = createConnection(() -> createJdbcUrl(false));
connection.createStatement().execute("CREATE DATABASE " + this.databaseName);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
Hope it helps!
From the top of my head, perhaps adding a hook such as executeAfterSuccessfulConnection or similar in the parent class, to be optionally implemented by subclasses such as MSSQLServerContainer.
JdbcDatabaseContainer already uses the containerIsStarted to run the scripts. I think we can take the advantage of it and run the sql command to create the database.
@Override
protected void containerIsStarted(InspectContainerResponse containerInfo) {
super.containerIsStarted(containerInfo);
execInContainer("sqlcmd", ...)
}
Sure! Though I remember there was some lifecycle gotcha in what I was trying to achieve - not sure what exactly.
I think the use case would be creating the container, a myDB database, and running ALTER DATABASE myDB SET READ_COMMITTED_SNAPSHOT ON before the application starts, with everything from TC and the app pointing to this new DB.
I remember we used flyway for schema migration with Spring Data JPA, and it tried to connect to myDB before we had a chance to do anything.
Not sure if there's anything similar in TC's startup that it'd try to connect to the DB early.
Other than that no worries, just trying to remember what I went through back then
So, currently i created a custom class
public class CustomMSSQLServerContainer<SELF extends CustomMSSQLServerContainer<SELF>> extends MSSQLServerContainer<SELF> {
private String databaseName;
@Override
public String getDatabaseName() {
return databaseName;
}
@Override
public SELF withDatabaseName(String databaseName) {
this.databaseName = databaseName;
return self();
}
@Override
protected void containerIsStarted(InspectContainerResponse containerInfo) {
super.containerIsStarted(containerInfo);
try {
String myDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
Class.forName(myDriver);
Connection connection = DriverManager.getConnection(getJdbcUrl(), getUsername(), getPassword());
connection.createStatement().execute("CREATE DATABASE " + this.databaseName);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
Can this be integrated in the module?
Another bump to this issue, This is blocking my progress on my tasking, as I can't move off of the schema's specified database...
@GregJohnStewart Did you consider any of the mentioned workarounds above? That should at least unblock you there.
I think my workaround is that in actuality my script does not mention the database name as much as once thought