Query Variables

PopSQL Query Variables make it convenient to add filters or logic to your SQL for use in the PopSQL app OR if you are sharing query results with a coworker and want to give them easy-to-use filters without having to edit the underlying SQL. This feature is not available on the PopSQL free plan.

Introduction to Query Variables

After creating a variable in PopSQL, you can use them in your query by placing the variable name between double curly braces, like: {{email}}.

Creating a Query Variable

Query variables can be created in two ways: from the query menu or from the editor.

Creating from the Query Menu

Click on the {} icon in the query menu. If the created variable is not present in the query, it will add the variable at the location of the cursor.

Creating from the Query Editor

You can also create from the editor by hovering over any query variable that you have written into your query. A yellow line will appear under variables that are in the query but not yet created.

Configuring a Query Variable

You can click the settings icon of a query variable to configure it. In the settings popup, you can:

• Rename your variable
• Change the type:
  • String
  • Number
  • Date
  • Boolean
  • Dropdown
  • Multiselect
• Set a default value
• Mark the variable required

When you run a query with variables, PopSQL will first replace your variables with the values you input, then run the query against your database. If variable fields are left empty, the curly brackets and variable names will be replaced by nothing and will likely cause errors with your query unless you have handled that scenario in your SQL statement (see below for examples).

Deleting a Query Variable

You can delete a query variable from its settings dropdown.

Examples

String: find user by email

String variables need to be wrapped in single quotes to work correctly since the curly brackets and variable name are replaced with the text from the variable form and no quotes are added automatically.

For string comparisons it's best practice to convert the column and the variable text into the same case so you don't run into issues matching strings just because the case of a letter doesn't match. In the example below we're using the LOWER function, which works in Redshift, Snowflake, BigQuery, and others.

select *
from users
where lower(email) = lower('{{email}}')

You can also use variables to do a partial search by using the LIKE or ILIKE operators:

select *
from users 
where email ilike '%{{email_domain}}'

In this example we recommend using ILIKE if your database supports it since it does a case-insensitive comparison. If your database doesn't support ILIKE you can still convert the email column to lowercase and only use lowercase letters when typing the email domain into the variable form.

If you want an empty variable form field to still execute the query without a filter, you can add some simple SQL logic that evaluates to TRUE when the variable returns as NULL:

select *
from users 
where (
    lower(email) = lower('{{email}}') 
    or 
    {{email}} is null
)

Number: find user by a numeric ID

The curly brackets and variable name are simply replaced by the number input into the variable form field. If you leave the form blank, the variable returns as NULL.

select *
from users
where id = {{user_id}}

If you want a blank variable selection to return the query successfully with no filter, you can add some SQL logic like this:

select *
from users
where (
    id = {{user_id}}
    or 
    {{user_id}} is null 
)

Date: count users signed up after a certain date

Dates need to be wrapped in single quotes. We also suggest converting your timestamps to dates and using >= to make your date ranges inclusive of the date chosen in the variable form.

select count(1)
from orders
where created_at::date >= '{{starts_at}}'

If your variable name ends with _date or _at, PopSQL will automatically assume the variable is a date and display a date picker in the variable form:

select *
from users
where created_at between '{{created_at}}' and '{{end_date}}' -- both of these will automatically show a date picker in the UI

Boolean: find activated users

select *
from users
where activated is {{activated}}

Dropdown: find paid users

Dropdown variables work just like String variables. The only difference is that you can provide a list of options rather than allowing for raw text input.

In this example we are using a Liquid control flow if statement to execute some logic on variables before the query runs. Liquid is the template language used to power PopSQL query variables.

select *
from users
{% if segment == 'free' %}
  where plan is null
{% elsif segment == 'paid' %}
  where plan is not null
{% endif %}

You can also use a standard text comparison like the String variable examples:

select * 
from users 
where user_segment = '{{segment}}'

Multiselect: find multiple event names

When you set up a multiselect variable, PopSQL will automatically wrap the selected values in single quotes and add commas between selected values. If only a single value is chosen, it is still wrapped in single quotes.

select *
from events
where name in ({{ event_names }})

Dynamic Query Variables

You can also use what we call dynamic query variables. These are query variables that can get their values from a database using a SQL query. For example, let's say that you have a city table that contains city IDs, and you would like to use them as your variable values. You can do so using an SQL Query query variable.

To start creating a dynamic variable, select SQL Query from the type dropdown.

You will then be able to choose the database connection and input the query that you would like to run.

Dynamic Query Variable Statement

To build a query statement that will fetch your values properly, there are three things that you must build into your query. They are:

1. You must specify the table column to use for the dropdown values. This is done by using <table_column_name> AS value in the statement.
2. You must specify the table column to use for the dropdown labels. This is done by using <table_column_name> AS label in the statement.
3. You must specify how to search your table when you filter down the options while typing. This is done by building a where clause using {{search_token}} to inject the string that you have typed.

For example, here are two statements that could be used to fetch dynamic values:

SELECT
  name AS label,
  id AS value
FROM city
WHERE name ILIKE '%{{search_token}}%';

SELECT
  name AS label,
  code AS value
FROM country
WHERE name ILIKE '%{{search_token}}%' OR code ILIKE '%{{search_token}}%';

Each example shows you how to use the AS keyword to specify the labels and values of the dropdown options and how to specify the search clause when filtering the options.

The below image shows what the city query would look like as a dynamic query variable. You can see that the name of the city is the label, the ID in parenthesis is the value, and the search token tor is used to filter the options in the dropdown.

Dynamic query variable statements are able to reference other query variables using the the Liquid template syntax (see below for more information).

For example, let's assume that we want to create a dynamic query variable that uses a SQL statement to populate the dropdown, but that we want to variably limit the amount of options that are shown in the dropdown. This could be accomplished like:

1. Create a numeric limit query variable and assign a value to it
2. Create a dynamic query variable (SQL Query) that references the above query variable, for example:

SELECT
  name AS label,
  id AS value
FROM city
LIMIT {{ limit  }};

Note: the special-case, aforementioned, {{search_token}}will take precedence over query variables that are defined. In other words, if a search_token query variable is created, its value will not be used if referenced in a dynamic query variable statement definition. This is a deliberate limitation as that variable is used specifically for the purpose of filtering the options dropdown selector.

Dynamic Query Limit

The dynamic query runs as you type in the search field. This filters down the options to easily find the value you are looking for. Each run of the query will return a maximum of 100 rows.

Dynamic Query Tooltips

You can also add a tooltip to the dropdown options to view more information if needed. You just need to add additional columns to your SELECT statement. For example,

SELECT
  name AS label,
  id AS value,
  country_code, district
FROM city
WHERE name ILIKE '%{{search_token}}%';

will add the following tooltip to the dropdown options:

Advanced

Liquid

PopSQL uses Liquid as its template language, so there's a lot of useful stuff you can do.

You can use an if and unless statements in your query:

select *
from users
{% unless skip_join %}
  inner join orders on orders.user_id = users.id
{% endunless %}
{% if email %}
  where users.email = '{{email}}'
  limit 10
{% endif %}

Keep in mind that the Liquid template will be evaluated before running the SQL query.