Migrations for schema changes in Express - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What happens when you run this migration script?

Consider this migration script using a common migration tool in Express apps. What will be the result after running it?

Express

exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.string('nickname').notNullable().defaultTo('guest');
  });
};

exports.down = function(knex) {
  return knex.schema.table('users', function(table) {
    table.dropColumn('nickname');
  });
};

A Removes the 'nickname' column from 'users' table.

B Adds a new column 'nickname' to 'users' table with default 'guest' and disallows null values.

C Creates a new table called 'nickname' with a string column.

D Renames the 'nickname' column to 'guest' in 'users' table.

💡 Hint

Look at the 'up' function and what it does to the 'users' table.

The 'up' function adds a new string column 'nickname' to the 'users' table. It sets it as not nullable and gives a default value 'guest'. The 'down' function reverses this by dropping the column.

📝 Syntax

intermediate

Identify the syntax error in this migration code

Which option correctly identifies the syntax error in this migration script?

Express

exports.up = function(knex) {
  return knex.schema.createTable('posts', function(table) {
    table.increments('id').primary()
    table.string('title').notNullable()
    table.text('content')
    table.timestamps
  });
};

A Missing parentheses after 'table.timestamps' to call the function.

B The 'text' column type is invalid in this context.

C The 'increments' method cannot be chained with 'primary()'.

D Missing semicolon after 'table.string('title').notNullable()'.

💡 Hint

Check how functions are called in JavaScript.

'table.timestamps' is a function and must be called with parentheses 'table.timestamps()' to add created_at and updated_at columns.

🔧 Debug

advanced

Why does this migration fail when adding a non-nullable column?

This migration tries to add a non-nullable column 'age' to 'users' table but fails. Why?

Express

exports.up = function(knex) {
  return knex.schema.table('users', function(table) {
    table.integer('age').notNullable();
  });
};

A Because the 'table' argument is missing in the callback function.

B Because 'integer' is not a valid column type in knex migrations.

C Because existing rows have no value for 'age', violating the non-null constraint.

D Because the migration script is missing a return statement.

💡 Hint

Think about what happens to existing data when adding a non-nullable column without a default.

Adding a non-nullable column without a default value fails if existing rows do not have a value for that column, causing a constraint violation.

🧠 Conceptual

advanced

What is the main purpose of the 'down' function in migration scripts?

Why do migration scripts include a 'down' function alongside the 'up' function?

A To reverse the changes made by the 'up' function, allowing rollback of schema changes.

B To apply additional schema changes after the 'up' function completes.

C To validate the schema after migration is applied.

D To seed the database with initial data.

💡 Hint

Think about how you undo a migration if something goes wrong.

The 'down' function defines how to undo the changes made by 'up', enabling safe rollback of database schema changes.

❓ state_output

expert

What is the state of the 'products' table after running this migration?

After running this migration, what columns will the 'products' table have?

Express

exports.up = function(knex) {
  return knex.schema.table('products', function(table) {
    table.dropColumn('description');
    table.string('summary').notNullable().defaultTo('No summary');
  });
};

A Only the 'description' column remains; 'summary' is not added due to error.

B Both 'description' and 'summary' columns exist with 'summary' nullable.

C The migration fails because you cannot drop and add columns in the same migration.

D The 'description' column is removed; a new 'summary' column is added with default 'No summary' and not nullable.

💡 Hint

Check what happens when you drop a column and add another in one migration.

The migration removes the 'description' column and adds a new 'summary' column with constraints and default value. This is valid and will succeed.

Practice

1. What is the main purpose of migrations in an Express app using a database?

easy

A. To create API routes for the app

B. To handle user authentication automatically

C. To manage and apply database schema changes step-by-step

D. To serve static files like images and CSS

Solution

1. Step 1: Understand migrations role

   Migrations help track and apply changes to the database schema in a controlled way.

2. Step 2: Compare other options

   Options A, B, and C relate to other app features, not schema changes.

3. Final Answer:

   To manage and apply database schema changes step-by-step -> Option C

4. Quick Check:

   Migrations = Manage schema changes [OK]

Hint: Migrations = step-by-step database structure updates [OK]

Common Mistakes:

• Confusing migrations with routing or middleware
• Thinking migrations handle user login
• Assuming migrations serve static files

2. Which of the following is the correct command to create a new migration file using a common migration tool like Sequelize CLI?

easy

A. sequelize new migration add-users-table

B. sequelize migrate new add-users-table

C. sequelize create migration add-users-table

D. sequelize migration:create --name add-users-table

Solution

1. Step 1: Recall Sequelize CLI syntax

   The correct command to create a migration is sequelize migration:create --name <name>.

2. Step 2: Check other options

   Options B, C, and D use incorrect command structures not recognized by Sequelize CLI.

3. Final Answer:

   sequelize migration:create --name add-users-table -> Option D

4. Quick Check:

   Correct CLI command = sequelize migration:create --name add-users-table [OK]

Hint: Use 'migration:create --name' to create migration files [OK]

Common Mistakes:

• Mixing up command order or keywords
• Omitting '--name' flag
• Using 'new' instead of 'create'

3. Given this migration snippet using Sequelize:

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn('Users', 'age', { type: Sequelize.INTEGER });
  },
  down: async (queryInterface) => {
    await queryInterface.removeColumn('Users', 'age');
  }
};

What will happen when this migration is run?

medium

A. Creates a new 'Users' table with an 'age' column

B. Adds an 'age' column of type INTEGER to the 'Users' table

C. Removes the 'age' column from the 'Users' table

D. Throws an error because 'down' is missing Sequelize parameter

Solution

1. Step 1: Analyze the 'up' function

   The 'up' function adds a new column 'age' of type INTEGER to the 'Users' table.

2. Step 2: Understand the 'down' function

   The 'down' function reverses this by removing the 'age' column, but this runs only on rollback.

3. Final Answer:

   Adds an 'age' column of type INTEGER to the 'Users' table -> Option B

4. Quick Check:

   'up' adds column, 'down' removes column [OK]

Hint: 'up' applies changes, 'down' reverts them [OK]

Common Mistakes:

• Confusing 'up' and 'down' functions
• Assuming 'down' runs on migration
• Thinking it creates a new table

4. You wrote this migration to rename a column but it throws an error:

module.exports = {
  up: async (queryInterface) => {
    await queryInterface.renameColumn('Users', 'username', 'user_name');
  },
  down: async (queryInterface) => {
    await queryInterface.renameColumn('Users', 'user_name', 'username');
  }
};

What is the most likely cause of the error?

medium

A. The table name 'Users' is incorrect or does not exist

B. Missing Sequelize parameter in the functions

C. renameColumn requires the column type as a third argument

D. The migration file name is invalid

Solution

1. Step 1: Check renameColumn usage

   renameColumn needs correct table and column names; no type argument is needed.

2. Step 2: Consider error causes

   If the table 'Users' does not exist or is misnamed, renameColumn will fail.

3. Final Answer:

   The table name 'Users' is incorrect or does not exist -> Option A

4. Quick Check:

   Table name must exist for renameColumn [OK]

Hint: Check table and column names exist before renaming [OK]

Common Mistakes:

• Assuming renameColumn needs column type
• Ignoring table name case sensitivity
• Forgetting to verify table existence

5. You want to add a new column 'email' to the 'Users' table but only if it doesn't already exist, to avoid errors during migration. Which approach best handles this in an Express app migration?

hard

A. Use a raw SQL query inside the migration to check column existence before adding

B. Directly add the column without checks; rely on migration rollback if error occurs

C. Modify the model only and skip migration since model syncs automatically

D. Delete the 'Users' table first, then add the column

Solution

1. Step 1: Understand migration safety

   Adding a column without checking can cause errors if it exists already.

2. Step 2: Use raw SQL for conditional check

   Running a raw SQL query to check if the column exists before adding prevents errors.

3. Step 3: Evaluate other options

   Directly add the column without checks; rely on migration rollback if error occurs risks errors, C ignores migration purpose, D is destructive and unsafe.

4. Final Answer:

   Use a raw SQL query inside the migration to check column existence before adding -> Option A

5. Quick Check:

   Check column first with SQL to avoid errors [OK]

Hint: Check column existence with SQL before adding [OK]

Common Mistakes:

• Skipping existence check causing migration failure
• Relying only on model sync without migration
• Dropping tables unnecessarily