Creating Models and Migrations in Sequelize

Understanding the Problem

We need to create a database table to store information about trees, specifically giant sequoias. Think of this like creating a special filing system for tree records. Just as a filing cabinet needs drawers and folders organized in a specific way, our database needs a well-structured table with specific rules about what information can be stored and how.

Planning Our Approach

1. Generate the basic model and migration files using Sequelize CLI
2. Add constraints to ensure data integrity
3. Add validations to ensure data quality
4. Run and verify the migration
5. Test the implementation

Step-by-Step Implementation

Step 1: Generate Model and Migration

First, let's generate our Tree model using the Sequelize CLI:

npx dotenv sequelize-cli model:generate --name Tree --attributes \
tree:string,\
location:string,\
heightFt:float,\
groundCircumferenceFt:float
    

Step 2: Modify the Migration File

In server/db/migrations/XXXXXX-create-tree.js:

'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Trees', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      tree: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      location: {
        type: Sequelize.STRING
      },
      heightFt: {
        type: Sequelize.FLOAT
      },
      groundCircumferenceFt: {
        type: Sequelize.FLOAT
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Trees');
  }
};
    

Step 3: Modify the Model File

In server/db/models/tree.js:

'use strict';
const { Model } = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  class Tree extends Model {
    static associate(models) {
      // define associations here
    }
  }
  
  Tree.init({
    tree: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        notEmpty: true
      }
    },
    location: DataTypes.STRING,
    heightFt: {
      type: DataTypes.FLOAT,
      validate: {
        min: 0
      }
    },
    groundCircumferenceFt: {
      type: DataTypes.FLOAT,
      validate: {
        min: 0
      }
    }
  }, {
    sequelize,
    modelName: 'Tree',
  });
  return Tree;
};
    

Understanding Models vs Migrations

Think of it this way:

• The Migration is like the blueprint for building a filing cabinet - it defines the physical structure
• The Model is like the rules for using the filing cabinet - it defines how we can interact with the data

Key Concepts Explained

Database Constraints

Constraints are like physical limitations on our filing system:

• allowNull: false - Like requiring every folder to have a label
• unique: true - Like ensuring no two folders have the same label
• defaultValue - Like having a timestamp automatically added to new folders

Model Validations

Validations are like having a person check the records before filing:

• notEmpty - Ensures the tree name isn't just blank spaces
• min: 0 - Ensures measurements can't be negative

Running and Verifying the Migration

Execute the migration:

npx dotenv sequelize-cli db:migrate
    

Verify the table structure:

sqlite3 db/dev.db ".schema Trees"
    

Testing

Run the provided tests:

npm test test/phase-1-spec.js
    

Common Pitfalls to Avoid

• Forgetting to add both database constraints AND model validations
• Mixing up allowNull and notEmpty (they serve different purposes)
• Forgetting to set default timestamps
• Using incorrect Sequelize data types

Additional Tips

When working with database schemas:

• Always plan your data structure before implementing
• Consider what constraints make sense for your data
• Think about what validations will help maintain data quality
• Test thoroughly after implementing