Announcing our $3.4M seed round from Gradient Ventures, FundersClub, and Y Combinator 🚀 Read more →
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
- 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 %}

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 %}
Spread the word
Tweet