sequelize-基本用法

最新推荐文章于 2024-08-04 17:53:38 发布

Winne_Shen

最新推荐文章于 2024-08-04 17:53:38 发布

阅读量4.2k

点赞数

分类专栏： sql

sql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

基本用法

要获得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:pass@example.com: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 queries
sequelize.query('your query', [, options])

// Quick example
sequelize.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
        }
      }
    }]
  */
})