Using Object.keys() and Object.values() to create INSERT binding params dynamically

[EvanGrillo]

This could be related to a bug, feature request, or misunderstanding of documentation in #bind section.

TABLE: {
    ‘one’: number,
    ‘two’: varchar,
    ‘three’: varchar,
    ‘four’: varchar,
    ‘five’: date

}

Each column in the table is nullable

Ideally, we can target columns based on payload:

let payload = {
    ‘one’: 1,
    ‘three’: ‘3’,
    ‘five’: new Date(‘2022-06-09’)

}

Use Object.keys() & Object.values() to

connection.execute(
    ‘
        INSERT INTO table_name (:fields)
        VALUES (:values)
    `,
    {
        fields: Object.keys(payload), 
        values: Object.values(payload) 
    }
)

What's the correct way to dynamically create binding params for both columns and values?

[cjbj]

You can bind data, but not the text of the SQL statement, so you will have to dynamically (and carefully - watch out for SQL Injection issues) concatenate text to create the SQL statement, and only use bind variables for data. See Binding Column and Table Names in Queries. The documentation Binding Multiple Values to a SQL WHERE IN Clause has a technique for constructing SQL statements that contain bind variables. You can combine the techniques from both sections to achieve your goal.

[EvanGrillo]

Thanks for your help. So with guards and bind handling aside, a simple implementation may look like:

connection.executeMany(
    ‘
        INSERT INTO table_name (${Object.keys(payload)})
        VALUES (${Object.keys(payload).map((i) => i = `:${i}`)})
    `,
    [Object.values(payload)]
)

[cjbj]

Thanks for sharing!