SQLX

Overview

SQLX is a powerful extension of SQL. As it is an extension, every SQL file is also a valid SQLX file!

Structure

SQLX contains the following components:

  • Config: contains information on the purpose of the script, such as view or table.

  • SQL: The SQL operation(s) to be performed. In-line Javascript or built-in functions can be injected here.

  • Javascript (and in-line Javascript): These provide all the incredible functionality of Javascript written alongside SQL!

  • Built-in functions: There are various useful built-in functions that can be used, such as ref() or self().

Config

All config properties, and the config itself, are optional. See ITableConfig in the API reference for exact options.

SQL

Anything written outside of control blocks ({}) is interpreted as SQL. Therefore to start an SQL block, just close off any prior blocks.

Javascript

Javascript can be used within SQLX via a Javascript block, which can then be injected into the SQL using in-line Javascript in order to dynamically modify the query.

For example:

js {
  const example = "foo";
}

SELECT * FROM ${example}

Javascript Blocks

Javascript blocks are defined in SQLX by writing js { }.

JavaScript blocks in SQLX can be used for defining reusable functions that can be used to generate repetitive parts of SQL code.

In-line Javascript

In-line Javascript can be used anywhere SQL is written in order to dynamically modify the query. It is injected by using ${}, for example ${console.log("foo")}.

Built-in functions

Built in functions have special functionality and can be executed either within in-line Javascript or javascript blocks.

For all built in functions, see ITableContext in the API reference. Some useful examples can be found here:

ref()

ref() enables you to easily reference another dataset in your project without having to provide the full SQL dataset name. ref() also adds the referenced dataset to the set of dependencies for the query.

Some examples can be found here.

resolve()

resolve() works similarly to ref(), but doesn't add the dataset to the dependency list for the query.

self()

self() returns the name of the current dataset. If the database, schema, or dataset name is overridden in the config{} block, self() will return the full and correct dataset name.

Here is an example of an incremental table using the self() function.

Additional Features

  • Pre-operations: defined in SQLX by writing pre_operations { }, SQL written inside will be executed before the main SQL. This can be useful for granting permissions, as can be seen in the publishing datasets guide. Actions may only include pre_operations if they create a dataset, for example with type: "table" or type: "view" or type: "incremental" in their config.

  • Post-operations: the same as pre-operations, but defined with post_operations { }, and runs after the main SQL.