DATABASE SCHEMA MIGRATIONS
WITHOUT DOWNTIME
MICHIEL ROOK
@michieltcs
Slide 2
Slide 2 text
@michieltcs
Slide 3
Slide 3 text
@michieltcs
CONTINUOUS DEPLOYMENT
Slide 4
Slide 4 text
@michieltcs
50+ DEPLOYS PER DAY
Slide 5
Slide 5 text
@michieltcs
HIGH AVAILABILITY
Slide 6
Slide 6 text
@michieltcs
DATABASE MIGRATIONS
Slide 7
Slide 7 text
@michieltcs
WITHOUT DOWNTIME?
Slide 8
Slide 8 text
@michieltcs
DATABASE SCHEMA
MIGRATIONS
Slide 9
Slide 9 text
@michieltcs
DATABASE SCHEMA
Slide 10
Slide 10 text
@michieltcs
Slide 11
Slide 11 text
@michieltcs
Slide 12
Slide 12 text
@michieltcs
DATABASE SCHEMA
MIGRATIONS
Slide 13
Slide 13 text
@michieltcs
SQL
Slide 14
Slide 14 text
@michieltcs
UP
Slide 15
Slide 15 text
@michieltcs
UP
CREATE TABLE addresses(id INT NOT NULL,
street VARCHAR(255) NOT NULL, PRIMARY KEY('id'));
Slide 16
Slide 16 text
@michieltcs
UP DOWN
Slide 17
Slide 17 text
@michieltcs
UP DOWN
DROP TABLE addresses;
Slide 18
Slide 18 text
@michieltcs
TRANSACTIONS
Slide 19
Slide 19 text
@michieltcs
Slide 20
Slide 20 text
@michieltcs
FLYWAY
Slide 21
Slide 21 text
@michieltcs
V20170228150619__create_addresses_table.sql
CREATE TABLE addresses (id INT NOT NULL, street VARCHAR(255)
NOT NULL, PRIMARY KEY(id));
Slide 22
Slide 22 text
@michieltcs
$ flyway migrate
Flyway Community Edition 5.0.7 by Boxfuse
Database: jdbc:mysql://localhost:3306/migrate (MySQL 5.7)
Successfully validated 1 migration (execution time 00:00.018s)
Creating Schema History table: `migrate`.`flyway_schema_history`
Current version of schema `migrate`: << Empty Schema >>
Migrating schema `migrate` to version 20170228150619 - create addresses table
Successfully applied 1 migration to schema `migrate` (execution time 00:00.086s)
@michieltcs
ADDING
TABLES
ADDING
COLUMNS
CREATING
INDEXES
sometimes ...
Slide 78
Slide 78 text
@michieltcs
EXAMPLE:
RENAMING A COLUMN
Slide 79
Slide 79 text
@michieltcs
surname last_name
Slide 80
Slide 80 text
@michieltcs
EXPAND - CONTRACT
Slide 81
Slide 81 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
1
Slide 82
Slide 82 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
1
ALTER TABLE person
ADD last_name VARCHAR(255);
Slide 83
Slide 83 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
1 2
Slide 84
Slide 84 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
1 2
public void setLastName(String lastName) {
this.lastName = lastName;
this.surname = lastName;
}
Slide 85
Slide 85 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
1 2
public String getLastName() {
return (lastName ? lastName : surname);
}
Slide 86
Slide 86 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
MIGRATE OLD
RECORDS
1 2 3
Slide 87
Slide 87 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
MIGRATE OLD
RECORDS
1 2 3
UPDATE person SET person.last_name = person.surname
WHERE person.last_name IS NULL;
Slide 88
Slide 88 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
MIGRATE OLD
RECORDS
READ FROM
NEW COLUMN
1 2 3 4
Slide 89
Slide 89 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
MIGRATE OLD
RECORDS
READ FROM
NEW COLUMN
1 2 3 4
public String getLastName() {
return lastName;
}
Slide 90
Slide 90 text
@michieltcs
CREATE
COLUMN WITH
NEW NAME
WRITE TO OLD &
NEW COLUMNS
MIGRATE OLD
RECORDS
READ FROM
NEW COLUMN
1 2 3 4
remove old
column and
code