Skip to content

Tilda Command Line Utilities: Migrate

Jump to bottom
Laurent Hasson edited this page Sep 17, 2019 · 2 revisions

<-- Command-Line Utilities

Migrate

This utility takes the model definitions available in a deployed environment and migrates the database target to match. It is multi-tenant aware and can migrate multiple databases in a group simultaneously, including multiple databases at different version levels.

Migrate is central to Tilda's Iterative promise, encourages aggressively agile processes. Migrate automates many database migration tasks such as:

  • Adding new columns, tables, views
  • Modifying a column's type or nullable status
  • Renaming columns, tables and views
  • Adding/removing/changing primary key definitions
  • Adding/removing/changing unique index definitions
  • Adding new non-unique indices
  • Manage additional constructs such as complex views with pivoting, formulas or time series, along with other database artifacts such as the Refill_* functions.
  • Maintain standard tilda_app and tilda_readonly roles and ACL rules for managed artifacts (Schemas, Tables, Views, Functions...).

Migrate understands the full data model being deployed, and assumes the model has been properly validated by Gen. It can do interesting things such as:

  • Change the identify of a table
  • Manage indices
  • Change the size of string columns
  • Convert an int column to a string
  • Keep dependent views in sync, including views that are not managed by Tilda directly.

One important consideration is that Migrate is non destructive. This means a few things:

  • If a column no longer exists in the model, it's not removed from the table unless explicitly stated in a migration element.
  • If a non unique index exists in the database but not in the model, it won't be affected.
  • If a developer in a personal schema, creates views outside of Tilda that depend on Tilda-managed entities, Migrate will attempt to migrate those as well if possible, i.e., capture the DDL for those views, including comments and ACL and drop them and restore them as appropriate.

Migrate is pretty powerful but has limitations nevertheless that are pretty much intrinsic to software development life cycle rules of APIs in general. After all, tables and views are akin to interface and implementation definitions.

  • If you remove a column that is used by a Tilda view, you'll get an error in Gen and will need to correct the model. However, if an external view (non managed) depends on a deleted column, Migrate won't be able to handle it.
  • If you add a new column that is not nullable and doesn't specify a default value, migration will fail.
  • If you shrink a column's string size from 1024 to 10 for example and there are values in the database with a length > 10, migration will fail.
  • If you change a column from nullable to not nullable and there are null values in the database, migration will fail.
  • If you change the type of a column and some values can't be converted (e.g., String to Int with a value of 'abc' in the database), migration will fail.
  • If you change an identity of table (a primary key definition of a unique index) and there are duplicate rows in the database, migration will fail.

When migration fails, it typically requires human intervention to either "fix" the data model, or manually migrate the database (i.e., creating a separate migration script to address any of those issues). These limitations are pretty standard in any type of software that defines interfaces and APIs (which a data-driven model does). For example, if you remove a public method in a public class that is part of an API, you'll break any software that was depending on that method. The same issue arises for columns in tables.

The same way Gen operates, Migrate is there to support a rapid iterative development process where "breaking the schema" is (1) encouraged, and (2) easily manageable. In a project involving over 1000 tables and views across a couple dozen schemas, we have been able to maintain a pretty clean model across many product releases and keep a pretty sane overall data model for our applications.

To run the utility is straightforward: just call it with a classpath that includes your runtime environment (i.e., your JAR files). The utility will automatically identify all the Tilda resources that were generated from the Gen utility and included as part of your build process.

java_run tilda.Migrate

As for all Tilda code, we use ultra-logging techniques that generate rich detailed logs of the whole process including each model found in the runtime environment, analysis steps, and migration actions.

🎈 NOTE: The utility will remind you with some ascii art that it's always a good idea to have a backup before performing a migration. Although the Tilda project strives to protect your data and perform non-destructive migrations only, there is always the possibility of a bug somewhere or an unforeseen condition.

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!! A MIGRATION HAS BEEN REQUESTED. AS A RESULT, DATA IN YOUR DATABASE MAY BE CHANGED.
!!!     ______ ____  ______   ____   ___    ______ __ __ __  __ ____  _____    ___   
!!!    / ____// __ \/_  __/  / __ ) /   |  / ____// //_// / / // __ \/ ___/   /__ \ 
!!!   / / __ / / / / / /    / __  |/ /| | / /    / ,<  / / / // /_/ /\__ \     / _/ 
!!!  / /_/ // /_/ / / /    / /_/ // ___ |/ /___ / /| |/ /_/ // ____/___/ /    /_/   
!!!  \____/ \____/ /_/    /_____//_/  |_|\____//_/ |_|\____//_/    /____/    (_)    
!!!
!!! THE FOLLOWING DATABASE(S) WILL BE ANALYZED:
!!!     ===> MAIN: jdbc:postgresql://localhost/Pepper?reWriteBatchedInserts=true&USER=postgres
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

🎈 NOTE: You can run the migration utility as a database administrator, which is often useful in a development environment. In production however, running with a regular role is strongly recommended, and the utility will always warn you if you are using a superuser account. when running in a normal account, you will be prompted to escalate your role when Migrate deals with ACL definitions in the databse, which in some databases such as PostreSQL requires superuser abilities.

###################################################################################################################
###                                                                                                             ###
###  W A R N I N G :   T H I S   C O N N E C T I O N   U S E S   A   S U P E R U S E R   A C C O U N T   ! ! !  ###
###  =========================================================================================================  ###
###                                                                                                             ###
###  _|    _|    _|    _|_|_|       _|         _|_|    _|      _|    _|_|       _|_|_|  _|_|    _|_|_|    _|    ###
###  _|_|  _|  _|  _|    _|       _|  _|     _|      _|  _|  _|  _|  _|  _|       _|    _|  _|  _|      _|  _|  ###
###  _|  _|_|  _|  _|    _|       _|_|_|     _|  _|  _|  _|  _|  _|  _|  _|       _|    _|  _|  _|_|    _|_|_|  ###
###  _|    _|  _|  _|    _|       _|  _|     _|  _|  _|  _|  _|  _|  _|  _|       _|    _|  _|  _|      _|  _|  ###
###  _|    _|    _|      _|       _|  _|       _|_|    _|      _|    _|_|       _|_|_|  _|_|    _|_|_|  _|  _|  ###
###                                                                                                             ###
###################################################################################################################

🎈 NOTE: The utility requires "human attendance" to proceed by prompting the user to perform the analysis (entering 'y'), and after reviewing recommended migration actions, approve the actual migration process (by entering 'yes'). An additional step might be required when using a non superuser role to deploy the ACL information. the utility will prompt for a superuser id and password.

As with Gen, Migrate can be used straight from a command line, or integrated in an IDE.

Clone this wiki locally