The Ultimate Showdown of Database Migration Tools

Transcript

1. The Ultimate Showdown of Database Migration Tools Pasha Finkelshteyn VS Anton Arhipov @asm0di0 @antonarhipov
2. Relational DBs Application-first approach & box products What are we talking about
3. Why do we need migrations? Manage schema versions in the db-first approach Maintain schema compatibility in app-first approach On-premise software, where we don’t control the update cycle
4. Why do we need migrations? If you don’t use one of the popular tools, you’ll have to write your own
6. You have a column full_name and you need to split it by space into first_name and last_name
7. You have a column full_name and you need to split it by space into first_name and last_name
8. You have a column full_name and you need to split it by space into first_name and last_name
9. Change Format
10. SQL src/main/resources/db/migration/V1__Create_person_table.sql ALTER TABLE users ADD COLUMN first_name VARCHAR(255); ALTER TABLE users ADD COLUMN last_name VARCHAR(255); UPDATE users SET first_name = split_part(full_name, ' ', 1), last_name = split_part(full_name, ' ', 2);
11. Java src/main/java/db/migration/V3__Anonymize.java public class V1__SplitFullName extends BaseJavaMigration { @Override public void migrate(Context context) throws Exception { try (Statement stmt = context.getConnection().createStatement()) { stmt.execute("ALTER TABLE users ADD COLUMN first_name VARCHAR(255)"); stmt.execute("ALTER TABLE users ADD COLUMN last_name VARCHAR(255)"); stmt.execute(""" UPDATE users SET first_name = split_part(full_name, ' ', 1), last_name = split_part(full_name, ' ', 2) """);
12. Why Java? Complex data transformations LOB operations Conditional logic External interactions
13. <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.6.xsd"> <changeSet id="split-full-name" author="pasha"> <addColumn tableName="users"> <column name="first_name" type="VARCHAR(255)"/> <column name="last_name" type="VARCHAR(255)"/> </addColumn> <update tableName="users"> <column name="first_name" valueComputed="split_part(full_name, ' ', 1)"/> <column name="last_name" valueComputed="split_part(full_name, ' ', 2)"/> </update> </changeSet> </databaseChangeLog> XML
14. YAML databaseChangeLog: - changeSet: id: split-full-name author: pasha changes: - addColumn: tableName: users columns: - name: first_name type: VARCHAR(255) - name: last_name type: VARCHAR(255) - update: tableName: users columns: - name: first_name valueComputed: split_part(full_name, ' ', 1) - name: last_name valueComputed: split_part(full_name, ' ', 2)
15. JSON { "databaseChangeLog": [ { "changeSet": { "id": "split-full-name", "author": "pasha", "changes": [ { "addColumn": { "tableName": "users", "columns": [ { "name": "first_name", "type": "VARCHAR(255)" }, { "name": "last_name", Too long, doesn’t fit the screen
16. SQL --liquibase formatted sql --changeset pasha:split-full-name ALTER TABLE users ADD COLUMN first_name VARCHAR(255); ALTER TABLE users ADD COLUMN last_name VARCHAR(255); UPDATE users SET first_name = split_part(full_name, ' ', 1), last_name = split_part(full_name, ' ', 2);
17. Groovy liquibaseRuntime('org.liquibase:liquibase-groovy-dsl:4.0.0') { exclude group: "org.codehaus.groovy", module: "groovy" exclude group: "org.codehaus.groovy", module: "groovy-sql" } liquibaseRuntime "org.apache.groovy:groovy:4.0.5" liquibaseRuntime "org.apache.groovy:groovy-sql:4.0.5" sql { comment('we should not have added this...') 'delete from my_table' } sql { """ insert into some_table(data_column, date_inserted) values('some_data', '${new Date().toString()}') """ }
18. Round 1
19. Looks good, right?
20. But… Western Naming Order: First Name + Family Name) In East Asia, the family name usually comes first. Single names (Mononyms) Arabic naming conventions Middle names and multiple given names
21. Can and will do roll back for you! Rollback support
22. <changeSet id="split-full-name" author="pasha"> <addColumn tableName="users"> <column name="first_name" type="VARCHAR(255)"/> <column name="last_name" type="VARCHAR(255)"/> </addColumn> <update tableName="users"> <column name="first_name" valueComputed="split_part(full_name, ' ', 1)"/> <column name="last_name" valueComputed="split_part(full_name, ' ', 2)"/> </update> </changeSet> XML
23. <changeSet id="split-full-name" author="pasha"> <addColumn tableName="users"> <column name="first_name" type="VARCHAR(255)"/> <column name="last_name" type="VARCHAR(255)"/> </addColumn> <update tableName="users"> <column name="first_name" valueComputed="split_part(full_name, ' ', 1)"/> <column name="last_name" valueComputed="split_part(full_name, ' ', 2)"/> </update> <rollback> <dropColumn tableName="users" columnName="first_name"/> <dropColumn tableName="users" columnName="last_name"/> </rollback> </changeSet> XML
24. Rollback support Can and will do roll back for you! Rollbacks are a lie! You should write them manually, as a usual migration
25. Round 2
26. Fixed scheme of versioning: V for migration R for repeated Versioning Scheme
27. R - Repeated migrations CREATE TABLE IF NOT EXISTS app_metadata ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); INSERT INTO app_metadata (key, value) VALUES ('app_version', '1.0.0') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value; R__Insert_app_version.sql
28. R - Repeated migrations -- ${flyway:timestamp} CREATE TABLE IF NOT EXISTS app_metadata ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); INSERT INTO app_metadata (key, value) VALUES ('app_version', '1.0.0') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value; R__Insert_app_version.sql Updates the checksum of the file
29. R - Repeated migrations CREATE TABLE IF NOT EXISTS app_metadata ( key TEXT PRIMARY KEY, value TEXT NOT NULL ); INSERT INTO app_metadata (key, value) VALUES ('git_commit', '${git.commit}') ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value; R__Insert_app_version.sql Inserts the git hash
30. Fixed scheme of versioning: V for migration R for repeated Versioning Scheme B for baseline
31. B - Baseline migrations db/migrations ├── V1__create_table_users.sql ├── V2__create_table_orders.sql ├── V3__create_table_products.sql ├── V4__insert_data.sql └── B1.0__initial_baseline.sql
32. Fixed scheme of versioning: V for migration R for repeated B for baseline Versioning Scheme U for undo
33. Fixed scheme of versioning: V for migration R for repeated B for baseline U for undo Versioning Scheme Anything you want!
34. migrations/ ├── changelog-001-create-users.xml ├── changelog-002-create-orders.xml ├── changelog-003-insert-default-users.xml ├── changelog-004-add-index-orders-date.xml ├── changelog-005-alter-users-add-column-status.xml ├── changelog-006-update-user-status-default.xml ├── changelog-007-drop-legacy-table.xml └── db-changelog.xml
35. migrations/ ├── schema/ │ ├── 001-create-users.sql │ ├── 002-create-orders.json │ ├── 005-alter-users-add-column-status.sql │ └── 007-drop-legacy-table.json ├── data/ │ ├── 003-insert-default-users.sql │ └── 006-update-user-status-default.json ├── index/ │ └── 004-add-index-orders-date.sql └── db-changelog.xml
36. Uses checksums but allows them to be disabled or updated Flyway strictly enforces checksums for migration integrity Versioning: Checksum support
37. Round 3
38. Learning Curve
39. • Learn supported languages • Make your choice • Invent best practices and guidelines Learning Curve
40. • Learn supported languages • Make your choice • Invent best practices and guidelines Just write your migrations • SQL where it’s enough • Java in case if SQL is not enough Learning Curve
41. Round 4
42. Preconditions -- Flyway precondition to check if the app_version table exists -- precondition: assert that the table 'app_version' exists -- precondition-on-fail: WARN (logs a warning and continues) -- precondition-sql: SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'app_version' -- Migration to add a new column to app_version ALTER TABLE app_version ADD COLUMN description VARCHAR(255);
43. Preconditions -- Flyway precondition to check if the app_version table exists -- precondition: assert that the table 'app_version' exists -- precondition-on-fail: WARN (logs a warning and continues) -- precondition-sql: SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'app_version' -- Migration to add a new column to app_version ALTER TABLE app_version ADD COLUMN description VARCHAR(255);
44. Preconditions -- Flyway precondition to check if the app_version table exists -- precondition: assert that the table 'app_version' exists -- precondition-on-fail: WARN (logs a warning and continues) -- precondition-sql: SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'app_version' -- Migration to add a new column to app_version ALTER TABLE app_version ADD COLUMN description VARCHAR(255);
45. Preconditions -- Flyway precondition to check if the app_version table exists -- precondition: assert that the table 'app_version' exists -- precondition-on-fail: WARN (logs a warning and continues) -- precondition-sql: SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'app_version' -- Migration to add a new column to app_version ALTER TABLE app_version ADD COLUMN description VARCHAR(255);
46. Preconditions -- Flyway precondition to check if the app_version table exists -- precondition: assert that the table 'app_version' exists -- precondition-on-fail: WARN (logs a warning and continues) -- precondition-sql: SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'app_version' -- Migration to add a new column to app_version ALTER TABLE app_version ADD COLUMN description VARCHAR(255);
47. Preconditions -- Flyway precondition to check if the app_version table exists -- precondition: assert that the table 'app_version' exists -- precondition-on-fail: WARN (logs a warning and continues) -- precondition-sql: SELECT COUNT(*) FROM information_schema.tables WHERE table_name = 'app_version' -- Migration to add a new column to app_version ALTER TABLE app_version ADD COLUMN description VARCHAR(255);
48. Preconditions: different databases?
49. https://www.red-gate.com/hub/product-learning/flyway/using-flyway-as-a-multi-database-migration-system
50. <databaseChangeLog> <changeSet id="add_description_column" author="dev"> <preconditions onFail="WARN"> <or> <!-- PostgreSQL: Check if app_version table exists in information_schema --> <sqlCheck expectedResult="1" dbms="postgresql"> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = 'app_version' </sqlCheck> <!-- MySQL: Check if app_version table exists in information_schema --> <sqlCheck expectedResult="1" dbms="mysql"> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'app_version' </sqlCheck> <!-- Oracle: Check if app_version table exists in user_tables --> <sqlCheck expectedResult="1" dbms="oracle"> SELECT COUNT(*) FROM user_tables WHERE table_name = 'APP_VERSION' </sqlCheck>
51. <databaseChangeLog> <changeSet id="add_description_column" author="dev"> <preconditions onFail="WARN"> <or> <!-- PostgreSQL: Check if app_version table exists in information_schema --> <sqlCheck expectedResult="1" dbms="postgresql"> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = 'app_version' </sqlCheck> <!-- MySQL: Check if app_version table exists in information_schema --> <sqlCheck expectedResult="1" dbms="mysql"> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'app_version' </sqlCheck> <!-- Oracle: Check if app_version table exists in user_tables --> <sqlCheck expectedResult="1" dbms="oracle"> SELECT COUNT(*) FROM user_tables WHERE table_name = 'APP_VERSION' </sqlCheck>
52. <sqlCheck expectedResult="1" dbms="postgresql"> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = current_schema() AND table_name = 'app_version' </sqlCheck> <!-- MySQL: Check if app_version table exists in information_schema --> <sqlCheck expectedResult="1" dbms="mysql"> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'app_version' </sqlCheck> <!-- Oracle: Check if app_version table exists in user_tables --> <sqlCheck expectedResult="1" dbms="oracle"> SELECT COUNT(*) FROM user_tables WHERE table_name = 'APP_VERSION' </sqlCheck> </or> </preconditions> <addColumn tableName="app_version"> <column name="description" type="varchar(255)"/>
53. <!-- MySQL: Check if app_version table exists in information_schema --> <sqlCheck expectedResult="1" dbms="mysql"> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'app_version' </sqlCheck> <!-- Oracle: Check if app_version table exists in user_tables --> <sqlCheck expectedResult="1" dbms="oracle"> SELECT COUNT(*) FROM user_tables WHERE table_name = 'APP_VERSION' </sqlCheck> </or> </preconditions> <addColumn tableName="app_version"> <column name="description" type="varchar(255)"/> </addColumn> </changeSet> </databaseChangeLog>
54. SELECT COUNT(*) FROM user_tables WHERE table_name = 'APP_VERSION' </sqlCheck> </or> </preconditions> <addColumn tableName="app_version"> <column name="description" type="varchar(255)"/> </addColumn> </changeSet> </databaseChangeLog>
55. Preconditions Robust precondition checking before applying changes Simple SQL preconditions For complex situations use Java For multiple databases - script all the things!
56. Round 5
57. Diff support
58. Can generate schema differences between environments Diff support
59. Diff support Community edition - No :( Enterprise - Yes (but we have never tried it) Can generate schema differences between environments
60. Round 6
61. Configuration Complexity
62. Configuration Complexity Configuration?
63. Configuration Complexity A lot of configuration options, anything is configurable, have to figure it out yourself Configuration?
64. <changeSet id="create-and-populate-table" author="example" runInTransaction="false"> <createTable tableName="example_table"> <column name="id" type="INT"> <constraints primaryKey="true" nullable="false"/> </column> <column name="name" type="VARCHAR(255)"/> </createTable> <insert tableName="example_table"> <column name="id" valueNumeric="1"/> <column name="name" value="First Entry"/> </insert> <!-- Intentional error: 'nonexistent_column' does not exist --> <insert tableName="example_table"> <column name="id" valueNumeric="2"/> <column name="nonexistent_column" value="This will cause an error"/> </insert> </changeSet>
65. <changeSet id="create-and-populate-table" author="example" runInTransaction="false"> <createTable tableName="example_table"> <column name="id" type="INT"> <constraints primaryKey="true" nullable="false"/> </column> <column name="name" type="VARCHAR(255)"/> </createTable> <insert tableName="example_table"> <column name="id" valueNumeric="1"/> <column name="name" value="First Entry"/> </insert> <!-- Intentional error: 'nonexistent_column' does not exist --> <insert tableName="example_table"> <column name="id" valueNumeric="2"/> <column name="nonexistent_column" value="This will cause an error"/> </insert> </changeSet>
66. <changeSet id="create-and-populate-table" author="example" runInTransaction="false"> <createTable tableName="example_table"> <column name="id" type="INT"> <constraints primaryKey="true" nullable="false"/> </column> <column name="name" type="VARCHAR(255)"/> </createTable> <insert tableName="example_table"> <column name="id" valueNumeric="1"/> <column name="name" value="First Entry"/> </insert> <!-- Intentional error: 'nonexistent_column' does not exist --> <insert tableName="example_table"> <column name="id" valueNumeric="2"/> <column name="nonexistent_column" value="This will cause an error"/> </insert> </changeSet>
67. flyway.conf flyway.mixed=true
68. flyway.conf flyway.mixed=true V1__create_users_table.sql -- flyway:transactional -- This migration runs entirely within a transaction CREATE TABLE users ( id SERIAL PRIMARY KEY, full_name VARCHAR(255) NOT NULL );
69. flyway.conf flyway.mixed=true V1__create_users_table.sql -- flyway:transactional -- This migration runs entirely within a transaction CREATE TABLE users ( id SERIAL PRIMARY KEY, full_name VARCHAR(255) NOT NULL );
70. Round 7
71. Change Approach
72. Change Approach Change-based: Just execute your SQL!
73. Change Approach Change-based: Just execute your SQL! Analyze database state Write a change describing what schema should be Write pre-checks and conditions
74. Round 8
76. Round 9