15

Sequelize官网翻译八 —— 原生查询

 3 years ago
source link: https://zhuanlan.zhihu.com/p/125022799
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Sequelize官网翻译八 —— 原生查询

有些场景下使用原生查询更方便，此时你可以使用sequelize.query方法。

默认方法调用会返回两个结果——一个是结果数组，另一个是元数据对象(比如影响结果的行数)。注意由于是原生查询，元数据是数据库特定的。有些数据库方言在结果对象里包含了元数据。但是方法总是会返回两个结果，对于MSSQL和MySQL，是两个引用指向了同一个对象。

 const [results, metadata] = await sequelize.query("UPDATE users SET y = 42 WHERE x = 12");
 // Results will be an empty array and metadata will contain the number of affected rows.

如果你不想拿到元数据，可以传递一个type参数告诉Sequelize如何组装结果。比如，对于一个简单查询，你可以

 const { QueryTypes } = require('sequelize');
 const users = await sequelize.query("SELECT * FROM `users`", { type: QueryTypes.SELECT });
 // We didn't need to destructure the result here - the results were returned directly

一些其他的查询类型可以参考这里。

另一个参数是模型。如果你传了模型参数，那么返回数据将会是那个模型的实例。

 // Callee is the model definition. This allows you to easily map a query to a predefined model
 const projects = await sequelize.query('SELECT * FROM projects', {
   model: Projects,
   mapToModel: true // pass true here if you have any mapped fields
 });
 // Each element of `projects` is now an instance of Project

其他参数可以参考这里。

 const { QueryTypes } = require('sequelize');
 await sequelize.query('SELECT 1', {
   // A function (or false) for logging your queries
   // Will get called for every SQL query that gets sent
   // 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 return 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: 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.
 console.log(await sequelize.query('SELECT * FROM projects', { raw: true }));

“点”属性和嵌套参数

如果表的属性名字包含点.，通过设置nest: true，结果对象将变为嵌套对象。背后是通过dottie.js实现的。

  • 没有nest: true
 const { QueryTypes } = require('sequelize');
 const records = await sequelize.query('select 1 as `foo.bar.baz`', {
   type: QueryTypes.SELECT
 });
 console.log(JSON.stringify(records[0], null, 2));

 {
   "foo.bar.baz": 1
 }
  • 有nest: true
 const { QueryTypes } = require('sequelize');
 const records = await sequelize.query('select 1 as `foo.bar.baz`', {
   nest: true,
   type: QueryTypes.SELECT
 });
 console.log(JSON.stringify(records[0], null, 2));

 {
   "foo": {
     "bar": {
       "baz": 1
     }
   }
 }

替换

替换是能用两种方式实现的查询: 要么使用命名参数(用:开头)，或者没有名字，用?来占位。

译者注：相当于Java的PreparedStatement

  • 如果传递的是一个数组，那么?将会被按照次序替换
  • 如果传递的是一个对象，:key将会被对象的键替换。如果将对象里包含不存在的键，或者:key在对象里不存在，将会抛出一个异常
 const { QueryTypes } = require('sequelize');
 ​
 await sequelize.query(
   'SELECT * FROM projects WHERE status = ?',
   {
     replacements: ['active'],
     type: QueryTypes.SELECT
   }
 );
 ​
 await sequelize.query(
   'SELECT * FROM projects WHERE status = :status',
   {
     replacements: { status: 'active' },
     type: QueryTypes.SELECT
   }
 );

数组替换是自动进行的，下面的语句查询了符合数组值的状态

 const { QueryTypes } = require('sequelize');
 ​
 await sequelize.query(
   'SELECT * FROM projects WHERE status IN(:status)',
   {
     replacements: { status: ['active', 'inactive'] },
     type: QueryTypes.SELECT
   }
 );

为了使用%，把它放在替换部分里。下面的语句查询了以ben开头的用户名字

 const { QueryTypes } = require('sequelize');
 ​
 await sequelize.query(
   'SELECT * FROM users WHERE name LIKE :search_name',
   {
     replacements: { search_name: 'ben%' },
     type: QueryTypes.SELECT
   }
 );

关联参数

关联参数跟替换类似。替换的值在查询发给数据库之前就被sequelize给插入了，而关联参数是在传递给数据库的SQL查询文本之外的。查询要么是关联参数要么是替换。关联参数用$1、$2...或者$key来引用，跟数据库无关。

  • 如果传入一个数组，$1绑定为数组的第一个参数(bind[0])
  • 如果传入一个对象，$key被绑在object['key']上。每个键必须以非数字的字母开头。$1不是一个合法的键，即便存在object['1']
  • 上面两种情况都可以用$$来转义$字符

数组或对象都比如包含所有绑定的值，否则Sequelize会抛异常。哪怕一些无视绑定参数的数据库也是如此。

数据库可能会在将来对此增加限制。关联参数不能是SQL的关键词，也不能是表或列的名字。双引号文本或数据里也会忽略它们。在PostgreSQL里，如果类型不能从$1::varchar里推断出来，就需要强转它们。

 const { QueryTypes } = require('sequelize');
 ​
 await sequelize.query(
   'SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $1',
   {
     bind: ['active'],
     type: QueryTypes.SELECT
   }
 );
 ​
 await sequelize.query(
   'SELECT *, "text with literal $$1 and literal $$status" as t FROM projects WHERE status = $status',
   {
     bind: { status: 'active' },
     type: QueryTypes.SELECT
   }

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK