Migration commands

Once migrations are 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

This is to produce files by introspecting your existing database.

When using the migration tool together with OrchidORM, the pull command generates both table classes files for your project and a migration.

When using as a standalone tool, it only produces a migration file.

npm run db pull
# or
pnpm db pull

Currently, it supports generating code to create:

• schemas
• tables
• columns with all possible column options
• enums
• primary keys
• foreign keys
• indexes
• EXCLUDE constraints
• database CHECK constraints
• domain types
• views (only in migration)
• timestamp defaults current_timestamp, transaction_timestamp() are simplified to the equivalent now()
• (for ORM) defines belongsTo and hasOne relations by analyzing foreign keys

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(),
    updated_at: t.timestamps().updatedAt,
    created_at: t.timestamps().createdAt,
  }));
});

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, use timestampNoTZ 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()),
  }));
}

new blank 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

Serial prefixes have 4 digits by default, you can use a custom digits amount by specifying a second argument:

pnpm db change-ids 5

After running the command, change migrationId to the desired prefix kind in the migrations' config.

When rake-db loads migrations directly from files (migrationsPath config is set):

• The command will create a special file in migrations: .rename-to-serial.json, it will be used to rename migrations in database after deploying to a remote server.
• Migration files and database entries are renamed automatically.

Alternatively, when rake-db accepts an array of migrations (migrations config is set):

• The command will generate a setting renameMigrations that you should copy-paste into rake-db config.
• It will output a series of mv commands to rename files (if migrations are stored in files), then you should navigate to the migrations directory and apply these commands.
• If migrations aren't stored in files, you'll have to rename migration object keys manually.

After deploying to a remote server which is going to run migrations, it will detect a prefix mismatch and will look for a file in the first case from above, or for a renameMigrations in the second case, and it will rename entries of already applied migrations in the database.

Don't remove the .rename-to-serial.json or renameMigrations until the app is deployed to all destinations.

custom commands

You can configure custom commands in such way:

import { rakeDb } from 'orchid-orm/migrations'; // import from 'rake-db' when not using Orchid ORM
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