CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/npm-db-migrate

Database migration framework for Node.js applications that enables developers to programmatically manage database schema changes and evolution over time.

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

SecuritybySnyk

Pending

The risk profile of this skill

Overview
Eval results
Files

database-interface.mddocs/

Database Interface

Database interface methods available within migration and seed files for database schema management and data manipulation. These methods provide a unified API across different database drivers (MySQL, PostgreSQL, SQLite3, MongoDB).

Capabilities

DDL Operations (Data Definition Language)

Schema operations for creating, modifying, and dropping database structures.

Create Table

Create a new database table with specified columns and constraints.

/**
 * Create a new database table
 * @param tableName - Name of the table to create
 * @param options - Table schema definition object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
createTable(tableName, options, callback): Promise;

Usage Examples:

// In migration up() method
exports.up = function(db) {
  return db.createTable('users', {
    id: { 
      type: 'int', 
      primaryKey: true, 
      autoIncrement: true 
    },
    name: { 
      type: 'string', 
      length: 255, 
      notNull: true 
    },
    email: { 
      type: 'string', 
      length: 255, 
      unique: true 
    },
    age: { 
      type: 'int' 
    },
    created_at: { 
      type: 'datetime', 
      defaultValue: 'CURRENT_TIMESTAMP' 
    }
  });
};

// With foreign key
exports.up = function(db) {
  return db.createTable('posts', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    title: { type: 'string', length: 255, notNull: true },
    user_id: { 
      type: 'int', 
      notNull: true,
      foreignKey: {
        name: 'posts_user_id_fk',
        table: 'users',
        rules: {
          onDelete: 'CASCADE',
          onUpdate: 'RESTRICT'
        }
      }
    }
  });
};

Drop Table

Remove an existing database table.

/**
 * Drop an existing database table
 * @param tableName - Name of the table to drop
 * @param callback - Completion callback (function)
 * @returns Promise
 */
dropTable(tableName, callback): Promise;

Usage Example:

// In migration down() method
exports.down = function(db) {
  return db.dropTable('users');
};

Rename Table

Rename an existing database table.

/**
 * Rename an existing database table
 * @param tableName - Current table name
 * @param newTableName - New table name
 * @param callback - Completion callback (function)
 * @returns Promise
 */
renameTable(tableName, newTableName, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.renameTable('old_users', 'users');
};

Column Operations

Add Column

Add a new column to an existing table.

/**
 * Add a new column to an existing table
 * @param tableName - Name of the table
 * @param columnName - Name of the new column
 * @param columnSpec - Column specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
addColumn(tableName, columnName, columnSpec, callback): Promise;

Usage Examples:

exports.up = function(db) {
  return db.addColumn('users', 'middle_name', {
    type: 'string',
    length: 100
  });
};

// Add column with constraints
exports.up = function(db) {
  return db.addColumn('users', 'status', {
    type: 'string',
    length: 20,
    notNull: true,
    defaultValue: 'active'
  });
};

Remove Column

Remove an existing column from a table.

/**
 * Remove an existing column from a table
 * @param tableName - Name of the table
 * @param columnName - Name of the column to remove
 * @param callback - Completion callback (function)
 * @returns Promise
 */
removeColumn(tableName, columnName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.removeColumn('users', 'middle_name');
};

Rename Column

Rename an existing column in a table.

/**
 * Rename an existing column in a table
 * @param tableName - Name of the table
 * @param oldColumnName - Current column name
 * @param newColumnName - New column name
 * @param callback - Completion callback (function)
 * @returns Promise
 */
renameColumn(tableName, oldColumnName, newColumnName, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.renameColumn('users', 'name', 'full_name');
};

Change Column

Modify the specification of an existing column.

/**
 * Modify the specification of an existing column
 * @param tableName - Name of the table
 * @param columnName - Name of the column to modify
 * @param columnSpec - New column specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
changeColumn(tableName, columnName, columnSpec, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.changeColumn('users', 'name', {
    type: 'string',
    length: 500,  // Increased from 255
    notNull: true
  });
};

Index Operations

Add Index

Create an index on one or more columns.

/**
 * Create an index on one or more columns
 * @param tableName - Name of the table
 * @param indexName - Name of the index
 * @param columns - Column name (string) or array of column names
 * @param callback - Completion callback (function)
 * @returns Promise
 */
addIndex(tableName, indexName, columns, callback): Promise;

Usage Examples:

// Single column index
exports.up = function(db) {
  return db.addIndex('users', 'users_email_idx', 'email');
};

// Multi-column index
exports.up = function(db) {
  return db.addIndex('posts', 'posts_user_created_idx', ['user_id', 'created_at']);
};

// Unique index
exports.up = function(db) {
  return db.addIndex('users', 'users_username_unique', 'username', true);
};

Remove Index

Drop an existing index.

/**
 * Drop an existing index
 * @param tableName - Name of the table
 * @param indexName - Name of the index to remove
 * @param callback - Completion callback (function)
 * @returns Promise
 */
removeIndex(tableName, indexName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.removeIndex('users', 'users_email_idx');
};

Foreign Key Operations

Add Foreign Key

Create a foreign key constraint between tables.

/**
 * Create a foreign key constraint between tables
 * @param tableName - Name of the table to add foreign key to
 * @param referencedTableName - Name of the referenced table
 * @param keyName - Name of the foreign key constraint
 * @param fieldMapping - Object mapping local to foreign columns
 * @param rules - Constraint rules (onDelete, onUpdate)
 * @param callback - Completion callback (function)
 * @returns Promise
 */
addForeignKey(tableName, referencedTableName, keyName, fieldMapping, rules, callback): Promise;

Usage Examples:

// Simple foreign key
exports.up = function(db) {
  return db.addForeignKey('posts', 'users', 'posts_user_fk', {
    'user_id': 'id'
  }, {
    onDelete: 'CASCADE',
    onUpdate: 'RESTRICT'
  });
};

// Multiple column foreign key
exports.up = function(db) {
  return db.addForeignKey('order_items', 'products', 'order_items_product_fk', {
    'product_id': 'id',
    'product_variant_id': 'variant_id'
  }, {
    onDelete: 'CASCADE'
  });
};

Remove Foreign Key

Drop an existing foreign key constraint.

/**
 * Drop an existing foreign key constraint
 * @param tableName - Name of the table
 * @param keyName - Name of the foreign key constraint to remove
 * @param callback - Completion callback (function)
 * @returns Promise
 */
removeForeignKey(tableName, keyName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.removeForeignKey('posts', 'posts_user_fk');
};

SQL Execution

Run SQL

Execute raw SQL statements.

/**
 * Execute raw SQL statements
 * @param sql - SQL statement to execute
 * @param params - Optional parameter array for prepared statements
 * @param callback - Completion callback (function)
 * @returns Promise
 */
runSql(sql, params, callback): Promise;

Usage Examples:

// Simple SQL execution
exports.up = function(db) {
  return db.runSql('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
};

// SQL with parameters
exports.up = function(db) {
  return db.runSql(
    'INSERT INTO settings (name, value) VALUES (?, ?)',
    ['app_version', '1.0.0']
  );
};

// Complex SQL operation
exports.up = function(db) {
  return db.runSql(`
    CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users 
    FOR EACH ROW 
    SET NEW.updated_at = CURRENT_TIMESTAMP
  `);
};

All (Query)

Execute a query and return all results.

/**
 * Execute a query and return all results
 * @param sql - SQL query to execute
 * @param params - Optional parameter array for prepared statements
 * @param callback - Completion callback (function)
 * @returns Promise<Array> - Query results
 */
all(sql, params, callback): Promise<Array>;

Usage Example:

exports.up = function(db) {
  return db.all('SELECT id, name FROM users WHERE active = ?', [true])
    .then(function(users) {
      console.log('Found', users.length, 'active users');
      // Process results...
    });
};

Data Manipulation (Seeds Only)

These methods are available in seed files for data manipulation:

Insert

Insert data into a table.

/**
 * Insert data into a table
 * @param tableName - Name of the table
 * @param data - Data object or array of objects to insert
 * @param callback - Completion callback (function)
 * @returns Promise
 */
insert(tableName, data, callback): Promise;

Usage Examples:

// Single record insert
exports.up = function(db) {
  return db.insert('users', {
    name: 'John Doe',
    email: 'john@example.com'
  });
};

// Multiple records insert
exports.up = function(db) {
  return db.insert('categories', [
    { name: 'Electronics', slug: 'electronics' },
    { name: 'Books', slug: 'books' },
    { name: 'Clothing', slug: 'clothing' }
  ]);
};

Update

Update existing data in a table.

/**
 * Update existing data in a table
 * @param tableName - Name of the table
 * @param data - Data object with new values
 * @param where - WHERE clause specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
update(tableName, data, where, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.update('users', 
    { status: 'verified' },
    { email: 'john@example.com' }
  );
};

Delete

Delete data from a table.

/**
 * Delete data from a table
 * @param tableName - Name of the table
 * @param where - WHERE clause specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
delete(tableName, where, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.delete('users', { 
    email: 'john@example.com' 
  });
};

Lookup

Query data from a table.

/**
 * Query data from a table
 * @param tableName - Name of the table
 * @param column - Column name to query
 * @param value - Value to search for
 * @param callback - Completion callback (function)
 * @returns Promise<Array> - Query results
 */
lookup(tableName, column, value, callback): Promise<Array>;

Usage Example:

exports.up = function(db) {
  return db.lookup('users', 'email', 'admin@example.com')
    .then(function(users) {
      if (users.length === 0) {
        return db.insert('users', {
          name: 'Admin',
          email: 'admin@example.com',
          role: 'admin'
        });
      }
    });
};

Truncate

Remove all data from a table while preserving structure.

/**
 * Remove all data from a table while preserving structure
 * @param tableName - Name of the table to truncate
 * @param callback - Completion callback (function)
 * @returns Promise
 */
truncate(tableName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.truncate('temp_data');
};

Column Specification Options

When creating or modifying columns, use these specification options:

/**
 * Column specification object for table operations
 */
interface ColumnSpec {
  /** Column data type */
  type: string;
  /** Column length/size */
  length?: number;
  /** Precision for decimal types */
  precision?: number;
  /** Scale for decimal types */
  scale?: number;
  /** Whether column allows NULL values */
  notNull?: boolean;
  /** Default value for the column */
  defaultValue?: any;
  /** Whether column is primary key */
  primaryKey?: boolean;
  /** Whether column auto-increments */
  autoIncrement?: boolean;
  /** Whether column values must be unique */
  unique?: boolean;
  /** Foreign key specification */
  foreignKey?: {
    name: string;
    table: string;
    rules?: {
      onDelete?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
      onUpdate?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
    };
  };
}

Data Types

Available data types for column specifications:

/**
 * Standard data types available across database drivers
 */
interface DataTypes {
  STRING: 'string';           // VARCHAR
  TEXT: 'text';              // TEXT/LONGTEXT
  INTEGER: 'int';            // INTEGER
  BIGINT: 'bigint';          // BIGINT
  DECIMAL: 'decimal';        // DECIMAL/NUMERIC
  FLOAT: 'real';             // FLOAT/REAL
  DATE: 'date';              // DATE
  TIME: 'time';              // TIME
  DATETIME: 'datetime';      // DATETIME/TIMESTAMP
  BOOLEAN: 'boolean';        // BOOLEAN/TINYINT(1)
  BINARY: 'binary';          // BINARY/VARBINARY
  BLOB: 'blob';              // BLOB
}

Usage Example:

exports.up = function(db) {
  return db.createTable('products', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    name: { type: 'string', length: 255, notNull: true },
    description: { type: 'text' },
    price: { type: 'decimal', precision: 10, scale: 2 },
    in_stock: { type: 'boolean', defaultValue: true },
    created_at: { type: 'datetime', defaultValue: 'CURRENT_TIMESTAMP' }
  });
};

Database-Specific Considerations

MySQL

  • Supports all DDL and DML operations
  • Foreign keys require InnoDB engine
  • Index names must be unique per table
  • Column length required for STRING/VARCHAR types

PostgreSQL

  • Supports all DDL and DML operations
  • Schema-aware operations supported
  • Advanced data types available (JSON, arrays, etc.)
  • Case-sensitive identifiers

SQLite3

  • Limited ALTER TABLE support
  • No DROP COLUMN support in older versions
  • Foreign keys must be enabled explicitly
  • Limited data type enforcement

MongoDB

  • Schema operations create collections and indexes
  • Data types mapped to BSON types
  • Limited relational constraints
  • Document-based operations for data manipulation

Error Handling

Database interface methods can throw various errors:

exports.up = function(db) {
  return db.createTable('users', tableSpec)
    .catch(function(error) {
      if (error.message.includes('already exists')) {
        console.log('Table already exists, skipping...');
        return Promise.resolve();
      }
      throw error;
    });
};

Types

/**
 * Database interface callback function signature
 */
type DatabaseCallback = (error?: Error, result?: any) => void;

/**
 * WHERE clause specification for queries
 */
interface WhereClause {
  [columnName: string]: any;
}

/**
 * Field mapping for foreign keys
 */
interface FieldMapping {
  [localColumn: string]: string; // foreign column
}

/**
 * Foreign key constraint rules
 */
interface ForeignKeyRules {
  onDelete?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
  onUpdate?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
}

docs

cli-interface.md

configuration-management.md

data-seeding.md

database-interface.md

database-operations.md

index.md

migration-management.md

tile.json