全栈技术博客-文章详情

egg项目创建之连接配置

最后更新时间：2022-06-28 10:58:11

egg nodejs

## egg的连接配置

- 安装
```js
$ npm i --save egg-sequelize
$ npm install --save mysql2 # For both mysql and mariadb dialects
或者使用其他方式连接
$ npm install --save pg pg-hstore
$ npm install --save tedious
```
-  在`config/plugin.js`里配置插件

```js
exports.sequelize = {
    enable: true,
    package: 'egg-sequelize'
}
```
-   在`conif/config.{env}.js`里进行连接配置
```js
exports.sequelize = {
      dialect: 'mysql', // 支持的数据连接类型: mysql, mariadb, postgres, mssql
      database: 'test',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: '',
      // delegate: 'myModel', // load all models to `app[delegate]` and `ctx[delegate]`, default to `model`
      // baseDir: 'my_model', // load all files in `app/${baseDir}` as models, default to `model`
      // exclude: 'index.js', // ignore `app/${baseDir}/index.js` when load models, support glob and array
      // more sequelize options
};
```

-   也可以进行以下这样连接
```js
exports.sequelize = {
      dialect: 'mysql', // support: mysql, mariadb, postgres, mssql
      connectionUri: 'mysql://root:@127.0.0.1:3306/test',
      // delegate: 'myModel', // load all models to `app[delegate]` and `ctx[delegate]`, default to `model`
      // baseDir: 'my_model', // load all files in `app/${baseDir}` as models, default to `model`
      // exclude: 'index.js', // ignore `app/${baseDir}/index.js` when load models, support glob and array
      // more sequelize options
};
```

-   egg-sequelize 有一个默认的 sequelize 选项如下

```js
{
        delegate: 'model',
        baseDir: 'model',
        logging(...args) {
          // if benchmark enabled, log used
          const used = typeof args[1] === 'number' ? `[${args[1]}ms]` : '';
          app.logger.info('[egg-sequelize]%s %s', used, args[0]);
        },
        host: 'localhost',
        port: 3306,
        username: 'root',
        benchmark: true,
        define: {
          freezeTableName: false,
          underscored: true,
        },
};
```

## 模型文件

默认情况下，请将模型置于 `app/model` 目录下。

## 约定

| model file        | class name               |
| ----------------- | ------------------------ |
| `user.js`         | `app.model.User`         |
| `person.js`       | `app.model.Person`       |
| `user_group.js`   | `app.model.UserGroup`    |
| `user/profile.js` | `app.model.User.Profile` |

- 表格始终具有时间戳字段：`created_at datetime`，`updated_at datetime`。
-   使用下划线样式列名，如: `user_id`，`comments_count`。

## 实例

### 标准

首先定义模型

> 注意：`options.delegate` 默认为 `model`，所以 `app.model` 是 [Sequelize实例](http://docs.sequelizejs.com/class/lib/sequelize.js~Sequelize.html#instance-constructor-constructor), 可以使用如下方法：`app.model.sync, app.model.query ...`
>
> ```
> // app/model/user.js
> module.exports = app => {
>   const { STRING, INTEGER, DATE } = app.Sequelize;
>   const User = app.model.define('user', {
>     login: STRING,
>     name: STRING(30),
>     password: STRING(32),
>     age: INTEGER,
>     last_sign_in_at: DATE,
>     created_at: DATE,
>     updated_at: DATE,
>   });
>   User.findByLogin = async function(login) {
>     return await this.findOne({
>       where: {
>         login: login
>       }
>     });
>   }
>   // don't use arraw function
>   User.prototype.logSignin = async function() {
>     return await this.update({ last_sign_in_at: new Date() });
>   }
>   return User;
> };
> ```

现在您可以在控制器中使用它：

```js
// app/controller/user.js
class UserController extends Controller {
  async index() {
    const users = await this.ctx.model.User.findAll();
    this.ctx.body = users;
  }
  async show() {
    const user = await this.ctx.model.User.findByLogin(this.ctx.params.login);
    await user.logSignin();
    this.ctx.body = user;
  }
}
```

### 关联

在 `Model.associate()` 中定义所有关联，egg-sequelize 将在加载所有模型后执行它。请参见下面的示例：

### 多数据源

egg-sequelize 支持独立加载多个数据源，您可以使用 `config.sequelize.datasources` 配置和加载多个数据源。

```js
// config/config.default.js
exports.sequelize = {
  datasources: [
    {
      delegate: 'model', // load all models to app.model and ctx.model
      baseDir: 'model', // load models from `app/model/*.js`
      database: 'biz',
      // other sequelize configurations
    },
    {
      delegate: 'admninModel', // load all models to app.adminModel and ctx.adminModel
      baseDir: 'admin_model', // load models from `app/admin_model/*.js`
      database: 'admin',
      // other sequelize configurations
    },
  ],
};
```

我们可以这样定义模型：

```js
// app/model/user.js
module.exports = app => {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const User = app.model.define('user', {
    login: STRING,
    name: STRING(30),
    password: STRING(32),
    age: INTEGER,
    last_sign_in_at: DATE,
    created_at: DATE,
    updated_at: DATE,
  });
  return User;
};
```

```js
// app/admin_model/user.js
module.exports = app => {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const User = app.adminModel.define('user', {
    login: STRING,
    name: STRING(30),
    password: STRING(32),
    age: INTEGER,
    last_sign_in_at: DATE,
    created_at: DATE,
    updated_at: DATE,
  });
  return User;
};
```

如果为不同的数据源定义同一个模型，那么对于不同的数据库，同一个模型文件将执行两次，因此我们可以使用两个参数获取 sequelize 实例：

```js
// app/model/user.js
// if this file will load multiple times for different datasource
// we can use the secound argument to get the sequelize instance
module.exports = (app, model) => {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const User = model.define('user', {
    login: STRING,
    name: STRING(30),
    password: STRING(32),
    age: INTEGER,
    last_sign_in_at: DATE,
    created_at: DATE,
    updated_at: DATE,
  });
  return User;
};
```

### 自定义 Sequelize

默认情况下，egg-sequelize 将使用 sequelize@5，您可以自定义 sequelize 版本使用 `config.sequelize.Sequelize` 绕过，例如：

```js
// config/config.default.js
exports.sequelize = {
  Sequelize: require('sequelize'),
};
```

### 完整示例

```js
// app/model/post.js
module.exports = app => {
  const { STRING, INTEGER, DATE } = app.Sequelize;
  const Post = app.model.define('Post', {
    name: STRING(30),
    user_id: INTEGER,
    created_at: DATE,
    updated_at: DATE,
  });
  Post.associate = function() {
    app.model.Post.belongsTo(app.model.User, { as: 'user' });
  }
  return Post;
};
```

```js
// app/controller/post.js
class PostController extends Controller {
  async index() {
    const posts = await this.ctx.model.Post.findAll({
      attributes: [ 'id', 'user_id' ],
      include: { model: this.ctx.model.User, as: 'user' },
      where: { status: 'publish' },
      order: 'id desc',
    });
    this.ctx.body = posts;
  }
  async show() {
    const post = await this.ctx.model.Post.findByPk(this.params.id);
    const user = await post.getUser();
    post.setDataValue('user', user);
    this.ctx.body = post;
  }
  async destroy() {
    const post = await this.ctx.model.Post.findByPk(this.params.id);
    await post.destroy();
    this.ctx.body = { success: true };
  }
}
```

## 同步模型到数据库

**我们强烈建议您使用 [Sequelize - Migrations](http://docs.sequelizejs.com/manual/tutorial/migrations.html) 创建或迁移数据库。**

**此代码只应在开发中使用。**

```js
// {app_root}/app.js
module.exports = app => {
  if (app.config.env === 'local' || app.config.env === 'unittest') {
    app.beforeStart(async () => {
      await app.model.sync({force: true});
    });
  }
};
```

egg项目创建之连接配置

留言条数·0