npm package: @gulibs/tegg-sequelize
Description: Sequelize plugin for Egg.js 4.x using @gulibs/sequelize-typescript with PostgreSQL Alter Helper support
Author: HectorGu
Latest version: 1.2.1

# @gulibs/tegg-sequelize

Sequelize plugin for Egg.js 4.x using `sequelize-typescript`.

## Requirements

- Node.js >= 22.18.0
- Egg.js >= 4.1.0-beta.35
- Sequelize >= 6.37.5

## Installation

```bash
npm i @gulibs/tegg-sequelize
# or
pnpm add @gulibs/tegg-sequelize
```

## Usage

### Enable Plugin

```typescript
// config/plugin.ts
import sequelizePlugin from '@gulibs/tegg-sequelize';

export default {
  ...sequelizePlugin(),
};
```

### Configure

#### Single Client

```typescript
// config/config.default.ts
export default {
  teggSequelize: {
    client: {
      dialect: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'password',
      database: 'test',
      models: ['app/model'], // Default: ['app/model']
    }
  }
};
```

#### Multi Clients

```typescript
// config/config.default.ts
export default {
  teggSequelize: {
    clients: {
      db1: {
        dialect: 'mysql',
        host: 'localhost',
        database: 'db1',
        username: 'root',
        password: 'password',
        models: ['app/model/db1'],
      },
      db2: {
        dialect: 'mysql',
        host: 'localhost',
        database: 'db2',
        username: 'root',
        password: 'password',
        models: ['app/model/db2'],
      },
    }
  }
};
```

### Define Models

Create model files in `app/model/`:

```typescript
// app/model/User.ts
import { Table, Column, Model, DataType } from '@gulibs/tegg-sequelize';

@Table({
  tableName: 'users',
  timestamps: true,
})
export default class User extends Model {
  @Column({
    type: DataType.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  })
  id!: number;

  @Column({
    type: DataType.STRING(100),
    allowNull: false,
  })
  name!: string;

  @Column({
    type: DataType.STRING(100),
    unique: true,
    allowNull: false,
  })
  email!: string;

  @Column({
    type: DataType.DATE,
  })
  createdAt!: Date;

  @Column({
    type: DataType.DATE,
  })
  updatedAt!: Date;
}
```

### Use in Controller

#### Single Client

```typescript
// app/controller/user.ts
import { Controller } from 'egg';

export default class UserController extends Controller {
  async index() {
    const { ctx, app } = this;
    
    // Access Sequelize instance
    const sequelize = app.teggSequelize;
    
    // Access models
    const User = sequelize.models.User;
    const users = await User.findAll();
    
    ctx.body = users;
  }

  async create() {
    const { ctx, app } = this;
    const { name, email } = ctx.request.body;
    
    const User = app.teggSequelize.models.User;
    const user = await User.create({ name, email });
    
    ctx.body = user;
  }
}
```

#### Multi Clients

```typescript
// app/controller/user.ts
import { Controller } from 'egg';

export default class UserController extends Controller {
  async index() {
    const { ctx, app } = this;
    
    // Access specific client
    const db1 = app.teggSequelize.get('db1');
    const db2 = app.teggSequelize.get('db2');
    
    // Or use alias
    const db1Alt = app.teggSequelizes.get('db1');
    
    const User = db1.models.User;
    const users = await User.findAll();
    
    ctx.body = users;
  }
}
```

## Configuration Options

### EggSequelizeConfig

| Option | Type | Default | Description |
|--------|------|---------|-------------|
| `default` | `EggSequelizeClientOption` | - | Default options mixed into every client |
| `app` | `boolean` | `true` | Whether to load plugin in app process |
| `agent` | `boolean` | `false` | Whether to load plugin in agent process |
| `client` | `EggSequelizeClientOption` | - | Single client configuration |
| `clients` | `Record<string, EggSequelizeClientOption>` | - | Multi clients configuration |

### EggSequelizeClientOption

Extends all [Sequelize options](https://sequelize.org/docs/v6/other-topics/dialect-specific-things/), plus:

| Option | Type | Description |
|--------|------|-------------|
| `models` | `string \| string[] \| ModelCtor[]` | Model paths or model classes |
| `customFactory` | `(options, app, clientName) => Sequelize` | Custom factory to create Sequelize instance |

## API

### app.teggSequelize

- **Single client**: Direct access to Sequelize instance
- **Multi clients**: Use `app.teggSequelize.get('clientId')` to access specific client

### app.teggSequelizes

Alias to `app.teggSequelize` for multi-client mode.

### Decorators

All decorators from `sequelize-typescript` are re-exported:

- `@Table`
- `@Column`
- `@PrimaryKey`
- `@AutoIncrement`
- `@ForeignKey`
- `@BelongsTo`
- `@HasMany`
- `@HasOne`
- `@BelongsToMany`
- And more...

See [sequelize-typescript documentation](https://github.com/RobinBuschmann/sequelize-typescript) for full list.

## Examples

### Transaction

```typescript
async create() {
  const { ctx, app } = this;
  const sequelize = app.teggSequelize;
  
  const transaction = await sequelize.transaction();
  try {
    const User = sequelize.models.User;
    const user = await User.create(
      { name: 'John', email: 'john@example.com' },
      { transaction }
    );
    
    await transaction.commit();
    ctx.body = user;
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}
```

### Associations

```typescript
// app/model/User.ts
import { Table, Column, Model, HasMany } from '@gulibs/tegg-sequelize';
import Post from './Post.js';

@Table({ tableName: 'users' })
export default class User extends Model {
  @Column
  name!: string;

  @HasMany(() => Post)
  posts!: Post[];
}

// app/model/Post.ts
import { Table, Column, Model, ForeignKey, BelongsTo } from '@gulibs/tegg-sequelize';
import User from './User.js';

@Table({ tableName: 'posts' })
export default class Post extends Model {
  @Column
  title!: string;

  @ForeignKey(() => User)
  @Column
  userId!: number;

  @BelongsTo(() => User)
  user!: User;
}

// Usage
const User = app.teggSequelize.models.User;
const users = await User.findAll({
  include: [{ model: sequelize.models.Post }]
});
```

## PostgreSQL Alter Helper

When using PostgreSQL, this plugin provides a **PostgresAlterHelper** to handle PostgreSQL-specific ALTER operations that `sync({ alter: true })` cannot handle.

### Common Issues with `sync({ alter: true })`

PostgreSQL has stricter requirements than other databases:

1. ❌ Column type changes need explicit USING clauses
2. ❌ ENUM types are immutable and difficult to modify
3. ❌ Multiple sync calls can create duplicate constraints
4. ❌ Special index types (GIN, GIST) need specific syntax

### Using PostgresAlterHelper

#### Single Client

```typescript
// In your controller or service
const { app } = this;

// Access the helper
const helper = app.postgresHelper;

// Change column type
await helper.changeColumnType('Users', 'age', 'INTEGER', '"age"::integer');

// Add enum value
await helper.addEnumValue('enum_Orders_status', 'cancelled');

// Clean up duplicate constraints
await helper.cleanupDuplicateConstraints('Users', 'email');
```

#### Multi Clients

```typescript
// Access helper for specific client
const helper = app.postgresHelper.get('db1');

// Or use alias
const helper = app.postgresHelpers.get('db1');

// Use the helper
await helper.changeColumnType('Users', 'age', 'INTEGER', '"age"::integer');
```

### Available Methods

| Method | Description |
|--------|-------------|
| `changeColumnType()` | Change column type with USING clause |
| `addEnumValue()` | Add value to existing ENUM type |
| `getEnumValues()` | Get all values from ENUM type |
| `replaceEnum()` | Replace entire ENUM type |
| `cleanupDuplicateConstraints()` | Remove duplicate unique constraints |
| `listUniqueConstraints()` | List all unique constraints |
| `createIndexWithMethod()` | Create index with specific method (GIN, GIST, etc.) |
| `dropIndex()` | Drop index safely |

### Examples

#### Change Column Type

```typescript
// Change from STRING to INTEGER
await app.postgresHelper.changeColumnType(
  'Users',
  'age',
  'INTEGER',
  '"age"::integer'
);

// Custom conversion
await app.postgresHelper.changeColumnType(
  'Orders',
  'total',
  'NUMERIC(10,2)',
  'ROUND("total"::numeric, 2)'
);
```

#### Manage ENUM Types

```typescript
// Add a new value
await app.postgresHelper.addEnumValue('enum_Orders_status', 'cancelled');

// Add before a specific value
await app.postgresHelper.addEnumValue('enum_Orders_status', 'processing', {
  before: 'completed'
});

// Replace entire ENUM
await app.postgresHelper.replaceEnum(
  'Orders',
  'status',
  'enum_Orders_status',
  ['pending', 'processing', 'completed', 'cancelled', 'refunded']
);
```

#### Clean Up Duplicate Constraints

```typescript
// List all constraints
const constraints = await app.postgresHelper.listUniqueConstraints('Users');
console.log(constraints);

// Clean up duplicates
await app.postgresHelper.cleanupDuplicateConstraints('Users', 'email');
```

#### Create Special Indexes

```typescript
// GIN index for full-text search
await app.postgresHelper.createIndexWithMethod('Articles', ['search_vector'], {
  name: 'idx_articles_search',
  using: 'GIN'
});

// GIST index for geometric data
await app.postgresHelper.createIndexWithMethod('Locations', ['coordinates'], {
  name: 'idx_locations_geo',
  using: 'GIST'
});
```

### Best Practices

✅ **DO:**
- Use PostgresAlterHelper in migrations
- Test changes on staging first
- Validate data before replacing ENUMs

❌ **DON'T:**
- Don't use `sync({ alter: true })` in production
- Don't skip data validation when replacing ENUMs
- Don't ignore errors

### Migration Example

```typescript
// migrations/20240119-update-schema.ts
import type { QueryInterface } from 'sequelize';

export async function up(queryInterface: QueryInterface) {
  const sequelize = queryInterface.sequelize;
  const { PostgresAlterHelper } = await import('@gulibs/tegg-sequelize');
  const helper = new PostgresAlterHelper(sequelize as any);
  
  // Change column type
  await helper.changeColumnType('Users', 'age', 'INTEGER', '"age"::integer');
  
  // Add enum value
  await helper.addEnumValue('enum_Orders_status', 'cancelled');
  
  // Clean up duplicates
  await helper.cleanupDuplicateConstraints('Users', 'email');
}

export async function down(queryInterface: QueryInterface) {
  // Rollback logic
}
```

For more details, see the [PostgreSQL documentation](https://github.com/gulibs/sequelize-typescript/blob/main/POSTGRESQL_GUIDE.md) in `@gulibs/sequelize-typescript`.

## TypeScript Support

Full TypeScript support out of the box. All types are exported:

```typescript
import type { 
  EggSequelize, 
  EggSequelizeConfig,
  EggPostgresAlterHelper 
} from '@gulibs/tegg-sequelize';

// Custom configuration
const config: EggSequelizeConfig = {
  client: {
    dialect: 'mysql',
    host: 'localhost',
    // ... full type hints
  }
};
```

## Debugging

Enable logging to see what's happening:

```typescript
// config/config.default.ts
export default {
  teggSequelize: {
    client: {
      dialect: 'mysql',
      // Enable Sequelize query logging
      logging: console.log,
      // Or use Egg.js logger
      logging: (msg: string) => app.logger.info(msg),
    }
  }
};
```

## Troubleshooting

### Models not loading

Check:
1. Model paths are correct
2. Model files have default exports
3. No circular dependencies between models

### Connection errors

Check:
1. Database credentials are correct
2. Database server is running
3. Network/firewall settings

## License

MIT

## Links

- [Egg.js Documentation](https://eggjs.org)
- [Sequelize Documentation](https://sequelize.org)
- [@gulibs/sequelize-typescript](https://github.com/gulibs/sequelize-typescript)