How to Use Variables in a Query

Use variables in your SQL queries by wrapping them in a double curly braces, like {{id}}. This feature is only available to Premium Team users.

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

If your variable type is a string, you'll need to wrap it in quotes:

select *
from users
where email = '{{email}}'

If your variable name ends with _date or _at, PopSQL will show you a datepicker where you can easily select a date.

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

Configuring a query variable

Once you have a query variable in your SQL, you'll see an input for it above your query. Click the settings icon to configure the variable: 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

Examples

String: find user by email

select *
from users
where email = '{{email}}'

Number: find user by ID

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

Date: count users signed up after a certain date

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

Boolean: find activated or non-activated users

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

Dropdown: find users that are free or paid

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

Multiselect: find multiple event names

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

Advanced

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 %}