Team Foundation Server (TFS) integrated flyway for database versioning

1 overview

In the system development process, we have the software source code version management, already has the relatively ripe solution. By using source code management tools such as TFVC or git, you can easily implement version operations such as fallback, compare, branch merge, and so on for software code. Do we also need versioning for software-dependent databases, and how do we manage versioning?

If you need to implement versioning on a database, let's start by looking at one such example:

Suppose a simple software system has the following architecture, including a software application and a database node, which provides services to the user through a database connection.

This simple software structure, in the system development process of the actual situation, it is likely that:

Figure 2

As can be seen in the system development process, we are not facing a single database instance, also need to manage different developers, different running environment of the database. The structure of different database instances or inconsistent data will lead to different results of software operation, even logic errors, system crashes and other phenomena. How can I ensure that the database instances on different servers are managed uniformly? This is a headache for many software development teams.

In the actual development process, many development teams update database scripts in different environments by hand, and some even update the database with some temporary configuration. By managing the database in this way, the development team may immediately face some of the following confusion:

-What is the status of this DB instance?

-who operated the last update?

-is the required database script/update already applied to this DB instance?

-How do I create a database instance that is exactly the same?

-Wait ...

Faced with these critical issues, you may be completely at a loss. In order to solve these problems, we need to implement version management of the database instance, so as to accurately understand the health of the database.

A common scenario for database versioning scenarios is to create a table (Schema table) in the database that stores database change records, and to record key information, such as the content of each change, the change, the change time, and the version order, as the underlying data for versioning. According to this idea, the development team can manually maintain the records of this table, but also with the tool to automatically maintain the information of this table. In this article, I'm going to introduce a tool for automating the management of database versions (Flyway), and using this tool to integrate TFS systems, manage database scripts in a code base (TFVC or Git), and use the continuous integration or continuous delivery capabilities of TFS Systems, Achieve the goal of fully automatic maintenance of database scripts.

Flyway is an open source tool dedicated to database versioning and supports including DB2, MySQL

, Oracle, PostgreSQL, SQL Server, SQLite, Sybase, Greenplum, and more than 20 common databases, and can be run on Windows, Linux platforms, and can be integrated in ant, Automation tools such as MAVEN can even be integrated directly into your Java program. Based on these characteristics, many research and development teams use Flyway as the Version management tool for DB instances. In the following sections, I mainly describe how to apply this tool to the continuous integration of TFS Server to automate the management of data scripts.

2 Download flyway command-line tools

First, you need to download the necessary software. In the following experiment, we use the Flyway command-line tool to implement versioning on a SQL Server DB instance on a Windows server. You can also try the Maven, ant approach to automating deployment in your TFS system.

l:http://flywaydb.org

L Tool File: Flyway-commondline-w.2.0-windows-x64.zip

Description: Flyway is a Java-based software tool, but the Java Runtime Environment (JRE) is already included in the package above, and in the process of running flyway, this tool automatically sets the system environment variables required by Java, so you do not need to install the JRE on the server. or configure Java_home and so on.

3 Configuring Environment variables

In order to run the Flyway command in any directory, you need to add the Flyway running software directory to the environment variable path, such as:

4 Quick Start for Flyway

After installing the above environment, we can take a quick look at the function of Flyway through the following instructions.

1 View Flyway features. You can run Flyway in any directory to see the parameters of this tool

Figure 3

Fllyway has the following 6 main functions:

command	features
Baseline	Create a baseline for the existing state of the database for the first time using Flyway
Clean	Delete all objects in the database
Info	Print basic information about the database version, such as installed, current, and pending scripts
Validate	Verify the current script conflicts with data
Repair	Fix contents in metadata table
Migrate	upgrade, To apply a file in a database script to a DB instance

Flyway also has a wealth of parameter configurations. If you do not set any references during the script run, Flyway automatically uses the configuration information from the configuration file config/flyway.conf in the installation directory, such as the server address, account password, and so on. The following table is all the reference information for Flyway:

Options (Format:-key=value) ------- Driver:fully qualified classname of the JDBC driver Url:jdbc URL to use to connect to the database User:user to, connect to the database Password:password to, connect to the database schemas:comma-separated List of the schemas managed by Flyway Table:name of Flyway ' s metadata table Locations:classpath locations to scan recursively for migrations resolvers:comma-separated List of custom migrationresolvers Skipdefaultresolvers:skips default resolvers (JDBC, SQL and SPRING-JDBC) Sqlmigrationprefix:file name prefix for SQL migrations Repeatablesqlmigrationprefix:file name prefix for repeatable SQL migrations Sqlmigrationseparator:file name separator for SQL migrations sqlmigrationsuffix:file name suffix for SQL migrations Mixed:allow mixing transactional and non-transactional statements Encoding:encoding of SQL Migrations Placeholderreplacement:whether placeholders should be replaced Placeholders:placeholders to replace in SQL migrations Placeholderprefix:prefix of every placeholder Placeholdersuffix:suffix of every placeholder Installedby:username that'll be recorded in the metadata table Target:target version up to which Flyway should use migrations Outoforder:allows migrations to is run "out of order" callbacks:comma-separated List of Flywaycallback classes Skipdefaultcallbacks:skips default callbacks (SQL) Validateonmigrate:validate when running migrate Ignoremissingmigrations:allow missing migrations when validating Ignorefuturemigrations:allow Migrations when validating Cleanonvalidationerror:automatically clean on a validation error Cleandisabled:whether to disable clean Baselineversion:version to tag schema with when executing baseline Baselinedescription:description to tag schema with when executing baseline Baselineonmigrate:baseline on migrate against uninitialized Non-empty schema Configfile:config file to use (default: <install-dir>/conf/flyway.conf) Configfileencoding:encoding of the config file (default:utf-8) Jardirs:dirs for JDBC Drivers & Java migrations (default:jars) Add-x to print debug output Add-q to suppress all output, except for errors and warnings Add-n to suppress prompting for a user and password Add-v to print the Flyway version and exit Example ------- Flyway-user=myuser-password=s3cr3t-url=jdbc:h2:mem-placeholders.abc=def Migrate More info at Https://flywaydb.org/documentation/commandline

2 Create a new database on the database server with the name Flywaydb

3 Create a new account on the database server, flyway it, and configure it as the Dbowner permission for the database in the previous step

4 Initializing a database baseline with Flyway

Write the configuration file under the Flyway installation directory, set the database path, account and password

Figure 4– Modifying configuration information

Run Flyway's baseline command: Flyway baseline, if the command succeeds you will see a similar feedback message and see in the database that the Schema_version data table was created successfully.

Figure 5– Run flyway baseline command

Figure 6– Baseline command to create the version data table in the database: Schema_version

5 Preparing the upgraded database script

We first create a new SQL script file in the SQL directory of the installation directory, and the naming rules for filenames are as follows:v1.1__< descriptive text >sql

For example, you can use the file name of the following example:

-V1.1__createusertable.sql

-V1.2__insertuserinfo.sql

Note: The delimiter in the file name is two underscores, otherwise the system always prompts you to name the file incorrectly!!!

6 Running the Database upgrade script

Figure 7

The above command applies all the scripts in the SQL directory to the database and updates the version records in the Schema_version table, such as:

Figure 8

Next update, you just need to follow the above rules, add database script file, run the Flyway migrate command again, you can complete the database version of the upgrade, Shema_version will clearly record the course of the upgrade version.

5 Configuring TFS Continuous Integration

Beginning with TFS 2015, TFS offers new, continuous integration capabilities that use an entry-and-order process to complete all tasks in the continuous integration processes. The following example configures the Flyway task in a continuous release as an example of the current latest TFS 2018.

5.1 Uploading a script file to the TFS code base (Git)

Figure 9

5.2 Configuring continuous integration, publishing code

The purpose of configuring continuous integration is to publish database scripts in the build results each time the database code is updated for use by the publication. If you do not need process control during the release process, you can also refer directly to the following configuration to configure the Flyway release task in the build process.

In the following, I configured a simple production process that uploads the database files in the code base to the production results.

Figure 10

5.3 Configuring the Flyway tool in the publishing process

1) Add the production definition created in the previous step in the publishing process.

2) Install your own need to increase the release environment, such as

Figure 11

3) Add Flyway tasks in the publishing environment, such as

A command-line task was added to the publishing process, with the following commands and parameters:

Flyway migrate-locations=filesystem:$ (agent.releasedirectory) \$ (BUILD. Definitionname) \sqlscripts-url=jdbc:jtds:sqlserver://sql2017:1433/flywaydb-user=$ (flyway.user)-password=$ ( Flyway.password)

Note: Because the Flyway command requires an account and password for incoming database access, we can define password as an encrypted parameter (Flyway.password) in the publishing process for the confidentiality of the password data. The specific tasks are set up as follows:

Figure 12

4) perform the release process

After publishing is complete, you can query the publication's results in the Publish list, or you can query the publication log for details if the publication fails.

Figure 13-Publishing Success List

Figure 14– View details in the Publish log

6 Summary

In the above demonstration, we introduced the code base of TFS system, the continuous integration/release function, and integrated the Flyway tool, which can easily implement the version management of the database and provide a clear management idea for the system developer to manage the database update in the application software.

(Http://www.cnblogs.com/danzhang TFS MVP Zhang Hongjun)

--end--

Team Foundation Server (TFS) integrated flyway for database versioning