Bind parameters

[jesslilly]

Sequelize is awesome, but I have an enhancement idea. Bind parameters. I am using mostly sequelize for my database queries, but I had a big nasty query that was taking too long. So I changed the code to use node-sqlite3 instead and use bind variables. Now the query is much faster. Could you implement bind variables in sequelize?

[janmeier]

Have a look at: http://sequelizejs.com/documentation#usage-raw-queries, should be what you are looking for. If not, please re-open the issue

[jesslilly]

Very good, but I was thinking that you could use bind parameters for all queries that touch the database.

For example:

Project.find(123).success(function(project) {
  // project will be an instance of Project and stores the content of the table entry
  // with id 123. if such an entry is not defined you will get null
})

Will generate SQL:

SELECT * FROM Projects WHERE ID = ?
Bind Parameter 123

This query execution plan could be reused by the RDBMS.

[jesslilly]

I can't find a button to re-open.

[mickhansen]

Why would you need bind parameters for anything other than raw queries?

[mickhansen]

Oh, are you thinking that Sequelize should use prepared statements?

[mickhansen]

Ah, @janmeier explained it to me. We probably won't be able to support this, since it's not supported by the drivers it seems :)

[mickhansen]

@jesslilly We can't really seem to find any resources indicating increased performance. And with the added complexity we won't attempt to support this.

If you can provide resources for that document performance benefits for mysql/postgres we might revisit this.

[jesslilly]

Sure. Thanks for reviewing it.

[holm]

Using prepared statements can make queries significantly faster, especially if the query is complex and returns few rows. This is because the prepared statement will be parsed and analyzed just once, which can be a big part of queries that are fast to execute and return little data.

[mickhansen]

@holm In cases where the connection is reused that is definitely true (which should be the case for most saturated pools).