Flyway

In Flyway, filtering is the process of descoping objects and properties from your projects. This means you can set up the comparison to ignore said properties and objects. The result is that the items being filtered will not feature in migrations and will not cause differences in version control.

How to set it up

Flyway Desktop supports filtering with a filter file. PostgreSQL projects can be filtered by object type or property name, with more filtering conditions coming in the future.

The filter file has to be manually created in one of two ways:

create a file called filter.rgf in the project folder (next to flyway.toml)
specify a particular file name in flyway.toml (under redgateCompare.filterFile) and create that file instead

The format of the filter file is subject to change as we introduce new filtering options. It currently looks as follows:

Example filter file

Filter objects

{
  "version": "1.1",
  "postFilters":
  [
    {
      "filterBy": "objectType",
      "filterValue": "table",
      "effect": "exclude"
    }
  ]
}

The above example filter can be read as "exclude all objects of type (objectType) table".

Filter properties

{
  "version": "1.1",
  "postFilters":
  [
    {
      "filterTarget": "property",
      "filterBy": "name",
      "filterValue": "collations",
      "effect": "exclude"
    }
  ]
}

The above example filter can be read as "Exclude all property(s) with the name collations".

Currently only post filters (which apply at comparison/deployment time) are supported. In the future this file may also include pre filters which apply when querying the database.

The list of filters is applied, in order, for each object in the comparison. The resulting include/exclude state after all the filters have been processed is then used for that object. This means that more general filter rules can appear at the top of the list, and more specific exceptions can appear at the bottom.

filterBy - (mandatory)

Property Value	Description
`any`	matches any object
`objectType`	matches items that are objects e.g table, function, stored procedure etc
`name`	currently only properties can be filtered by name. So `filterTarget` has to be property

filterTarget - (Optional) - Defaults to object

Property Value	Description
`object`	will cause `filterBy` to affect objects
`property`	will cause `filterBy` to affect properties of an object

filterValue - (mandatory)

This is the value you wish to match and want the filter to act on.

An invalid combination of filterBy and filterTarget will cause an error.

Example scenarios

These examples apply to all supported database engines i.e. PostgreSQL and MySQL

I want to ignore all objects of a particular type e.g. views

{
    "version": "1.1",
    "postFilters":
    [
        {
            "filterBy": "objectType",
            "filterValue": "view",
            "effect": "exclude"
        }
    ]
}

I want to ignore all properties of a particular name e.g. collations

{
    "version": "1.1",
    "postFilters":
    [
        {
            "filterTarget": "property",
            "filterBy": "name",
            "filterValue": "collation",
            "effect": "exclude"
        }
    ]
}

How do I ignore multiple property names and object types e.g. collations and all views

{
    "version": "1.1",
    "postFilters":
    [
        {
            "filterBy": "objectType",
            "filterValue": "view",
            "effect": "exclude"
        },
        {
            "filterTarget": "property",
            "filterBy": "name",
            "filterValue": "collation",
            "effect": "exclude"
        }
    ]
}

PostgreSQL specific example scenarios

These examples apply only to PostgreSQL and its variants

How do I ignore all privileges

{
    "version": "1.1",
    "postFilters":
    [
        {
            "filterTarget": "property",
            "filterBy": "name",
            "filterValue": "privileges",
            "effect": "exclude"
        }
    ]
}

How do I ignore all owners

{
    "version": "1.1",
    "postFilters":
    [
        {
            "filterTarget": "property",
            "filterBy": "name",
            "filterValue": "owner",
            "effect": "exclude"
        }
    ]
}