hiring-schema

This repository provides migration scripts for the hiring database.

Usage no npm install needed!

<script type="module">
  import hiringSchema from 'https://cdn.skypack.dev/hiring-schema';
</script>

README

Hiring Schema

This repository provides migration scripts for the hiring database.

Install dependencies

  • Install Node
  • Install db-migrate: npm install -g db-migrate
  • Install node_modules: npm install

Create a migration

Using JavaScript

db-migrate create my-migration-name

This will create one file:

  • migrations/$timestamp-my-migration-name.js

Within that file are two functions: exports.up and exports.down, which represent the code to apply or unapply the migration.

I recommend adding the following for intellisense:

// At the top of the file:
const DB = require("db-migrate-postgres");

// Before each function, and make the function async:
/** @param {DB} db */
exports.up = async function(db) {
  ...
};

/** @param {DB} db */
exports.down = async function(db) {
  ...
};

See futher documentation here: https://db-migrate.readthedocs.io

Using SQL files

If the migration you need to do is better managed in a SQL file, run this:

db-migrate create my-migration-name --sql-file

This will create three files:

  • migrations/$timestamp-my-migration-name.js
  • migrations/sql/$timestamp-my-migration-name-up.sql
  • migrations/sql/$timestamp-my-migration-name-down.sql

The JavaScript file is created to load and execute the SQL as part of the migration. The up and down SQL files will be executed to apply and unapply the migration respectively.

Testing the migration

db-migrate up --dry-run

This will show the SQL to be executed, without actually running it. When you are ready to test live, run db-migrate up. You can run db-migrate down to perform the unapply, which you may end up doing several times as you code a migration.

Committing

Ultimately, these migrations should be committed, and executed finally by TeamCity. That way, we can re-apply migrations after we wipe the UAT database.