SchemaUtils.createDatabase should work without database connection (+other improvements)

[Aditya94A]

Here's the use case:

1. Try to connect to DB X
2. First check if it exists
   2.1 If it exists, go ahead and connect to it
   2.2 If it does not exists, create it and then connect to it

This would be essential in building a resilient system which would allow spinning up new environments quickly with no manual processes involved. I'm picturing spinning up dev/staging/prod environments with auto-created databases, as well as automatically creating feature-based temporary environments for QA approval testing via CI/CD flows.

So, SchemaUtils.createDatabase should work in the same "level" as Database.connect() to allow this. That is, I first want to check for the existence of a database, create it if it doesn't exist, and then connect to it.

So, perhaps an alternate version of createDatabase that tries to establish a connection to the SQL server all on it's own could be used to accomplish this. Or perhaps extend Database.connect() with a flag createDatabaseIfNotExists = true.

Another Util method SchemaUtils.doesDatabaseExist(): Boolean would be helpful as well.

cc @hichem-fazai

[hfazai]

@AdityaAnand1 Thanks for suggesting these improvements. I will create a PR to provide :

• SchemaUtils.isDatabaseExists(dbname) that checks if the given database exists or not.

• Add an optional parameter (autoConnect) to the createDatabase method. So that it allow to connect to the database after creatiion.

Here is an example:

    fun main() {
    //Important is that you connect to the instance, not a specific database initially
    Database.connect("jdbc:sqlserver://localhost:1433;integratedSecurity=true", "com.microsoft.sqlserver.jdbc.SQLServerDriver" ) 

    transaction {
        SchemaUtils.createDatabase("dbname", autoConnect=true)
    }
}

=> createDatabase will check if the database exists. Then create it if the result is false. Finally, connect to it.

@AdityaAnand1 is this exactly what you need ?

[Aditya94A]

Yes, exactly.

As a policy, I always want to default to creating a database on a server if it does not yet exist. This should do it 😁

[Tapac]

@hichem-fazai , I'm not sure that it's possible to change a scheme within the current transaction.
Also, I don't like a restriction with >Important is that you connect to the instance, not a specific database initially, why I can't connect to one database and then change it to another.

I guess that the more complex approach to work with scheme/databases should be provided. It should cover other cases like #145 , #254.
Do you mind to look at this?

[hfazai]

@Tapac you can connect to one database and then change to another, but my comment was to give solution when you don't even know if the initial database exists or not.

• I tried to switch between databases using connection.setCatalog() and it's working fine.
• To check if database exist I'm getting connection.metadata { catalogs} and check if it contains the given database.
• As you said switching between schemas is challenging. connection.setSchema doesn't work, so i'm trying to understand why and if there is another way to do that.

[hfazai]

I guess it's not possible to change a scheme(at least for mssql) as it is mentioned here.

[hfazai]

To support schema in a proper way I propose to add an optional field (String or a new Schema class) so that every create, insert or select specifies the schema name to which the table belongs.

object table: Table("table") {
    val id = integer("id")

    override val schema = "myschema"
}

[Tapac]

@AdityaAnand1 , I just want to say that options like autoConnect looks like a workaround.

I think that something like a code below looks more predictable (but more challenging in implementation):

val newSchema : Schema = SchemaUtils.createSchema("dbname")
transaction.selectSchema(newSchema)

or

val schemaToConnect = Schema("dbname")
Database.connect("url", schema = schemaToConnect)

It will allow improving code where you need to make cross-joins from different schemas or make selects to other schemas from a current connection:

tableA.innerJoin(tableB.fromScheme("dbname"))
tableB.fromScheme("dbname").insert {} 

Functions' names are just examples.

[Tapac]

I guess it's not possible to change a scheme(at least for mssql) as it is mentioned

That's why simple support of schemes in queries should be enough.

To support schema in a proper way I propose to add an optional field

Using strings is not a very good idea as it limits you to use extension functions for schemes (if you want to). Also, the field on an original table could not be changed, so I think new class should be introduced.

class SchemeTable<T:Table>(val scheme: String, private val table: Table) : Table() {
    override val tableName: String = "$scheme.${table.tableName}"
    
}

It just an example and there could be a lot of pitfalls in implementation by I hope you catch the idea.