@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.

Installation

``sh
pnpm install --save @jcoreio/sequelize-sql-tag
`

Compatibility

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.

Examples

`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.
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:

`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
},
})
}
`

API

sqlquery

Creates arguments for sequelize.query.

$3

#### 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.

$3

The sql, options arguments to pass to sequelize.query.

sql.escapequery

Creates a raw SQL string with all expressions in the template escaped.

$3

#### 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 expressions
is a Sequelize Model class, attribute, Sequelize instance, or nested sqlquery containing
such, then an error will be thrown.

$3

The raw SQL.

sql.with(sequelize)

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}))}

// returns INSERT INTO "Users" "name", "birthday" VALUES ('Jim', 'Jan 1 2020'), ('Bob', 'Jan 2 1986')
`

#### 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 })
}
``