Icon

SQL & JavaScript operations

Operations are callable units of work. Operations can be written in JavaScript or SQL, and they may be private (e.g. used only for development, or called by other operations within the application), scheduled for periodic execution, or deployed as endpoints. Here's a quick look at why Transposit supports SQL, when to use JavaScript instead, and basic usage examples for each.

Why use SQL?

We believe SQL is the most efficient way of exploring and transforming your application data.

SQL is designed to easily join, filter, and organize your data in the way you need it. It abstracts away the details of data representation and allows you, the developer, to express your intent, while the relational engine translates that intent, optimizes it, and executes it. In this way, SQL offers a simpler way of stating this intent than you would ever get writing your own custom code.

SQL is also a standard language familiar to many developers. Its simplicity and power means it’s the best database technology a majority of the time.

Additionally, you don’t need to be a SQL expert to use and take full advantage of Transposit— knowing how to write a select, join, and where clause is all you need in most cases. If you ever need to do something more advanced, just take a look in our SQL reference or get support from our team.

Why use JavaScript?

Our execution engine also supports JavaScript, so you can use both SQL and JavaScript for what they’re best at. When you’re looking to make complicated data manipulations that would be awkward and hard to debug with SQL, you can fall back to JavaScript. This is helpful when you need to write additional business logic or quickly transform the data resulting from one of your SQL queries.

SQL Operations

A primary use of SQL operations is querying data. Here's an example of a basic SELECT statement:

SELECT * FROM database.get_users WHERE lastName='smith' LIMIT 50

Data can be joined, as you'd expect in SQL:

SELECT * FROM database.get_users
AS A JOIN database.get_user AS B ON A.id = B.id
WHERE A.lastName='smith' LIMIT 50

Creating SQL operations from a template (choose SQL from template in the menu) starts you off with scaffolded code based on the syntax and parameters of a given data connection operation.

Learn more in the SQL operations reference.

JavaScript Operations

JavaScript operations are useful for manipulating data or encoding business logic, and can themselves run queries.

A basic example of a JavaScript operation:

function run(params) {
api.log("hello world");
return {
"mission": "complete"
};
}

To run another operation from inside a JavaScript operation, use the run method. The first argument is the operation reference; the second argument is a map of operation parameters to run with:

api.run("connection.operation", {});

To run a SQL statement inside a JavaScript operation, use the query method:

api.query("select * from connection.operation");

To print to the console, use console.log, which accepts any number of arguments:

console.log("hello world");

Learn more in the JavaScript operations reference.

Usage limits

Transposit enforces usage limits. This helps to protect our infrastructure as well as your API rate limits.

A request may consist of many operations. A request starts when a developer runs an operation in the console, a scheduled task is started or an endpoint is accessed.

Console limits

Currently Transposit does not support multiple users modifying an application in the console simultaneously. Doing so will cause unexpected behavior and lost code.

If you need to have more than one user modify an application in the console, coordinate to make sure that each person accesses the console in a sequential fashion.

Request limits

Memory

  • Each request is given a quota of 1 GB of memory allocated.
  • This is not configurable by the developer.

CPU time

  • Each request is given a quota of 1 minute of CPU time.
  • This is not configurable by the developer.

Execution timeout

  • Each request is given a quota of 1 minute of execution time.
  • This is configurable by the developer, under Code > Operations > Properties. If you configure this timeout, it only applies when this operation is the first operation to run.
  • The maximum value is 5 minutes.
  • If operations are running in parallel (for example, if you use runBulk), you'll use CPU time more quickly than execution time; if you use timing events like setTimeout, you'll likely use CPU time less quickly than execution time.

Operations

  • Each request is given a quota of 1000 operations calls.
  • Each call to a SQL or JavaScript operation counts against this limit.
  • This is not configurable by the developer, but inlining operations is a workaround.

API call limits

To make sure you don't accidentally run through your API rate limits for your data connections, Transposit limits the number of API calls.

  • By default, the limit is 500 per data connection per request.
  • This can be changed for a particular data connector by the data connector's author.
  • This can be further overridden in your application in the settings for the data connection. Navigate to Data connection > Configure, and edit the value under Max API calls.