What's the best way to verify database schema?

General Discussion
1

I’ve started using preconditions to do simple existence checks in front of refactorings like create table, create index, etc. The problem is that our dev environments have different flavors of the db, in various states (some at the result of bad dev testing). As we begin to use Liquibase to enforce future schema integrity, even if a table exists, it doesn’t necessarily exist with the proper schema today.

I’d like to detect the schema differences in our Liquibase run so that the next day we can decide to either destroy a corrupt dev db, or assign devs to write changesets that convert a bad schema+data to the proper form. And I want to be very detailed, for example:  verify a given column has the correct data type/length, nullability, and default value.

So how can I approach this?  Has anyone tackled this before?

I have an idea but I wanted to hear what the community thinks first.

Cheers,
Trevor

2

Hello,

if i had hibernate as tool for my or-mapping, I would use the hibernate validate check. I don’t think that liquibase is able to validate the schema because it only tracks the changes. And changes can be SQL statements, or liquibase xml changes etc. But liquibase will check the integrity of the changes itself because of the stored checksums.

An OR mapper on the other side knows exactly your objects and tables, thus it will be able to check if the tables are consistent.

Oliver

3

Preconditions would be your best bet for checkign the state of the database before each change.  Something like:

                      <createTable tableName="person>         ....     </createTable> </changeSet> </ol> <p>When it runs, if the person table exists (the not exists precodntion fails), the changeSet will be marked as ran and the update will continue. </p> <p>As a side note, the doing precondition checks should be kept to a minimum.  It makes your updates take longer, because we need to check the database schema before each change, rather than just relying on what is in the databasechangelog.  It is a necessary step when you are moving to use liquibase and you are dealing with many databases in different states, but a good step would probably be to decide on what your starting state “should” be and make some changeSets with preconditions to ensure that it is actually in that state.  From that point, you can use the normal liquibase flow in which you don’t worry about preconditions.</p> <p>Nathan</p>