MsSqlBuilder - why is WithDatabase private?

[pborgmans]

Is there a reason why WithDatabase is declared private on the MsSqlBuilder?
I would like to work in a self named database instead of master to execute my EF Core migrations.

Thanks in advance!

[HofmeisterAn]

Is there a reason why WithDatabase is declared private on the MsSqlBuilder?

Yes, the Microsoft SQL Server image does not support configuring a database name by default. To create a custom database, you can follow the steps mentioned here. You need to copy the scripts via WithResourceMapping to the container before it starts. Or use as mentioned EF or a client library.

[fretje]

I'm also interested to know why this is not supported.

Also, the link you're referring to, to "create a custom database" talks about building a whole new image. I want to use the standard sqlserver 2022 image (which is perfectly possible with the "WithImage" method), simply with another database name (which was perfectly possible with TestContainers 2.3.0 with the "WithDatabase" method).

Having to build a whole new docker image, just for that, seems like a bit overkill.
Also seems like a regression from the previous version?

[HofmeisterAn]

Does this mean that I have to embed these 3 files into my test project?

Yes, or an implementation of IResourceMapping. There are various patterns and mechanisms available to create a custom database, this is not the only option. Typically, a database migration tool is used. E.g. if you are using EF, all you need to do is:

var connectionString = new SqlConnectionStringBuilder(weatherForecastTest._msSqlContainer.GetConnectionString());
connectionString.InitialCatalog = Guid.NewGuid().ToString("D"); // New database name, EF will create it.

Make sure you are calling Database.EnsureCreated() in your DbContext implementation.

And then use the functions "WithResourceMapping" and "WithEntrypoint" just to create a database?

Exactly, but this allows executing any SQL script (not just creating the database). As I suggested in #855 (comment), we can integrate this feature into Testcontainers. This enables developers to map their own custom SQL scripts and execute them before the container indicates readiness too, similar to the PostgreSQL image.

If you would like to do additional initialization in an image derived from this one, add one or more *.sql, *.sql.gz, or *.sh scripts under /docker-entrypoint-initdb.d (creating the directory if necessary). After the entrypoint calls initdb to create the default postgres user and database, it will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service.

On top of that we can expose a WithDatabase(string) API. I would like to reiterate that this limitation is inherent to the MSSQL image, and not a problem specific to Testcontainers.

[chrisrlewis]

I have stumbled across this thread while trying to get an MsSqlContainer up and running for integration testing. I have spent some time wondering why my db tables and migrations have been appearing in the 'master' database and why it doesn't seem possible to provide a database name.

I've read the above, and considered the reasoning and looked at the https://github.com/microsoft/mssql-docker/tree/80e2a51d0eb1693f2de014fb26d4a414f5a5add5/linux/preview/examples/mssql-customize thread and #801.

For what it's worth, from my perspective I just want to spin up a new database with a supplied name, and I want to be able to do this simply and quickly. In my view, usability is the most important consideration when using third party libraries and tools. The typical project may use very many libraries, etc. and we time-poor software engineers don't have time to delve into finer intricacies of achieving basic things. The code also needs to be readable and understandable for anyone who needs to read it.

Can we have a .WithDatabaseName("mydatabase") for doing this? I've seen this has been available on a JDBC module, and the same for MsSql would be very welcome.

[HofmeisterAn]

I can only reference previous discussions and emphasize that I am in favor of supporting this feature. However, calling WithDatabase(string) must set up container instances with the desired database rather than simply modifying the connection string to a non-existent database. The instances should be immediately usable without relying on additional user configurations, setups, or frameworks. Therefore, I propose enhancing the MSSQL module by overriding the default entrypoint to enable the execution of custom SQL scripts. This can be utilized to create the desired custom database and make WithDatabase(string) public.

• [Enhancement]: Make MsSqlBuilder.WithDatabase public #986 (comment)
• [Enhancement]: Make MsSqlBuilder.WithDatabase public #986 (comment)
• [Enhancement]: Allow users to override database name in the sql connection string #1029 (comment)
• [Enhancement]: Allow users to override database name in the sql connection string #1029 (comment)

[chrisrlewis]

Thanks for your swift reply @HofmeisterAn. I appreciate that there is complexity and a lot to consider outside of my simple use case.

What you mention above (modifying the connection string for a non-existent database) has worked for my use case, though I hear what you say about this being potentially problematic. In my case, I'm using EF Core so - at least at first look - it does give me what I need.

I will keep a look out for updates on the repo. Do you have a timescale for the changes?

[HofmeisterAn]

In my case, I'm using EF Core so - at least at first look - it does give me what I need.

Exactly. It works for someone using EF, but not for someone relying on a plain SQL connection. The changes I propose are not rocket science. If someone really needs it, it can be implemented quite quickly (and contributed). However, as I mentioned, I do not plan to do it using my free resources soon. It is not blocking anyone from using the module at all.

Do you have a timescale for the changes?

No, not really. Maybe if I am in the mood during the upcoming holidays. Otherwise, sometime in 2024 😅.

[iozcelik]

Yes, @fretje is right. It is possible to rename. I think it will be change to public and support database naming.