Migrations setup and overview
Migrations allow you to evolve your database schema over time. This migration toolkit has several benefits over writing raw SQL migrations or using other tools:
- write migrations in TypeScript, that enables performing insert queries and having any logic
- write only code to create or add something, and it will be automatically possible to undo the migration
- it shares the same column types library as the ORM, which allows you to write a
createTable
migration and copy-paste columns to your table class - optionally, automatically updating table files of ORM after running a migration, instead of copy-pasting
how it works
rake-db
automatically creates a table schemaMigrations
where it saves all migration file names that were applied.
All changes are wrapped into a single transaction. If you have 3 pending migrations, and the last one throws an error, none of them will be applied.
The transaction beings with setting a pg_advisory_xact_lock. If you're deploying a cluster of node.js applications, and each application starts with running migrations, the first of them will set a lock and apply the migrations, the rest will wait for a lock, and after the lock is released all migrations are already applied.
Locally, migrations are compiled from TS to JS on the fly before running. When developing to remote server, it may be preferable to precompile them first. If you're using rake-db
as a standalone tool, try ORM initializer script to use configs from it, the script allows to choose between tsx
, vite
, and ts-node
and generates configs accordingly, package.json has build:migrations
and db:compiled
scripts.
setup
It is already set up if you ran npx orchid-orm@latest
command from a quickstart.
Install this migration tool by running:
npm i -D rake-db
INFO
rake-db
is named after a command in Ruby on Rails because took some inspiration from it.
Since the configuration is done in a regular TypeScript, it's possible to perform any logic and use any configuration tools to specify database connection options.
We suggest to keep database configuration options exported from a separate file, so it can be used both by migration tool and by db
instance in a project.
Example structure (it's created automatically if you follow quickstart):
src/
└── db/
├── migrations/ - contains migrations files that can be migrated or rolled back.
│ ├── recurrent/ - optional: sql files for triggers and functions
│ │ └── my-function.sql - sql file containing CREATE OR REPLACE
│ ├── timestamp_createPost.ts
│ └── timestamp_createComment.ts
├── baseTable.ts - for defining column type overrides.
├── config.ts - database credentials are exported from here.
├── db.ts - main file for the ORM, connects all tables into one `db` object.
├── dbScript.ts - script run by `npm run db *command*`.
└── seed.ts - for filling tables with data.
Export database options:
In this example, dotenv
is used and configured to first get env variables from .env.local
and then to get them from the .env
file.
DATABASE_URL
contains db credentials, also you can specify a db schema and ssl mode in it, see database setup.
// db/config.ts
import { config } from 'dotenv';
import path from 'path';
config({ path: path.resolve(process.cwd(), '.env.local') });
config();
const database = {
databaseURL: process.env.DATABASE_URL,
};
if (!database.databaseURL) throw new Error('DATABASE_URL is missing in .env');
export const config = {
database,
};
Configure a db
script:
// db/dbScript.ts
import { rakeDb } from 'rake-db';
import { appCodeUpdater } from 'orchid-orm/codegen';
import { config } from './config';
import { BaseTable } from './baseTable';
export const change = rakeDb(config.database, {
// relative path to the current file:
migrationsPath: './migrations',
// it also can be an absolute path:
// migrationsPath: path.resolve(__dirname, 'migrations'),
// This is needed only if you use a bundler such as Vite:
migrations: import.meta.glob('./migrations/*.ts'),
// column type overrides and snakeCase option will be taken from the BaseTable:
baseTable: BaseTable,
// optionally, for automatic code updating after running migrations:
// baseTable is required when setting appCodeUpdater
appCodeUpdater: appCodeUpdater({
// paths are relative to the current file
tablePath: (tableName) => `./tables/${tableName}.table.ts`,
ormPath: './db.ts',
}),
// true by default, whether to use code updater by default
useCodeUpdater: false,
// custom commands can be defined as follows:
commands: {
// dbOptions is an array of database configs
// config is the config of `rakeDb` (that contains migrationPath, appCodeUpdater, etc)
// args of type string[] is an array of command line arguments startring after the command name
async seed(dbOptions, config, args) {
const { seed } = await import('./seed');
await seed();
},
},
});
Add the db
script to your package.json
:
{
"scripts": {
"db": "ts-node src/db/dbScript.ts"
}
}
And now it's possible to use it from a command line:
npm run db new createSomeTable
pnpm db new createSomeTable
yarn db new createSomeTable
awaiting rakeDb
rakeDb
function starts executing immediately after it's called, node.js
will keep the program alive until it has at least one pending promise, and it closes after rakeDb
is finished.
But some other environments may not wait for rakeDb
to finish automatically, then you'll need to await for it manually in such a way:
export const change = rakeDb(dbConfig, rakeDbConfig);
// wait for `rakeDb` to finish:
await change.promise;
rakeDb lazy
rakeDb
is designed to be launched with CLI, it will execute one command, and finish.
But in some cases you might want to run it programmatically, and you can do it with rakeDb.lazy
:
export const { change, run } = rakeDb.lazy(dbConfig, rakeDbConfig);
// run a command programmatically:
await run(['migrate']);
// optionally, you can provide a partial `rakeDbConfig` to override some values,
// here we override the logger.
await run(['migrate'], {
log: true,
logger: {
log(message: string): void {
console.log(message);
},
warn(message: string): void {
console.warn(message);
},
error(message: string): void {
console.error(message);
},
},
});
rakeDb.lazy
is accepting the same options as rakeDb
, and returns two functions.
change
is to be used in migrations to wrap database changes with it.
run
is a function to execute a command, it accepts the same CLI args as rakeDb
(see commands section), optionally takes config overrides, returns a Promise<void>
.
ReferenceError: require is not defined
If you encounter the error ReferenceError: require is not defined
, it means you're running on ESM and your node.js runner could not import ts
migration.
This may happen with ts-node/esm
, vite
.
To resolve it, simply add the import
function to the rakeDb
config:
export const change = rakeDb(config.database, {
import: (path) => import(path),
});
rakeDb
rakeDb
function in the setup script takes connection options, migration config, and command line arguments:
const rakeDb = async (
options: MaybeArray<AdapterOptions>,
partialConfig?: Partial<MigrationConfig>,
args: string[] = process.argv.slice(2),
) => {
// ...
};
The first is of the same type AdapterOptions
which is used when configuring the query builder and the ORM. Provide an array of such options to migrate two and more databases at the same time, which helps maintain a test database.
The second optional argument of type MigrationConfig
, all properties are optional, here is the type:
type MigrationConfig = {
// columnTypes and snakeCase can be applied form ORM's BaseTable
baseTable?: BaseTable;
// or it can be set manually:
columnTypes?: (t) => {
// the same columnTypes config as in BaseTable definition
};
// set to true to have all columns named in camelCase in the app, but in snake_case in the db
// by default, camelCase is expected in both app and db
snakeCase?: boolean;
// basePath and dbScript are determined automatically
// basePath is a dir name of the file which calls `rakeDb`, and dbScript is a name of this file
basePath?: string;
dbScript?: string;
// path to migrations directory
migrationsPath?: string;
// path to recurrent migrations directory
// migrationsPath + '/recurrent' is the default
recurrentPath?: string;
// table in your database to store migrated versions
migrationsTable?: string;
// function to import typescript migration file
import?(path: string): void;
// specify behavior for what to do when no primary key was defined on a table
noPrimaryKey?: 'error' | 'warn' | 'ignore';
// log options, see "log option" in the query builder document
log?: boolean | Partial<QueryLogObject>;
// standard console by default
logger?: {
log(message: string): void;
error(message: string): void;
};
appCodeUpdater?(params: {
// abstract syntax tree of changes
ast: RakeDbAst;
// connection options
options: AdapterOptions;
// to resolve relative paths
basePath: string;
// the same object is passed between various appCodeUpdater calls
cache: object;
// the logger object from the above config
// if log: false in the above config, logger is undefined
logger?: {
log(message: string): void;
error(message: string): void;
};
}): Promise<void>;
useCodeUpdater?: boolean;
// throw if a migration doesn't have a default export
forceDefaultExports?: boolean;
beforeMigrate?(db: Db): Promise<void>;
afterMigrate?(db: Db): Promise<void>;
beforeRollback?(db: Db): Promise<void>;
afterRollback?(db: Db): Promise<void>;
};
To configure logging, see log option in the query builder document.
Note that migrationsPath
can accept an absolute path, or a relative path to the current file.
Defaults are:
basePath
is the dir name of the file you're callingrakeDb
frommigrationPath
issrc/db/migrations
recurrentPath
issrc/db/migrations/recurrent
(directory doesn't have to exist if don't need it)migrationsTable
isschemaMigrations
snakeCase
isfalse
, so camelCase is expected in both the app and the databaseimport
will use a standardimport
functionnoPrimaryKey
iserror
, it'll bite if you accidentally forgot to add a primary key to a new tablelog
is onlogger
is a standardconsole
useCodeUpdater
istrue
, but it won't run anything if you don't specifyappCodeUpdater
config
The third optional argument of rakeDb
is an array of strings from the command line, by default it will use process.argv
to get the arguments, but you can override it by passing arguments manually.
snakeCase
By default, this option is false
and camelCase is expected in a database, change it to true
if all or most of the columns in your database are in snake_case.
When snakeCase
is true
, all column names in migrations will be translated into snake_case automatically.
It changes behavior of db pull
command at handling column names and timestamps, see db pull for details.
appCodeUpdater
appCodeUpdater
is a module that will add new and update existing project files when running migrations.
To prevent running when not needed, append --code false
flag to cli command:
npm run db migrate --code false
If you don't want to run it on every migration, set useCodeUpdater
to false and run migration with --code
flag to run code updater when needed:
npm run db migrate --code
What appCodeUpdater
does:
- creates base table file if it doesn't exist
- creates main
db
file if it doesn't exist - creates a new table file when creating a table
- adds table entry to
db
file when creating a table - adds new columns, indexes, and foreign keys to the table file when they are added in a migration
- changes columns, indexes, and foreign keys in the table file when they are changed in a migration
- changes
table
andschema
property in the table file when renaming a table - removes table entry from
db
file when dropping a table
appCodeUpdater
does not delete or rename existing files, because it is better to be done manually. A modern editor will update all file usage in imports across the project when renaming a file or an exported class.
seeds
To make database seeds, create own script with the needed logic.
In the example, new db instance is constructed with createDb
, but you can import db
object from where it's defined in your app.
// db/seed.ts
import { db } from './db';
export const seed = async () => {
await db.table.createMany([{ name: 'record 1' }, { name: 'record 2' }]);
await db.close();
};
Add a custom command to rake-db
config:
// db/dbScript
// ...snip imports
export const change = rakeDb(config.database, {
// ...other options
commands: {
async seed(options) {
const { seed } = await import('./seed');
await seed();
},
},
});
Run the seeds with the command:
npm run db seed
# or
pnpm db seed
recurrent migrations
Recurrent migrations are useful when you want to update SQL functions, triggers, and other database items regularly.
This feature is optional, it's not required to have a recurrent
directory.
For example, store add
SQL function into src/db/migrations/recurrent/add.sql
:
CREATE OR REPLACE FUNCTION add(integer, integer) RETURNS integer
AS 'select $1 + $2;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
When you run the command recurrent
(aliased as rec
), rake-db
will recursively scan the recurrent
directory and execute all sql files in parallel.
As they are executed in parallel, if one functions depends on the other better place it in a single sql file.
As it is scanned recursively, you can structure recurrent
directory as it feels better, for example:
src/
└── db/
└── migrations/
└── recurrent/
├── functions/
│ └── my-function.sql
└── triggers/
└── my-trigger.sql
before and after callbacks
To run custom code before or after migrate
or rollback
command, define functions in rakeDb
config object:
Supported callbacks are beforeMigrate
, afterMigrate
, beforeRollback
, afterRollback
.
Example: each time when npm run db migrate
is run, after the migration was successfully applied, this will create new records of a specific table if it is empty.
If options
is an array of multiple database configs, callbacks are run for each of the databases.
export const change = rakeDb(options, {
async afterMigrate(db: Db) {
const haveRecords = await db('table').exists();
if (!haveRecords) {
await db('table').createMany([
{ name: 'one' },
{ name: 'two' },
{ name: 'three' },
]);
}
},
});