#FlywayDB Migration

[learning-code-ficusoft]

Steps to automate schema changes and data pipeline deployments with GitHub or Azure DevOps.

Managing database schema changes and automating data pipeline deployments is critical for ensuring consistency, reducing errors, and improving efficiency. This guide outlines the steps to achieve automation using GitHub Actions or Azure DevOps Pipelines.

Step 1: Version Control Your Schema and Pipeline Code

Store database schema definitions (SQL scripts, DB migration files) in a Git repository.

Keep data pipeline configurations (e.g., Terraform, Azure Data Factory JSON files) in version control.

Use branching strategies (e.g., feature branches, GitFlow) to manage changes safely.

Step 2: Automate Schema Changes (Database CI/CD)

To manage schema changes, you can use Flyway, Liquibase, or Alembic.

For Azure SQL Database or PostgreSQL (Example with Flyway)

Store migration scripts in a folder:

pgsql

├── db-migrations/ │ ├── V1__init.sql │ ├── V2__add_column.sql

Create a GitHub Actions workflow (.github/workflows/db-migrations.yml):

yaml

name: Deploy Database Migrations on: [push] jobs: deploy: runs-on: ubuntu-latest steps: - name: Checkout code uses: actions/checkout@v3 - name: Install Flyway run: curl -L https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/9.0.0/flyway-commandline-9.0.0-linux-x64.tar.gz | tar xvz && mv flyway-*/flyway /usr/local/bin/ - name: Apply migrations run: | flyway -url=jdbc:sqlserver://$DB_SERVER -user=$DB_USER -password=$DB_PASS migrate

In Azure DevOps, you can achieve the same using a YAML pipeline:

yaml

trigger: branches: include: - main pool: vmImage: 'ubuntu-latest' steps: - checkout: self - script: | flyway -url=jdbc:sqlserver://$(DB_SERVER) -user=$(DB_USER) -password=$(DB_PASS) migrate

Step 3: Automate Data Pipeline Deployment

For Azure Data Factory (ADF) or Snowflake, deploy pipeline definitions stored in JSON files.

For Azure Data Factory (ADF)

Export ADF pipeline JSON definitions into a repository.

Use Azure DevOps Pipelines to deploy changes:

yaml

trigger: branches: include: - main pool: vmImage: 'ubuntu-latest' steps: - task: AzureResourceManagerTemplateDeployment@3 inputs: deploymentScope: 'Resource Group' azureSubscription: 'AzureConnection' resourceGroupName: 'my-rg' location: 'East US' templateLocation: 'Linked artifact' csmFile: 'adf/pipeline.json'

For GitHub Actions, you can use the Azure CLI to deploy ADF pipelines:

yaml

steps: - name: Deploy ADF Pipeline run: | az datafactory pipeline create --factory-name my-adf --resource-group my-rg --name my-pipeline --properties @adf/pipeline.json

Step 4: Implement Approval and Rollback Mechanisms

Use GitHub Actions Environments or Azure DevOps approvals to control releases.

Store backups of previous schema versions to roll back changes.

Use feature flags to enable/disable new pipeline features without disrupting production.

Conclusion

By using GitHub Actions or Azure DevOps, you can automate schema changes and data pipeline deployments efficiently, ensuring faster, safer, and more consistent deployments.

WEBSITE: https://www.ficusoft.in/snowflake-training-in-chennai/

[inextures]

FlywayDB Migration with Spring Boot

What is Flyway DB?

Flyway is an open-source database migration tool that helps you manage and version your database schema changes across all your instances. It supports various databases such as MySQL, Oracle, SQL Server, PostgreSQL, and many more. To learn more about Flyway, you can use the flywaydb. Many software projects use relational databases. This requires the handling of database migrations, also often called schema migrations.

The first and most important practice is not to use “spring.jpa.hibernate.ddl-auto=create/update/create-drop” in production. With these properties, you could update and migrate your database schema with Hibernate directly. This might be a valid option for pet projects, but not for enterprise applications in production as you can’t control the automatic migration process in detail. You also won’t get information about the current database schema version of an environment (e.g. staging, test, dev …), so thats why we can use Flyway to resolve the above mentioned problems. In this blog, we will explore how to use Flyway with Spring Boot in detail.

Configuring Flyway Database :

Add below flyway-core dependancy to enable FlywayDB in springboot application :

<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>

NOTE : For the spring-boot version 3.0.2 and greater, we have to use version 17 for JDK and for JDK17 flyway is not supported so we have to add one extra dependency based on your database, Which is given below :

<dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-mysql</artifactId> </dependency>

It is based on just 7 basic commands:Migrate,Clean,Info,Validate,Undo,Baseline andRepair (This is not supported in community version).

Add below properties into the “application.properties” file :

# Flyway Configuration spring.flyway.baseline-on-migrate=true spring.flyway.enabled=true spring.flyway.locations=classpath:db/migration spring.jpa.hibernate.ddl-auto=validate

Creating Database Migrations :

Flyway requires you to create database migration scripts, under the path db/migration folder specified as per in properties file, with filename in a specific format that includes a version number & descriptive name, for e.g. “V[VERSION_NUMBER]__[NAME].sql” with SQL statements to be executed/validated before starting the server.

For example, let’s say we want to create a table called users in our database. We can create a migration script with the following name “V1__Create_User_Table.sql” with below sql statements :

CREATE TABLE USERS ( ID INT AUTO_INCREMENT PRIMARY KEY, USERID int, Name varchar(20) );

Conclusion :

In conclusion, Flyway is a powerful tool that enables easy database migration management in Spring Boot applications. With its seamless integration with Spring Boot, developers can easily manage database schema changes and updates throughout the application development lifecycle. The use of Flyway simplifies the process of database migration, reduces the risk of data loss, and makes it easy to maintain database versions in a structured manner. By adopting Flyway with Spring Boot, developers can focus on building the core functionality of their application, while Flyway takes care of the database migration process in a simple and efficient way.

Originally published by: FlywayDB Migration with Spring Boot

[craigbrownphd-blog-blog]

Configuring and Accessing Datasource With FlyWayDB: Getting Started With Java Spring

We already built a basic CRUD application with Spring Boot in my Building a Basic CRUD RESTFull Spring Boot MVC application. There, we created our Rest Controllers and integrated it with an AngularJS front-end. Despite that, we did not integrate it with a database, we just give back stubbed data so far. It is well known that is no web application is any of worth without a proper DataSource configured up to store data in it. In this module, we focus on configuring and accessing DataSource with FlyWayDB in Spring Boot. This will involve working with the Spring Boot starters and the auto-configuration. We work with the Data JPA framework, entity models, and the FlywayDB database migration framework. After this article, you will have a better understanding of how Spring Boot integrates with 3rd party frameworks as well with the usage of Spring-Boot starters. https://goo.gl/1gDwJw

[nehanguyen]

Introduction To Vert.x: Using Traditional Java Libraries With Vert.x

In this Java tutorial we explore how you can use traditional blocking operations in the asynchronous and non-blocking Vert.x Verticles. We also talk about Currying with Vert.x Handlers. We discuss using FlywayDB to do database schema migrations.

source https://morioh.com/p/65a9cb14bb9f

[mrfamous001]

Ask HN: How do you organize and manage database migrations? 9 by anonfunction | 0 comments on Hacker News. When building services that rely on relational databases, in my case postgres, what are some best practices and tools to help manage schema changes? We've been using migrations and doing it all manually but it's become a bottleneck and a little bit of a nightmare with multiple consumers of the database needing to make schema changes. Another concern is multiple environments, from local development to staging and production. We're using docker-compose for local development which runs the entire "full" schema and then I'm manually applying the migration files to staging and production before we deploy. I've looked at some projects like flywaydb[1] and liquibase[2] but both are not completely free and seem proprietary. Does anyone know of another open source system that could help manage database schema versioning and migrations? Thanks so much HN, this is something that I have been struggling with. 1: http://bit.ly/2Jz98t0 2: http://bit.ly/31XVJkB

[topicprinter]

When building services that rely on relational databases, in my case postgres, what are some best practices and tools to help manage schema changes?

We've been using migrations and doing it all manually but it's become a bottleneck and a little bit of a nightmare with multiple consumers of the database needing to make schema changes.

Another concern is multiple environments, from local development to staging and production. We're using docker-compose for local development which runs the entire "full" schema and then I'm manually applying the migration files to staging and production before we deploy.

I've looked at some projects like flywaydb[1] and liquibase[2] but both are not completely free and seem proprietary. Does anyone know of another open source system that could help manage database schema versioning and migrations?

Thanks so much HN, this is something that I have been struggling with.

1: https://flywaydb.org/

2: https://www.liquibase.org/

Comments URL: https://news.ycombinator.com/item?id=21405501

Points: 9

# Comments: 3