Developer-Centric SQL Migrations Table of Contents Ausgangssituation Was gehört zu einer performanten Migration? Was ist die Herausforderung bei gleichzeitigen Entwicklungstätigkeiten? Wie kann jOOQ helfen? Das Beispielprojekt Datenbank aufsetzen Spring Shell bauen Testdaten erzeugen Migrationsskripte erstellen Migrationsskripte ausführen Zusätzlichen Index erstellen Spalte umbenennen Was nicht funktioniert Fazit IT-Modernisierungsprojekte enthalten oft die Herausforderung, über die Jahre umfangreich gewachsene Bestandsdaten in die Zielarchitektur zu migrieren. Dieser Artikel zeigt wie jOOQ dabei unterstützen kann, sowohl Änderungen am Source- als auch am Target-Schema zu erkennen, einige Fehler durch Automatisierung zu vermeiden und Änderungen durch Typsicherheit zur Compile-Zeit verifizieren zu können. Ausgangssituation Die hier beschriebene Situation erlebten wir in ähnlicher Form bereits in einigen Migrationsprojekten. Das Quellsystem ist eine Oracle-Datenbank, das Zielsystem eine PostgreSQL-Datenbank. Dabei wurde die Oracle Datenbank bisher im Wesentlichen als Datensenke betrachtet und enthält keine Geschäftslogik. Die Datenbank wurde mittels ora2pg in ein Schema "Staging" in PostgreSQL migriert. Dieses Schema halten wir dabei in derselben Datenbank wie das spätere Zielschema. Wir wählen diesen Ansatz, damit wir einfache SQL Abfragen zur Migration nutzen können. Alternativ kann es sinnvoll sein, das Staging-Schema in einer eigenen Datenbank, bzw. Datenbankcluster zu halten, um die Datenbanken zu entkoppeln. Wir nutzen Postgres Foreign Data Wrapper, um genauso einfach auf die Daten zuzugreifen. Dies erscheint sinnvoll, denn bei vielen Cloudanbietern kann der einmal zugewiesene Speicher nicht wieder einfach freigegeben werden. Liegt das Staging-Schema in einem eigenen Datnbankcluster, kann der gesamte Server einfach gelöscht werden. Wir halten es für sinnvoll möglichst frühzeitig mit der Entwicklung der Migrationsskripte zu beginnen. Häufig stellen wir fest, dass die Altdaten Konstellationen enthalten, die man im zukünftigen Schema nicht mehr zulassen möchte. Dann stellt sich die Frage, wie mit den Altdaten umgegangen werden soll. Migration und Re-Implementierung der Anwendung finden gleichzeitig statt. Dieser Umstand erweist sich als Glücksfall, denn die Migration sorgt so als Treiber für einen "Datenbank-First"-Ansatz. Für die Re-Implementierung ist hilfreich , dass wir bereits mit den Altdaten arbeiten. Dies liefert eine realistische Datenmenge, denn Abfragen auf leeren Tabellen sind stets schnell. Die Nutzung von Produktionsdaten zu Testzwecken ist dagegen ein anderes Thema. Immer wenn sich das Zielschema ändert, müssen die Migrationsskripte angepasst werden. Die Migrationsskripte selbst sind von den Liquibase oder Flyway-Skripten getrennt. Was gehört zu einer performanten Migration? Es gibt verschiedene Arten von Migrationen. In der Regel ist es sinnvoll bei IT-Systemen eine inkrementelle Migrationsstrategie zu verfolgen. Das kann bedeuten, dass Teile des IT-Systems jeweils in einem Schritt migriert werden (Big Bang). Eine performante Migration der Daten ist dann wichtig. Beim Bulk-Load von Daten in eine PostgreSQL-Datenbank oder zwischen Tabellen gibt es einiges zu beachten. Ähnliches gilt auch für andere Datenbanken. Angelehnt an die Liste von EDB sind folgende Punkte wichtig für unser Szenario (PostgreSQL → PostgreSQL mittels SQL): Tabellen in die Migriert werden sollen, sollten vorher leer sein. Tabellen sollten "unlogged" sein. Tabellen sollten keine Indizes haben. Tabellen sollten keine Constraints haben. Tabellen sollten keine Triggers haben. Tabellen sollten keine Foreign Keys haben. Dann werden die Daten mittels SQL Skripten geladen. Und dann werden die Indizes, Constraints, Triggers und Foreign Keys wiederhergestellt. Zum Abschluss sollte ein Analyze ausgeführt werden. Was ist die Herausforderung bei gleichzeitigen Entwicklungstätigkeiten? Wird die Anwendung neu entwickelt, so ändert sich das Zielschema. Dabei sind insbesondere die Änderungen an den Zieltabellen interessant. Neue Indizes Neue Constraints (inklusive Foreign Keys) Neue Trigger Umbenennungen von Spalten Neue Not-Null Spalten Es ist wichtig, dass diese Änderungen entweder automatisch erkannt und berücksichtigt werden oder vor Ausführung der Skripte auffallen. Da die Laufzeiten für die Migrationen sehr lang sein können (Stunden bis Tage), müssen einfach Fehler vorab oder früh erkannt werden können. Sonst kann die (unbeobachtete) Probemigration erst nach vielen Stunden fehlschlagen und wird nicht abgeschlossen. Damit sind viele Stunden wertvoller Projektzeit verschwendet. Wie kann jOOQ helfen? jOOQ generiert aus einem Datenbankschema ein Klassenmodell. Dieses beinhaltet Informationen über die gängigen Datenbankobjekte wie Tabellen, Spalten, Indizes, Constraints, Triggers und Foreign Keys. Mittels dieses Klassenmodells kann man dann einfach alle Indizes oder Constraints einer Tabelle entfernen oder neu erzeugen. Wenn wir dabei alle Tabellen mittels eines topologischen Sortieralgorithmus sortieren, dann können wir die Tabellen in der richtigen Reihenfolge behandeln. Dies gilt im Kontext dieses Blog-Post zumindest solange, wie der "Tabellengraph" keine Zyklen enthält. Damit vergessen wir keine Objekte zu löschen oder zu erzeugen, die im Rahmen der Weiterentwicklung noch entstehen oder gelöscht werden. Statt die Änderungen direkt anzuwenden, können wir uns das SQL ausgeben lassen und einsammeln. Im Beispiel gibt es aktuell keinen Schalter, die Skripte auch direkt gegen die Datenbank auszuführen. Generell sollten die Aktionen nicht vom Notebook eines Entwicklers ausgeführt werden, sondern von einem dedizierten Migrations-System (virtuelle Maschine). Wenn man die Migration manuell startet, sollte man darauf achten, dass dies unter einem Terminal-Multiplexer wie tmux oder screen geschieht, damit die Ausführung nicht abbricht, wenn der ausführende Benutzer die Verbindung beendet. Die Dauer der Migration hängt stark von der Größe der Datenbank ab. Selbst für einige kleine Datenbanken mit < 250 Mio Einträgen und mehreren Tabellen kann die Migration mehrere Stunden dauern. Bei größeren Migrationen ist sicherlich nochmal eine gesonderte Betrachtung notwendig, um Laufzeiten zu optimieren. Das Beispielprojekt Das Beispielprojekt ist auf GitHub verfügbar. Eine etwas ausführlichere Dokumentation findet sich auf den zugehörigen Github Pages. Datenbank aufsetzen Die PostgreSQL Datenbank kann mittels Docker Compose aufgesetzt werden. docker compose up -d Verbinden kann man sich mittels des Nutzers jooq_demo_admin und dem Passwort jooq_demo_admin. Dieser Nutzer hat die notwendigen Rechte auf der Datenbank und den Schemas. Es gibt die folgenden Schemas: jooq_demo Das Zielschema, welches später von der Anwendung genutzt werden soll staging Das Staging-Schema, welches aus der Oracle Datenbank migriert wurde extensions Das Schema, in dem die Erweiterungen für die Datenbank liegen (z.B. uuid-ossp), welche nicht zu viele eigene Funktionen / Objekte bereitstellen Wenn die Datenbank läuft, dann können die Tabellen mittels ./gradlew :db:update erzeugt werden. Die Ausführung von liquibaseUpdate triggert auch die Erzeugung der jOOQ Klassen. Es wird eine minimale Web Oberfläche mittels Adminer bereitgestellt, die unter http://localhost:8080 erreichbar ist. Wer sich darüber anmelden möchte, der kann die folgenden Daten verwenden: Attribut Wert Datenbank System PostgreSQL Server postgres Benutzer jooq_demo_admin Passwort jooq_demo_admin Datenbank jooq_demo Spring Shell bauen Die Anwendung, welche die Testdaten generiert, Migrationsskripte erstellt und ausführt, ist eine Spring Shell Anwendung. Ein Gradle build erzeugt die Anwendung. ./gradlew build Testdaten erzeugen Jetzt benötigen wir Testdaten in der Staging Datenbank. Die Daten werden mittels DataFaker erzeugt und mittels jOOQ in die Datenbank geschrieben. java -jar library-migration/build/libs/library-migration-0.0.1-SNAPSHOT.jar generateData Migrationsskripte erstellen Jetzt geht es darum, die eigentlichen Migrationsskripte erstellen zu lassen. java -jar library-migration/build/libs/library-migration-0.0.1-SNAPSHOT.jar generateScripts Die Migrationsskripte werden im scripts Verzeichnis des Sub-Projekts library-migration abgelegt. Es wird ein Skript 0000_run_all.sql erzeugt, welches mittels psql ausgeführt werden kann. Dieser Schritt enthält die eigentliche Magie. Wenn wir jetzt einmal die Skripte anschauen, die erzeugt werden, so sehen wir folgende Liste: 0000_run_all.sql 0010_disable_indexe.sql 0020_drop_constraints.sql 0030_unlog_tables.sql 1010_create_mapping_tables.sql 1020_members.sql 1030_books.sql 1040_checkout.sql 2040_log_tables.sql 2050_add_constraints.sql 2060_enable_indexe.sql 2070_analyze_tables.sql Einige Dateien sind aktuell leer, wie disable_indexe und enable_indexe, da keine expliziten Indexe definiert sind. Wir erkennen aber alle Schritte wieder, die wir im Rahmen eines Builds durchführen würden. Wir brauchen aber nur die Skripte schreiben, welche die wirkliche Migration der Daten durchführen. Book Mapping using a lookup table var sql = dsl.insertInto( target, target.ID, target.INSTANCE_ID, target.MEMBER_ID, target.CHECKOUT_DATE, target.RETURN_DATE, target.ACTUAL_RETURN_DATE) .select(dsl.select( Routines.uuidGenerateV7(), instance.ID, mappingMembers.UUID, source.CHECKOUT_DATE, source.RETURN_DATE, source.ACTUAL_RETURN_DATE) .from(source) .join(mappingBooks) .on(source.ISBN13.eq(mappingBooks.ISBN13)) .join(mappingMembers) .on(source.MEMBER_ID.eq(mappingMembers.MEMBER_ID)) .join(instance) .on(instance.BOOK_ID.eq(mappingBooks.UUID))) .onConflictDoNothing() .getSQL(); Dieses Beispiel befindet sich in der Klasse LibraryMigration, welche die Methode migrateTables unserer FullMigrationSupport Oberklasse überschreibt. Überschriebene Methode migrateTables @Override protected void migrateTables() throws Exception { createBookMappingTables(migrationScriptsCollector.newScript("1010_create_mapping_tables.sql")); mapMembers(migrationScriptsCollector.newScript("1020_members.sql")); mapBooks(migrationScriptsCollector.newScript("1030_books.sql")); mapCheckouts(migrationScriptsCollector.newScript("1040_checkout.sql")); } Migrationsskripte ausführen Jetzt müssen die Daten mittels der Skripte migriert werden. java -jar library-migration/build/libs/library-migration-0.0.1-SNAPSHOT.jar applyScripts Das führt einfach die Skripte aus (mittels Spring ScriptUtils Klasse). Zusätzlichen Index erstellen Wir wollen einen zusätzlichen Index auf der Tabelle book erstellen. Dieser sollte dann auch automatisch in den Migrationsskripten gedroppt und neu erstellt werden. ./gradlew :db:update -PliquibaseExtraArgs="contexts=demo-1" ./gradlew --no-build-cache clean build java -jar library-migration/build/libs/library-migration-0.0.1-SNAPSHOT.jar generateScripts Das Skript 0010_disable_index.sql enthält jetzt den Befehl zum Löschen des Indexes und entsprechend 2060_enable_indexe.sql enthält den Befehl zum Erstellen des Indexes. Es gibt also keine Indexe, die man vergessen kann zu löschen und zu erstellen. Das ist schon mal sehr hilfreich. Was hier passiert ist, dass an ein Liquibase Update (mittels ./gradlew :db:update) den Task jooqCodegen auslöst. Damit wird das jOOQ Klassenmodell aktualisiert. Wir schauen uns jetzt einmal an, was passiert, wenn wir eine Spalte umbenennen. Spalte umbenennen Wir nennen in der Tabelle checkout die Spalte return_date in borrowed_until_date um. ./gradlew :db:update -PliquibaseExtraArgs="contexts=demo-2" ./gradlew --no-build-cache clean build Der Build schlägt fehl, da es die Spalte mit dem alten Namen nicht mehr gibt. Fehlermeldung /Users/rat/devel/playground/jooq-migration/library-migration/src/main/java/com/opitzconsulting/cattlecrew/jooqmigration/migration/LibraryMigration.java:90: Fehler: Symbol nicht gefunden target.RETURN_DATE, Da es nur ein Demo ist, können wir die Spalte wieder umbenennen und den Build wiederholen. ./gradlew :db:rollbackCount -PliquibaseCommandValue=1 -PliquibaseExtraArgs="contexts=demo-2" Wer noch mehr ausprobieren möchte, der kann das natürlich auch einfach mittels SQL auf der Datenbank machen. Was nicht funktioniert Wenn man eine neue Not-Null Spalte hinzufügt, dann wird nicht automatisch erkannt, dass in den Abfragen kein Wert für die Spalte bereitgestellt wird. Wer sich die Datenbankdefinition genau angesehen hat, wird feststellen, dass ein PostgreSQL-spezifisches Exclusion Constraint auf der Checkout Tabelle definiert ist. Dieses wurde vom Meta-Modell nicht erkannt und auch nicht in den Skripten berücksichtigt. Daher ist es immer noch wichtig, sich die generierten Skripte anzuschauen und ggf. zu prüfen und zu ergänzen. Gleiches gilt auch, wenn zyklische Abhängigkeiten zwischen Tabellen bestehen. Fazit Auch wenn Änderungen nicht erkannt werden, ist die Nutzung von jOOQ für die Erstellung von Migrationsskripten sehr hilfreich und kann einige Fehlerquellen adressieren, die bei gleichzeitiger Entwicklungstätigkeit auftreten können, wenn das Zielschema nicht eingefroren ist. Notwendige Ergänzungen können im Code oder in den generierten Skripten vorgenommen werden.