Skip to main content

Database changes checklist

Write migration​

When you write the migration, leave the template in place.

Up migration​

  • Number each action of your up migration with a comment (UP 1, UP 2, ...)
  • Up migrations should include a migration path for existing data
  • We prefer non-nullable columns -- if your column is nullable, add a comment explaining why
  • New columns must include reasonable defaults (hardcoded or derived from existing data)
  • Do not use .enum() -- we prefer virtual enums (TEXT columns that are validated in business logic). See https://internal-docs.dreambound.com/engineering/practices/enums. Also add a Schema and GraphQLEnum export in /models/enums.
  • Array types should be implemented like table.specificType("columnName", "text[]").defaultTo("{}").notNullable()

Down migration​

  • Each UP action must be paired, in reverse, with an UNDO action
  • If you do not have a paired action, you must leave the comment and explain why
/**
 * UNDO 2
 * We don't need to add back the `snacks` table because I ate all the snacks
 */

Some lessons we have learned the hard way:

  • Making something non-null? If a nullable column becomes non-null, this migration will fail. This is especially hard to test if it happens during a down migration. You will need a plan for what to do with these values.

Verify down migration​

Do not skip this part

Up and down migrations should be exactly reversible in schema or you will cause other developers a lot of pain.

When writing your down migration, always reference what is in the database (i.e. the ground truth), not a migration.

For each of these checks,

  • Run your migration UP
  • Copy the result
  • Run your migration DOWN, then UP
  • Copy the result -- This should be identical (ordering differences OK)

Verify columns and types​

Are columns and types exactly the same?

SELECT
  column_name,
  column_default,
  is_nullable,
  data_type,
  character_maximum_length,
  datetime_precision,
  udt_name
FROM information_schema.columns
WHERE table_name = '<TABLE NAME>';

Verify indexes​

Are indexes exactly the same? Be sure to check this because indexes are sometimes non-obvious.

SELECT
  tablename,
  indexname,
  indexdef
FROM pg_indexes
WHERE  tablename = '<TABLE NAME>';

If renaming a column, make sure you also rename any associated indexes. These are not automatically renamed!

Verify foreign keys and constraints​

SELECT
  conrelid::regclass table_name,
  conname foreign_key,
  pg_get_constraintdef(oid) constraint
FROM pg_constraint
WHERE conrelid::regclass::text = '<TABLE NAME>';

Importing constants​

Don't import enums/constants, redefine them in the migration so that if they change, the migration doesn't break.

Migration sanity checks​

  • Run your migration up, then down, then up.

Objection and GraphQL​

A new column must be enforced in 3 places:

  • On the Objection model, its TypeScript type
  • On the Objection model, its JSONSchema definition
  • GraphQL enum / scalar

Is your new column a JSON column? If so, reconsider and ensure this makes sense -- JSON columns are harder to type and validate, especially if they are nullable.

  • Add it to jsonAttributes array. If you do not do this, Objection won't parse data correctly.
  • If your column contains multiple specific data points, they should be independently resolved in GraphQL (not as a generic JSON blob).

Implementations​

  • TypeScript types
  • JSONSchema definition (in jsonSchema)
    • Include if new columns should be required
  • Filters
    • Do we need to filter on this, internally or on GraphQL? Add relevant filters to filters.
  • Sortable
    • Do we need to sort on this? Add column to sortable.

Sanity checks​

  • Test that the column resolves correctly.
  • Does this column need particular authorization rules that are different from its parent table?

Database seeder​

  • Implement at least 3 examples of this column.
  • Prefer using a dynamic seeder (faker) instead of hard-coding values.
  • If the column is nullable, implement a null example as well.

Airtable​

  • Does this column need to get sent to Airtable? This is usually Yes. File a ticket to implement this.

Front-ends​

  • As a rule of thumb (with occasional exceptions), all columns should be accessible and editable from at least the Admin Portal.
  • File tickets to implement view/list, add, edit, and delete.