@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

$3

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

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

$3

#### 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'],
},
}
}
};
`

$3

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;
}
`

$3

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

$3

| 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 | - | Multi clients configuration |

$3

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

$3

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

$3

Alias to app.teggSequelize for multi-client mode.

$3

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

$3

`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;
}
}
`

$3

`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.

$3

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

$3

#### 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');
`

$3

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

$3

#### 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'
});
`

$3

✅ 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

$3

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

$3

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

$3

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