Migration commands #

After the migration tool, rake-db, was set and configured, you can use it from a command line.

create and drop a database #

Create and drop a database from a command line:

npm run db create
npm run db drop

Unless databaseURL contains db superuser credentials, these commands will ask for a database administrator username and password.

If a custom schema is chosen for a connection, db create will also try to create this schema (won't fail if it is already exists).

reset a database #

reset is a shortcut command to drop, create and migrate.

npm run db reset

pull #

Generate migration file from an existing database using pull command:

npm run db pull

This will create a single migration file with all the tables and columns.

If appCodeUpdater is configured in rake-db config file, it will also generate project files.

Currently, it supports generating code to create:

• schemas
• tables
• enums
• columns with all possible column options
• primary keys
• foreign keys
• indexes
• defines belongsTo and hasOne relations by analyzing foreign keys
• domain types
• views
• the defaults current_timestamp, transaction_timestamp() are simplified to the equivalent now()

How updatedAt and createdAt timestamps are handled #

Assuming we have two tables in a database, one with camelCase columns and the other with snake_case:

CREATE TABLE "camel" (
  "id" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "camelCaseColumn" text NOT NULL,
  "createdAt" timestamptz DEFAULT now(),
  "updatedAt" timestamptz DEFAULT now()
);

CREATE TABLE "snake" (
  "id" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "snake_case_column" text NOT NULL,
  "created_at" timestamptz DEFAULT now(),
  "updated_at" timestamptz DEFAULT now()
);

When snakeCase is false (default), db pull will produce the following migration, where snake_case columns are explicitly named and timestampsSnakeCase is used:

import { change } from '../dbScript';

change(async (db) => {
  await db.createTable('camel', (t) => ({
    id: t.identity().primaryKey(),
    camelCaseColumn: t.text(),
    ...t.timestamps(),
  }));

  await db.createTable('snake', (t) => ({
    id: t.identity().primaryKey(),
    snakeCaseColumn: t.name('snake_case_column').text(),
    ...t.timestampsSnakeCase(),
  }));
});

When snakeCase is true, db pull will produce the following migration, this time camelCase columns are explicitly named, and timestamps have no shortcut:

import { change } from '../dbScript';

change(async (db) => {
  await db.createTable('camel', (t) => ({
    id: t.identity().primaryKey(),
    camelCaseColumn: t.name('camelCaseColumn').text(),
    createdAt: t
      .name('createdAt')
      .timestamp()
      .default(t.sql`now()`),
    updatedAt: t
      .name('updatedAt')
      .timestamp()
      .default(t.sql`now()`),
  }));

  await db.createTable('snake', (t) => ({
    id: t.identity().primaryKey(),
    snakeCaseColumn: t.text(),
    ...t.timestamps(),
  }));
});

If timestamps in your database don't have a time zone, all the above works as well, but will use timestampNoTZ and timestampNoTZSnakeCase instead.

Custom and unknown columns #

If column type is a custom one defined by user, or if it is not supported yet, db pull will log a warning and output the column as follows:

await db.createTable('table', (t) => ({
  column: t.type('unsupported_type'),
}));

It works, just when using t.type in the application to define a column, you need to use as method to treat it as another column:

export class Table extends BaseTable {
  readonly table = 'table';
  columns = this.setColumns((t) => ({
    // treat unsupported type as text
    column: t.type('unsupported_type').as(t.text()),
  }));
}

generate migration #

Generate a new migration file by using new command:

npm run db new migrationName
# or
pnpm db new migrationName

Migration name can be an any string in any case, it should be descriptive for the team.

If the migration name matches one of the known patterns, it will generate a template:

• create[table] to create a new table, example: createProduct
• drop[table] to drop a table, example: dropProduct
• change[table] to change a table, example: changeProduct
• add[something]To[table] to add columns to a table, example: addDetailsToProduct
• remove[something]From[table] to remove columns from a table, ex.: removeDetailsFromProduct

migrate #

Migrate command will run all not applied yet migrations, sequentially in order. After applying migrations, it will also run recurrent migrations if they exist.

npm run db migrate

Pass a number to migrate only this specific number of migrations:

npm run db migrate 3

up #

The same as migrate, but it won't run recurrent migrations.

rollback, down #

The rollback command will revert one last applied migration. down is an alias.

npm run db rollback
# or
npm run db down

Pass a number to revert multiple last applied migrations, or pass all to revert all of them:

npm run db rollback 3
npm run db rollback all

redo #

Shortcut for rollback + migrate. It is useful when you edit a migration and want to reapply it.

By default, rolls back and migrate one migration. Pass a number to re-run multiple file.

Will run recurrent migrations if any exist.

# redo one last migration:
npm run db redo

# redo 3 last migrations:
npm run db redo 3

recurrent, rec #

recurrent migrations are SQL files that have SQL functions, triggers, etc.

rec is an alias.

All sql files in the recurrent directory (by default src/db/migrations/recurrent) will be executed in parallel when running this command, and also after running migrate or redo commands.

npm run recurrent
# or
npm run rec

custom commands #

rakeDb allows to specify your own functions for a custom commands:

import { rakeDb } from 'rake-db';
import { createDb } from 'pqb';
import { config } from './config';

export const change = rakeDb(
  // config may have array of databases, for dev and for test
  config.databases,
  {
    commands: {
      async custom(dbConfigs, config, args) {
        // dbConfig is array of provided database configs
        for (const dbConfig of dbConfigs) {
          const db = createDb(dbConfig);

          // perform some query
          await db('table').insert(someData);

          // closing db after using it
          await db.close();
        }

        // config is this config object we're inside
        config.commands.custom; // this is a function we're inside of

        // command line arguments of type string[]
        console.log(args);
      },
    },
  },
);

Running this command will perform a query and log arguments ['one', 'two'] to the console:

npm run db custom one two