CF Snippets


For modern CFML, use the script syntax. For querying your database in script, use QueryExecute().

QueryExecute() supports all the same options as the cfquery tag in a concise, script-friendly syntax.

Set the Default Datasource in Application.cfc

Sometimes it's a little nicer not needing to specify the datasource in your DB queries. When you have an application which mainly queries a single database, go ahead and set the default datasource - it's simple and saves you typing.
// in Application.cfc...
// datasource configuration
this.datasources["myDB"] = {
  // datasource config here...
this.datasource = "myDB";

//  you can now omit datasource settings from queryExecute() calls and  tags.
var rows = queryExecute( "SELECT * FROM pages" );

Configure Query Caching

Use the cachedWithin or cachedAfter query options to cache a query for or after a specified timespan. This query will be cached and reused for up to 6 hours.
var sql = "SELECT * FROM users";
var queryOptions = {
  cachedWithin: createTimespan( 0, 6, 0, 0 )
var data = queryExecute( sql, params, queryOptions );

Return query as array

You may find manipulating an array object easier than manipulating a query object. Using the returntype option, you can ask queryExecute() to return an array for functional-style processing on that query data.
var usernames = queryExecute( 
  "SELECT id,name FROM users",
  { "returntype": "array" }
// Now we can use closures on the result
var newUsernames = usernames
  .filter( user => CONTAINS "Born" )
  .each( user => "Born", "Bourne" );

Pass a named SQL parameter with queryExecute()

Use parameters for all user-submitted data to protect against SQL injection.
var params = {
  username: { value: form.username, sqltype: "varchar" },
  password: { value: form.password, sqltype: "varchar" }
var data = queryExecute(
  "SELECT * FROM users WHERE username=:username AND password=:password",

Get Generated Primary Key from an INSERT statement

If you use MySQL to generate database record ids for you (e.g. a PRIMARY KEY created via an autonumber field or UUID() call), use the result option to specify a variable name which will contain the result of the query - including a generatedKey field for retrieving the primary key of the new record.
var queryOptions = {
  result: "newUser"
var data = queryExecute(
  "INSERT INTO users (username, password)
  VALUES ( 'mike.born', UUID() )",
writeOutput( "New user id: #newUser.generatedKey#" );

Select query using named datasource

If you do not have a default datasource configured, you'll need to use this variant to specify which datasource to use.
var queryOptions = { datasource: "appMain" };
var data = queryExecute(
  "SELECT * FROM users", {}, queryOptions

Get New Snippets In Your Inbox

No spam, not too many emails.