@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

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

Usage

Enable Plugin

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

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

Configure

Single Client

// 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

// 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/:

// 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

// 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

// 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, 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 for full list.

Examples

Transaction

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: '[email protected]' },
      { transaction }
    );
    
    await transaction.commit();
    ctx.body = user;
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

Associations

// 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

// 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

// 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

// 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

// 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

// 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

// 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

// 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 in @gulibs/sequelize-typescript.

TypeScript Support

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

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:

// 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
• Sequelize Documentation
• @gulibs/sequelize-typescript