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:

sql

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

up or migrate

The up command will run all not applied yet migrations, sequentially in order. migrate is an alias. After applying migrations, it will also run recurrent migrations if they exist.

npm run db up

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

npm run db up 3

up or migrate force

This command may be useful if you're using timestamp-prefixed migrations.

If your co-worker has committed a migration with an earlier timestamp than your local migration:

migrations/
├── 20221017200326_theirMigration.ts
└── 20221017200326_yourMigration.ts

Your migration was already applied, running migrate or up fails because their migration comes above, run:

npm run db up force

Your migration will be rolled back, then both of them will be applied.

May the force be used only if your migrations aren't committed to a remote repository yet.

down or rollback

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

npm run db down

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

npm run db down 3
npm run db down all

redo

Shortcut for down + up. 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

status, s

status or s command will display a list of migrations to your terminal:

$ pnpm db s

 Database: db-name

 Status | Migration ID | Name
------------------------------------------
   Up   | 0001         | First migration
   Up   | 0002         | Second migration
  Down  | 0003         | Third migration
------------------------------------------

Add path or p command argument to also output paths to migration files:

$ pnpm db s p

 Database: db-name

 Status | Migration ID | Name
------------------------------------------
   Up   | 0001         | First migration
file:///migrations/0001_first_migration

   Up   | 0002         | Second migration
file:///migrations/0002_second_migration

  Down  | 0003         | Third migration
file:///migrations/0003_third_migration
------------------------------------------

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

rebase

Use rebase when you pull changes from a repository, and the changes contain new migrations with the same numbers that you already have locally.

Imagine you have such migration files locally:

0001_a.ts
0002_b.ts

Your colleague pushed their work that also contains migration files:

0001_c.ts
0002_d.ts

After pulling the change, you have:

0001_a.ts
0001_c.ts
0002_b.ts
0002_d.ts

Running pnpm db migrate fails because of conflicting numbers. Run the command:

pnpm db rebase

It will roll back your local migrations "0001_a.ts" and "0002_b.ts", rename them to "0003_a.ts" and "0004_b.ts", and will migrate all files.

Migrations after running the command:

0001_c.ts
0002_d.ts
0003_a.ts
0004_b.ts

The rebase command relies on the fact that your local migrations were already applied to the database. In case if they were not, and there are files "0001_a.ts" and "0001_b.ts", rebase command will ask interactively which file to keep above the other.

If supply migrations via the migrations setting in the config (might be useful with Vite), rebase can't work with that.

change-ids

When switching migrations from the timestamp prefixes to serial prefixes (or vice-versa), run change-ids command to rename files and reflect the change in the database table that tracks migrations:

pnpm db change-ids serial
# or to migrate to timestamp
pnpm db change-ids timestamp

After running the command, change migrationId to the desired prefix kind in the rake-db config.

This will create a special file in migrations: .rename-to-serial.json.

After deploying to a remote server which is going to run migrations, rake-db will notice that applied migrations have timestamp prefix, but migration files have serial, and it will use the .rename-to-serial.json file to apply the renaming on a remote server.

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

Migration commands #

create and drop a database #

reset a database #

pull #

How updatedAt and createdAt timestamps are handled #

custom and unknown columns #

generate migration #

up or migrate #

up or migrate force #

down or rollback #

redo #

status, s #

recurrent, rec #

rebase #

change-ids #

custom commands #