-
Notifications
You must be signed in to change notification settings - Fork 1
Database Migrations
Important Note: It is highly unlikely that you will require a schema change in the course of maintaining this project. However, if one is required, this page is meant to provide guidance on the process of managing those changes.
Schema changes should only be undertaken with care and with the proper expertise on-hand. Any parts of the website and API that rely on schema being modified are liable to break and must be updated accordingly.
All changes to the project database schema should be managed via pg-migrator and migration scripts. DO NOT EDIT THE PRODUCTION SCHEMA DIRECTLY.
This project uses pg-migrator as a lightweight tool to manage schema migrations for PostgreSQL.
The official documentation goes into greater detail about how to use pg-migrator and write migration scripts. All of the migration scripts simply use SQL - there's no special syntax required.
The migration scripts for this project are in the UN SDG National Reporting Platform API repository under sql/migration. Within the migration directory, there should be two subdirectories: up and down. "Upgrade" migration scripts should be placed in the up directory, while "downgrade" migration scripts should be placed in the down directory.
The only command-line argument pg-migrator requires is a connection string. To run pg-migrator against a typical local installation of PostgreSQL, you would run something like:
pg-migrator postgres://postgres@localhost/postgres
This will bring your database up to the latest version in your "upgrade" migration scripts.
pg-migrator provides a very quick and easy way to tear down and restore your database schema for development purposes. However, scripts for downgrading the database should not be used as a method for rolling back changes to any production environments.
Ideally, all migration scripts should be thoroughly tested before deploying them against production environments. But in the case a mistake is made, it is best to create another upgrade migration script to rectify the issue. This is primarily a warning due to the complexity of cleanly rolling back version history in production environments in an automated manner.