Feature: Allow setting environment variables when using JDBC Support for Databases

[jonasgrunert]

Hi everyone,
first of all thank you for a great library and a great documentation. I would like to suggest a feature which I would also contribute too.
But before jumping into the work I would like to discuss the idea.
When using the JDBC schema to enable a databse either during test or during the applications runtime it is currently impossible to use environment variables without programmatically starting the container (#3929). I would like to add this feature and have three possible solutions in mind.

Env Variables as Query Parameters

This idea basically would allow to set any enviroment vraiables via the url e.g. ?MSSQL_COLLATION=UTF-8. The pros are that it does not require an additional place to maintain variables from a users perspective. On the other hand imnplementation wise, it would be hard to differntiate between connections params and enviroment vraiables, which would lead to a need for another prefic like use for the initscript e.g. ENV_. Additionally the user had to url escape possibly complex strings, which would hurt readability and make for long obscure URLs.

Dedicated Properties File with specific name

As used for MSSQL container as an example it is a viable pattern to use a dedicted text file in the resources folder to enable specific settings. This proposed solution would follow the approach set in configuration for coonfiguration on the class path. This would allow for example to search for environemt variables in tc_environment.properties. While I like this approach more than the query parameters I would see it as a drawback that if I use multiple database all Enviroment variables have to be in one file. One could attempt to define a single specific filename per Image, but this would require a bigger implementation effort per module, without a significant benefit ion cases where there are two containers of the same image.

Dedicated Properties file specified via URL parameter

Thius approach is by far my favourite. This would allow to specify a prperties file via an URL Paramter as currently possible for an init script. The specifc property could be TC_ENVFILE and would allow the same options as TC_INITSCRIPT meaning either a classpath file or a file loaded from the file:: protocl from the current working directory. Analog we could offer a TC_ENVFUNCTION query parameter which would take the path to a function, which should return a Properties object.

Either way I am looking forward to your feedback and would also implement the feature if I can get your confirmation, that is a good proposal.

[kiview]

Hi @jonasgrunert, thanks for your suggestion and detailed proposal of possible implementations.

I feel the feature request is generally reasonable and it would directly unlock a lot of more advanced container configurations, while still relying on the simplicity of the JDBC URL integration. However, I also wonder which are the use cases where this is necessary and how widespread they are.

The approach as outlined in #3929 is very powerful, generic, and Testcontainers idiomatic. In addition, we see increasing support for such an approach even in other frameworks (such as DynamicPropertySource in Spring). And even if not directly supported by the framework, I haven't yet seen a codebase, that could not utilize a database Testcontainers instance that was created programmatically.

So independent from the actual implementation, I would like us to clarify if adding this feature is really necessary in the first place.

I would be also interested in @rnorth's take on this.

[rnorth]

I think I'd echo that; if you can, then programmatically creating the container object and passing its connection URL to your code under test is the way that gives the most flexibility. Out of interest, are you using a framework that makes this difficult?

I think we'd be reluctant to make the JDBC magic tc URL handling that much more complicated without widespread demand from more users.

I could suggest a workaround as well, if you really need to carry on with the tc URL method but also create containers programmatically: if you create your own subclass of JdbcDatabaseContainerProvider (and add a ServiceLoader entry to the classpath), then you could use a URL like jdbc:tc:mycustomdb: ... to obtain an instance that you have created programatically. This is how all Testcontainers modules that support JDBC tc URLs work.

This isn't documented as an approach, but I had it in my mind as a 'break glass' customization mechanism back in the early days of the project.

[jonasgrunert]

Thank you for replying. I can not estimate how wide ranging the need for this Feature really is. The need in my project comes from specifying a collation for the MSSQL Server. This is possible by setting an env variable, what I think is a docker idiomatic way to do it. I could check for the other JDBC providers which Environment variables are supported there.

[kiview]

But for your specific use case, don't you think that instantiating MSSQLServerContainer as an object and setting the environment variables there works fine?

[jonasgrunert]

I know that this is possible. But in that line of judgement I am inclined to ask myself why the other approach exists.
I provide a ready to run project setup for my colleagues and part of that is a readily built test and local dev setup. And for that I would like to be able to configure the database with a system wide setting.

[apokralipsa]

One more case where having the url query params translated to environment variables would come in handy is setting a specific timezone for a Postgres database. While this would be possible by creating the container manually in code, it would be nice to be able to keep the simplicity of jdbc url configuration when setting up a Spring Boot app.

[giger85]

Hi, is there any update on this issue?

[LeosBitto]

The official container image of the MS SQL server requires setting the environment varible TZ to start the server with the proper timezone (otherwise it always uses UTC). It would be very nice to be able to set this in the JDBC URL.