Simplifying Data Import and Export for Database Development

Extracting and importing data for development and testing is made trickier due to issues such as constraints, dependencies, and special data types. This article introduces a cross-RDBMS solution with JSON for data storage and PowerShell cmdlets that use ODBC to help automate extraction and import, and JSON Schema for validation. Just provide a DSN, and you’re good to go! Read more

Planning a Database Testing Strategy for Flyway

With Flyway, you can adopt a test-driven development strategy that will allow you to test and evaluate databases, and database objects, at every phase of the database development lifecycle. The further down the delivery pipeline that bugs appear, the more costly in time and resources they are to fix. This approach will allow you to catch many of them before the database change even gets committed to version control, making a continuous delivery process much easier to adopt and sustain. Read more

Running Database Assertion Tests with Flyway

A database assertion test aims to check that data within a relational database conforms to specific business rules. This article demonstrates how to run these assertion tests automatically in Flyway, checking that the queries under test always produce the expected results. Read more

Running Structured Database Tests in Flyway

How to run structured Arrange-Asset-Act-Teardown (AAAT) tests, each time Flyway successfully migrates a database, where you can annotate each test script to specify exactly how the test run should proceed, and then save all results and any errors to a simple ASCII report. Read more

A Test Data Management Strategy for Database Migrations

This article will help you understand the steps to better test data management, when using a migrations-based approach to database development and deployment. It explains the different types of data required and why, the need for separation of DDL and DML code, and the most efficient way to create, load and switch between the different required data sets. Read more

Infrequently Asked Questions About Flyway

You can have Flyway up and running in minutes if you're a solo developer managing a single database. However, as you seek to 'scale up' Flyway to accommodate more complex database systems, team-based development, and stricter quality controls, you'll need to tackle some knottier questions. Without adequate answers, tasks such as multi-database management, automation and workflow, and Continuous Integration will be difficult. Hopefully, this article will help. Read more

Testing Databases: What’s Required?

An overview of the challenges of database testing and test data management, reviewing the different types of database test that need to run during development work, what sort of test data they require, and how to manage all the required data sets, during development, in a way that allows rapid cycles of parallel testing. Read more

Transferring Database Documentation into SQL DDL Scripts

We all love having documentation in source code, if not writing it. We just want to ensure that it gets propagated and retained so you and your team members can read it if they need to. This article demonstrates a cross-RDBMS PowerShell task that will extract the comments from a primary JSON source of database documentation and add it to a set of SQL DDL source files. Read more

Cross-RDBMS Code Quality Reports in Flyway

Before you commit your Flyway migration files, you may want to run some automated checks for style or 'code smells'. This article demonstrates how to run basic cross-RDBMS code quality checks using SQL Fluff. We analyze the results in PowerShell to produce reports and analytics on the number or types of issues found. Read more

Reviewing SQL Migration Files Before a Flyway Migration

Your finger is hovering over the 'enter' key to set off the Flyway "migrate" command, but you hesitate. There is a large stack of migration files for this project: don't all these files need to be checked first? Yes, they do, but how? This article demonstrates how, once armed with the file path locations of all the scripts, you can use PowerShell to search them for various purposes such to review them for potentially disruptive changes, or run code quality checks, or to verify documentation standards. Read more

Try Before You Commit in Flyway

Sometimes we want to check whether it is possible to run a Flyway migration without error, but not actually make the changes. We might just need to 'sanity test' the performance of a migration on the Staging server, for example. By using a placeholder 'switch' to trigger a SQL Exception, we can get Flyway to roll-back its transaction, and therefore the migration, on demand. Read more

Managing Datasets for Database Development Work using Flyway

A cross-RDBMS way of exporting, deleting and inserting data, for database development work. It is a PowerShell automation technique for Flyway that uses JSON files for data storage. It should help a team maintain datasets between database versions, as well as to switch between the datasets required to support different types of testing. Read more

Performance Testing Databases with Flyway and PowerShell

Performance tests are central to the quality of the database changes we deliver because they ensure that any business process that accesses the database continues to return its results in an acceptable time. When Flyway creates a new version of the database, it is the ideal time to run these performance checks. Read more

Running Unit and Integration Tests during Flyway Migrations

In this article I'll give a practical example of developing a database, with Flyway, in such a way that it is automatically tested with whatever unit tests and integration tests you specify whenever you migrate the branch you're working on to the next version. If a test fails, you can work out why, undo the migration and then try again. Read more

Basic Functional Testing for Databases Using Flyway

How to use Flyway Teams to run basic tests whenever it successfully executes a migration, checking that all the business processes supported by our database always produce the expected results. Read more

Taming Database Documentation with Flyway and MySQL

Database object documentation is essential for explaining to busy developers, and the wider business, the purpose of each object and how to use it. The solution presented in this article consists of a SQL script to allow developers to add comments to MySQL database objects, without affecting the database version, and a simple way to generate a documentation report, in JSON. The SQL script will execute automatically as a callback, during any Flyway Teams migration run, and the report will allow the team to spot any gaps quickly. Read more

Dealing with Database Data and Metadata in Flyway Developments

Before you get very far with database development and testing, you need to be clear about your strategy for handling data. In this article I'll explain some of these issues in general terms, and then demonstrate how you can navigate these problems easily with Flyway. Read more

Debugging Flyway Callbacks and Migrations that Use Placeholders

Placeholders come in very handy in Flyway, but troubleshooting the SQL migration and callback scripts that use them can be tricky. This articles demonstrates how to develop, test and debug these scripts in a tool designed for these tasks, such as SSMS, leaving Flyway to do what it's designed for, which is running the scripts. Read more

Testing a Flyway Database Migration

Often, we want to test the new version of a database, produced by a Flyway migration, before committing the new migration file, or to test the same migration run on a number of different databases. This article demonstrates how to do it, by generating and using JSON parameter files to run a series of Flyway actions on any number of databases, on any number of servers. Read more

Bulk Loading Data via a PowerShell Script in Flyway

The test data management strategy for any RDBMS needs to include a fast, automated way of allowing developers to "build-and-fill" multiple copies of any version of the database, for ad-hoc or automated testing. The technique presented in this article uses a baseline migration script to create the empty database at the required version, which then triggers a PowerShell callback script that bulk loads in the right version of the data. Read more

Adding Test Data to Databases During Flyway Migrations

How to generate "realistic but fake" SQL Server test data, using SQL Data Generator, and then use SQL Data Compare to produce an INSERT script that Flyway can run to load the data into the database. The technique is useful for managing small volumes of test data or for "topping up" existing data when you create new entities. Read more

Dry Runs for Database Migrations using Flyway

How Dry Run scripts work, and how they can be used to deliver a single-script release artifact to Staging, verify placeholder substitutions in SQL migration scripts, and simplify team code reviews. Read more

Test-Driven Development for Flyway Using Transactions

When you are using Flyway, you can easily adopt test-driven development practices that will allow you to test your database migration script, to make sure it works exactly as you intended, before you even let Flyway execute it. Read more

Managing SQL Server Database Documentation during Flyway Development

How to create and maintain a 'data dictionary' for your SQL Server databases, in JSON format, which you can then use to add and update the table descriptions, whenever you build a new version using Flyway. Read more

Running Dynamic Code Quality Checks on Flyway Databases: Table Smells

How to extend the range of SQL code analysis, during database development, to include dynamic analysis of the database metadata. This will allow regular checks for problems with the design of your tables and indexes ("table smells") that can affect database performance and data integrity. Read more

Running SQL Code Analysis during Flyway Migrations

A set of PowerShell automation script tasks for running database build and migrations tasks. This article describes the SQL code analysis task, which will check the syntax of the SQL code in your databases and your migration scripts for 'code smells'. Read more

Getting Data In and Out of SQL Server Flyway Builds

This article provides SQL "bulk copy" routines to extract data from and load it into a SQL Server database, rapidly, and demonstrates a way to automatically bulk insert the test data in a Flyway build, using a SQL callback. Read more