Skip to content

DB Migrations

Jump to bottom
Ilmar Türk edited this page Jun 27, 2023 · 14 revisions

What are DB migrations?

Database migrations are a way to update database structure (migrate) to a new one or roll back to previous one. For example when citizenos-api code change requires a change in DB structure, these changes can be easily applied and reverted on an existing database. You can think of it as database versioning.

How to use Citizen OS DB migrations?

Citizen OS API uses Sequelize ORM and their standard migrations system.

Please read before going any further:

Shortcuts

There are some shortcuts described in package.json:

  • dbstatus - Migration status. Runs Sequelize db:migrate:status.
  • dbmigrate - Run migration. Runs Sequelize db:migrate.
  • dbrollback - Roll back migration. Runs Sequelize db:migrate:undo.
  • dbdump - Regenerate db/config/database.sql. MUST be in sync with the code. Example: DATABASE_URL=postgres://citizenos:citizenos@localhost:5432/citizenos npm run dbdump

These are used as any other NPM script - npm run <command>.

NOTE: Sequelize migrations use Citizen OS API configuration, so if you run any of those, DB connection configuration is taken from API configuration. You can, as in API, override from env.

How to develop with migrations?

When a citizenos-api code change requires a DB change, a migration MUST be created.

RULES:

  • Any change in the model MUST be described as a migration.
  • You MUST test that migration and rollback works.
  • db/config/database.sql MUST contain the latest DB structure the code runs on. Created using DATABASE_URL=postgres://citizenos:citizenos@localhost:5432/citizenos npm run dbdump.

WORKFLOW:

  • Create a feature branch
  • Describe your changes in the MODEL (/db/models/..)
  • Test your changes forcing DB sync (FORCE_DB_SYNC=1 mocha ...)
  • Describe your changes as a new migration (Manually or use node_modules/.bin/sequelize model:generate. For example: node_modules/.bin/sequelize model:generate --name User2 --attributes firstName:string,lastName:string,email:string)
  • Create clean DB from "master" forcing DB sync (FORCE_DB_SYNC=1 mocha ...)
  • Switch to feature branch and deploy your migration (DATABASE_URL="postgres://citizenos:citizenos@localhost:5432/citizenos" npm run dbmigrate)
  • Run tests with WITHOUT DB sync (FORCE_DB_SYNC=0 mocha...)
  • Create latest DB structure to db/config/database.sql using DATABASE_URL=postgres://citizenos:citizenos@localhost:5432/citizenos npm run dbdump.
    • VERY IMPORTANT: Needs to be up-to-date and reflect the migrations! master branch database.sql MUST reflect actual testing environment state, prod branch database.sql MUST reflect actual live state.
  • Run rollback of the migration.
  • Run tests in "master" WITHOUT DB sync (FORCE_DB_SYNC=0 mocha...)
  • Merge your latest awesome code back to "master".

READ:

Clone this wiki locally