1"use strict";(globalThis.webpackChunk=globalThis.webpackChunk||[]).push([[6459],{4071(e,n,s){s.d(n,{Ay:()=>d,RM:()=>a});var t=s(74848),r=s(28453),i=s(34692);const a=[];function l(e){const n={a:"a",code:"code",strong:"strong",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",...(0,r.R)(),...e.components};return(0,t.jsx)(i.T,{children:(0,t.jsxs)(n.table,{children:[(0,t.jsx)(n.thead,{children:(0,t.jsxs)(n.tr,{children:[(0,t.jsx)(n.th,{}),(0,t.jsx)(n.th,{children:"PostgreSQL"}),(0,t.jsx)(n.th,{children:"MariaDB"}),(0,t.jsx)(n.th,{children:"MySQL"}),(0,t.jsx)(n.th,{children:"MSSQL"}),(0,t.jsx)(n.th,{children:"SQLite"}),(0,t.jsx)(n.th,{children:"Snowflake"}),(0,t.jsx)(n.th,{children:"db2"}),(0,t.jsx)(n.th,{children:"ibmi"}),(0,t.jsx)(n.th,{children:"Oracle"})]})}),(0,t.jsxs)(n.tbody,{children:[(0,t.jsxs)(n.tr,{children:[(0,t.jsx)(n.td,{children:(0,t.jsx)(n.code,{children:"uuidV1"})}),(0,t.jsxs)(n.td,{children:[(0,t.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/uuid-ossp.html",children:(0,t.jsx)(n.code,{children:"uuid_generate_v1"})})," (requires ",(0,t.jsx)(n.code,{children:"uuid-ossp"}),")"]}),(0,t.jsx)(n.td,{children:(0,t.jsx)(n.a,{href:"https://mariadb.com/kb/en/uuid/",children:(0,t.jsx)(n.code,{children:"UUID"})})}),(0,t.jsx)(n.td,{children:(0,t.jsx)(n.a,{href:"https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_uuid",children:(0,t.jsx)(n.code,{children:"UUID"})})}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"})]}),(0,t.jsxs)(n.tr,{children:[(0,t.jsx)(n.td,{children:(0,t.jsx)(n.code,{children:"uuidV4"})}),(0,t.jsxs)(n.td,{children:[(0,t.jsx)(n.strong,{children:"pg >= v13"}),": ",(0,t.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/functions-uuid.html",children:(0,t.jsx)(n.code,{children:"gen_random_uuid"})})," ",(0,t.jsx)("br",{}),(0,t.jsx)(n.strong,{children:"pg < v13"}),": ",(0,t.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/uuid-ossp.html",children:(0,t.jsx)(n.code,{children:"uuid_generate_v4"})})," (requires ",(0,t.jsx)(n.code,{children:"uuid-ossp"}),")"]}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:(0,t.jsx)(n.a,{href:"https://learn.microsoft.com/en-us/sql/t-sql/functions/newid-transact-sql?view=sql-server-ver16",children:(0,t.jsx)(n.code,{children:"NEWID"})})}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"}),(0,t.jsx)(n.td,{children:"N/A"})]})]})]})})}function d(e={}){const{wrapper:n}={...(0,r.R)(),...e.components};return n?(0,t.jsx)(n,{...e,children:(0,t.jsx)(l,{...e})}):l(e)}},88252(e,n,s){s.r(n),s.d(n,{assets:()=>o,contentTitle:()=>c,default:()=>x,frontMatter:()=>d,metadata:()=>t,toc:()=>h});const t=JSON.parse('{"id":"querying/raw-queries","title":"Raw SQL (literals)","description":"We believe that ORMs are inherently leaky abstractions. They are a compromise between the flexibility of SQL and the convenience of an object-oriented programming language.","source":"@site/docs/querying/raw-queries.mdx","sourceDirName":"querying","slug":"/querying/raw-queries","permalink":"/docs/v7/querying/raw-queries","draft":false,"unlisted":false,"editUrl":"https://github.com/sequelize/website/tree/main/docs/querying/raw-queries.mdx","tags":[],"version":"current","lastUpdatedBy":"renovate[bot]","lastUpdatedAt":1775795365000,"sidebarPosition":8,"frontMatter":{"sidebar_position":8,"title":"Raw SQL (literals)"},"sidebar":"tutorialSidebar","previous":{"title":"Operators","permalink":"/docs/v7/querying/operators"},"next":{"title":"Querying JSON","permalink":"/docs/v7/querying/json"}}');var r=s(74848),i=s(28453),a=s(4071),l=s(34692);const d={sidebar_position:8,title:"Raw SQL (literals)"},c=void 0,o={},h=[{value:"Writing raw SQL",id:"writing-raw-sql",level:2},{value:"Replacements",id:"replacements",level:3},{value:"Examples",id:"examples",level:4},{value:"Bind Parameters",id:"bind-parameters",level:3},{value:"Examples",id:"examples-1",level:4},{value:"\u26a0\ufe0f Don't put parameters in strings",id:"\ufe0f-dont-put-parameters-in-strings",level:3},{value:"Query Variable Mode",id:"query-variable-mode",level:3},{value:"<code>sql.identifier</code>
1",id:"sqlidentifier",level:3},{value:"<code>sql.list</code>",id:"sqllist",level:3},{value:"<code>sql.join</code>",id:"sqljoin",level:3},{value:"<code>sql.where</code>",id:"sqlwhere",level:3},{value:"<code>sql.attribute</code>",id:"sqlattribute",level:3},{value:"Use the association reference syntax",id:"use-the-association-reference-syntax",level:4},{value:"Use the Casting Syntax",id:"use-the-casting-syntax",level:4},{value:"Use the JSON Extraction syntax",id:"use-the-json-extraction-syntax",level:4},{value:"<code>sql.cast</code>",id:"sqlcast",level:3},{value:"<code>sql.uuidV4</code> & <code>sql.uuidV1</code>",id:"sqluuidv4--sqluuidv1",level:3},...a.RM,{value:"<code>sql.random</code>",id:"sqlrandom",level:3},{value:"<code>sql.col</code>",id:"sqlcol",level:3},{value:"<code>sql.jsonPath</code>",id:"sqljsonpath",level:3},{value:"<code>sql.unquote</code>",id:"sqlunquote",level:3},{value:"<code>sql.fn</code>",id:"sqlfn",level:3},{value:"<code>sequelize.query</code>",id:"sequelizequery",level:2},{value:""Dotted" attributes and the <code>nest</code> option",id:"dotted-attributes-and-the-nest-option",level:3}];function u(e){const n={a:"a",admonition:"admonition",code:"code",em:"em",h2:"h2",h3:"h3",h4:"h4",li:"li",ol:"ol",p:"p",pre:"pre",section:"section",strong:"strong",sup:"sup",table:"table",tbody:"tbody",td:"td",th:"th",thead:"thead",tr:"tr",ul:"ul",...(0,i.R)(),...e.components};return(0,r.jsxs)(r.Fragment,{children:[(0,r.jsx)(n.p,{children:"We believe that ORMs are inherently leaky abstractions. They are a compromise between the flexibility of SQL and the convenience of an object-oriented programming language.\nAs such, it does not make sense to try to provide a 100% complete abstraction over SQL (which could easily be more difficult to read than the SQL itself)."}),"\n",(0,r.jsxs)(n.p,{children:["For this reason, Sequelize treats raw SQL as a first-class citizen. ",(0,r.jsx)(n.strong,{children:"You can use raw SQL almost anywhere in Sequelize"}),(0,r.jsx)(n.sup,{children:(0,r.jsx)(n.a,{href:"#user-content-fn-1",id:"user-content-fnref-1","data-footnote-ref":!0,"aria-describedby":"footnote-label",children:"1"})}),", and thanks to the ",(0,r.jsx)(n.code,{children:"sql"})," tag, it's\neasy to write SQL that is both safe, readable and reusable."]}),"\n",(0,r.jsx)(n.h2,{id:"writing-raw-sql",children:"Writing raw SQL"}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql"})," tag is a template literal tag that allows you to write raw SQL:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-typescript",children:"import { sql } from '@sequelize/core';\n\nconst id = 5;\n\nawait sequelize.query(sql`SELECT * FROM users WHERE id = ${id}`);\n"})}),"\n",(0,r.jsxs)(n.p,{children:["As indicated above, raw SQL can be used almost anywhere in Sequelize. For instance, here is one way to use raw SQL to customize the ",(0,r.jsx)(n.code,{children:"WHERE"})," clause of a ",(0,r.jsx)(n.code,{children:"findAll"})," query:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-typescript",children:"import { sql } from '@sequelize/core';\n\nconst id = 5;\n\nconst users = await User.findAll({\n where: sql`id = ${id}`,\n});\n"})}),"\n",(0,r.jsxs)(n.p,{children:["In the above example, we used a variable in our raw SQL. Thanks to the ",(0,r.jsx)(n.code,{children:"sql"})," tag, Sequelize will automatically escape that variable to remove any risk of SQL injection."]}),"\n",(0,r.jsxs)(n.p,{children:["Sequelize supports two different ways to pass variables in raw SQL: ",(0,r.jsx)(n.strong,{children:"Replacements"})," and ",(0,r.jsx)(n.strong,{children:"Bind Parameters"}),".\nReplacements and bind parameters are available in all querying methods, and can be used together in the same query."]}),"\n",(0,r.jsx)(n.h3,{id:"replacements",children:"Replacements"}),"\n",(0,r.jsxs)(n.p,{children:["Replacements are a way to pass variables in your Query. They are an alternative to ",(0,r.jsx)(n.a,{href:"#bind-parameters",children:"Bind Parameters"}),"."]}),"\n",(0,r.jsx)(n.p,{children:"The difference between replacements and bind parameters is that replacements are escaped and inserted into the query by Sequelize before the query is sent to the database,\nwhereas bind parameters are sent to the database separately from the SQL query text, and 'escaped' by the Database itself."}),"\n",(0,r.jsx)(n.p,{children:"Replacements can be written in three different ways:"}),"\n",(0,r.jsxs)(n.ul,{children:["\n",(0,r.jsxs)(n.li,{children:["By using the ",(0,r.jsx)(n.code,{children:"sql"})," tag when the ",(0,r.jsxs)(n.a,{href:"#query-variable-mode",children:["query is in ",(0,r.jsx)(n.em,{children:"replacement"})," mode"]})]}),"\n",(0,r.jsxs)(n.li,{children:["By using numeric identifiers (represented by a ",(0,r.jsx)(n.code,{children:"?"}),") in the query. The ",(0,r.jsx)(n.code,{children:"replacements"})," option must be an array. The values will be replaced in the order in which they appear in the array and query."]}),"\n",(0,r.jsxs)(n.li,{children:["Or by using alphanumeric identifiers (e.g. ",(0,r.jsx)(n.code,{children:":firstName"}),", ",(0,r.jsx)(n.code,{children:":status"}),", etc\u2026). These identifiers follow common identifier rules (alphanumeric & underscore only, cannot start with a number). The ",(0,r.jsx)(n.code,{children:"replacements"})," option must be a plain object which includes each parameter (without the ",(0,r.jsx)(n.code,{children:":"})," prefix)."]}),"\n"]}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"replacements"})," option must contain all bound values, or Sequelize will throw an error."]}),"\n",(0,r.jsx)(n.h4,{id:"examples",children:"Examples"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { QueryTypes } from '@sequelize/core';\n\n// This query uses positional replacements\nawait sequelize.query('SELECT * FROM projects WHERE status = ?', {\n replacements: ['active'],\n});\n\n// This query uses named replacements\nawait sequelize.query('SELECT * FROM projects WHERE status = :status', {\n replacements: { status: 'active' },\n});\n\n// This query use replacements added by the sql tag\nawait sequelize.query(sql`SELECT * FROM projects WHERE status = ${'active'}`);\n\n// Replacements are also available in other querying methods\nawait Project.findAll({\n where: {\
1n status: sql`:status`,\n },\n replacements: { status: 'active' },\n});\n"})}),"\n",(0,r.jsx)(n.h3,{id:"bind-parameters",children:"Bind Parameters"}),"\n",(0,r.jsxs)(n.p,{children:["Bind parameters are a way to pass variables in your Query. They are an alternative to ",(0,r.jsx)(n.a,{href:"#replacements",children:"Replacements"}),"."]}),"\n",(0,r.jsx)(n.p,{children:"The difference between replacements and bind parameters is that replacements are escaped and inserted into the query by Sequelize before the query is sent to the database,\nwhereas bind parameters are sent to the database separately from the SQL query text, and 'escaped' by the Database itself."}),"\n",(0,r.jsx)(n.p,{children:"A query can have both bind parameters and replacements."}),"\n",(0,r.jsx)(n.p,{children:"Each database uses a different syntax for bind parameters, but Sequelize provides its own unification layer."}),"\n",(0,r.jsx)(n.p,{children:"Inconsequentially to which database you use, in Sequelize bind parameters are written following a postgres-like syntax. You can either:"}),"\n",(0,r.jsxs)(n.ul,{children:["\n",(0,r.jsxs)(n.li,{children:["By using the ",(0,r.jsx)(n.code,{children:"sql"})," tag when the ",(0,r.jsxs)(n.a,{href:"#query-variable-mode",children:["query is in ",(0,r.jsx)(n.em,{children:"bind parameter"})," mode"]})]}),"\n",(0,r.jsxs)(n.li,{children:["Use numeric identifiers (e.g. ",(0,r.jsx)(n.code,{children:"$1"}),", ",(0,r.jsx)(n.code,{children:"$2"}),", etc\u2026). Note that these identifiers start at 1, not 0. The ",(0,r.jsx)(n.code,{children:"bind"})," option must be an array which contains a value for each identifier used in the query (",(0,r.jsx)(n.code,{children:"$1"})," is bound to the 1st element in the array (",(0,r.jsx)(n.code,{children:"bind[0]"}),"), etc\u2026)."]}),"\n",(0,r.jsxs)(n.li,{children:["Use alphanumeric identifiers (e.g. ",(0,r.jsx)(n.code,{children:"$firstName"}),", ",(0,r.jsx)(n.code,{children:"$status"}),", etc\u2026). These identifiers follow common identifier rules (alphanumeric & underscore only, cannot start with a number). The ",(0,r.jsx)(n.code,{children:"bind"})," option must be a plain object which includes each bind parameter (without the ",(0,r.jsx)(n.code,{children:"$"})," prefix)."]}),"\n"]}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"bind"})," option must contain all bound values, or Sequelize will throw an error."]}),"\n",(0,r.jsxs)(n.admonition,{type:"info",children:[(0,r.jsxs)(n.p,{children:["Bind Parameters can only be used for data values. Bind Parameters cannot be used to dynamically change the name of a table, a column, or other non-data values parts of the query,\nbut you can use ",(0,r.jsx)(n.a,{href:"#sqlattribute",children:(0,r.jsx)(n.code,{children:"sql.attribute"})}),", and ",(0,r.jsx)(n.a,{href:"#sqlidentifier",children:(0,r.jsx)(n.code,{children:"sql.identifier"})})," for that."]}),(0,r.jsx)(n.p,{children:"Your database may have further restrictions with bind parameters."})]}),"\n",(0,r.jsx)(n.h4,{id:"examples-1",children:"Examples"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { QueryTypes } from '@sequelize/core';\n\n// This query use positional bind parameters\nawait sequelize.query('SELECT * FROM projects WHERE status = $1', {\n bind: ['active'],\n type: QueryTypes.SELECT,\n});\n\n// This query uses named bind parameters\nawait sequelize.query('SELECT * FROM projects WHERE status = $status', {\n bind: { status: 'active' },\n type: QueryTypes.SELECT,\n});\n\n// Bind parameters are also available in other querying methods\nawait Project.findAll({\n where: {\n status: sql`$status`,\n },\n bind: { status: 'active' },\n});\n"})}),"\n",(0,r.jsxs)(n.p,{children:["Sequelize does not currently support a way to ",(0,r.jsx)(n.a,{href:"https://github.com/sequelize/sequelize/issues/14410",children:"specify the DataType of a bind parameter"}),".\nUntil such a feature is implemented, you can cast your bind parameters if you need to change their DataType:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { QueryTypes } from '@sequelize/core';
1\n\nawait sequelize.query('SELECT * FROM projects WHERE id = CAST($1 AS int)', {\n bind: [5],\n type: QueryTypes.SELECT,\n});\n"})}),"\n",(0,r.jsxs)(n.admonition,{title:"Did you know?",type:"note",children:[(0,r.jsx)(n.p,{children:"Some dialects, such as PostgreSQL and IBM Db2, support a terser cast syntax that you can use if you prefer:"}),(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-typescript",children:"await sequelize.query('SELECT * FROM projects WHERE id = $1::int');\n"})})]}),"\n",(0,r.jsx)(n.h3,{id:"\ufe0f-dont-put-parameters-in-strings",children:"\u26a0\ufe0f Don't put parameters in strings"}),"\n",(0,r.jsxs)(n.p,{children:["Never put parameters in strings, ",(0,r.jsx)(n.strong,{children:"including postgres dollar-quoted strings"}),", as this can very easily lead to SQL injection attacks."]}),"\n",(0,r.jsxs)(n.p,{children:["You may be tempted to use parameters inside something like ",(0,r.jsx)(n.code,{children:"DO"})," blocks,\nand it is a common misconception that you can safely use replacements or bind parameters inside dollar-quoted strings, but that is not the case."]}),"\n",(0,r.jsxs)(n.p,{children:["For this reason, if you use the ",(0,r.jsx)(n.code,{children:"?"}),", ",(0,r.jsx)(n.code,{children:"$bind"})," or ",(0,r.jsx)(n.code,{children:":replacements"})," syntax, Sequelize will not consider these tokens as parameters if they are inside a string or an identifier."]}),"\n",(0,r.jsxs)(n.p,{children:["However, when using the ",(0,r.jsx)(n.code,{children:"sql"})," tag, Sequelize gives you full control, and you are responsible for ensuring that your query is safe."]}),"\n",(0,r.jsx)(n.p,{children:"Here is an example of a query that is vulnerable to SQL injection:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const id = '$$';\n\nsequelize.query(\n sql`\nDO $$\nDECLARE r record;\nBEGIN\n SELECT * FROM users WHERE id = ${id};\nEND\n$$;\n `,\n);\n"})}),"\n",(0,r.jsxs)(n.p,{children:["The above query looks like code, has syntax coloring that makes it look like code, but is really a regular dollar-quoted string\nthat will be interpreted as SQL by the ",(0,r.jsx)(n.code,{children:"DO"})," clause (similarly to ",(0,r.jsx)(n.code,{children:"eval"})," in JavaScript)."]}),"\n",(0,r.jsxs)(n.p,{children:["Dollar-quoted strings end as soon as ",(0,r.jsx)(n.code,{children:"$$"})," is encountered. If the user passes ",(0,r.jsx)(n.code,{children:"$$"})," as the ",(0,r.jsx)(n.code,{children:"id"})," parameter, the query will end early and will\nat best be invalid SQL, and at worst will allow the user to execute arbitrary SQL."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"DO $$\nDECLARE r record;\nBEGIN\n SELECT * FROM users WHERE id = '$$';\n -- ^ the contents of the DO clause ends here\nEND\n$$;\n"})}),"\n",(0,r.jsx)(n.h3,{id:"query-variable-mode",children:"Query Variable Mode"}),"\n",(0,r.jsxs)(n.p,{children:["While bind parameters written using the ",(0,r.jsx)(n.code,{children:"$"})," syntax, and replacements written using the ",(0,r.jsx)(n.code,{children:":"})," and ",(0,r.jsx)(n.code,{children:"?"})," syntaxes, will always be interpreted as\nbind parameters and replacements respectively, variables inserted in an ",(0,r.jsx)(n.code,{children:"sql"}),"-tagged template literal can be interpreted as bind parameters or replacements depending on the ",(0,r.jsx)(n.strong,{children:"Query Variable Mode"}),"."]}),"\n",(0,r.jsx)(n.p,{children:"It is not currently possible to configure that mode per query (this feature is planned for a future release). Instead, the mode\nis pre-determined by the method used to execute the query:"}),"\n",(0,r.jsxs)(n.ul,{children:["\n",(0,r.jsxs)(n.li,{children:[(0,r.jsx)(n.code,{children:"Model.insert"}),", ",(0,r.jsx)(n.code,{children:"Model.destroy"})," and ",(0,r.jsx)(n.code,{children:"Model.update"}),' are in "bind parameter" mode.']}),"\n",(0,r.jsx)(n.li,{children:'All other methods are in "replacement" mode.'}),"\n"]}),"\n",(0,r.jsxs)(n.p,{children:["This means that, for instance, variables used in ",(0,r.jsx)(n.code,{children:"findAll"})," will be added to the query as replacements:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const fundingStatus = 'funded';\n\nawait Project.findAll({\n where: and({ status: 'active' }, sql`funding = ${fundingStatus}`),\n});\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT * FROM projects WHERE status = 'active' AND funding = 'funded'\n"})}),"\n",(0,r.jsxs)(n.p,{children:["Whereas variables used in ",(0,r.jsx)(n.code,{children:"update"})," will be added to the query as bind parameters:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const fundingStatus = 'funded';
1\n\nawait Project.update(\n { funding: 'pending' },\n {\n where: and({ status: 'active' }, sql`funding = ${fundingStatus}`),\n },\n);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"UPDATE projects SET funding = $1 WHERE status = $2 AND funding = $3\n"})}),"\n",(0,r.jsx)(n.h3,{id:"sqlidentifier",children:(0,r.jsx)(n.code,{children:"sql.identifier"})}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql.identifier"})," function can be used to escape the name of an identifier (such as a table or column name) in a query."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { sql } from '@sequelize/core';\n\nawait sequelize.query(sql`SELECT * FROM ${sql.identifier('projects')}`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'-- The identifier quotes are dialect-specific, this is an example for PostgreSQL\nSELECT * FROM "projects"\n'})}),"\n",(0,r.jsx)(n.p,{children:"You can specify more than one identifier:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { sql } from '@sequelize/core';\n\nawait sequelize.query(sql`SELECT * FROM ${sql.identifier('public', 'users')}`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'-- The identifier quotes are dialect-specific, this is an example for PostgreSQL\nSELECT * FROM "public"."users"\n'})}),"\n",(0,r.jsx)(n.p,{children:"Using a Model class as an identifier will automatically use the table name of the Model:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { User, sql } from '@sequelize/core';\n\nclass User extends Model {}\n\nawait sequelize.query(sql`SELECT * FROM ${sql.identifier(User)}`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'-- The identifier quotes are dialect-specific, this is an example for PostgreSQL\nSELECT * FROM "users"\n'})}),"\n",(0,r.jsx)(n.h3,{id:"sqllist",children:(0,r.jsx)(n.code,{children:"sql.list"})}),"\n",(0,r.jsx)(n.p,{children:"When using an array as a variable in a query, Sequelize will by default treat it as an SQL array:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const statuses = ['active', 'pending'];\n\nawait sequelize.query(sql`SELECT * FROM projects WHERE status = ANY(${statuses})`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT * FROM projects WHERE status = ANY(ARRAY['active', 'pending'])\n"})}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql.list"})," function can be used to tell Sequelize to treat the value as an SQL list instead:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const statuses = ['active', 'pending'];\n\nawait sequelize.query(sql`SELECT * FROM projects WHERE status IN ${sql.list(statuses)}`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT * FROM projects WHERE status IN ('active', 'pending')\n"})}),"\n",(0,r.jsxs)(n.admonition,{type:"caution",children:[(0,r.jsxs)(n.p,{children:["When using ",(0,r.jsx)(n.code,{children:"sql.list"})," make sure that the array contains at least one value, otherwise ",(0,r.jsx)(n.code,{children:"()"})," will be used as the list, which is invalid SQL."]}),(0,r.jsxs)(n.p,{children:["Read more about this in ",(0,r.jsx)(n.a,{href:"https://github.com/sequelize/sequelize/issues/15142",children:"#15142"})]})]}),"\n",(0,r.jsx)(n.h3,{id:"sqljoin",children:(0,r.jsx)(n.code,{children:"sql.join"})}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql.join"})," function can be used to join multiple SQL fragments together.\nIt is designed to be the equivalent of ",(0,r.jsx)(n.a,{href:"https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/join",children:(0,r.jsx)(n.code,{children:"Array.prototype.join"})})," for SQL fragments."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const columns = [sql.identifier('name'), sql.identifier('funding')];\n\nawait sequelize.query(sql`SELECT ${sql.join(columns, ', ')} FROM projects`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'-- The identifier quotes are dialect-specific, this is an example for PostgreSQL\nSELECT "name", "funding" FROM projects\n'})}),"\n",(0,r.jsxs)(n.p,{children:["Like with the ",(0,r.jsx)(n.code,{children:"sql"})," tag, all non-sql values in the array passed to ",(0,r.jsx)(n.code,{children:"sql.join"})," will be escaped:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const values = ['active', 'pending'];\n\nawait sequelize.query(sql`SELECT * FROM projects WHERE status IN (${sql.join(values, ', ')})`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT * FROM projects WHERE status IN ('active', 'pending')\n"})}),"\n",(0,r.jsx)(n.p,{children:"The separator can also be any SQL fragment:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const values = ['active', 'pending'];\n\nawait sequelize.query(sql`SELECT * FROM projects WHERE status IN ${sql.join(values, sql`, `)}`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT * FROM projects WHERE status IN ('active', 'pending')\n"})}),"\n",(0,r.jsx)(n.h3,{id:"sqlwhere",children:(0,r.jsx)(n.code,{children:"sql.where"})}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql.where"})," function can be used to generate an SQL condition from a JavaScript object, using the same syntax as the ",(0,r.jsxs)(n.a,{href:"/docs/v7/querying/select-in-depth#applying-where-clauses",children:[(0,r.jsx)(n.code,{children:"where"})," option of the ",(0,r.jsx)(n.code,{children:"findAll"})," method"]}),"."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"const where = {\n status: 'active',\n funding: 'funded',\n};\n\nawait sequelize.query(sql`SELECT * FROM projects WHERE ${sql.where(where)}`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT * FROM projects WHERE status = 'active' AND funding = 'funded'\n"})}),"\n",(0,r.jsx)(n.p,{children:"It can also be used to generate an SQL condition where the left operand is something other than an attribute name:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"Post.findAll({\n where: sql.where(new Date('2012-01-01'), Op.between, [\n sql.attribute('createdAt'),\n sql.attribute('publishedAt'),\n ]),\n});\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'-- The left operand is a literal value, and the right operands are column names\n-- Something that is not possible to do with the POJO where syntax.\nSELECT * FROM "projects" WHERE \'2012-01-01\' BETWEEN "createdAt" AND "publishedAt"\n'})}),"\n",(0,r.jsx)(n.h3,{id:"sqlattribute",children:(0,r.jsx)(n.code,{children:"sql.attribute"})}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql.attribute"})," function can be used to reference the name of a Model attribute. It is similar to the ",(0,r.jsx)(n.a,{href:"#sqlidentifier",children:(0,r.jsx)(n.code,{children:"sql.identifier"})})," function,\nbut the name of the attribute will be mapped to the name of the column, whereas ",(0,r.jsx)(n.code,{children:"sql.identifier"})," escapes its value as-is:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"class User extends Model {\n @Attribute({\n type: DataTypes.STRING,\n columnName: 'first_name',\n })\n declare firstName: string;\n}\n\nUser.findAll({\n where: sql.where(\n // highlight-next-line\n sql.attribute('firstName'),\n Op.eq,\n 'John',\n ),\n});\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'SELECT * FROM "users" WHERE "first_name" = \'John\'\n'})}),"\n",(0,r.jsx)(n.admonition,{type:"caution",children:(0,r.jsxs)(n.p,{children:["Sequelize is only able to map the attribute name to the column name if it's aware of the Model.\nThis is typically the case for model methods, but is not the case for ",(0,r.jsx)(n.code,{children:"sequelize.query"}),"."]})}),"\n",(0,r.jsxs)(n.p,{children:["On top of this mapping, ",(0,r.jsx)(n.code,{children:"sql.attribute"})," also supports the entire range of the attribute syntax. This means that it's possible to:"]}),"\n",(0,r.jsx)(n.h4,{id:"use-the-association-reference-syntax",children:"Use the association reference syntax"}),"\n",(0,r.jsxs)(n.p,{children:["When ",(0,r.jsx)(n.a,{href:"/docs/v7/querying/select-in-depth#eager-loading-include",children:"eager loading associated models"}),", you can reference includes using the association reference syntax."]}),"\n",(0,r.jsxs)(n.p,{children:["The name of the association must start & end with a ",(0,r.jsx)(n.code,{children:"$"})," character, and the name of the attribute must be separated from the association name with a ",(0,r.jsx)(n.code,{children:"."})," character."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"User.findAll({\n include: [\n {\n association: User.associations.posts,\n where: sql.where(\n // highlight-next-line\n sql.attribute('$user$.name'),\n Op.eq,\n 'Zoe',\n ),\n },\n ],\n});\n"})}),"\n",(0,r.jsx)(n.h4,{id:"use-the-casting-syntax",children:"Use the Casting Syntax"}),"\n",(0,r.jsxs)(n.p,{children:["You can use the ",(0,r.jsx)(n.code,{children:"::"})," syntax to cast the attribute to a different type, just like in ",(0,r.jsx)(n.a,{href:"/docs/v7/querying/select-in-depth#casting",children:"POJO attributes"})]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"User.findAll({\n where: sql.where(\n // highlight-next-line\n sql.attribute('createdAt::text'),\n Op.like,\n '2012-%',\n ),\n});\n"})}),"\n",(0,r.jsx)(n.h4,{id:"use-the-json-extraction-syntax",children:"Use the JSON Extraction syntax"}),"\n",(0,r.jsxs)(n.p,{children:["You can use the ",(0,r.jsx)(n.a,{href:"/docs/v7/querying/json",children:"JSON extraction syntax"})," to access JSON properties, just like in POJO attributes"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"User.findAll({\n where: sql.where(\n // This will access the property `name` of the JSON column `data`\n // highlight-next-line\n sql.attribute('data.name'),\n Op.eq,\n 'John',\n ),\n});\n"})}),"\n",(0,r.jsx)(n.h3,{id:"sqlcast",children:(0,r.jsx)(n.code,{children:"sql.cast"})}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql.cast"})," function can be used to cast a value to the type of your choice:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"User.findAll({\n where: sql.where(\n // highlight-next-line\n sql.cast(sql.attribute('createdAt'), 'text'),\n Op.like,\n '2012-%',\n ),\n});\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'SELECT * FROM "users" WHERE CAST("createdAt" AS text) LIKE \'2012-%\'\n'})}),"\n",(0,r.jsx)(n.p,{children:"It's also possible to use a Sequelize DataType as the type:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"User.findAll({\n where: sql.where(\n // highlight-next-line\n sql.cast(sql.attribute('createdAt'), DataTypes.TEXT),\n Op.like,\n '2012-%',\n ),\n});\n"})}),"\n",(0,r.jsx)(n.admonition,{type:"info",children:(0,r.jsxs)(n.p,{children:["Attributes support a shorthand syntax for casting. See ",(0,r.jsxs)(n.a,{href:"#use-the-casting-syntax",children:["Casting syntax in ",(0,r.jsx)(n.code,{children:"sql.attribute"})]})," and ",(0,r.jsx)(n.a,{href:"/docs/v7/querying/select-in-depth#casting",children:"Casting Syntax in POJOs"})," for more information."]})}),"\n",(0,r.jsxs)(n.h3,{id:"sqluuidv4--sqluuidv1",children:[(0,r.jsx)(n.code,{children:"sql.uuidV4"})," & ",(0,r.jsx)(n.code,{children:"sql.uuidV1"})]}),"\n",(0,r.jsxs)(n.p,{children:["In supported dialects, using ",(0,r.jsx)(n.code,{children:"sql.uuidV4"})," and ",(0,r.jsx)(n.code,{children:"sql.uuidV1"})," will generate the dialect-specific function to generate a UUID.\nIn unsupported dialects, using these functions, except as ",(0,r.jsx)(n.a,{href:"/docs/v7/models/data-types#built-in-default-values-for-uuid",children:"the default value of an attribute"}),", will throw an error."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"sequelize.query(sql`INSERT INTO users (id) VALUES (${sql.uuidV4()})`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"-- postgres example\nINSERT INTO users (id) VALUES (gen_random_uuid())\n"})}),"\n",(0,r.jsx)(n.p,{children:"These functions are supported by the following dialects:"}),"\n",(0,r.jsx)(a.Ay,{}),"\n",(0,r.jsx)(n.h3,{id:"sqlrandom",children:(0,r.jsx)(n.code,{children:"sql.random"})}),"\n",(0,r.jsxs)(n.p,{children:[(0,r.jsx)(n.code,{children:"sql.random"})," generates a random float between 0 and 1, using the dialect-specific function to do so."]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"await sequelize.query(sql`INSERT INTO lottery (ticket) VALUES (${sql.random()})`);\n"})}),"\n",(0,r.jsxs)(n.p,{children:["The SQL generated by ",(0,r.jsx)(n.code,{children:"sql.random()"})," varies by dialect:"]}),"\n",(0,r.jsx)(l.T,{children:(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"PostgreSQL"}),(0,r.jsx)(n.th,{children:"MariaDB"}),(0,r.jsx)(n.th,{children:"MySQL"}),(0,r.jsx)(n.th,{children:"MSSQL"}),(0,r.jsx)(n.th,{children:"SQLite"}),(0,r.jsx)(n.th,{children:"Snowflake"}),(0,r.jsx)(n.th,{children:"db2"}),(0,r.jsx)(n.th,{children:"ibmi"}),(0,r.jsx)(n.th,{children:"Oracle"})]})}),(0,r.jsx)(n.tbody,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"RANDOM()"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"RAND()"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"RAND()"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"RAND()"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"((RANDOM() + 9223372036854775808.0) / 18446744073709551616.0)"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"RANDOM()"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"RAND()"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"RAND()"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"DBMS_RANDOM.VALUE()"})})]})})]})}),"\n",(0,r.jsxs)(n.admonition,{title:"About SQLite",type:"note",children:[(0,r.jsxs)(n.p,{children:["SQLite's native ",(0,r.jsx)(n.code,{children:"RANDOM()"})," function returns a random integer in the range of a 64-bit signed integer. Sequelize normalizes this to a float between 0 and 1."]}),(0,r.jsxs)(n.p,{children:["If you want to use the native ",(0,r.jsx)(n.code,{children:"RANDOM()"})," function in SQLite, you can use ",(0,r.jsx)(n.a,{href:"#writing-raw-sql",children:(0,r.jsx)(n.code,{children:"sql"})}),"."]})]}),"\n",(0,r.jsx)(n.admonition,{title:"About MSSQL",type:"note",children:(0,r.jsxs)(n.p,{children:["Using ",(0,r.jsx)(n.code,{children:"sql.random()"})," in an ",(0,r.jsx)(n.code,{children:"ORDER BY"})," clause is a common way to get random ordering,\nhowever in MSSQL, it is better to use ",(0,r.jsx)(n.code,{children:"NEWID()"})," for this purpose, as ",(0,r.jsx)(n.code,{children:"RAND()"})," is evaluated only once per query, and will not give you random ordering."]})}),"\n",(0,r.jsx)(n.h3,{id:"sqlcol",children:(0,r.jsx)(n.code,{children:"sql.col"})}),"\n",(0,r.jsx)(n.admonition,{type:"caution",children:(0,r.jsxs)(n.p,{children:["This function is available for backwards compatibility, and there are currently no plans to deprecate it,\nbut it is not recommended to use in new code. Prefer instead to use ",(0,r.jsx)(n.a,{href:"#sqlattribute",children:(0,r.jsx)(n.code,{children:"sql.attribute"})}),", ",(0,r.jsx)(n
1.a,{href:"#sqlidentifier",children:(0,r.jsx)(n.code,{children:"sql.identifier"})}),",\nand the ",(0,r.jsx)(n.code,{children:"sql"})," template tag."]})}),"\n",(0,r.jsxs)(n.p,{children:["This function is a third way to reference a column name. It's similar to ",(0,r.jsx)(n.a,{href:"#sqlidentifier",children:(0,r.jsx)(n.code,{children:"sql.identifier"})}),", but gives special meaning to the ",(0,r.jsx)(n.code,{children:"*"})," characters."]}),"\n",(0,r.jsx)(n.p,{children:"Here are a few examples:"}),"\n",(0,r.jsxs)(n.table,{children:[(0,r.jsx)(n.thead,{children:(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.th,{children:"Input"}),(0,r.jsx)(n.th,{children:(0,r.jsx)(n.code,{children:"sql.col"})}),(0,r.jsx)(n.th,{children:(0,r.jsx)(n.code,{children:"sql.identifier"})})]})}),(0,r.jsxs)(n.tbody,{children:[(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"*"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"*"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:'"*"'})})]}),(0,r.jsxs)(n.tr,{children:[(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:"users.*"})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:'"users".*'})}),(0,r.jsx)(n.td,{children:(0,r.jsx)(n.code,{children:'"users.*"'})})]})]})]}),"\n",(0,r.jsxs)(n.p,{children:["Unlike ",(0,r.jsx)(n.a,{href:"#sqlattribute",children:(0,r.jsx)(n.code,{children:"sql.attribute"})}),", this method does not support any other special syntax, and does not map its input to a column name."]}),"\n",(0,r.jsx)(n.h3,{id:"sqljsonpath",children:(0,r.jsx)(n.code,{children:"sql.jsonPath"})}),"\n",(0,r.jsx)(n.p,{children:"This function can be used to extract a JSON property from a JSON value"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"sequelize.query(sql`\n SELECT ${sql.jsonPath(sql.identifier('data'), ['addresses', 0, 'country'])} AS country\n FROM users\n`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"-- postgres\nSELECT data#>ARRAY['addresses', '0', 'country'] AS country FROM users\n-- other dialects\nSELECT JSON_EXTRACT(data, '$.addresses[0].country') AS country FROM users\n"})}),"\n",(0,r.jsx)(n.p,{children:"This can be useful to generate a JSON extraction query dynamically in a safe way."}),"\n",(0,r.jsx)(n.p,{children:"The JSON Path array accepts a mix of strings and numbers. If a string is used, it will be treated as a property name (used to access an object property).\nIf a number is used, it will be treated as an index (used to access an array element)."}),"\n",(0,r.jsxs)(n.p,{children:["Make sure to use the correct type for your use case, as using the string ",(0,r.jsx)(n.code,{children:"'0'"})," will try to access the property named ",(0,r.jsx)(n.code,{children:"'0'"})," instead of the first element of the array."]}),"\n",(0,r.jsxs)(n.p,{children:["Read more about this feature in the ",(0,r.jsx)(n.a,{href:"/docs/v7/querying/json",children:"JSON Extraction"})," chapter."]}),"\n",(0,r.jsx)(n.admonition,{type:"info",children:(0,r.jsxs)(n.p,{children:["Attributes support a shorthand syntax for JSON extraction. See ",(0,r.jsxs)(n.a,{href:"#use-the-json-extraction-syntax",children:["Casting syntax in ",(0,r.jsx)(n.code,{children:"sql.attribute"})]})," for more information."]})}),"\n",(0,r.jsx)(n.h3,{id:"sqlunquote",children:(0,r.jsx)(n.code,{children:"sql.unquote"})}),"\n",(0,r.jsxs)(n.p,{children:["The ",(0,r.jsx)(n.code,{children:"sql.unquote"})," function is used to execute the ",(0,r.jsx)(n.code,{children:"JSON_UNQUOTE"})," (or equivalent) function on a JSON value:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"sequelize.query(sql`\n SELECT ${sql.unquote(sql.jsonPath(sql.identifier('data'), ['addresses', 0, 'country']))} AS country\n FROM users\n`);\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"-- postgres (the #>> operator unquotes, unlike the #> operator)\nSELECT data#>>ARRAY['addresses', '0', 'country'] AS country FROM users\n-- other dialects\nSELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.addresses[0].country')) AS country FROM users\n"})}),"\n",(0,r.jsxs)(n.p,{children:["Read more about this feature in the ",(0,r.jsx)(n.a,{href:"/docs/v7/querying/json",children:"JSON Extraction"})," chapter."]}),"\n",(0,r.jsx)(n.h3,{id:"sqlfn",children:(0,r.jsx)(n.code,{children:"sql.fn"})}),"\n",(0,r.jsxs)(n.p,{children:["This function exists for backwards compatibility with older versions of Sequelize but is not recommended for new code, as ",(0,r.jsx)(n.code,{children:"sql"})," can be used to write\nSQL functions in a more natural way."]}),"\n",(0,r.jsxs)(n.p,{children:["For instance, the old way of writing a ",(0,r.jsx)(n.code,{children:"lower"})," function was:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"sql.fn('LOWER', sql.attribute('name'));\n"})}),"\n",(0,r.jsx)(n.p,{children:"Which can now be written as:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"sql`LOWER(${sql.attribute('name')})`;\n"})}),"\n",(0,r.jsx)(n.p,{children:"Both result in:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'LOWER("name")\n'})}),"\n",(0,r.jsx)(n.h2,{id:"sequelizequery",children:(0,r.jsx)(n.code,{children:"sequelize.query"})}),"\n",(0,r.jsxs)(n.p,{children:["As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can use the ",(0,r.jsx)(n.a,{href:"pathname:///api/v7/classes/_sequelize_core.index.Sequelize.html#query",children:(0,r.jsx)(n.code,{children:"sequelize.query"})})," method."]}),"\n",(0,r.jsx)(n.p,{children:'By default the function will return two arguments - a results array, and an object c
1ontaining metadata (such as amount of affected rows, etc). Note that since this is a raw query, the metadata are dialect specific. Some dialects return the metadata "within" the results object (as properties on an array). However, two arguments will always be returned, but for MSSQL and MySQL it will be two references to the same object.'}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"const [results, metadata] = await sequelize.query('UPDATE users SET y = 42 WHERE x = 12');\n// Results will be an empty array and metadata will contain the number of affected rows.\n"})}),"\n",(0,r.jsxs)(n.admonition,{type:"warning",children:[(0,r.jsxs)(n.p,{children:["When interpolating variables in your query, make absolutely sure that you are tagging your query with the ",(0,r.jsx)(n.code,{children:"sql"})," tag. ",(0,r.jsx)(n.code,{children:"sequelize.query"})," is\none of the few functions that will interpret plain strings as raw SQL, so forgetting to tag your query with ",(0,r.jsx)(n.code,{children:"sql"})," can lead to SQL injection vulnerabilities:"]}),(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-ts",children:"// Dangerous\nawait sequelize.query(`SELECT * FROM users WHERE first_name = ${firstName}`);\n\n// Safe\nawait sequelize.query(sql`SELECT * FROM users WHERE first_name = ${firstName}`);\n"})}),(0,r.jsxs)(n.p,{children:["All other functions that accept raw SQL will throw an error if you use a string that has not been tagged with ",(0,r.jsx)(n.code,{children:"sql"}),"."]}),(0,r.jsx)(n.p,{children:"This footgun may be removed in a future version of Sequelize."})]}),"\n",(0,r.jsx)(n.p,{children:"In cases where you don't need to access the metadata you can pass in a query type to tell sequelize how to format the results. For example, for a simple select query you could do:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { QueryTypes } from '@sequelize/core';\nconst users = await sequelize.query('SELECT * FROM `users`', {\n type: QueryTypes.SELECT,\n});\n// We didn't need to destructure the result here - the results were returned directly\n"})}),"\n",(0,r.jsxs)(n.p,{children:["Several other query types are available. ",(0,r.jsx)(n.a,{href:"https://github.com/sequelize/sequelize/blob/main/packages/core/src/query-types.ts",children:"Peek into the source for details"}),"."]}),"\n",(0,r.jsx)(n.p,{children:"A second option is the model. If you pass a model the returned data will be instances of that model."}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"// Callee is the model definition. This allows you to easily map a query to a predefined model\nconst projects = await sequelize.query('SELECT * FROM projects', {\n model: Projects,\n mapToModel: true, // pass true here if you have any mapped fields\n});\n// Each element of `projects` is now an instance of Project\n"})}),"\n",(0,r.jsxs)(n.p,{children:["See more options in the ",(0,r.jsx)(n.a,{href:"pathname:///api/v7/classes/_sequelize_core.index.Sequelize.html#query",children:"query API reference"}),". Some examples:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { QueryTypes } from '@sequelize/core';\nawait sequelize.query('SELECT 1', {\n // A function (or false) for logging your queries\n // Will get called for every SQL query that gets sent\n // to the server.\n logging: console.log,\n\n // If plain is true, then sequelize will only return the first\n // record of the result set. In case of false it will return all records.\n plain: false,\n\n // Set this to true if you don't have a model definition for your query.\n raw: false,\n\n // The type of query you are executing. The query type affects how results are formatted before they are passed back.\n type: QueryTypes.SELECT,\n});\n\n// Note the second argument being null!\n// Even if we declared a callee here, the raw: true would\n// supersede and return a raw object.\nconsole.log(await sequelize.query('SELECT * FROM projects', { raw: true }));\n"})}),"\n",(0,r.jsxs)(n.h3,{id:"dotted-attributes-and-the-nest-option",children:['"Dotted" attributes and the ',(0,r.jsx)(n.code,{children:"nest"})," option"]}),"\n",(0,r.jsxs)(n.p,{children:["If an attribute name of the table contains dots, the resulting objects can become nested objects by setting the ",(0,r.jsx)(n.code,{children:"nest: true"})," option. This is achieved with ",(0,r.jsx)(n.a,{href:"https://github.com/mickhansen/dottie.js/",children:"dottie.js"})," under the hood. See below:"]}),"\n",(0,r.jsxs)(n.ul,{children:["\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Without ",(0,r.jsx)(n.code,{children:"nest: true"}),":"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { QueryTypes } from '@sequelize/core';\nconst records = await sequelize.query('select 1 as `foo.bar.baz`', {\n type: QueryTypes.SELECT,\n});
1\nconsole.log(JSON.stringify(records[0], null, 2));\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-json",children:'{\n "foo.bar.baz": 1\n}\n'})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["With ",(0,r.jsx)(n.code,{children:"nest: true"}),":"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-js",children:"import { QueryTypes } from '@sequelize/core';\nconst records = await sequelize.query('select 1 as `foo.bar.baz`', {\n nest: true,\n type: QueryTypes.SELECT,\n});\nconsole.log(JSON.stringify(records[0], null, 2));\n"})}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-json",children:'{\n "foo": {\n "bar": {\n "baz": 1\n }\n }\n}\n'})}),"\n"]}),"\n"]}),"\n","\n",(0,r.jsxs)(n.section,{"data-footnotes":!0,className:"footnotes",children:[(0,r.jsx)(n.h2,{className:"sr-only",id:"footnote-label",children:"Footnotes"}),"\n",(0,r.jsxs)(n.ol,{children:["\n",(0,r.jsxs)(n.li,{id:"user-content-fn-1",children:["\n",(0,r.jsxs)(n.p,{children:["If you need to use raw SQL in a place that sequelize does not support, do not hesitate to open a feature request or a pull request. ",(0,r.jsx)(n.a,{href:"#user-content-fnref-1","data-footnote-backref":"","aria-label":"Back to reference 1",className:"data-footnote-backref",children:"\u21a9"})]}),"\n"]}),"\n"]}),"\n"]})]})}function x(e={}){const{wrapper:n}={...(0,i.R)(),...e.components};return n?(0,r.jsx)(n,{...e,children:(0,r.jsx)(u,{...e})}):u(e)}},34692(e,n,s){s.d(n,{T:()=>j});var t=s(96540),r=s(44684),i=s(6045);const a=new WeakMap;let l=0;function d(e=new Storage){return function(n,s){const r=(0,t.useRef)(null);null===r.current&&(r.current=l++);const d=(0,t.useRef)(Object.freeze(s)),c=function(e){if(a.has(e))return a.get(e);let n;try{n=new EventTarget}catch{n=document.createElement("div")}return a.set(e,n),n}(e),[o,h]=(0,t.useState)(()=>{const s=e.getItem(n);if(null==s)return d.current;try{return JSON.parse(s)}catch(t){return console.error("use-local-storage: invalid stored value format, resetting to default"),console.error(t),d.current}}),u=(0,t.useRef)(o);u.current=o;const x=(0,t.useCallback)(s=>{if((0,i.isFunction)(s)&&(s=s(u.current)),u.current!==s){if(void 0===s){if(u.current=d.current,h(d.current),null==e.getItem(n))return;e.removeItem(n)}else{const t=JSON.stringify(s);if(u.current=s,h(s),t===e.getItem(n))return;e.setItem(n,t)}c.dispatchEvent(new CustomEvent(`uls:storage:${n}`,{detail:{val:s,sourceHook:r.current}}))}},[c,n]);return(0,t.useEffect)(()=>{function e(e){if(e.key===n)try{if(null==e.newValue)return void x(void 0);x(JSON.parse(e.newValue))}catch{}}function s(e){e.detail.sourceHook!==r.current&&x(e.detail.val)}return c.addEventListener(`uls:storage:${n}`,s),window.addEventListener("storage",e),()=>{c.removeEventListener(`uls:storage:${n}`,s),window.removeEventListener("storage",e)}},[c,n,x]),[o,x]}}function c(e,n){return[n,()=>{throw new Error("setState is not supposed to be called server-side.")}]}const o="undefined"!=typeof window&&"undefined"!=typeof localStorage?d(localStorage):c,h=("undefined"!=typeof window&&"undefined"!=typeof localStorage&&d(sessionStorage),"dialectTableWrapper_HmXn"),u="dialectSelector_HIDu";var x=s(74848);function j(e){const n=(0,t.useRef)(null),[s,i]=o("preferred-dialect","all"),a=(0,t.useCallback)(e=>{const n=e.currentTarget.value;i(n)},[i]);return(0,t.useEffect)(()=>{const e=n.current;if(!e)return;const t=e.querySelector("table");if(!t)return void console.warn("DialectTableFilter expects to wrap a table");const i=t.children[0].children[0],a=[...i.children].map(e=>e.textContent);for(const n of i.children){const e=n.textContent;
1e&&(r.R.has(e)&&("all"!==s&&e!==s?n.classList.add("hidden"):n.classList.remove("hidden")))}const l=t.children[1];for(const n of l.children)for(let e=0;e<n.children.length;e++){const t=n.children[e],i=a[e];r.R.has(i)&&("all"!==s&&i!==s?t.classList.add("hidden"):t.classList.remove("hidden"))}},[s]),(0,x.jsxs)("div",{className:h,children:[(0,x.jsxs)("select",{onChange:a,value:s,className:u,children:[(0,x.jsx)("option",{value:"all",children:"All"}),[...r.R].map(e=>(0,x.jsx)("option",{value:e,children:e},e))]}),(0,x.jsx)("div",{ref:n,children:e.children})]})}},44684(e,n,s){s.d(n,{R:()=>t});const t=new Set(["PostgreSQL","MariaDB","MySQL","MSSQL","SQLite","Snowflake","db2","ibmi","Oracle"])}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.