Flyway DB Migration Script

• Flyway is an open source tool for database migration.
• Flyway is a version based database migration script.
• It defines multiple versions of different SQL scripts to be executed in order.
• It finds and Executes updates automatically.
• It is an open source tool license under Apache 2.0 that helps us to implement automated and version based migrations.
• We define update operations as SQL script or in Java code.
• It uses a metadata table to document, the current Database version and all executed updates.
• Run the migration From a command line client or Automatically As part of the build process or integrate in Java application.
• Flyway detect the required, update operations and execute them.
• Most migration should only be described in an SQL script.
• For some complex operations, like reading data from a blob and store it In new set of columns, we can use Java
• Manual migration may become complex as the size of database increases.
• Orchestration of scripts becomes easy.
• Version control of database changes.
• Reproducible builds and continuous integration.
• Well-defined release and deployment process.
• Automate DB changes
• To save time lost in coordination of manual DB changes from each environment.
• Greatly reduces application errors caused by missed DB changes.
• Better tracking of DB changes for each release of application code.
• To prevent errors resulting from duplicate database changes.
• Fits right into continuous integration, paradigm using tools such as bamboo, Jenkins, Maven.
• To recreate a new database from scratch.
• Simple, focused, powerful, plain old SQL, convention over configuration, zero required dependencies, fail fast, schema clean.
• Supported databases are Oracle, SQL Server, SQL Azure, DB2, DB2Z/OS, MySQL, Maria DB, Google cloud SQL, Postgres SQL, red shift, Vertica, Enterprises DB, H2, HSQL, Derby, SQL lite, SAP HANA, solid DB,Sysbase,HSE and Phoenix.
• Flyway is a open source database tool which helps us to give version control to our database.
• Naming convention of SQL files is as follows V{version}__create_database.sql
• Download flyway from https://flywaydb.org
• Extract archive.
• We see folders, CONF and SQL.
• All SQL files are kept in SQL folder.
• All configuration files are kept in conf folder
• Basic configuration are Flyway.URL, Flyway.User, flyway.Password.
• Flyway.URL parameter defines The url Which fly should use to connect to database.
• Flyway.User and flyway.Password are optional.
• Use command “flyway migrate” To migrate database.
• We can also use integrated flyway by including flyway jar using our dependency management system like maven or gradle.
• Create a flyway object.
• Set flyway datasource.
• Execute migrate function.
• Flyway has following commands
• baseline
• Tags the current version To the database.
• Creates the schema table and initialised it.
• Info
• Provides the database Information and its current state.
• Check for executed and pending SQL scripts.
• Migrate
• Deploy the migration against configured database.
• It has a table flyway_schema_history which tracks What migrations have run against the table.
• Execute the pending scripts.
• Clean
• Cleans out The development database and get rid of all tables or reverts the queries executed.
• Undo
• Runs the undo script, corresponding to the last migration that we have and undos that.
• Repair
• Remove any error or broken scripts.
• Location property with filesystem and classpath
• 
  
  
  
  
• Example of Flyway implementation in Spring Boot App can be found here
• https://github.com/gauravmatta/springmvc/tree/master/activiti-project
• https://github.com/gauravmatta/springmvc/tree/master/maven-springmvc-project