sequelize

What this skill does

# Sequelize Development Guidelines

You are an expert in Sequelize ORM, Node.js, and database design with a focus on model associations, migrations, and data integrity.

## Core Principles

- Sequelize is a promise-based ORM for Node.js and TypeScript
- Supports PostgreSQL, MySQL, MariaDB, SQLite, and Microsoft SQL Server
- Uses model definitions with DataTypes for schema declaration
- Provides comprehensive support for associations, transactions, and hooks
- Migrations should be used for all schema changes in production

## Database Connection

### Basic Setup

```typescript
import { Sequelize } from "sequelize";

// Option 1: Connection URI
const sequelize = new Sequelize(process.env.DATABASE_URL!, {
  dialect: "postgres",
  logging: process.env.NODE_ENV === "development" ? console.log : false,
  pool: {
    max: 10,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
});

// Option 2: Individual parameters
const sequelize = new Sequelize({
  dialect: "postgres",
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || "5432"),
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  logging: false,
});

// Test connection
async function testConnection() {
  try {
    await sequelize.authenticate();
    console.log("Connection established successfully.");
  } catch (error) {
    console.error("Unable to connect to the database:", error);
  }
}
```

## Model Definition

### Basic Model with TypeScript

```typescript
import {
  Model,
  DataTypes,
  InferAttributes,
  InferCreationAttributes,
  CreationOptional,
} from "sequelize";
import { sequelize } from "./database";

class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
  declare id: CreationOptional<number>;
  declare email: string;
  declare name: string | null;
  declare isActive: CreationOptional<boolean>;
  declare createdAt: CreationOptional<Date>;
  declare updatedAt: CreationOptional<Date>;
}

User.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
    },
    email: {
      type: DataTypes.STRING(255),
      allowNull: false,
      unique: true,
      validate: {
        isEmail: true,
      },
    },
    name: {
      type: DataTypes.STRING(255),
      allowNull: true,
    },
    isActive: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
    },
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE,
  },
  {
    sequelize,
    tableName: "users",
    modelName: "User",
    underscored: true, // Use snake_case for column names
  }
);

export { User };
```

### Data Types Reference

```typescript
// String types
DataTypes.STRING(255)      // VARCHAR(255)
DataTypes.TEXT             // TEXT
DataTypes.TEXT("tiny")     // TINYTEXT (MySQL)

// Numeric types
DataTypes.INTEGER          // INTEGER
DataTypes.BIGINT           // BIGINT
DataTypes.FLOAT            // FLOAT
DataTypes.DOUBLE           // DOUBLE
DataTypes.DECIMAL(10, 2)   // DECIMAL(10,2)

// Boolean
DataTypes.BOOLEAN          // BOOLEAN / TINYINT(1)

// Date/Time
DataTypes.DATE             // DATETIME/TIMESTAMP
DataTypes.DATEONLY         // DATE
DataTypes.TIME             // TIME

// Binary
DataTypes.BLOB             // BLOB

// JSON
DataTypes.JSON             // JSON (if supported)
DataTypes.JSONB            // JSONB (PostgreSQL)

// UUID
DataTypes.UUID             // UUID
DataTypes.UUIDV4           // Auto-generate UUID v4

// Enum
DataTypes.ENUM("active", "inactive", "pending")

// Array (PostgreSQL only)
DataTypes.ARRAY(DataTypes.STRING)
```

## Associations

### One-to-One

```typescript
class User extends Model {
  declare id: number;
  declare profile?: Profile;
}

class Profile extends Model {
  declare id: number;
  declare userId: number;
  declare bio: string;
  declare user?: User;
}

// Define associations
User.hasOne(Profile, {
  foreignKey: "userId",
  as: "profile",
});

Profile.belongsTo(User, {
  foreignKey: "userId",
  as: "user",
});
```

### One-to-Many

```typescript
class User extends Model {
  declare id: number;
  declare posts?: Post[];
}

class Post extends Model {
  declare id: number;
  declare authorId: number;
  declare title: string;
  declare author?: User;
}

// Define associations
User.hasMany(Post, {
  foreignKey: "authorId",
  as: "posts",
});

Post.belongsTo(User, {
  foreignKey: "authorId",
  as: "author",
});
```

### Many-to-Many

```typescript
class Post extends Model {
  declare id: number;
  declare tags?: Tag[];
}

class Tag extends Model {
  declare id: number;
  declare name: string;
  declare posts?: Post[];
}

// Define associations with junction table
Post.belongsToMany(Tag, {
  through: "PostTags",
  foreignKey: "postId",
  otherKey: "tagId",
  as: "tags",
});

Tag.belongsToMany(Post, {
  through: "PostTags",
  foreignKey: "tagId",
  otherKey: "postId",
  as: "posts",
});
```

## Querying

### Basic Queries

```typescript
// Find all
const users = await User.findAll();

// Find with conditions
const activeUsers = await User.findAll({
  where: {
    isActive: true,
  },
});

// Find one
const user = await User.findOne({
  where: { email: "[email protected]" },
});

// Find by primary key
const user = await User.findByPk(1);

// Find or create
const [user, created] = await User.findOrCreate({
  where: { email: "[email protected]" },
  defaults: {
    name: "New User",
  },
});
```

### Advanced Queries with Operators

```typescript
import { Op } from "sequelize";

// Multiple conditions
const users = await User.findAll({
  where: {
    [Op.and]: [
      { isActive: true },
      { createdAt: { [Op.gte]: new Date("2024-01-01") } },
    ],
  },
});

// OR condition
const users = await User.findAll({
  where: {
    [Op.or]: [{ name: "John" }, { name: "Jane" }],
  },
});

// LIKE
const users = await User.findAll({
  where: {
    email: { [Op.like]: "%@example.com" },
  },
});

// IN
const users = await User.findAll({
  where: {
    id: { [Op.in]: [1, 2, 3] },
  },
});

// Comparison operators
const users = await User.findAll({
  where: {
    id: { [Op.gt]: 10 },      // Greater than
    age: { [Op.gte]: 18 },    // Greater than or equal
    score: { [Op.lt]: 100 },  // Less than
    rank: { [Op.lte]: 5 },    // Less than or equal
    status: { [Op.ne]: "inactive" }, // Not equal
  },
});
```

### Eager Loading (Include)

```typescript
// Load user with posts
const user = await User.findOne({
  where: { id: 1 },
  include: [
    {
      model: Post,
      as: "posts",
    },
  ],
});

// Nested includes
const user = await User.findOne({
  where: { id: 1 },
  include: [
    {
      model: Post,
      as: "posts",
      include: [
        {
          model: Tag,
          as: "tags",
        },
      ],
    },
  ],
});

// Include with conditions
const users = await User.findAll({
  include: [
    {
      model: Post,
      as: "posts",
      where: {
        publishedAt: { [Op.ne]: null },
      },
      required: false, // LEFT JOIN (include users without posts)
    },
  ],
});
```

### Pagination and Ordering

```typescript
const page = 1;
const pageSize = 20;

const { count, rows: users } = await User.findAndCountAll({
  where: { isActive: true },
  order: [
    ["createdAt", "DESC"],
    ["name", "ASC"],
  ],
  limit: pageSize,
  offset: (page - 1) * pageSize,
});

const totalPages = Math.ceil(count / pageSize);
```

### Aggregations

```typescript
// Count
const count = await User.count({
  where: { isActive: true },
});

// Sum
const total = await Order.sum("amount", {
  where: { status: "completed" },
});

// Max/Min
const maxPrice = await Product.max("price");
const minPrice = await Product.min("price");

// Group by
const stats = await Order.findAll({
  attributes: [
    "status",
    [sequelize.fn("COUNT", sequelize.col("id")), "count"],
    [sequelize.fn("SUM", sequelize.col("amount")), "total"],
  ],
  group: ["status"],
});
```

## CRUD Operations

### Create

```typescript
// Create single record
const user = await User.c