sequelize-基本用法

基本用法

要获得ball rollin，你首先必须创建一个Sequelize实例。以如下方式使用它：

const sequelize = new Sequelize('database', 'username', 'password', {  dialect: 'mysql'});

这将保存传递的数据库凭证并提供所有其他方法。

此外，您可以指定一个非默认主机/端口：

const sequelize = new Sequelize('database', 'username', 'password', {  dialect: 'mysql'  host: "my.server.tld",  port: 9821,})

如果你只是没有密码：

const sequelize = new Sequelize({  database: 'db_name',  username: 'username',  password: null,  dialect: 'mysql'});

您也可以使用连接字符串：

const sequelize = new Sequelize('mysql://user:[e-mail]:9821/db_name', {  // Look to the next section for possible options})

选项

除了主机和端口，Sequelize还有一大堆选项。如下：

• 请参阅Sequelize API
• 参见模型定义
• 查看交易

const sequelize = new Sequelize('database', 'username', 'password', {  // the sql dialect of the database  // currently supported: 'mysql', 'sqlite', 'postgres', 'mssql'  dialect: 'mysql',  // custom host; default: localhost  host: 'my.server.tld',  // custom port; default: dialect default  port: 12345,  // custom protocol; default: 'tcp'  // postgres only, useful for Heroku  protocol: null,  // disable logging; default: console.log  logging: false,  // you can also pass any dialect options to the underlying dialect library  // - default is empty  // - currently supported: 'mysql', 'postgres', 'mssql'  dialectOptions: {    socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock',    supportBigNumbers: true,    bigNumberStrings: true  },  // the storage engine for sqlite  // - default ':memory:'  storage: 'path/to/database.sqlite',  // disable inserting undefined values as NULL  // - default: false  omitNull: true,  // a flag for using a native library or not.  // in the case of 'pg' -- set this to true will allow SSL support  // - default: false  native: true,  // Specify options, which are used when sequelize.define is called.  // The following example:  //   define: { timestamps: false }  // is basically the same as:  //   sequelize.define(name, attributes, { timestamps: false })  // so defining the timestamps for each model will be not necessary  define: {    underscored: false    freezeTableName: false,    charset: 'utf8',    dialectOptions: {      collate: 'utf8_general_ci'    },    timestamps: true  },  // similar for sync: you can define this to always force sync for models  sync: { force: true },  // pool configuration used to pool database connections  pool: {    max: 5,    idle: 30000,    acquire: 60000,  },  // isolation level of each transaction  // defaults to dialect default  isolationLevel: Transaction.ISOLATION_LEVELS.REPEATABLE_READ})

提示：您还可以为记录部分定义一个自定义函数。只需传递一个函数。第一个参数将是记录的字符串。

读取replication

Sequelize支持读取replication，即当您想要执行SELECT查询时，您可以连接多个服务器。当你读replication，你指定一个或多个服务器作为读副本和一个服务器充当写入主控，负责处理所有的写操作和更新，并将其传播到副本（注意，实际的复制过程不通过Sequelize处理，而应该由数据库后端设置）。

const sequelize = new Sequelize('database', null, null, {  dialect: 'mysql',  port: 3306  replication: {    read: [      { host: '8.8.8.8', username: 'read-username', password: 'some-password' },      { host: '9.9.9.9', username: 'another-username', password: null }    ],    write: { host: '1.1.1.1', username: 'write-username', password: 'any-password' }  },  pool: { // If you want to override the options used for the read/write pool you can do so here    max: 20,    idle: 30000  },})

如果您有任何适用于所有副本的常规设置，则不需要为每个实例提供这些副本。在上面的代码中，将数据库名称和端口传播到所有副本。如果您将任何副本保留，则用户和密码也会发生同样的情况。每个副本有以下选项：host，port，username，password，database。

Sequelize使用池来管理到副本的连接。内部Sequelize将维护使用pool配置创建的两个池。

如果你想修改这些，你可以在实例化Sequelize时将它作为一个选项来传递，如上所示。

每个write或useMaster: true查询将使用写入池。对于SELECT读池将被使用。只读副本使用基本的循环调度进行切换。

Dialects

随着Sequelize的发布1.6.0，library独立于特定的dialects。这意味着，您必须自己将相应的连接器库添加到您的项目中。

MySQL

为了使Sequelize与MySQL很好地协同工作，您需要安装mysql2@^1.0.0-rc.10或更高版本。一旦完成，你可以像这样使用它：

const sequelize = new Sequelize('database', 'username', 'password', {  dialect: 'mysql'})

注意：您可以通过设置dialectOptions参数将选项直接传递到方言库 。请参阅选项 的示例（目前只支持mysql）。

SQLite

为兼容SQLite，你需要sqlite3@~3.0.0。像这样配置Sequelize：

const sequelize = new Sequelize('database', 'username', 'password', {  // sqlite! now!  dialect: 'sqlite',  // the storage engine for sqlite  // - default ':memory:'  storage: 'path/to/database.sqlite'})

或者你可以使用连接字符串以及路径：

const sequelize = new Sequelize('sqlite:/home/abs/path/dbname.db')const sequelize = new Sequelize('sqlite:relativePath/dbname.db')

PostgreSQL

PostgreSQL库是pg@^5.0.0 || ^6.0.0你只需要定义方言：

const sequelize = new Sequelize('database', 'username', 'password', {  // gimme postgres, please!  dialect: 'postgres'})

注意： pg@^7.0.0目前不支持。

MSSQL

MSSQL库是tedious@^1.7.0你只需要定义方言：

const sequelize = new Sequelize('database', 'username', 'password', {  dialect: 'mssql'})

执行原始SQL查询

由于经常使用的情况下执行原始/已经准备好的SQL查询更容易，您可以使用该函数sequelize.query。

• 请参阅Sequelize.query API
• 请参阅查询类型

使用方法如下：

// Arguments for raw queriessequelize.query('your query', [, options])// Quick examplesequelize.query("SELECT * FROM myTable").then(myTableRows => {  console.log(myTableRows)})// If you want to return sequelize instances use the model options.// This allows you to easily map a query to a predefined model for sequelize e.g:sequelize  .query('SELECT * FROM projects', { model: Projects })  .then(projects => {    // Each record will now be mapped to the project's model.    console.log(projects)  })// Options is an object with the following keys:sequelize  .query('SELECT 1', {    // A function (or false) for logging your queries    // Will get called for every SQL query that gets send    // to the server.    logging: console.log,    // If plain is true, then sequelize will only return the first    // record of the result set. In case of false it will all records.    plain: false,    // Set this to true if you don't have a model definition for your query.    raw: false,    // The type of query you are executing. The query type affects how results are formatted before they are passed back.    type: Sequelize.QueryTypes.SELECT  })// Note the second argument being null!// Even if we declared a callee here, the raw: true would// supersede and return a raw object.sequelize  .query('SELECT * FROM projects', { raw: true })  .then(projects => {    console.log(projects)  })

查询中的替换可以通过两种不同的方式完成，使用命名参数（开头:）或未命名，

使用的语法取决于传递给函数的替换选项：

• 如果一个数组被传递，?将按照它们出现在数组中的顺序被替换
• 如果一个对象被传递，:key将被替换为该对象的键。如果对象包含在查询中找不到的键（反之亦然），则会抛出异常。

sequelize  .query(    'SELECT * FROM projects WHERE status = ?',    { raw: true, replacements: ['active']  )  .then(projects => {    console.log(projects)  })sequelize  .query(    'SELECT * FROM projects WHERE status = :status ',    { raw: true, replacements: { status: 'active' } }  )  .then(projects => {    console.log(projects)  })

一个注意：如果表的属性名称包含点，则结果对象将被嵌套：

sequelize.query('select 1 as `foo.bar.baz`').then(rows => {  console.log(JSON.stringify(rows))  /*    [{      "foo": {        "bar": {          "baz": 1        }      }    }]  */})