user:kluong:improvement_-_use_a_database_migration_tool [Smart Campus Energy Lab Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Improvement - Use a database migration tool ======

===== 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.