--- url: /guide/migration-commands.md description: >- Migration CLI commands for creating and dropping databases, pulling schema, and managing migration files. --- # Migration commands Once migrations are [configured](/guide/migration-setup-and-overview#setup), you can use it from a command line. ## create and drop a database Create and drop a database from a command line: ```sh 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. When the `schema` config is specified in the db connection settings or when `migrationsTable` contains a schema, `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. ```sh 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 definition files for your project and a migration. When using as a standalone tool, it only produces a migration file. ```sh 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: ```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: ```ts import { change } from '../db-script'; 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: ```ts import { change } from '../db-script'; 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: ```ts 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: ```ts export const Table = defineTable('table', (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: ```sh 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. ```sh npm run db up ``` Pass a number to migrate only this specific number of migrations: ```sh 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: ```sh 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. ```sh npm run db down ``` Pass a number to revert multiple last applied migrations, or pass `all` to revert all of them: ```sh 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. ```sh # redo one last migration: npm run db redo # redo 3 last migrations: npm run db redo 3 ``` ## transaction per migration By default, all pending migrations are executed in a single transaction. If the last one fails, all the changes caused by previous are discarded, partially applied state is prevented. You can override this strategy to have a single transaction per every migration with a `--transaction=per-migration` CLI arg: ```sh npm run db up --transaction=per-migration npm run db down --transaction=per-migration npm run db redo --transaction=per-migration ``` You can also set it in the config (the CLI arg takes precedence over the config): ```ts export const change = rakeDb(config.database, { transaction: 'per-migration', // ...rest }); ``` ## status, s `status` or `s` command will display a list of migrations to your terminal: ```sh $ 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: ```sh $ 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. ```sh 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: ```sh 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: ```sh 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: ```sh 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: ```ts // for porsager/postgres driver: import { rakeDb } from 'orchid-orm/migrations/postgres-js'; // when using ORM import { rakeDb } from 'rake-db/postgres-js'; // when using rake-db as a standalone tool // for node-postgres driver: import { rakeDb } from 'orchid-orm/migrations/node-postgres'; // when using ORM import { rakeDb } from 'rake-db/node-postgres'; // when using rake-db as a standalone tool // for Bun SQL driver: import { rakeDb } from 'orchid-orm/migrations/bun'; // when using ORM import { rakeDb } from 'rake-db/bun'; // when using rake-db as a standalone tool 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: ```sh npm run db custom one two ```