← Back to Blog Home

A Simple Approach to Complicated Database Defaults

DevOps & System Admin.
by: Meredith Lind
Build an NPM Package in TypeScript from the Ground Up

Article summary

Database tables rarely stay the same over the course of a project. We often need to add tables, add or rename columns, or break up a table to support evolving project demands or updated domain modeling. Migrating existing data correctly is not always trivial.

Here are two scenarios I ran into recently on a project where I needed extra handling for non-nullable columns:

  • Scenario A: Move several non-nullable columns into a new table and preserve the current values.
  • Scenario B: Add a non-nullable column that’s calculated from columns on other tables.

Postgres makes it easy to add simple default values for columns. However, a simple

alter table add column <name> <type> not null default <default_value>

doesn’t give enough flexibility to handle the scenarios above.

The Basic Pattern

We need to define the defaults in terms of existing columns and/or tables. Here is a simple approach:

  1. Add the columns and set to nullable.
  2. Update fields; seed data.
  3. Alter the columns and set to non-nullable.

The down direction of your migration will follow a similar pattern but in reverse:

  1. Add the columns to the old table and set to nullable at first.
  2. Update the table with the values from the new table.
  3. Alter columns on the old table and set to non-nullable.
  4. Drop the new table.

How to Use the Pattern

Let’s walk through what this looks like for Scenario B.

First, add a migration and create the new column. In this example, we are adding a new field (retail price) that’s calculated from the existing base price field.

import Knex from "knex";

export async function up(knex: Knex) {
  await knex.schema.alterTable("Product", async (t) => {
    t.decimal("retailPrice", 18, 4).nullable();
  });
}

Second, write an update statement to migrate the existing pricing data. This is where the update statement could get interesting! In this case, if there is no base price, we will default to $10 just for fun.

import Knex from "knex";

export async function up(knex: Knex) {
  await knex.schema.alterTable("Product", async (t) => {
    t.decimal("retailPrice", 18, 4).nullable();
  });
  await knex.raw(`UPDATE "Product" SET "retailPrice" = COALESCE("Product"."basePrice" * 1.25, 10.00)`);
 }
}

If the update statement executed as expected, we know there should be no null values for the retail price column.

Third, complete the up direction by altering the retail price column to be non-nullable.

import Knex from "knex";

export async function up(knex: Knex) {
  await knex.schema.alterTable("Product", async (t) => {
    t.decimal("retailPrice", 18, 4).nullable();
  });

  await knex.raw(`UPDATE "Product" SET "retailPrice" = COALESCE("Product"."basePrice" * 1.25, 10.00)`);
}
  await knex.schema.alterTable("Product", async (t) => {
    t.decimal("retailPrice", 18, 4).notNullable();
  });

It is important to make sure both the up and down directions of every migration execute correctly. The pattern laid out above also applies to more complicated scenarios.

In this case, the down direction of the migration is as simple as dropping the new column.

export async function down(knex: Knex) {
  await knex.schema.alterTable("Product", async (t) => {
    t.dropColumn("retailPrice");
  });
}

That’s all there is to it! The next time you write a migration and need to provide complicated defaults, try out this pattern. Happy migrating!

Related Posts