Migrations: Creating and editing DB models
Setting up a fresh database
poi migrate prepare
This command will generate the following tables and triggers in your DB, if they don't already exist:
schema_migrationstable to track which migrations have already been run. This is directly influenced from Rails migrations.
trg_update_modified_column- a trigger to automatically set
current_timestampwhenever any row is updated in a table which contains this column.
Creating a new model
poi migrate new createUsers
This will create a file called
YYYYMMDDHHmmSS is the actual timestamp on which you run the command). The file will look like the following:
module M20170828164533_createUsers where import Control.Monad import Database.Rivet.V0 import Text.InterpolatedString.Perl6 (qc) migrate :: Migration IO () migrate = sql up down up = ([qc| -- INSERT YOUR MIGRATION SQL HERE |]) down = ([qc| -- INSERT YOUR ROLLBACK SQL HERE |])
Now edit this file to create your tables, indexes, constraints, triggers, etc. using raw SQL:
module M20170828164533_createUsers where import Control.Monad import Database.Rivet.V0 import Text.InterpolatedString.Perl6 (qc) migrate :: Migration IO () migrate = sql up down up = ([qc| CREATE TABLE users ( id serial primary key ,created_at timestamp with time zone not null default current_timestamp ,updated_at timestamp with time zone not null default current_timestamp ,username text not null ,password text not null ,first_name text ,last_name text ,status user_status not null default 'inactive' CONSTRAINT chk_status CHECK ((status IN ('active', 'inactive', 'deleted', 'blocked'))) ); CREATE INDEX idx_users_created_at on users(created_at); CREATE INDEX idx_users_updated_at on users(updated_at); CREATE INDEX idx_users_status on users(status); CREATE UNIQUE INDEX idx_users_username on users(username); CREATE TRIGGER trg_modify_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_modified_column(); |]) down = ([qc| DROP TABLE users; |])
We should probably have our own quasi-quoter called
sql or something, which allows mixing of raw SQL along with custom helper functions. We can write helper functions to generated indexes, triggers for audit logs, triggers for updating
updated_at, triggers for pushing to DB based
Now, run the migration, with the following command:
poi migrate up
Here is what this will do, under the hood:
- This will connect to the development database (by default) and execute all pending migrations. The timestamp/version of all migrations in the
<projectRoot>/migrations/directory will be looked-up in the
schema_migrationstable. Any migration which is not there in the table will be executed in ascending order of the timestamp/version.
- Each individual migration will be wrapped within a single BEGIN/COMMIT block - which means that if any migration throws an error:
- that particular migration will be rolled back,
- all previous migrations (which have already executed successful) will persist,
- and all migrations which are yet to be executed, will be aborted.
- Once the migration runs successfully, it will run the model code-generator under the hood, to create/modify/delete any model files that need to be updated as a result of this migration.
Editing existing models
The worlflow remains pretty much the same as "Creating a new model":
- Create a migration file
- Write a bunch of
ALTERstatements in the migration
poi migrate up
Other useful command-line arguments
poi migrate [ up | down | redo | prepare | new ] --env environmentName Explicitly pass an environment to the script. Default value is `development` or the value of the `APP_ENV` environment variable (in that order) --version regex Pass a specific migration version to the script. A fuzzy (or regex) match will be attempted with the given argument. If exactly one migration matches, it will be targeted, else all matching migrations will be printed out STDOUT.