Support prepared statements

[orangemug]

A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied. - https://www.postgresql.org/docs/9.2/static/sql-prepare.html

Other implementations

• SQLite: https://www.sqlite.org/c3ref/stmt.html
• PostgreSQL: http://dev.mysql.com/doc/refman/5.7/en/prepare.html
• MySQL: http://dev.mysql.com/doc/refman/5.7/en/prepare.html
• Informix: https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_0927.htm

Basically in short anything that only uses ? can be prepared ahead of time.

What this means for SQL stamp is that

• {=key, optionalDefault} stays as is
• {!key, optionalDefault} changed in the prepare step and will now error if altered in the run step
• {?key, replaceTruthy, replaceFalsey} should be replaced with a ? rather than raw true output
• {>path, optionalDataKeys*} stays as is

So the API would look something like

var preparedStatment = sqlStamp.prepare(__dirname+"../lib/sql/foo.sql");

// Insert this into the database
var sql = preparedStatment.sql();

// Args to run prepared statement
preparedStatment.getArgs({paramValue: "paramValue"});

We would aim to keep backwards compatibility with the current API for the next few versions

[orangemug]

We should provide working examples of these for

• PostgreSQL
• Sqlite
• MySQL (later)

mapbox/node-sqlite3 seems to have good support for this, so sounds like a good place to start

The adding of examples should be done in issue #18

[orangemug]

The big breaking change here is

{?key, replaceTruthy, replaceFalsey} should be replaced with a ? rather than raw true output

My preference would be to make a breaking change, although I also know that people are currently using this feature.

My suggested steps:

1. I suggest we first collect a set of use cases for using the raw ternary switch, to see if we can find better patterns to do the same thing using ?
2. If we manage to find adequate work arounds we make the change and put in a transitional raw operator (something like {^}) and make the breaking change to {?}
3. Wait a month or two and remove the transitional operator ({^})

@oliverbrooks / @pasupulaphani care to add some use cases to this ticket?