SQL Syntax

​

Introduction

The SQL syntax you can use depends on the type of database your query is retrieving data from.

You can check the syntax documentation for each database or repository to learn more about the specific SQL syntax you can use.

​

Additional logic

With Latitude, you can use additional logic to make your queries more dynamic and powerful.

In any query, you can use Logic Blocks, which are always enclosed in curly brackets {}. These blocks can contain any logic you need, and they can be used to modify the query, add conditions, or even create loops.

Before sending the query to the database, Latitude compiles the logic in your query, and then sends the compiled query to the database. Your database will never see the logic, only the final query. This means that Latitude logic is compatible with any supported source type.

Logic blocks can be used for:

• Variables: You can use logic to create variables and use them in your query.
• Interpolation: You can use logic to interpolate values into your query.
• Conditions and Loops: You can use logic to add conditions and create loops in your query.
• Configuration: You can use logic to configure your query.

​

Variable definition and modification

Variables can be defined and modified within the logic block. They can be used to store values and perform calculations.

This can be done with the following syntax: {variable_name = value or expression}

-- variable definition
{foo = 10}
{bar = 5}

-- variable modification
{foo = foo + bar}

Read more about variables in the Variables section.

​

Adding values to the query

Values can be added to the query just by enclosing them in curly brackets {expression}.

{user_name = 'Latitude'}
SELECT * FROM users WHERE name = {user_name}

Values do not need to be defined as variables, you can use any expression or value directly in the query.

{user_name = 'Latitude'}
SELECT *
FROM users
WHERE name = {user_name} -- variable
AND age > {18} -- value
AND created_at > {param('register_date')} -- function

By default, all values will always be safely parameterized. This means that Latitude will not directly include the value to the query, but instead it adds a reference to the value and sends the value separately to the database.

In the example above, the compiled query actually looks like this:

SELECT * FROM users WHERE name = $1 AND age > $2 AND created_at > $3

And we send the values to your database separately:

$1: 'Latitude' $2: 18 $3: '2021-01-01'

How the actual reference looks like depends on your source type, all of them are compatible with this feature. This is a security measure to prevent SQL injection and other vulnerabilities.

If you need to insert a value directly into the query bypassing this parameterization, you can use the interpolate function. Read more about it in the Interpolation section.

​

Conditions and Loops

You can use logic blocks to add conditions and loops to your query. This can be done using the {#if} and {#each} blocks. These blocks will decide whether to include or exclude a part of the query based on the condition.

SELECT * FROM users
{#if condition}
WHERE name = 'Latitude'
{#end}

To learn more about them, check the Conditions and Loops sections.

​

Configuration

Finally, some logic blocks can only be used to configure how Latitude will handle the query, but not to modify the query itself. This can be done using the {@config} block.

{@config ttl = 3600}
-- rest of the query

To learn more about how to configure your query, check the Query Configuration section.