Wie man ein nodejs Backend von MySQL auf MS SQL umstellt

Hast du schon einmal das Datenbackend deiner Node Webapplikation ausgetauscht? Wenn ja wirst du dich vielleicht mit Schaudern daran erinnern. Falls du noch vor einer solchen Mammutaufgabe stehst, zeigt dir dieser Erfahrungsbericht, wie so manche der konkret auftretenden Probleme gelöst werden können. Der Artikel ist für Node.js Entwickler mit SQL Grundkenntnissen verfasst.

Allgemeines

Grundsätzlich sind für diese Aufgabe natürlich Unit- und Integrationstests sehr hilfreich. So fallen einem auch die Unterschiede schnell auf.

Für die Umsetzung selbst gibt es prinzipiell zwei Möglichkeiten:

  1. Die neue Datenbankanbindung ersetzt stückweise die alte. Funktionale Module werden ausgetauscht.
  2. Die neue Datenbankanbindung wird zusätzlich implementiert, die alte also beibehalten. Der Applikation wird zum Start mitgegeben, welche Anbindung verwendet werden soll.

Möglichkeit 1 scheint auf den ersten Blick einfacher zu sein, weil man hier nur eine Implementierung am Laufen halten muss. Allerdings hat man mit dieser Variante während des Umbaus nie eine vollständig funktionierende Version. Und dieser Umbau kann bei solch einem zentralen Modul wie der Datenbankanbindung langwierig werden. In vielen Fällen, auch in unserem Fall, ist Variante 2 daher von Vorteil. Dies muss jedoch immer im Einzelfall bewertet werden.

Einsatz eines Query Builders

Wie soll nun die Unterstützung beider Backends implementiert werden?

Doch nicht etwa so:

if(mysql)
   query = "SELECT * FROM `my_table` ..."
else
   query = "SELECT * FROM [my_table] ..."

Und das noch bei jeder einzelnen Query…schlechte Idee!

Wir verwenden seit einiger Zeit für node Applikationen knex. Dies ist ein Query Builder für JavaScript, der sich aber auch um die Verbindung zur Datenbank inklusive connection pooling kümmert („batteries included“, wie es auf der Webseite heißt). Das Zusammenstoppeln der SQL Queries überlässt man also soweit es geht knex, und verwendet die auf der Webseite beschriebene JS API mit „schönen“ JavaScript Objekten, Arrays, usw.

Knex unterstützt als Backend verschiedene Datenbanksysteme, darunter glücklicherweise MS SQL und auch MySQL. Der Plan wäre also, die bestehenden Queries durch knex zu ersetzen, und der Applikation bei der Initialisierung zu sagen „verwende die MS SQL Datenbank“ (Oder eben die MySql Datenbank).

Soweit, so einfach. Aber funktioniert der Ansatz auch in der Praxis?

Nun ja, es stellte sich heraus, dass dies wohl bei den meisten Standardfällen funktioniert. Allerdings gibt es zwischen MySQL und MS SQL doch relativ viele Unterschiede. Knex kann als Abstraktion weder alle Features jeder einzelnen Datenbank abdecken, noch unterstützt jedes einzelne Datenbanksystem alle knex Features (Beispiele folgen). Dies ist jedoch kein Ausschlussgrund für knex, da man per knex.raw jede beliebige native Query absenden kann. Hier wird dann jedoch meist wieder eine if Abfrage nach dem Datenbackend notwendig.

Konkrete Probleme

Automatisch upzudatende created_at und last_change Spalten

Unsere Anwendung benötigt created_at und last_change timestamps für diverse Datenzeilen. Unter MySQL wurde dies in der Schema-Definition mit folgenden Zeilen definiert:

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

So werden die Werte beim INSERT durch den DEFAULT Wert automatisch gesetzt. last_change wird bei UPDATE wieder neu überschrieben. So übernimmt die MySQL Datenbank das Schreiben dieser Werte selbst, ohne Zutun der Applikation.

Für MS SQL funktioniert unsere created_at Spalte analog. Eine ON UPDATE Klausel für die last_change Spalte gibt es aber unter MS SQL schlicht nicht. Die Lösung wäre hier ein Trigger, der bei jeder UPDATE Operation den Wert aktuell hält:

CREATE TRIGGER dbo.after_update_my_table ON dbo.mytable
    AFTER INSERT, UPDATE
    AS
         UPDATE dbo.my_table set last_change=GETDATE()
         FROM dbo.my_table AS current_table
         INNER JOIN inserted AS ins
         ON current_table.id = ins.id

Diese MS SQL Query kann bei der Schemadefinition per knex mit knex.schema.raw Funktion ausgeführt werden.

Increment IDs von eingefügten Daten

In den meisten Fällen will man nach dem Hinzufügen von Datenreihen die jeweilige inkrementelle ID der hinzugefügten Daten auslesen. Bei der insert Methode bietet knex den returning Parameter an. Außerdem kann die returning Funktion verwendet werden (dies funktioniert sogar bei batchInsert). Der Teufel liegt hier im Detail. Wie schon in der Dokumentation beschrieben, kann ein Microsoft SQL Server z.B. auf ein INSERT nichts zurückliefern, wenn es auf der Tabelle einen INSERT Trigger gibt. Diesen haben wir gerade gesetzt. Aber Moment, brauchen wir den Trigger auf INSERT in diesem Fall überhaupt? Es stellt sich heraus, dass ein AFTER UPDATE Trigger hier reicht, beim INSERT wird ja schon der Default-Wert schlagend. Also diesen schnell umstellen, schon bekommen wir wieder die eingefügte ID. Falls du in deiner Applikation doch INSERT Trigger brauchst, kannst du dich zwischen unschönen und total hässlichen Workarounds entscheiden (siehe auch hier).

Bei Tabellen ohne INSERT Trigger kann MS SQL aber im Vergleich zu MySQL mit den returning values deutlich höher Punkten. So kann MySQL nach einem Insert nur die zuletzt hinzugefügte ID zurückliefern. MS SQL erlaubt nicht nur, die Werte mehrerer Spalten zurückzuliefern, sondern ermöglicht knex bei batchInsert auch alle IDs zu returnieren. Bei MySQL kommt hier nur die letzte ID zurück. Dies macht batchInsert für MySQL in den meisten Fällen unbrauchbar.

Einen weiteren Unterschied zwischen den beiden Datenbanken ist die striktere Behandlung von auto-inkrement IDs. Unter MySQL kann man fast „problemlos“ Werte für die ID Spalten selbst setzen (sowohl bei INSERT als auch bei UPDATE). Bei MS SQL sieht die Sache anders aus. Für INSERTs kann man noch per SET IDENTITY_INSERT [my_table] ON; der Datenbank sagen, dass man das jetzt bitte trotzdem machen möchte. Das ist auch zum Beispiel beim Einspielen von Backups, oder beim Setzen von Seed Daten oft hilfreich. Ein UPDATE erlaubt MS SQL aber auf auto-increment IDs prinzipiell nicht. Dies würde aber ohnehin in einer Applikation meist auf einen groben Hack hindeuten, und sollte normalerweise nicht vorkommen.

Batch update

MySQL unterstützt das simultane Ändern vieler Zeilen per INSERT … ON DUPLICATE KEY UPDATE Syntax (siehe Dokumentation) mit Hilfe der VALUES Funktion. Es gibt hier weder bei MS SQL ein Pendant, noch unterstützt knex dieses Konstrukt. Potentiell wäre das MS SQL MERGE Statement in Frage gekommen. Allerdings würden wir hier natürlich nicht um exzessives Verwenden von knex.raw und verschiedene Codepfade für die beiden Datenbanken herumkommen. Weil die betroffene Funktionalität jedoch nicht Performancekritisch ist, wurde der komplizierte Teil der Datenbankabfrage in die Applikation gezogen. Dadurch braucht die Datenbankverbindung nur noch simple SELECT und UPDATE Statements ausführen.

Weitere „Probleme“

Wenn wir schon von groben Hacks sprechen: hast du schon mal, um die Anzahl aller Einträge herauszufinden, bei denen eine boolean Flag auf true gesetzt ist, einfach SUM(my_bool_column) verwendet? Das funktioniert bei MySQL, weil es hier keinen expliziten boolean Datentyp gibt. Hier wird stattdessen TINYINT verwendet, den man natürlich aufaddieren kann. Jedenfalls sollte man diesen Hack ohnehin nicht verwenden, weshalb das Problem ja auch nicht auftreten sollte 😉

Zusätzlich sind noch manche Kleinigkeiten aufgetreten (unterschiedliche Timestamp Typen usw.), und vom Locking will ich hier gar nicht sprechen. Das würde den Artikel aber bei weitem sprengen. Mit etwas Hartnäckigkeit lassen sich zumindest alle Probleme lösen, und man kann eine Applikation anbieten, die sowohl mit MS SQL, als auch mit MySQL läuft.