[FEATURE REQUEST] The possibility of create databases on the fly

[Neirth]

Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it

I get frustrated whenever I try to create a database on the fly within my microservices. I would like to be able to make on-the-fly databases with the Microsoft SQL Server driver that my microservices can consume, since many times, I deploy them with the name of the desired database (For example, in continuous integration and testing situations) . It's something I can do with other database drivers, however, but not with yours.

Describe the preferred solution

For example with H2 or MySQL I have the ability to create databases on the fly. Either the driver itself creates them without specifying anything, or instead, adding some additional parameter as in the case of MySQL (createDatabaseIfNotExist = true). I think that the solution would be to incorporate some type of parameter to the Microsoft SQL jdbc driver that allows creating databases on the fly and does not cause an exception if the database does not exist.

I think, as a developer, that this is not as complex as simply adding a handler of the desired parameter and that in case of some type of exception related to the fact that the database does not exist, instead of returning it to the application, that issue an SQL statement "CREATE DATABASE (Database name)" on the connection string with the database name excluded and try connecting to the newly created database again.

Describe alternatives you've considered

Another solution to this problem could be that the driver creates the databases in the air instead of throwing an exception that it could not create them. But it is clear that there are situations in which this kind of behavior does not interest.

Additional context

Above all, I find this functionality useful in testing or pre-production environments, where it may be that the required database does not exist and the continuous integration process cannot continue since the driver has thrown a fatal exception and has killed the process to be tested. . This option would also be interesting for ephemeral architecture environments, where one day you can have a microservice in charge of a specific task and the next another microservice that does totally different tasks.

Reference Documentations/Specifications

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-connp-props-miscellaneous.html#idm44976554867664

[lilgreenbird]

hi @Neirth Thanks for your suggestion. We'd like to see if a feature like this has community support (upvotes) before spending time adding it to the GA product.

[Neirth]

Hi @lilgreenbird, Thanks for the response. The comment describing the desired feature has received 5 upvotes. I think it is a feature that today, many development teams are going to appreciate it. 😁✌

[Neirth]

Hi,

Any news with this topic?

[lilgreenbird]

hi @Neirth

This will be triaged along with other feature requests and bug fixes when we do planning for the next semester. We will take into consideration the 4 upvotes + yours.

[Neirth]

Hi @lilgreenbird

Perfect! Keep in mind that the number of upvotes has tripled, implying that they are 13 + me. I hope you know how to see the value of this feature in the same way that we have seen it in the community.

[cheenamalhotra]

Adding some notes:

This feature brings possible risks where driver will do privileged actions with provided user credentials on master database which drivers don’t generally do until that’s an approved act by security board.

This feature request would have to be approved by security team before it’s acceptable.

[Neirth]

Hi @cheenamalhotra,

About what you comment, in the end if it is true that the user needs to have privileges to be able to create databases (Basically because the feature is called creating databases on the fly). This in the end is not very different from catching and launching an operation to create a database from a connection in an application. If the user does not have the necessary permissions to be able to carry out this operation, it should not be possible to execute it, no matter how much it is marked to create the database on the fly. The MySQL driver developers did it as follows, for inspiration https://github.com/mysql/mysql-connector-j/blob/4f7120a617b9d5efb9dedda9064b9896db424a60/src/main/protocol-impl/java/com/mysql/cj/protocol/a/NativeProtocol.java#L526.

I also left a Pull Request proposing a way to add this feature, but obviously it may be that there are discrepancies with the programming style that you have and you want to approach it in another way.

[cheenamalhotra]

(Accidentally deleted last comment, reposting) It shouldn’t be a blocker, as it will be documented that anyone opening a connection would need additional create permissions. But we would need to verify if that doesn’t break any security rules.

Concerns also rise when default settings are used to create both On Premise and Azure Database. Default subscription settings on Azure can turn expensive if not monitored well. The syntax is complex and if the driver owns “Create Database” it should also support customizations and that becomes a complicated affair. Otherwise driver restricts support of advanced customizations. Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver15&tabs=sqlpool

For these reasons we would need to run it by Security board.

[Neirth]

Hi @cheenamalhotra,

Of course, I had not taken into account the question you are commenting on. It is evident that this characteristic, in environments such as azure, will require a special treatment. (Offtopic: Actually, it's funny because I'm the first to use Azure SQL and haven't fallen for this). Be that as it may, it is good that you want to give it a supervision that this feature does not break anything at the security level. Personally, I work in an organization with the same criteria and the truth is that I can empathize a lot with you.

If you need to contact me for any question related to the issue, do not hesitate to mention me.