Building Database Foundations with Sequelize

Understanding the Problem

We need to build a complete database system for tracking trees and insects. This involves several interconnected tasks:

• Creating database tables with appropriate columns and constraints
• Setting up models with validations
• Seeding the database with initial data

Think of this like building a digital nature preserve. Just as a real preserve needs organized records of its flora and fauna, our database needs structured ways to store and validate information about trees and insects.

Devising a Plan

1. Set up the Trees table and model
   • Create migration file with appropriate columns
   • Add database constraints
   • Create model with validations
   • Test the implementation
2. Seed the Trees table
   • Create seeder file
   • Add data for famous Sequoia trees
   • Implement up and down migrations
3. Set up the Insects table and model
   • Create migration with specified columns
   • Add constraints and validations
   • Test the implementation
4. Seed the Insects table
   • Create seeder for smallest insects
   • Implement reversible seeding

Carrying Out the Plan

Step 1: Creating the Trees Table

// Generate the model and migration
// npx sequelize-cli model:generate --name Tree --attributes tree:string,location:string,heightFt:float,groundCircumferenceFt:float

// Migration file (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');
  }
};

Tree Model with Validations

// models/tree.js
'use strict';
const { Model } = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  class Tree extends Model {
    static associate(models) {
      // Define associations here if needed
    }
  }
  
  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;
};

Step 2: Seeding Trees

// Generate seeder
// npx sequelize-cli seed:generate --name biggest-trees

// seeders/XXXXXX-biggest-trees.js
'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.bulkInsert('Trees', [
      {
        tree: 'General Sherman',
        location: 'Sequoia National Park',
        heightFt: 274.9,
        groundCircumferenceFt: 102.6,
        createdAt: new Date(),
        updatedAt: new Date()
      },
      // Additional trees...
    ]);
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.bulkDelete('Trees', {
      tree: ['General Sherman', 'General Grant', 'President', 'Lincoln', 'Stagg']
    });
  }
};

Step 3: Creating the Insects Table

// Generate model and migration
// npx sequelize-cli model:generate --name Insect --attributes name:string,description:string,territory:string,fact:string,millimeters:float

// Migration file
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Insects', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      description: {
        type: Sequelize.STRING
      },
      territory: {
        type: Sequelize.STRING
      },
      fact: {
        type: Sequelize.STRING(240)
      },
      millimeters: {
        type: Sequelize.FLOAT,
        allowNull: false
      },
      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('Insects');
  }
};

Insect Model with Validations

// models/insect.js
'use strict';
const { Model } = require('sequelize');

module.exports = (sequelize, DataTypes) => {
  class Insect extends Model {
    static associate(models) {
      // Define associations here if needed
    }
  }
  
  Insect.init({
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        notEmpty: true,
        isTitle(value) {
          const words = value.split(' ');
          const isValidTitle = words.every(word => 
            word[0] === word[0].toUpperCase()
          );
          if (!isValidTitle) {
            throw new Error('Name must be title cased');
          }
        }
      }
    },
    description: DataTypes.STRING,
    territory: DataTypes.STRING,
    fact: {
      type: DataTypes.STRING,
      validate: {
        len: [0, 240]
      }
    },
    millimeters: {
      type: DataTypes.FLOAT,
      allowNull: false,
      validate: {
        min: 0
      }
    }
  }, {
    sequelize,
    modelName: 'Insect',
  });
  
  return Insect;
};

Looking Back and Understanding

Key Concepts

Migrations vs Models

Think of migrations as construction blueprints and models as building managers:

• Migrations (Blueprints)
  • Define the physical structure of the database
  • Handle creation and modification of tables
  • Must be reversible (up/down)
  • Run once and modify the database directly
• Models (Building Managers)
  • Define how we interact with the data
  • Handle data validation
  • Implement business rules
  • Run every time we access the data

Common Patterns

When building database tables, follow these patterns:

1. Always include id, createdAt, and updatedAt columns
2. Add constraints at the database level when possible
3. Use model validations for complex business rules
4. Make migrations reversible with clear up/down functions

Testing Strategies

Verify your implementation by:

• Running migrations up and down
• Testing model validations
• Checking database constraints
• Verifying seeded data

Common Testing Commands

// Run migrations
npx sequelize-cli db:migrate

// Undo last migration
npx sequelize-cli db:migrate:undo

// Run seeds
npx sequelize-cli db:seed:all

// Undo seeds
npx sequelize-cli db:seed:undo:all

// Check database structure
sqlite3 db/dev.db ".schema Trees"
sqlite3 db/dev.db ".schema Insects"

// View data
sqlite3 db/dev.db "SELECT * FROM Trees;"
sqlite3 db/dev.db "SELECT * FROM Insects;"

Best Practices

• Always make migrations reversible
• Test both the up and down migrations
• Use meaningful names for migrations and models
• Keep migrations focused and atomic
• Add appropriate validations at both database and model levels
• Document your code with clear comments

Further Learning

To deepen your understanding, try:

• Adding more complex validations
• Creating relationships between models
• Implementing custom model methods
• Adding indexes for performance
• Creating composite unique constraints