How to use the slonik.sql.join function in slonik
To help you get started, we’ve selected a few slonik examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
gajus / slonik-utilities / src / routines / updateDistinct.js View on Github 
): Promise => {
let booleanExpression = sql.join(
Object
.entries(namedAssignmentPayload)
.map(([key, value]) => {
// $FlowFixMe
return sql`${sql.identifier([normalizeIdentifier(key)])} IS DISTINCT FROM ${value}`;
}),
sql` OR `,
);
if (booleanExpressionValues) {
booleanExpression = sql.join(
[
booleanExpression,
sql.join(
Object
.entries(booleanExpressionValues)
.map(([key, value]) => {
// $FlowFixMe
return sql`${sql.identifier([normalizeIdentifier(key)])} = ${value}`;
}),
sql` AND `,
),
],
sql` AND `,
);
}
const result = await connection.query(sql`
UPDATE ${sql.identifier([tableName])}
SET ${assignmentList(namedAssignmentPayload)}export default async (
connection: DatabaseConnectionType,
tableName: string,
namedAssignmentPayload: NamedAssignmentPayloadType,
// eslint-disable-next-line flowtype/no-weak-types
booleanExpressionValues: Object = null,
): Promise => {
let booleanExpression = sql.join(
Object
.entries(namedAssignmentPayload)
.map(([key, value]) => {
// $FlowFixMe
return sql`${sql.identifier([normalizeIdentifier(key)])} IS DISTINCT FROM ${value}`;
}),
sql` OR `,
);
if (booleanExpressionValues) {
booleanExpression = sql.join(
[
booleanExpression,
sql.join(
Object
.entries(booleanExpressionValues)// eslint-disable-next-line flowtype/no-weak-types
booleanExpressionValues: Object = null,
): Promise => {
if (booleanExpressionValues) {
const nonOverlappingNamedAssignmentBindings = pickBy(namedAssignmentPayload, (value, key) => {
return value !== booleanExpressionValues[key];
});
if (Object.keys(nonOverlappingNamedAssignmentBindings).length === 0) {
return {
rowCount: 0,
};
}
const booleanExpression = sql.join(
Object
.entries(booleanExpressionValues)
.map(([key, value]) => {
// $FlowFixMe
return sql`${sql.identifier([normalizeIdentifier(key)])} = ${value}`;
}),
sql` AND `,
);
const result = await connection.query(sql`
UPDATE ${sql.identifier([tableName])}
SET ${assignmentList(nonOverlappingNamedAssignmentBindings)}
WHERE ${booleanExpression}
`);
return {// eslint-disable-next-line flowtype/no-weak-types
booleanExpressionValues: Object = null,
): Promise => {
let booleanExpression = sql.join(
Object
.entries(namedAssignmentPayload)
.map(([key, value]) => {
// $FlowFixMe
return sql`${sql.identifier([normalizeIdentifier(key)])} IS DISTINCT FROM ${value}`;
}),
sql` OR `,
);
if (booleanExpressionValues) {
booleanExpression = sql.join(
[
booleanExpression,
sql.join(
Object
.entries(booleanExpressionValues)
.map(([key, value]) => {
// $FlowFixMe
return sql`${sql.identifier([normalizeIdentifier(key)])} = ${value}`;
}),
sql` AND `,
),
],
sql` AND `,
);
}const columnNames = Object.keys(normalizedNamedValueBindings);
const uniqueConstraintColumnNames = inputUniqueConstraintColumnNames || columnNames;
if (difference(uniqueConstraintColumnNames, columnNames).length > 0) {
throw new Error('Unique constraint column names must not contain column names not present in named value bindings.');
}
const updateColumnNames = difference(columnNames, uniqueConstraintColumnNames);
if (columnNames.length === 0) {
throw new Error('Named value bindings object must have properties.');
}
const columnIdentifiers = sql.join(
columnNames
.map((columnName) => {
return sql.identifier([columnName]);
}),
sql`, `,
);
const conflictColumnIdentifiers = sql.join(
uniqueConstraintColumnNames.map((uniqueConstraintColumnName) => {
return sql.identifier([uniqueConstraintColumnName]);
}),
sql`, `,
);
let updateClause;const updateColumnNames = difference(columnNames, uniqueConstraintColumnNames);
if (columnNames.length === 0) {
throw new Error('Named value bindings object must have properties.');
}
const columnIdentifiers = sql.join(
columnNames
.map((columnName) => {
return sql.identifier([columnName]);
}),
sql`, `,
);
const conflictColumnIdentifiers = sql.join(
uniqueConstraintColumnNames.map((uniqueConstraintColumnName) => {
return sql.identifier([uniqueConstraintColumnName]);
}),
sql`, `,
);
let updateClause;
if (updateColumnNames.length) {
updateClause = sql.join(
updateColumnNames
.map((updateColumnName) => {
return sql`${sql.identifier([updateColumnName])} = ${sql.identifier(['excluded', updateColumnName])}`;
}),
sql`, `,
);return sql.identifier([columnName]);
}),
sql`, `,
);
const conflictColumnIdentifiers = sql.join(
uniqueConstraintColumnNames.map((uniqueConstraintColumnName) => {
return sql.identifier([uniqueConstraintColumnName]);
}),
sql`, `,
);
let updateClause;
if (updateColumnNames.length) {
updateClause = sql.join(
updateColumnNames
.map((updateColumnName) => {
return sql`${sql.identifier([updateColumnName])} = ${sql.identifier(['excluded', updateColumnName])}`;
}),
sql`, `,
);
}
const targetColumnNames = uniq([
...uniqueConstraintColumnNames,
...updateColumnNames,
]);
const whereClause = sql.join(
targetColumnNames.map((targetColumnName) => {
const value = normalizedNamedValueBindings[normalizeNamedValueBindingName(targetColumnName)];if (updateColumnNames.length) {
updateClause = sql.join(
updateColumnNames
.map((updateColumnName) => {
return sql`${sql.identifier([updateColumnName])} = ${sql.identifier(['excluded', updateColumnName])}`;
}),
sql`, `,
);
}
const targetColumnNames = uniq([
...uniqueConstraintColumnNames,
...updateColumnNames,
]);
const whereClause = sql.join(
targetColumnNames.map((targetColumnName) => {
const value = normalizedNamedValueBindings[normalizeNamedValueBindingName(targetColumnName)];
if (value === null) {
return sql`${sql.identifier([targetColumnName])} IS NULL`;
}
return sql`${sql.identifier([targetColumnName])} = ${value}`;
}),
sql` AND `,
);
const selectQuery = sql`
SELECT ${sql.identifier([configuration.identifierName])}
FROM ${sql.identifier([tableName])}
WHEREif (updateClause) {
return connection.oneFirst(sql`
INSERT INTO ${sql.identifier([tableName])} (${columnIdentifiers})
VALUES (${sql.join(boundValues, sql`, `)})
ON CONFLICT (${conflictColumnIdentifiers})
DO UPDATE
SET
${updateClause}
RETURNING ${sql.identifier([configuration.identifierName])}
`);
}
maybeId = await connection.maybeOneFirst(sql`
INSERT INTO ${sql.identifier([tableName])} (${columnIdentifiers})
VALUES (${sql.join(boundValues, sql`, `)})
ON CONFLICT (${conflictColumnIdentifiers})
DO NOTHING
`);
if (maybeId) {
return maybeId;
}
return connection.oneFirst(selectQuery);
};slonik
A Node.js PostgreSQL client with strict types, detailed logging and assertions.
