a template tag for Sequelize that quotes Models' table names, attribute names, and puts other expressions into bind parameters
npm install @jcoreio/sequelize-sql-tag




a template tag for Sequelize that quotes Models' table
names, attribute names, and puts other expressions into bind parameters
Using the table and attribute names from your Sequelize Models is much more
refactor-proof in raw queries than embedding raw identifiers.
``sh`
pnpm install --save @jcoreio/sequelize-sql-tag
Requires sequelize@^4.0.0. Once v5 is released I'll check if it's still
compatible. Not making any effort to support versions < 4, but you're welcome
to make a PR.
`js
const Sequelize = require('sequelize')
const sql = require('@jcoreio/sequelize-sql-tag')
const sequelize = new Sequelize('test', 'test', 'test', { dialect: 'postgres', logging: false })
const User = sequelize.define('User', {
name: {type: Sequelize.STRING},
birthday: {type: Sequelize.STRING},
active: {type: Sequelize.BOOLEAN},
})
const lock = true
sequelize.query(...sqlSELECT ${User.rawAttributes.name} FROM ${User}
WHERE ${User.rawAttributes.birthday} = ${new Date('2346-7-11')} AND
${User.rawAttributes.active} = ${true}
${lock ? sqlFOR UPDATE : sql`}then(console.log);`
// => [ [ { name: 'Jimbob' } ], Statement { sql: 'SELECT "name" FROM "Users" WHERE "birthday" = $1 AND "active" = $2 FOR UPDATE' } ]
Sometimes custom subqueries within a Sequelize where clause can be useful.sql.escape
In this case, there is no way to use query parameters. You can use in this context to inline the escaped values rather than using
query parameters:
`js
const { Op } = Sequelize
const User = sequelize.define('User', {
name: { type: Sequelize.STRING },
})
const Organization = sequelize.define('Organization', {
name: { type: Sequelize.STRING },
})
const OrganizationMember = sequelize.define('OrganizationMember', {
userId: { type: Sequelize.INTEGER },
organizationId: { type: Sequelize.INTEGER },
})
User.belongsToMany(Organization, { through: OrganizationMember })
Organization.belongsToMany(User, { through: OrganizationMember })
async function getUsersInOrganization(organizationId, where = {}) {
return await User.findAll({
where: {
...where,
// Using a sequelize include clause to do this kind of sucks tbh
id: {
[Op.in]: Sequelize.literal(sql.escape
SELECT ${OrganizationMember.rawAttributes.userId}
FROM ${OrganizationMember}
WHERE ${OrganizationMember.rawAttributes.organizationId} = ${organizationId}
),`
},
// SELECT "userId" FROM "OrganizationMembers" WHERE "organizationId" = 2
},
})
}
sqlquery Creates arguments for sequelize.query.
#### Sequelize Model class
Will be interpolated to the model's tableName.
#### Model attribute (e.g. User.rawAttributes.id)
Will be interpolated to the column name for the attribute
#### sqlnested
Good for conditionally including a SQL clause (see examples above)
#### Sequelize.literal(...)
Text will be included as-is
#### Arrays of values tagged template literals
Will be included as-is joined by commas.
#### All other values
Will be added to bind parameters.
The sql, options arguments to pass to sequelize.query.
sql.escapequery Creates a raw SQL string with all expressions in the template escaped.
#### Sequelize Model class
Will be interpolated to the model's tableName.
#### Model attribute (e.g. User.rawAttributes.id)
Will be interpolated to the column name for the attribute
#### sqlnested
Good for conditionally including a SQL clause (see examples above)
#### Sequelize.literal(...)
Text will be included as-is
#### Arrays of values tagged template literals
Will be included as-is joined by commas.
#### All other values
Will be escaped with QueryGenerator.escape(...). If none of the expressionsModel
is a Sequelize class, attribute, Sequelize instance, or nested sqlquery containing
such, then an error will be thrown.
The raw SQL.
Returns an interface using the QueryGenerator from the given Sequelize instance.
The returned interface has the following tagged template literals:
#### escapequery
Just like sql.escape, but doesn't require any of the expressions to be a Sequelize Model class
or attribute.
#### valuessql
Used for building VALUES lists. Only works inside an array expression.
The items will be included as-is joined by commas. For example:
`js
const users = [
{ name: 'Jim', birthday: 'Jan 1 2020' },
{ name: 'Bob', birthday: 'Jan 2 1986' },
]
const { escape, values } = sql.with(sequelize)
escape
INSERT INTO ${User}
${User.rawAttributes.name}, ${User.rawAttributes.birthday}
VALUES ${users.map(({ name, birthday }) => values(${name}, ${birthday}))}INSERT INTO "Users" "name", "birthday" VALUES ('Jim', 'Jan 1 2020'), ('Bob', 'Jan 2 1986')
// returns `
#### literalsql
Like sql.escape, but wraps the escaped SQL in Sequelize.literal.
#### querysql
Returns a function that executes the query. Example:
`js
const Sequelize = require('sequelize')
const sql = require('@jcoreio/sequelize-sql-tag')
const sequelize = new Sequelize('test', 'test', 'test', {
dialect: 'postgres',
logging: false,
})
const User = sequelize.define('User', {
name: { type: Sequelize.STRING },
})
async function insertUser(user) {
const { query } = sql.with(sequelize)
await query
INSERT INTO ${User} ${User.rawAttributes.name} VALUES (${user.name});
({ transaction })``
}