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.
pnpm install --save @jcoreio/sequelize-sql-tagRequires 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.
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(...sql`SELECT ${User.rawAttributes.name} FROM ${User}
WHERE ${User.rawAttributes.birthday} = ${new Date('2346-7-11')} AND
${User.rawAttributes.active} = ${true}
${lock ? sql`FOR 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.
In this case, there is no way to use query parameters. You can use
sql.escape in this context to inline the escaped values rather than using
query parameters:
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
},
})
}Creates arguments for sequelize.query.
Will be interpolated to the model's tableName.
Will be interpolated to the column name for the attribute
Good for conditionally including a SQL clause (see examples above)
Text will be included as-is
Will be included as-is joined by commas.
Will be added to bind parameters.
The sql, options arguments to pass to sequelize.query.
Creates a raw SQL string with all expressions in the template escaped.
Will be interpolated to the model's tableName.
Will be interpolated to the column name for the attribute
Good for conditionally including a SQL clause (see examples above)
Text will be included as-is
Will be included as-is joined by commas.
Will be escaped with QueryGenerator.escape(...). If none of the expressions
is a Sequelize Model class, attribute, Sequelize instance, or nested sql`query` 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:
Just like sql.escape, but doesn't require any of the expressions to be a Sequelize Model class
or attribute.
Used for building VALUES lists. Only works inside an array expression.
The items will be included as-is joined by commas. For example:
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})`)}
`
// returns `INSERT INTO "Users" "name", "birthday" VALUES ('Jim', 'Jan 1 2020'), ('Bob', 'Jan 2 1986')`Like sql.escape, but wraps the escaped SQL in Sequelize.literal.
Returns a function that executes the query. Example:
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 })
}