Approve new user sign-ups

Let's say we're a startup, and we have a product out in private beta. Users can sign up, and we want a tool to approve certain users. We'll build that in 5 minutes, from scratch.

1. Show users

Let's start by writing our SQL query to pull in our users:

select * from users order by id;

Your query should now look like this:

Let's hit Run (or ⌘⏎) to preview the query. Once that looks good, we can hit "Save" (or ⌘S). Next, let's drag on a Table from the right hand side. It should automatically be populated with data from the query we just wrote.

Nice! Now we have a table of our users. Let's go search them now!

2. Search users

To search users, let's drag on a TextInput from the right hand side:

Nice! Now we have to modify our SQL query to search by the value in our TextInput. Let's change our SQL query to:

select * from users where first_name ILIKE {{'%' + textinput1.value + '%'}}

We're adding the % in order to do partial matching. Without them, "mary" would match "Mary", but "mar" wouldn't. With them, "ma" matches both "Mary" and "Marilyn".

👍

JavaScript inside of {{ }}

Inside {{ }}, everything is JavaScript. So, you're able to prepend and append %s with the + operator.
We have to do it inside of JavaScript because Retool converts your SQL into a parameterized query. This is to prevent SQL injection. If you did it outside (eg. select * from users where first_name ILIKE %{{textinput1.value}}%, that would translate into (select * from users were first_name ILIKE %$1%), which is no longer valid SQL.

Now when you type different names into the TextInput, the query will automatically refresh, which'll cause your Table to refresh as well. Great - fuzzy search built in just a few minutes!

3. Approve users

To approve users, let's drag on two separate buttons - one for approving and one for rejecting. Let's give them labels, and perhaps make the reject button red. Your app should now look like this:

Okay - cool. Now we need another query, query2, to update the active flag. For query2, we want to perform an update using a REST API so let's fill in the PUT request, and set active to true.

After we approve a user, we also want to refresh the original users query, so we can see the active boolean changing in the Table. To do that, let's scroll down to the After this query runs section, and choose to refresh query1 (or whatever the name of your query is).

Finally, let's hook up our button to trigger query2 on click.

Let's select the approve Button. Let's click on +New and create a new event handler. Now select our event handler's Action as Trigger query and Query is set to query2.

Great! Let's save the query, and repeat the same for the "Reject" button.

For now, we'll need two queries, so let's just duplicate this query, but change the active to false. Then, let's head back to the button and tell it to run this new query on click:

Great! That's it! We should be able to hit the approve or reject button on a user, and it'll cause the user to become active or inactive. Well done!

4. Actually using it

This tool is now production-ready. If your coworkers want to use it themselves (without having to bug you), just send them the link to this tool! If you give them specific permissions, they won't be able to edit the tool and change the queries - they'll just be able to use the tool you've created.