Raw query with named substitution parameters gets quoted parameters

[czardoz]

I'm using sequelize to perform a simple raw query:

    var queryStr = 'SELECT * FROM :tableName WHERE :deletionColumn > 0 LIMIT :limit;';

    return self.connection.query(queryStr, {
        replacements: {
            tableName: self.name,
            deletionColumn: self.options.deletionColumn,
            limit: self.options.limit
        }
    }).then(function (rowsToDelete) {
        _.each(rowsToDelete, function (row) {
            log.debug('Preparing to delete row: ', row);
        });
    });

The resulting query however, is (from the sequelize logs):

Executing (default): SELECT * FROM 'test' WHERE 'deleted' > 0 LIMIT 421;

The generated query is invalid (table name must not be quoted).

Are there any options I can pass that'll disable the quoting?
I'm using MySQL 5.6 here.

[janmeier]

This is not possible currently

First we'd need to come up with a syntax for this - any suggestions @czardoz ?

[czardoz]

Coming up with new syntax might not be necessary.

Something like this should work too:

     replacements: {
            tableName: {value: self.name, quote: false}
        }

Edit: Apologies for the bad formatting, I'm typing on my phone

[janmeier]

Yeah, that coudl work as well :)

PR welcome

[mickhansen]

Hmm, but the value of tableName could be a JSON object that needs to be inserted as JSON.
We probably want to use $ prefixed keys.

[mickhansen]

This change would also not make a lot of room for introducing proper prepared queries later, perhaps a different identifier than : for tables/identifiers? Not sure what other libraries do for this type of stuff? Some research might be warranted.

[janmeier]

I do remember we previously had support for using ?? as unescaped with unnamed replacement - but that won't work since :: has a different meaning in postgres

[czardoz]

This is a pretty uncommon use case (I think), and I am not sure if this needs to be implemented at all. I've currently worked around this using Mustache, and it works just fine. The documentation should definitely be updated to point this out though.

[alitaheri]

guys come on, don't we already have a familiar syntax for this: sequelize.col('deleted') 😁

[janmeier]

@subjectix Quoting columns is not the issue - its not quoting everything else

[alitaheri]

From what I get, we need some way to escape from quoting. I wasn't explicitly referring to col I was referring to the pattern. we can add a feature like: sequelize.unquote('tableOrColumnOrFunction') that we can use like:

replacements: {
    tableName: sequelize.unquote('tableName')
}

The unquote function will return an instance of a function we can check with instanceof and not quote the result if it's string only sanitize it.

[janmeier]

@subjectix Actually we already have sequelize.literal which might be usefull here - we'd just need to implement support for that in the format method then

[alitaheri]

Good idea! I didn't know that 😁