Database Refactoring

Evolving a DB schema with Riot

To allow smooth migrations from one release to another Riot ships with Revolt, the Riot Evolution Tool.

Revolt allows developers to perform refactorings that do not only affect the source code but also the database schema. With Revolt you can define DDL operations in a RDBMS-independent way and bundle them with your application. Upon startup Revolt checks whether the database is up-to-date and prints out a SQL migration script in case any pending refactorings are detected. (You can also enable the automatic mode if you want Revolt to perform the changes on its own.)

The refactorings are expressed by regular Java beans which can be defined in your Spring application context. Revolt provides a custom NamespaceHandler which makes the configuration easy to read and understand:

<db:history module="myApp" data-source="dataSource">
  <db:change-set id="initial">
    <db:create-table name="foo">
      <db:column name="id" type="BIGINT" not-null="true" primary-key="true" auto-increment="true" />
      <db:column name="bar" type="VARCHAR" length="255" />
    </db:create-table>
  </db-change-set>
  <db:change-set id="bar2boo">
    <db:rename-column table="foo" column="bar" rename-to="boo" />
  </db:change-set>
</db:history>

So whenever you need to change something in your schema, simply add a new changeset to the evolution history. Here's a list of the refactorings that are currently supported by Revolt:

  • create-table
  • create-index
  • add-column
  • add-foreign-key
  • add-unique-constraint
  • create-auto-increment-seq
  • modify-column
  • rename-column
  • rename-table
  • drop-column
  • drop-foreign-key
  • drop-index
  • drop-table
  • drop-unique-constraint
  • insert-data
  • update-data

Revolt keeps track of all applied change-sets in an internal log-table. Each log entry consists of three values: the module id, change-set id and a sequential number.

If Revolt detects that a module is new, i.e. no log entries exists for that module id, the schema is evolved in-memory before the SQL is generated. This is especially useful for long living agile projects as otherwise lots of unnecessary steps would have to be performed.