Concepts > Parameters

Parameters

Parameters in Rockset are a mechanism for securely passing variables into SQL Queries. Query Lambdas support receiving parameters at runtime and setting default values for parameters. This document explains how to use Parameters in Rockset SQL Queries and Query Lambdas.

When to Use Parameters

Use parameters to safely specify literal values in your SQL at runtime. Parameters are automatically escaped and are thus safe from SQL injection attacks. Instead of inserting values into a SQL string manually (eg. by string concatenation), you should use parameters.

Supported Types

The following data types are currently supported for parameters:

TypeExample ValueFormat
stringfoo
int0
float0
date2020-01-01YYYY-[M]M-[D]D
datetime2020-01-01 00:00:00YYYY-[M]M-[D]D[( )[H]H:[M]M:[S]S[.DDDDDD]]
time00:00:00[H]H:[M]M:[S]S[.DDDDDD]
timestamp2020-01-01T00:00:00.000000ZYYYY-[M]M-[D]D[(T)[H]H:[M]M[:[S]S[.DDDDDD]]][time zone]

When creating and querying parameters, ensure that the value of your parameter is formatted correctly using the respective format above.

Execute a Parameterized Query

From the Rockset Console

  1. Navigate to the Query Editor in the Rockset Console.

  2. Select the “Parameters” tab in the section below the query editor, and then click “Add Parameter” button. Parameters

  3. In the modal, name your parameter, select a type from the list of Supported Types, and provide a value for your parameter. Ensure that the value of your parameter is formatted correctly for the selected type. parameters modal

  4. Write a SQL query that uses your parameter. To use parameter foo in your SQL, you can insert the text :foo anywhere in the SQL query (e.g. SELECT :foo). Rockset will interpret this value as a literal of the type you specified, so you do not need to put quotes around it (with the exception of SQL keywords, which do need to be escaped).

  5. Execute your new query.

From the REST API

Both the Query Endpoint and Query Lambdas support parameters out of the box. Please refer to those docs for an example of executing a query with parameters.

Creating a Query Lambda with Default Parameters

Query Lambdas support saving default values for parameters. These default values are used when the Query Lambda is invoked at runtime, if an explicit value for that parameter is not passed. Default values are not required for parameters in Query Lambdas.

  1. Follow the steps above to create and execute a parameterized query.

  2. Click the “Create Query Lambda” button in the blue command bar above the query editor.

Create Query Lambda

  1. Name your Query Lambda and optionally set a default value for the parameter(s) you have defined. You can also select “No Default Value” if you want the lambda to fail anytime a certain parameter isn’t specified. Once you have submitted the form, you will be redirected to the Query Lambda Details page.

Default Parameters

  1. Select an API Key and copy-paste your desired code snippet from the Query Lambda Details page. These snippets contain default values for the parameters that should be replaced by the values you intend to call the Query Lambda with. Test that the Query Lambda returns the output you expected, based on the parameters specified or omitted.
Join us on Slack!
Building on Rockset? Come chat with us!