Flyway: “Hello database migrations”

flyway-logo

Flyway is a open source database migration tool that allows you to manage database changes using migrations. Last week the version 4.0.1 has been released and I decided to write my first post about Flyway.

I started to use Flyway command-line almost 3 years ago (version 2.2.1) and the main reason that took me to use and keep using nowadays is: it’s simplicity, “database migrations made easy”. This key factor is translated into the following:

  • Zero dependencies (you need java and your jdbc driver)
    • You can download the version that already includes both: java and the driver;
    • This is a key factor for the easy setup process;
  • Easy to setup, no need to install
    • You just have to configure Flyway: target server, migrations location, etc;
    • This makes the deploy process extremely easy;
  • The scripts are written in SQL
    • You, or your team, do not have to learn or use a different language to create migrations.

Flyway commands

Flyway provides 6 basic commands:

  • migrate
    • Apply all migrations until the latest version, or until a specific target version;
    • If the metadata table doesn’t exist this command will create it automatically;
    • This is they key command of the Flyway workflow;
  • clean
    • Drops all database objects in the configured schemas;
    • This command should be used with caution, especially in system databases or in production environment;
    • Its useful in development and test environments enabling a fresh start  cleaning completely your database;
  • info
    • Gives the current status information about all the migrations;
    • This is done by checking the migrations scripts against the metadata table;
    •  Allows you to know if a migration was applied with success, or still pending or was ignored;
  • validate
    • Validates applied migrations against the available migration scripts on your folder;
    • Allows to validate if all migrations were applied, i.e. do not exist pending migrations;
    • Allows to validate if a migration script was change after being successfully applied;
      • This validation is made trough checksum validation;
      • Allows to reliably recreate the database schema;
  • baseline
    • Allows to set the baseline of an existing database;
    • All migrations upto and including the baseline version will me ignored;
    • If the metadata table doesn’t exist this command will create it automatically;
  • repair
    • This command repairs the metadata table;
    • Remove migrations from the table marked as failed;
    • Realign the checksums of the applied migrations to the ones of the available migrations

flyway_commands

Flyway configuration

The Flyway configuration can be specified in two ways:

  1. In the configuration file: flyway.conf
  2. In the command-line using the format “-key=value” (this way overrides the previous one)

The following picture shows all available configuration options for Flyway version 3.2.1:

flyway_options

Flyway metadata table

The metadata table is used to track the state of the database. Allows to know which migrations have already been applied, when were applied and by whom. Additionally also tracks migration checksums.

flyway_metadata_table

The default name of the metadata table is “schema_version”. If the database is empty and the metadata table do not exist, Flyway will create it automatically.

Flyway scans the migrations directory and check migrations against the metadata table. Migrations are sorted based on their version number and applied in order.

Flyway command-line structure

When you download and extract Flyway command-line you will find the following structure:

flyway_strucuture

  • conf
    • In the folder you will find the configuration file “flyway.conf”
  • drivers
    • This folder contains the jdbc drivers
  • jars
    • In this folder you can add java migrations
  • lib
    • This folder contains Flyway jar files
  • sql
    • In this folder you can add SQL migrations
  • flyway.cmd
    • File responsible for executing Flyway (Windows command script)

 

My new friend FoRG

In February I made a new friend, I have joined the 2016 Friends of Redgate (FoRG) Program.

I found Redgate and his products when I started working as DBA with special focus on database development and database changes delivery. SQL Source Control  and DLM Autoforg-2016-logomation Suite are great products for this purpose. However, what really impressed me were Redgate’s people: always ready to give you support, always ready to listen your feedback.

As a Friend of Redgate I have the opportunity to use their tools and provide my honest feedback about them directly to product and development teams.

So, I’m honored for being part of this community and I’ m looking forward to learn and share knowledge through my blog posts, speaking, or any other means.

SQLSaturday #485 Lisbon 2016

SQLSaturday #485 Lisbon 2016, held on February 20, was my third SQLSaturday event and my first as speaker. When  I received the email saying that my session had been accepted in schedule’s event I felt happy and honored.    sqlsatuardaylisbon2016

My session, Road to database automation, was accepted as lightning talk. So I had 10 minutes to address the first stage of the database automation: database source control and it´s benefits. During that time I presented the reasons that lead me to invest on database source control and what tools I used to achieve it. You can find my presentation in the end of this post.

The presentation went well and fast. But the best happened after the presentation: the attendees feedback and curiosity. The possibility of discuss my favorite topics with other people with similar challenges was amazing.

This post is also the opportunity to give my congratulations to the event organization, headed by André Batista @klunkySQL, who provided great moments of conviviality, and to say thank you to the portuguese sql family for welcoming me so well.

IMG_20160221_111027

I hope to see you all soon.


 

 

Database Source Control – How?

In this post I describe some decision that you need to make when putting your database development under source control.

“Commit a SQL script to a local repository and the push to a central repository” sounds easy. In fact the all process should be easy.sqlscript

The SQL script is the fundamental resource of the database source control process.

The script can represent two perspectives:

  • The desired definition of the database object (table, view, stored procedure, function…), a.k.a state-based approach;
  • The desired modification to be applied on the database object, a.k.a the migrations-based approach;

About this two approaches I like the definition presented by Alex Yates:

  • State: Your source of truth is how the database should be.
  • Migrations: Your source of truth is how the database should change.

So, this is your first big decision: state-based approach or migrations-based approach? Can I use both approaches? (The discussion of which one it’s the better approach will be explored on a future post).

The correct answer it’s directly related to… your budget. Each one of the solutions (state, migrations or even the combination of the two) will do the work and will cost time and money.

In your budget you should consider the following tools:

  • For state-based approach
    • Redgate SQL Source Control (+info)
    • SQL Server Data Tools (SSDT) (+info)
    • DBSourceTools (+info)
  • For migrations-based approach

The next decision is to choose the source control system. All the tools above identified support or can be used with the following source control systems:

  • Mercurial
    • Is a distributed source control management tool (+info)
  • Git
    • Distributed version control system designed to handle everything from small to very large projects with speed and efficiency (+info)
  • TFVC
    • Team Foundation Version Control (TFVC) is a centralized version control that lets you apply granular permission and restrict access down to a file level (+info)

As “normal” user, meaning that I do not use APIs to work with this systems, my preference was built through the use of GUIs:sourcecontrolsystems

  1. Mercurial ->  TortoiseHg (+info)
  2. Git -> SourceTree (+info)
  3. TFVC -> Visual Studio (+info)

 

Currently my database repositories are Mercurial or Git because I prefer distribute systems. Then, once I use the migrations-based approach my first choice is Mercurial/TortoiseHg because in my opinion have a better branch representation. It´s less powerful than Git making it easier to use and teach (I taught a telecommunications team use TortoiseHg with great success).

Summing up: to answer the question “How to put database under source control?” you need to make two main decisions:

  1. SQL scripting strategy
    • State-based or migrations-based approach
  2. Source Control System
    • Centralized or distributed system

 

Database Source Control – Why?

Nowadays the question “Why put database under source control?” no longer makes sense. Probably “How to put database under source control?” is now the correct question.

But before answering the second question let me answer the first identifying the reasons that led me/us team to invest on database source control:

  •   Is the first step of database automation
    • In other words, if you want to assemble a database deployment pipeline you should put your database changes into a source control system;
  • Keep history of each change
    • Allows traceability:
      • Everything went well: just do it and forget it;
      • Something was broken: you will need to know what changed, when was changed, why it was changed and who changed it;
  • Your best communication system
    • Allows to share code (SQL scripts);
    • In team context a source control system helps to manage possible conflict of interest;
  • Enforce standards (my reference was Sjors Takes)
    • Standards, preferably combined with automation, help to reduce/eliminate conflicts.

database

How to put database under source control? is the question that follows.

Database automation motivation

The first question I thought was: if applications development includes “by default” concepts as source control, continuous integration and continuous delivery, why not database development?

As Len Bass said in a conference: “A script is just a program”. All database changes can be scripted, so what is so special about databases? The answer is simple: data, specifically data persistence. One of my favorite answers was given by Grant Fritchey: “Deploying databases has the inherent problem of retaining the data after the deployment”.

Faced with this challenge the second question that I made was: why should I invest my time and effort in automating the database development process? I found the following  answers/reasons in literature and in my day to day job:

  •   Database are out of pace with application development
    • The database changes usually come from the development teams, however in most cases the DBA is responsible for applying this changes. This means that development teams and the DBA need to be synchronized, in the best case scenario this is a communication challenge;
  • No traceability of database changes (changes history)
    •  There will always be a moment where you need to answer the following questions: What was changed? Who changed it? When it was changed? Why it was changed?
    • In the ideal world the source control system should be able to answer this questions;
  • Manual database processes prevent the Continuous Integration (CI) and Continuous Delivery (CD) in their full extent
    • The strength of your process is equal to the strength of your weakest step of the process;
    •  Is acceptable to say that if the process contains a manual step, you can not say that you have a automated process? (Almost automated is not automated)
  • Manual database release process
    • This process is time consuming and error prone;
    • This make the release process less frequent and risky;
  • Tests are manual or do not exists
    • This have a serious impact in the application quality;
  • Bugs in production environment
    • Database related bugs are only discovered after deployment to production;
    • Fixes and hot-fixes have time cost, what can lead to delay a release;
  • Inability to roll back to the previous version of the database
  • Database setup in a new environment is time consuming
  • Databases become a bottleneck in agile delivery processes
    • Databases can be the most expensive component and the bigger troublemaker (part of it is because it’s “easy to blame” the database for the application problems).

The adoption of the database automation allows to:

  • Enable control over database development
    • If you have control, you have better conditions to improve;
  • Keep a versioned “history” of database states;
  • Increase release frequency through repeatability of processes
    •  Greater predictability over releases;
    • Efficiency by eliminating the repetition of a range of manual activities
  • Reduce time spent fixing bugs
    • With more control you are less expose to error;
  • Speed up response to change
    • Automated deployment of smaller units of change;
  • Remove/reduce human intervention in the release process
    • The build step is automatic triggered by a “push” into source control repository;
    • The deploy step is automatic triggered by a successfully build process;
  • Have greater reliability of the release process
    • Providing a series of automated test stages prior to deployment;
  • Maintain an agile development environment

PortoData 15th December 2015 – Road to database automation

Last Tuesday (15 December 2015) I made my first presentation in Porto.Data community. “Road to database automation” was the title and the this session had two main goals: discuss the challenges of database inclusion in the Application Lifecycle Management (ALM) and present a set of strategies and tools to achieve this inclusion.

The effort of database inclusion in the “agile context” is designated as Database Lifecycle Management (DLM) and is composed by 3 main parts: database source control, database continuous integration and database continuous delivery.

In the first approach to database automation are presented some strategies/techniques and tools, such as Mercurial (TortoiseHg) and Git (SourceTree) for source control and Flyway for database migrations management,  to deal with the challenges that a database version control system implies.


Arduino Christmas Lights

One of my first projects using Arduino happened during the Christmas season, so seemed a good idea to create Christmas lights to illuminate and brighten the house.

The first step is to gather the necessary hardware:

  • 1 x Arduino, I used the Arduino 2009;
  • 2 x breadboard, should be enough to perform the tests;
  • 1 x switch button, it will be used to switch between the diferrent lightning effects;
  • 8 x red LEDs
  • 8 x green LEDs
  • 8 x yellow LEDs
  • 4 x 56Ω resistor
  • 4 x 33Ω resistor
  • 4 x 47Ω resistor
  • 1 x 10kΩ resistor
  • wiring

The second step is to connect LEDs so that there are no excessive current circulating in the circuit since a small change in voltage may cause large current variations (the current through an LED varies exponentially according to the voltage that crosses).

The solution is to use resistors, which have the purpose of limiting the amount of current to a safe value. To define the resistors values and the appropriate way to connect the LEDs I resorted to the following website: LED center.

Relevant information:

  • DC source voltage: 5V (Arduino output)
  • Forward voltage:
    • Red LED: 2.0V
    • Yellow LED: 2.1V
    • Green LED: 2.2V
  • Forward current: 20mA for 3mm and 5 mm LEDs
  • Number of LEDs: 4 x LEDs for each Arduino output (it were used 6 outputs)
LEDs_Red
Red LEDs
LEDs_Yellow
Yellow LEDs
LEDs_Green
Green LEDs

The represent the whole circuit I used Fritzing. This tool, which is an open-source project, allows documenting prototypes, share those same prototypes and even design the layout of a printed circuit board (PCB).

Circuit_Breadboard
Circuit using breadboards

To download the Fritzing file (.fz extension) click here.And so it concludes the part referring to the hardware.

The third step is the lighting effects program creation for Arduino. The program was written using the Arduino IDE and is defined by a set of lighting effects and the ability of switch between them. The following lighting effects were defined:

  1. blinkAllLights:  the lights turn on and off periodically;
  2. runningLights 0: the lights starts as off and then light up and down successively;
  3. fadingLights: the LEDs intensity is increased and decreased step by step;
  4. runningLights 255:  the lights starts as on and then light down and up successively;
  5. Adds the above affects,1 to 4, in a sequential manner within a loop.

The switch button lets you toggle between the different lighting effects. It should be mentioned that the fading effect implies using the PWM Arduino outputs.

The code can be downloaded from this link.

Merry Christmas and Happy New Year!

SQLSaturday#429 Porto 2015 experience

sqlSaturday429

My first contact with SQLSaturday event occurred in 2014 on the SQLSaturday#341 Porto 2014 edition as simple and curious attendant. A full day of learning, sharing and meeting people sounds to good to be true especially if it´s free. At the end of the day I was amazed, and asked myself why organize and event like this? The answer is quite simple: sharing.

The same principle lead me to join Porto.Data community. It was here that I met Ivan Campos  the community leader, who invited me to make part of the SQLSaturday#341 Porto 2015 organization. Of course I accepted his challenge with great pleasure and apprehension, after all I was just a “rookie”.

The organizing team was mainly composed by Ivan Campos (@WizardDBA), I (@EdPiairo), Paulo Correia (@paulofmc) and Filipe Coelho (@DataPlumbR). But we were not alone, a group of volunteers helped us materializing the event, and we also count with the guidance from the members with more experience like Niko Neugebauer (@NikoNeugebauer), Pedro Simões (@pedro_mg_simoes ), and others. I’m proud to say that the event was a great success and I want to thank all who contributed to this success.

This success was the result of the work carried out over months before the event. As important as the final result of the event were the lessons learned.  The importance of having a plan was well understood by the team. It’s very important that each member know what, when and where to do.  It´s also important to exist the role “plan master”, who is responsible for ensuring that the plan is being implemented. The plan and the plan master gain more relevance when the team has junior members (as me).  Here’s a tip: you should have a survival guide to help the team in the organization process allowing better deal with stress/adrenaline.

In the end of the day I presented two types of feelings. As a team, we share the “mission accomplished” feeling, we were then a team. As an individual, a feeling of “profit”, I had the opportunity to know and hang out with the speakers, the opportunity to meet new people, the opportunity to learn and share knowledge.

For all the reasons presented above I leave you this advice: you definitively should try!

Ultimately, I want thank Ivan for his invite, wich has provided me a great experience, and congratulate him for the success as leader of the event organization.