Differences

This shows you the differences between two versions of the page.

--- user:kluong:improvement_-_use_a_database_migration_tool [2021/10/07 14:21]
kluong created
+++ user:kluong:improvement_-_use_a_database_migration_tool [2022/02/17 01:26] (current)
kluong
@@ Line 1: / Line 1: @@
 ====== Improvement - Use a database migration tool ======
-It could be useful to use a database migration tool for the gateway like https://flywaydb.org/. Adding tables would be much easier to audit / run in production if we had a tool like this. Migrations could run before the application starts up for example and since they're checked into version control they can be audited and approved with the normal workflow.
+===== Background =====
+One of the greatest advantages of software engineering is that the actual build step is much quicker than physically building something. Think about how fast code often compiles - while it's true that large projects like the linux kernel, or the chromium project can take hours to compile, it's still orders of magnitude faster than something like constructing a car.
+Because of this advantage, modern software engineering often deals with change. Since the design->build cycle happens so fast - single changes are cheap. Database schemas are no exception to this - schemas often evolve over time as requirements change and more features are added. There are two ways to do this:
+  * Manually execute commands in the database as they are changed
+  * Use database migrations
+Manually executing commands works, although it can be a little more slow and error prone. Database migrations are a technique of codifying those changes in software, and executing those changes in an automated fashion. This technique is popular enough that many popular web frameworks such as Ruby on Rails or Django have built-in support for migrations.
+===== Migrations =====
+Let me explain a little with an example. Consider we want to create a pretty simple table in postgres (or some other database like sqlite3:
+<code>
+CREATE TABLE weather_node (
+    time_received TIMESTAMP NOT NULL,
+    schema INTEGER,
+    node_addr INTEGER,
+    uptime_ms BIGINT,
+    batt_mv INTEGER
+)
+</code>
+Let's say we want to add another column - we'd want to run a command like this:
+<code>
+ALTER TABLE weather_node ADD COLUMN temp_c INTEGER
+</code>
+We could run these commands manually, or we could codify both using a migration. There's a library called yoyo migrations that we can use. Here's what some of the code looks like:
+<code>
+# migrate.py
+import yoyo
+backend = yoyo.get_backend('sqlite://./test.db')
+# execute the migrations in the ./migrations folder
+migrations = yoyo.read_migrations('./migrations')
+backend.apply_migrations(backend.to_apply(migrations))
+</code>
+This is what the rest of the directory looks like:
+<code>
+$ tree .
+.
+├── migrate.py
+├── migrations
+│   ├── 0001.create.py
+│   └── 0002.alter-table.py
+</code>
+You'll notice there are two files here - each containing a migration corresponding to the changes we want to make above:
+<code>
+$ cat 0001.create.py
+from yoyo import step
+steps = [
+   step(
+       """
+CREATE TABLE heartbeat (
+    time_received TIMESTAMP NOT NULL,
+    schema INTEGER,
+    node_addr INTEGER,
+    uptime_ms BIGINT,
+    batt_mv INTEGER
+)
+       """
+   )
+]
+</code>
+<code>
+$ cat 0002.alter-table.py
+from yoyo import step
+steps = [
+   step(
+       """
+ALTER TABLE heartbeat ADD COLUMN temp_c INTEGER
+       """
+   )
+]
+</code>
+Running migrate.py executes both steps automatically, and also creates some extra tables to keep track of the state of the migrations, letting us not have to worry about the current schema.
+===== Concluding =====
+Adding tables and modifications would be much easier to audit / run in production if we had a tool like this. Migrations could run at the beginning of a the startup sequence for the gateway for example and since the changes are checked into version control they can be audited and approved with the normal workflow.