Query Lambdas

Query Lambdas are named, parameterized SQL queries stored in Rockset that can be executed from a dedicated REST endpoint. Using Query Lambdas, you can save your SQL queries as separate resources in Rockset and manage them successfully through development and production.

Why Query Lambdas?

We recommend using Query Lambdas to build applications backed by Rockset as opposed to querying with raw SQL directly from application code. See our release blog for the full story on how Query Lambdas can increase developer velocity in your application development.

Version Control SQL Queries

Each Query Lambda maintains a version history and programmatic executions of that Lambda must provide a version. Any update to a Query Lambda automatically creates a new version. Versioning allows you to build and test changes without affecting production queries. Each Query Lambda - Version pair is immutable, so once specified in application code the underlying query will never change without the application source code itself changing.

Learn more about enforcing version control with Query Lambdas here.

Manage the SQL Development Lifecycle

Working with raw SQL is dangerous and tedious enough as-is, especially when embedding it into application code. Avoid SQL injection security risks and potential schema exposure by saving your queries and turning them into REST endpoints. Once a Query Lambda is created, a REST endpoint is automatically generated which you can then query directly with your query parameters as HTTP parameters. Collaborating on SQL queries is now as easy as sharing a link, while your application code remains unchanged.

Monitor Individual Query Metrics

Without custom implementation work application-side, there is no way to understand how a particular query is performing. Now that your SQL queries are saved as abstracted resources in Rockset, however, you can monitor and track their performance as they are executed in production. Rockset automatically maintains several statistics and performance metrics for each of your Query Lambdas.

Learn more about performance monitoring and errors with Query Lambdas here.

Share Query Executions Externally

By enabling public access to a Query Lambda, you can share the ability to both execute a query and view its results publicly, including with those not in your Rockset organization. This also enables you to programmatically execute queries from your application without an API key or other authentication. Anyone with the URL to your public Query Lambda will be able to execute it at any time and view its complete JSON response in real-time, including the SQL query results, query execution metrics, and collections queried.

Creating Query Lambdas

Query Lambdas can be created and updated in the Query Lambdas tab of the Rockset Console or by using the Rockset API directly. Our official client libraries also natively support creating Query Lambdas. See library-specific documentation for relevant syntax.

Each Query Lambda is tied to a specific query text and parameter set. You can set default values for query parameters (making them optional during executions of your query Lambda), or you can make them mandatory for each execution (failing to pass along will result in an error).

For example, the following basic query can be saved as a Query Lambda:

SELECT

    :echo as echo

As part of the Query Lambda creation process you would specify parameter echo and optionally provide a default value (for this example, let’s assume a default value of "Hello, world!"). We can save this SQL / parameter pair as a Query Lambda called helloWorldLambda, and then execute it through a REST endpoint from anywhere (given an API key):

curl --request POST \
  --url https://$ROCKSET_SERVER/v1/orgs/self/ws/commons/lambdas/helloWorldLambda/versions/{version} \
  -H 'Authorization: ApiKey ...' -H 'Content-Type: application/json'
>>> [{ "echo": "Hello, world!" }]

curl --request POST \
  --url https://$ROCKSET_SERVER/v1/orgs/self/ws/commons/lambdas/helloWorldLambda/versions/{version} \
  -H 'Authorization: ApiKey ...' -H 'Content-Type: application/json'
  -d '{
    "parameters": [
      {
        "name": "echo",
        "value": "Hola, mundo!"
      }
    ]
  }'
>>> [{ "echo": "Hola, mundo!" }]

Using Query Lambdas with Workspaces

Like Collections, each Query Lambda is associated with a particular workspace (by default, they will be created in the commons workspace). You can set this workspace at the time of creation.

Note that Query Lambdas in one workspace are not limited to querying collections in that same workspace. Any Query Lambda in any workspace can query any number of collections in any number of workspaces within your Rockset organization.

Executing Query Lambdas

Executing a Query Lambda is as easy as hitting its dedicated REST endpoint, which is automatically generated on creation (and each update). Each Query Lambda will generate a REST endpoint with the following format:

https://$ROCKSET_SERVER/v1/orgs/self/ws/{workspace}/lambdas/{queryLambda}/versions/{version}

You can also find this URL by navigating to the Query Lambdas tab of the Rockset Console and then selecting your newly created Query Lambda. There, you can find both the dedicated REST endpoint as well as instructions on how to execute the Query Lambda.

Execute the Query Lambda by hitting its dedicated REST endpoint with a POST request directly, passing in any query parameters as REST parameters as necessary.

Publicly Sharing Query Lambdas

By default Query Lambdas can only be viewed by users in your Rockset organization, but you have the option to allow users to publicly share Query Lambdas by generating a unique link that executes the query using an HTTP POST endpoint.

To make a Query Lambda publicly accessible, navigate to the  Query Lambdas tab of the Rockset Console and select the appropriate Query Lambda. Click the “Make Public” button located in the upper right corner, which will allow you to enable public access. Once enabled, you will be given a unique Public Access Link and a unique Public Access Endpoint that can be shared with others outside of your Rockset organization and without an API Key. You can view these links anytime by returning to this page and clicking Manage Public Access.

Managing Public Access

By default, Query Lambdas can only be viewed and executed by authenticated users in your Rockset account. You can enable public access to the Query Lambda to generate a unique URL which allows anyone with the link to execute your Query Lambda and view its response without any authentication.

To enable public access, navigate to the Query Lambdas tab of the Rockset Console and select the Query Lambda you would like to share publicly. Click the Make Public button located in the top-right corner, and then toggle Enable Public Access in the pop-up modal.

Once enabled, you will be given two unique URLs which can be shared publicly for accessing this Query Lambda:

1. The Public Access Link contains a simple UI which allows others to execute the Query Lambda and view its formatted response inside their browser. This is most useful for human access by other team members or the public.
2. The Public Access Endpoint is an authenticated REST API endpoint which will trigger the execution of your Query Lambda and return its response via an HTTP POST request. This is most useful for programmatic access by applications.

Scheduling Query Lambdas (beta)

Note: This feature is currently in beta and Insert Into Select queries cannot be scheduled using this mechanism currently.

You can schedule your query lambdas for automatic execution and configure certain actions to be taken based on the results of the executed query lambda.

Navigate to the details page for the query lambda that you are interested in scheduling and then visit the scheduled executions tab. You will be able to see all of the scheduled executions that you have set up for any versions of this query lambda.

Click the 'Create Scheduled Execution' to create a new scheduled query lambda. First, choose the version of this query lambda that is to be executed. You will then need to specify the execution schedule. This should be provided in UNIX cron format. You will also need to provide a Rockset API key that has the necessary permissions required to execute your query lambda.

Specify the URL of your webhook and provide a value to be used for the authorization header if needed. The webhook payload must be in JSON format and will be sent to your webhook as the body of the request (this can also be left out). There are special character sequences which you can use within this payload:

• "{{QUERY_RESULTS}}" will be replaced by the results of the query execution.
• "{{QUERY_ID}}" will be replaced by the ID of the of the query that was triggered.

Note that a request will be made to the webhook only if there is at least one doc in the results of the executed query lambda.

There are certain restrictions on scheduled query lambdas:

• The minimum duration between executions is 1 minute.
• If the last query triggered by a scheduled query lambda has not completed execution by the time the next execution would be triggered, another query will not be run untiil the previous query has completed.
• There is a size limit on the size of the query results that scheduled query lambdas can handle. Currently, that size limit is 1MB. It is recommended that you write your query in such a way that it minimizes the result size.
• Each organization is limited in the number of scheduled query lambdas that they can have at any one time. Contact Rockset support if you are interested in increasing this limit.
• The request to the webhook payload will always be a POST request.
• Insert Into Select queries cannot currently be used with scheduled lambdas.