Database agnostic : staying within "lowest common denominator"


One of the bonuses of using Liquibase (in particular with an ORM) is database independence. And Liquibase is ahead of the competition here. With the profileration of Cloud and managed databases the topic of database independence has become even more important. 99% percent of applications do not need a particular type of RDMBS, in particular now when the organizational aspect is removed from the equation.

Liquibase features for database independence should be expanded upon to assist the developer in not making stupid mistakes which effectively makes it impossible to use on certain other databases. Doing actual tests against each and every database (which you want to support) is very costly, which is why the hope is to leverage Liquibase in this respect.

The idea is to pick some low-hanging fruits, meaning things that Liquibase can rather easily implement but which has great impact on achieving real database independence.

In particular this proposal focus on three things:

  • Stopping the developer from using a reserved word as an database object name. Here 'object name' means column name, table name, sequence name, index name, constraint names, etc. I believe Liquibase already has this knowledge.

  • Stopping the developer from using a value for a database object which is too long. Databases have their own rules about the maximum length of an identifier, for example there's the well-known Oracle restriction of 30 characters (albeit increased in the 12.2 onwards) and PostgresSQL's 63 character limit, etc. This can be a little hard to implement in Liquibase if it has to be a 100% correct implementation, because the rule may be different by the type of object (one limit for table name, another for column name, etc) and different by db version too. A crude implementation would simply be to allow the Liquibase user to define something like "don't allow me use an identifier longer than x chars".

  • Stopping the developer from using a column type size which is too big. This is mainly about VARCHAR columns. I don't really see the use-case for CLOB and BLOB checks. In any case, the developer may unknowingly think it is good a idea to define his column as VARCHAR(5000), but it is really not as it won't work on some databases. The developer should be stopped from doing that.

The point here is that it is simply not possible for a developer to know/remember what these restrictions are across a sub-set of databases. But Liquibase knows what they are. Let's use that!

Possibly, all of this can be implemented by use of preCondition ?

The user should be able to state his target set of databases, e.g.

dbms = "oracle,mssql,mysql,postgresql"

and then there should be a way to warn, halt or whatever when restrictions are broken.

All of the above should of course be an opt-in feature in Liquibase, meaning it should be stated actively in the changelog that such restrictions should apply.

I don't have solid ideas on what an implementation in Liquibase should look like. I just know I've been bitten by all of the above 3 mentioned pitfalls in the past and Liquibase seems uniquely positioned to mitigate such risk.






Affects versions